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');
}
