public static function getByOrganisationId($organisationId, $role = null) { $where = array('1=1'); if ($role !== null) { $where[] = PdoHelper::formatQuery('role = %s', array($role)); } $query = 'SELECT * FROM {table} WHERE id IN(SELECT `user_id` FROM `user_organisation` WHERE `organisation_id` = %s && ' . join(' && ', $where) . ')'; return self::fetchAll($query, $organisationId); }
public static function getByQuery($query, $rows = null, $page = null) { // TODO: this WILL be slow at some point return self::fetchPage('SELECT * FROM {table} WHERE `name` LIKE \'%%' . PdoHelper::escape($query) . '%%\'', $rows, $page); }
public function getQuery($includeRelations = true) { $length = ''; if ($this->getLength()) { $length = '(' . $this->getLength() . ')'; } $query = sprintf('`%s` %s%s %s ', $this->getName(), $this->getType(), $length, $this->getAttributes()); $query .= !$this->getNullable() ? 'NOT null' : 'null'; if ($this->getDefaultValue()) { $query .= PdoHelper::formatQuery(' DEFAULT %s', [$this->getDefaultValue()]); } if ($this->getComment()) { $query .= PdoHelper::formatQuery(' COMMENT %s', [$this->getComment()]); } if ($this->getIncrement()) { $query .= ' AUTO_INCREMENT'; } if ($includeRelations) { if ($this->getIndex()) { $query .= sprintf(', %s (`%s`)', $this->getIndex(), $this->getName()); } if ($this->getRelationTable() !== null && $this->getRelationColumn() !== null) { $query .= sprintf(', FOREIGN KEY(%s) REFERENCES %s(`%s`) ON UPDATE %s ON DELETE %s', $this->getName(), $this->getRelationTable(), $this->getRelationColumn(), $this->getRelationUpdateType(), $this->getRelationDeleteType()); } } return $query; }
public static function getTotalBandwidth($organisationId, $startDate = null, $endDate = null, $sourceId = null, $masterOnly = null) { $where = []; if ($sourceId !== null) { $where[] = PdoHelper::formatQuery('`source_id` = %s', [$sourceId]); } else { $where[] = PdoHelper::formatQuery('`source_id` IN((SELECT `id` FROM `source` WHERE `organisation_id` = %s))', [$organisationId]); } if ($startDate !== null) { $where[] = PdoHelper::formatQuery('`created` >= FROM_UNIXTIME(%s)', [strtotime($startDate)]); } if ($endDate !== null) { $where[] = PdoHelper::formatQuery('`created` <= FROM_UNIXTIME(%s)', [strtotime($endDate)]); } if ($masterOnly !== null) { $where[] = PdoHelper::formatQuery('`master` = %s', [Boolean::parse($masterOnly)]); } return self::scalar('SELECT SUM(`filesize`) FROM {table} WHERE ' . join(' && ', $where) . ' ORDER BY `created` DESC'); }