protected function store()
{
$fileTable = IcingaConfigFile::$table;
$fileKey = IcingaConfigFile::$keyName;
$this->db->beginTransaction();
try {
$existingQuery = $this->db->select()->from($fileTable, 'checksum')->where('checksum IN (?)', array_map(array($this, 'dbBin'), $this->getFilesChecksums()));
$existing = $this->db->fetchCol($existingQuery);
foreach ($existing as $key => $val) {
if (is_resource($val)) {
$existing[$key] = stream_get_contents($val);
}
}
$missing = array_diff($this->getFilesChecksums(), $existing);
/** @var IcingaConfigFile $file */
foreach ($this->files as $name => $file) {
$checksum = $file->getChecksum();
if (!in_array($checksum, $missing)) {
continue;
}
$this->db->insert($fileTable, array($fileKey => $this->dbBin($checksum), 'content' => $file->getContent()));
}
$this->db->insert(self::$table, array('duration' => $this->generationTime, 'last_activity_checksum' => $this->dbBin($this->getLastActivityChecksum()), 'checksum' => $this->dbBin($this->getChecksum())));
/** @var IcingaConfigFile $file */
foreach ($this->files as $name => $file) {
$this->db->insert('director_generated_config_file', array('config_checksum' => $this->dbBin($this->getChecksum()), 'file_checksum' => $this->dbBin($file->getChecksum()), 'file_path' => $name));
}
$this->db->commit();
} catch (Exception $e) {
$this->db->rollBack();
throw $e;
var_dump($e->getMessage());
}
return $this;
}
public function loadGroupByRelationValues($fieldname, $condition, $countValues = false)
{
if ($condition) {
$condition = "WHERE " . $condition;
}
if ($countValues) {
if ($this->model->getVariantMode() == OnlineShop_Framework_IProductList::VARIANT_MODE_INCLUDE_PARENT_OBJECT) {
$query = "SELECT dest as `value`, count(DISTINCT src_virtualProductId) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
} else {
$query = "SELECT dest as `value`, count(*) as `count` FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
}
$subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
$query .= " AND src IN (" . $subquery . ") GROUP BY dest";
OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
$result = $this->db->fetchAssoc($query);
OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
return $result;
} else {
$query = "SELECT dest FROM " . $this->model->getCurrentTenantConfig()->getRelationTablename() . " a " . "WHERE fieldname = " . $this->quote($fieldname);
$subquery = "SELECT a.o_id FROM " . $this->model->getCurrentTenantConfig()->getTablename() . " a " . $this->model->getCurrentTenantConfig()->getJoins() . $condition;
$query .= " AND src IN (" . $subquery . ") GROUP BY dest";
OnlineShop_Plugin::getSQLLogger()->log("Query: " . $query, Zend_Log::INFO);
$result = $this->db->fetchCol($query);
OnlineShop_Plugin::getSQLLogger()->log("Query done.", Zend_Log::INFO);
return $result;
}
}
protected function _getSqlDropSequence(Zend_Db_Adapter_Abstract $db, $sequenceName)
{
$seqList = $db->fetchCol('SELECT sequence_name FROM ALL_SEQUENCES');
if (in_array($sequenceName, $seqList)) {
return 'DROP SEQUENCE';
}
return null;
}
/**
* remove all relations for application
*
* @param string $applicationName
*
* @return void
*/
public function removeApplication($applicationName)
{
$tableName = SQL_TABLE_PREFIX . 'relations';
$select = $this->_db->select()->from($tableName)->columns('rel_id')->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%');
$relation_ids = $this->_db->fetchCol($select);
if (is_array($relation_ids) && count($relation_ids) > 0) {
$this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids));
}
}
/**
* Test the Adapter's fetchCol() method.
*/
public function testAdapterFetchCol()
{
$id = $this->getResultSetKey('id');
$table = $this->getIdentifier(self::TABLE_NAME);
$result = $this->_db->fetchCol('SELECT * FROM ' . $this->_db->quoteIdentifier($table) . ' WHERE date_created > ? ORDER BY id', array('2006-01-01'));
$this->assertEquals(2, count($result));
// count rows
$this->assertEquals(1, $result[0]);
$this->assertEquals(2, $result[1]);
}
/**
* @param $oo_id
* @param bool $createMissingChildrenRows
* @throws \Zend_Db_Adapter_Exception
*/
public function doUpdate($oo_id, $createMissingChildrenRows = false)
{
if (empty($this->fields) && empty($this->relations) && !$createMissingChildrenRows) {
return;
}
$this->idTree = [];
$fields = implode("`,`", $this->fields);
if (!empty($fields)) {
$fields = ", `" . $fields . "`";
}
$result = $this->db->fetchRow("SELECT " . $this->idField . " AS id" . $fields . " FROM " . $this->storetable . " WHERE " . $this->idField . " = ?", $oo_id);
$o = new \stdClass();
$o->id = $result['id'];
$o->values = $result;
$o->childs = $this->buildTree($result['id'], $fields);
if (!empty($this->fields)) {
foreach ($this->fields as $fieldname) {
foreach ($o->childs as $c) {
$this->getIdsToUpdateForValuefields($c, $fieldname);
}
$this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $fieldname);
}
}
if (!empty($this->relations)) {
foreach ($this->relations as $fieldname => $fields) {
foreach ($o->childs as $c) {
$this->getIdsToUpdateForRelationfields($c, $fieldname);
}
if (is_array($fields)) {
foreach ($fields as $f) {
$this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $f);
}
} else {
$this->updateQueryTable($oo_id, $this->fieldIds[$fieldname], $fieldname);
}
}
}
// check for missing entries which can occur in object bricks and localized fields
// this happens especially in the following case:
// parent object has no brick, add child to parent, add brick to parent & click save
// without this code there will not be an entry in the query table for the child object
if ($createMissingChildrenRows) {
$idsToUpdate = $this->extractObjectIdsFromTreeChildren($o->childs);
if (!empty($idsToUpdate)) {
$idsInTable = $this->db->fetchCol("SELECT " . $this->idField . " FROM " . $this->querytable . " WHERE " . $this->idField . " IN (" . implode(",", $idsToUpdate) . ")");
$diff = array_diff($idsToUpdate, $idsInTable);
// create entries for children that don't have an entry yet
$originalEntry = $this->db->fetchRow("SELECT * FROM " . $this->querytable . " WHERE " . $this->idField . " = ?", $oo_id);
foreach ($diff as $id) {
$originalEntry[$this->idField] = $id;
$this->db->insert($this->querytable, $originalEntry);
}
}
}
}
/**
* @param mixed $parentId
* @param int $parentLevel
* @return $this
*/
protected function _updateChildLevels($parentId, $parentLevel)
{
$select = $this->_conn->select()->from($this->_table, $this->_idField)->where($this->_parentField . '=?', $parentId);
$ids = $this->_conn->fetchCol($select);
if (!empty($ids)) {
$this->_conn->update($this->_table, [$this->_levelField => $parentLevel + 1], $this->_conn->quoteInto($this->_idField . ' IN (?)', $ids));
foreach ($ids as $id) {
$this->_updateChildLevels($id, $parentLevel + 1);
}
}
return $this;
}
/**
* remove all relations for application
*
* @param string $applicationName
*
* @return void
*/
public function removeApplication($applicationName)
{
$tableName = SQL_TABLE_PREFIX . 'relations';
$select = $this->_db->select()->from($tableName, array('rel_id'))->where($this->_db->quoteIdentifier('own_model') . ' LIKE ?', $applicationName . '_%')->limit(10000);
do {
$relation_ids = $this->_db->fetchCol($select);
if (is_array($relation_ids) && count($relation_ids) > 0) {
$this->_db->delete($tableName, $this->_db->quoteInto($this->_db->quoteIdentifier('rel_id') . ' IN (?)', $relation_ids));
} else {
break;
}
} while (true);
}
protected function _update($type)
{
$countSelect = $this->_read->select()->from($this->_summaryTable, 'summary_id')->where('type_id=?', $type['type_id'])->having("('" . now() . "' - INTERVAL {$type['period']} {$type['period_type']}) <= MAX(add_date)");
$summaryIds = $this->_read->fetchCol($countSelect);
$customerSelect = $this->_read->select()->from($this->_customerTable, 'visitor_id')->where("? - INTERVAL {$type['period']} {$type['period_type']} <= login_at", now())->where("logout_at IS NULL OR logout_at <= ? - INTERVAL {$type['period']} {$type['period_type']}", now());
$customers = $this->_read->fetchCol($customerSelect);
$customerCount = count($customers);
$customers = $customerCount > 0 ? $customers : 0;
$customersCondition = $this->_read->quoteInto('visitor_id NOT IN(?)', $customers);
$visitorCount = $this->_read->fetchOne("SELECT COUNT(visitor_id) FROM {$this->_visitorTable} WHERE ('" . now() . "' - INTERVAL {$type['period']} {$type['period_type']}) <= first_visit_at OR (NOW() - INTERVAL {$type['period']} {$type['period_type']}) <= last_visit_at AND {$customersCondition}");
if ($customerCount == 0 && $visitorCount == 0) {
return;
}
$data = array('type_id' => $type['type_id'], 'visitor_count' => $visitorCount, 'customer_count' => $customerCount, 'add_date' => now());
if (count($summaryIds) == 0) {
$this->_write->insert($this->_summaryTable, $data);
} else {
$conditionSql = $this->_write->quoteInto('summary_id in (?)', $summaryIds);
$this->_write->update($this->_summaryTable, $data, $conditionSql);
}
}
/**
* Returns a list of the tables in the database.
*
* @return array
*/
public function listTables()
{
$sql = "SELECT tabname " . "FROM systables ";
return $this->_adapter->fetchCol($sql);
}
/**
* Fetch the first column of all rows of the result set as an array
*
* @param DbQuery $query
*
* @return array
*/
public function fetchColumn(DbQuery $query)
{
return $this->dbAdapter->fetchCol($query->getSelectQuery());
}
/**
* Recalculates all token dates, timing and results
* and outputs text messages.
*
* Does not reflect changes to tracks or rounds.
*
* @param int $sourceId A source identifier
* @param int $userId Id of the user who takes the action (for logging)
* @return \Gems_Task_TaskRunnerBatch A batch to process the synchronization
*/
public function synchronizeSources($sourceId = null, $userId = null)
{
$batch_id = 'source_synch' . ($sourceId ? '_' . $sourceId : '');
$batch = $this->loader->getTaskRunnerBatch($batch_id);
if (!$batch->isLoaded()) {
if ($sourceId) {
$sources = array($sourceId);
} else {
$select = $this->db->select();
$select->from('gems__sources', array('gso_id_source'))->where('gso_active = 1');
$sources = $this->db->fetchCol($select);
}
foreach ($sources as $source) {
$batch->addTask('Tracker_SourceSyncSurveys', $source, $userId);
// Reset cache after basic synch
$batch->addTask('CleanCache');
// Reset cache after field synch
$batch->addTask('AddTask', 'CleanCache');
$batch->addTask('AddTask', 'Tracker\\UpdateSyncDate', $source, $userId);
}
}
return $batch;
}
/**
* Get a list of suggested tags to a request entered by the user
*
* @param Zend_Db_Adapter_Abstract $db
* @param string $partialTag //request entered by the user
* @param int $limit // count records
* @return array
*/
public static function GetTagSuggestions($db, $partialTag, $limit = 0)
{
$partialTag = trim($partialTag);
if (strlen($partialTag) == 0) {
return array();
}
// Переведем строку к нижнему регистру
$partialTag = mb_convert_case($partialTag, MB_CASE_LOWER, "UTF-8");
$select = $db->select();
$select->distinct();
$select->from(array('t' => 'blog_posts_tags'), 'lower(label)')->joinInner(array('p' => 'blog_posts'), 't.post_id = p.id', array())->where('lower(t.label) like lower(?)', $partialTag . '%')->where('p.status = ?', self::STATUS_LIVE)->order('lower(t.label)');
if ($limit > 0) {
$select->limit($limit);
}
return $db->fetchCol($select);
}
/**
* Get the tables in the database.
*
* @param string $prefix Optionally, show only tables with this prefix.
* @return array
*/
public function getTableNames()
{
$prefix = $this->getPrefix();
$sql = "SHOW TABLES " . ($prefix ? "LIKE '{$prefix}%'" : '');
return $this->_dbAdapter->fetchCol($sql);
}
/**
* Convert the submitted form-data to a filter to be used for retrieving the data to export
*
* Now only handles the organization ID and consent codes, but can be extended to
* include track info or perform some checks
*
* @param array $data
* @return array
*/
protected function _getFilter($data)
{
$filter = array();
if (isset($data['ids'])) {
$idStrings = $data['ids'];
$idArray = preg_split('/[\\s,;]+/', $idStrings, -1, PREG_SPLIT_NO_EMPTY);
if ($idArray) {
// Make sure output is OK
// $idArray = array_map(array($this->db, 'quote'), $idArray);
$filter['respondentid'] = $idArray;
}
}
if ($this->project->hasResponseDatabase()) {
$this->_getResponseDatabaseFilter($data, $filter);
}
if (isset($data['tid'])) {
$select = $this->db->select();
$select->from('gems__respondent2track', array('gr2t_id_respondent_track'))->where('gr2t_id_track = ?', $data['tid']);
if ($trackArray = $this->db->fetchCol($select)) {
$filter['resptrackid'] = $trackArray;
}
}
if (isset($data['oid'])) {
$filter['organizationid'] = $data['oid'];
} else {
//Invalid id so when nothing selected... we get nothing
// $filter['organizationid'] = '-1';
}
// Consent codes
$filter['consentcode'] = array_diff((array) $this->util->getConsentTypes(), (array) $this->util->getConsentRejected());
if (isset($data['rounds']) && !empty($data['rounds'])) {
$select = $this->loader->getTracker()->getTokenSelect(array('gto_id_token'));
// Only get positive receptioncodes
$select->andReceptionCodes(array())->onlySucces();
// Apply track filter
if (isset($data['tid']) && !empty($data['tid'])) {
$select->forWhere('gto_id_track = ?', (int) $data['tid']);
}
// Apply survey filter
if (isset($data['sid']) && !empty($data['sid'])) {
$select->forSurveyId((int) $data['sid']);
}
// Apply organization filter
if (isset($data['oid'])) {
$select->forWhere('gto_id_organization in (?)', $data['oid']);
}
// Apply round description filter
if ($data['rounds'] == self::NoRound) {
$select->forWhere('gto_round_description IS NULL OR gto_round_description = ""');
} else {
$select->forWhere('gto_round_description = ?', $data['rounds']);
}
$tokens = array();
$result = $select->getSelect()->query();
while ($row = $result->fetch(\Zend_Db::FETCH_NUM)) {
$tokens[] = $row[0];
}
if (empty($tokens)) {
// Add invalid filter
$filter['organizationid'] = -1;
}
$filter['token'] = $tokens;
}
// \Gems_Tracker::$verbose = true;
return $filter;
}
