public function testMSSQLEncoding()
 {
     $testing = $this->container->getParameter('testing');
     $conn = $this->getConnection($testing['db']['mssql']);
     $conn->exec("IF OBJECT_ID('dbo.encoding', 'U') IS NOT NULL\n\t\t\t    DROP TABLE dbo.encoding");
     $conn->exec("CREATE TABLE dbo.encoding (\n\t\t\tcol1 nvarchar(255),\n\t\t\tcol2 nvarchar(255)\n\t\t)");
     // @todo insert encoded data to DB
     // encode data in iso-8859-2
     $data = "ěščřžýáíéŽřťůú";
     $encodedData = mb_convert_encoding($data, 'ISO-8859-2', mb_detect_encoding($data, mb_detect_order(), true));
     $conn->exec("INSERT INTO dbo.encoding VALUES\n\t\t\t('some data', 'some other data'),\n\t\t\t('data in iso-8859-2', N'" . $data . "')\n\t\t");
     $this->createConfig();
     $testing = $this->container->getParameter('testing');
     $this->createCredentials($testing['db']['mssql']);
     $this->createQuery('test', ['name' => 'testQuery', 'query' => 'SELECT * FROM dbo.encoding', 'outputTable' => 'in.c-main.db-ex-test-encoding', 'incremental' => 0, 'primaryKey' => '']);
     $this->extract();
     $outFile = tempnam('/tmp', 'db-ex-test-encoding');
     $this->storageApi->exportTable('in.c-main.db-ex-test-encoding', $outFile);
     $expectedFile = __DIR__ . '/data/encoding.csv';
     $this->assertFileEquals($expectedFile, $outFile);
 }
    /**
     * @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;
            }
        }
    }