/**
* Overrides \RestfulDataProviderDbQuery::getQuery().
*
* Join with the terms table.
*/
protected function getQuery() {
$query = parent::getQuery();
// Add a node access tag.
$query->addTag('node_access');
$query->innerJoin('users', 'user', 'node.uid = user.uid');
// Explicitly set the alias of the column, so it will match the public field
// name.
$query->addField('user', 'name', 'name');
return $query;
}
/**
* {@inheritdoc}
*/
public function getQuery()
{
$query = parent::getQuery();
// Add a query for meter_category.
$field = field_info_field('field_group_node');
$table_name = _field_sql_storage_tablename($field);
$request = $this->getRequest();
$query->leftJoin($table_name, 'gn', "message.mid = gn.entity_id AND gn.entity_type='message'");
$query->innerJoin('field_data_field_node', 'fn', "message.mid = fn.entity_id AND fn.entity_type='message'");
$query->innerJoin('node', 'node', "fn.field_node_target_id = node.nid");
// Show only publish content in active stream.
$query->condition('node.status', 1);
if (!empty($request['topics'])) {
// Join related to Articles tables to get V&V activities with user's
// topics of interest.
$query->innerJoin('field_data_c4m_vocab_topic', 'crt', "node.nid = crt.entity_id AND crt.entity_type='node'");
}
$query->addField('gn', 'field_group_node_target_id', 'group_node');
if (!empty($request['group'])) {
if (empty($request['hide_articles'])) {
$or = db_or();
$or->condition('gn.field_group_node_target_id', $request['group'], is_array($request['group']) ? 'IN' : '=');
if (!empty($request['topics'])) {
$and = db_and();
$and->isNull('gn.field_group_node_target_id');
$and->condition('node.type', 'article');
$and->condition('crt.c4m_vocab_topic_tid', $request['topics'], is_array($request['topics']) ? 'IN' : '=');
$or->condition($and);
} else {
$or->isNull('gn.field_group_node_target_id');
}
$query->condition($or);
} else {
$query->condition('gn.field_group_node_target_id', $request['group'], is_array($request['group']) ? 'IN' : '=');
}
}
$query->addTag('activity_stream_entity_field_access');
return $query;
}
/**
* {@inheritdoc}
*/
public function getQuery()
{
// Handle summary section
// Pass to the formatter a summary of all categories and their total kWh consumption.
// Prepare a sum query.
$request = $this->getRequest();
$filter = !empty($request['filter']) ? $request['filter'] : array();
$meter_account = !empty($filter['meter_account']) ? $filter['meter_account'] : NULL;
// Make sure there is 'meter_account' filter.
if (!$meter_account) {
throw new \RestfulBadRequestException('Please supply filter[meter_account].');
}
// Prepare summary data for the formatter.
$this->prepareSummary();
$query = parent::getQuery();
// Add a query for meter_category and meter_account.
$this->addQueryForCategoryAndAccount($query);
// Round the timestmap to nearest 10 min
$query->addExpression('FLOOR(timestamp / 600) * 600', 'timestamp_rounded');
// Add human readable date-time field
$query->addExpression('from_unixtime(timestamp) ', 'datetime');
// Make summary fields
$query->addExpression('SUM(sum_kwh)', 'sum_kwh');
$query->addExpression('MIN(min_power_factor)', 'min_power_factor');
$query->addExpression('AVG(avg_power)', 'avg_power');
// Set grouping.
// If filtering for 'meter IN ...', don't sum over meters - add a group by meter.
if (!empty($this->request['filter']['meter']['operator']) && $this->request['filter']['meter']['operator'] == 'IN') {
$query->groupBy('meter_nid');
}
$query->groupBy('timestamp_rounded');
$query->groupBy('rate_type');
$query->groupBy('negawatt_electricity_normalized.frequency');
return $query;
}