def build_count_query_for_subject(console_subject):
dataset = console_subject.dataset
query = None
# indicator = report.indicators[0]
if dataset is not None:
query = _from(dataset.columns[0])
query = query.select(fn.Count("*"))
for join in dataset.joins:
query = _join(query, join)
if dataset.filters:
query = _filter(query, dataset.filters)
return query
def build_query_for_subject(console_subject):
dataset = console_subject.dataset
query = None
if dataset is not None:
query = _from(dataset.columns[0])
for column in dataset.columns:
query = _select(query, column)
for join in dataset.joins:
query = _join(query, join)
if dataset.filters:
query = _filter(query, dataset.filters)
return query
def build_count_query_for_subject_chart(console_subject, columns_dict, report):
dataset = console_subject.dataset
query = None
if dataset is not None:
query = _from(dataset.columns[0])
if report.indicators:
for indicator in report.indicators:
query = _indicator(query, indicator, columns_dict.get(indicator.columnId))
else:
query = query.select(fn.Count("*"))
for join in dataset.joins:
query = _join(query, join)
if dataset.filters:
query = _filter(query, dataset.filters)
return query
def build_query_for_subject_chart(chart_id, report=None):
console_subject = load_console_subject_by_report_id(chart_id)
columns_dict = column_list_convert_dict(console_subject.dataset.columns)
if report is None:
report = load_report_by_id(chart_id)
if report.chart.type == ChartType.COUNT:
q = build_count_query_for_subject_chart(console_subject, columns_dict,
report)
else:
dataset = console_subject.dataset
q = _from(dataset.columns[0])
for join in dataset.joins:
q = _join(q, join)
if dataset.filters:
q = _filter(q, dataset.filters)
for indicator in report.indicators:
q = _indicator(q, indicator, columns_dict.get(indicator.columnId))
truncation = report.chart.settings.get('truncation', None)
if truncation is not None:
truncation_type = truncation['type']
count = truncation['count']
for dimension in report.dimensions:
q = _dimension(q, dimension, columns_dict.get(dimension.columnId))
q = _groupby(q, columns_dict.get(dimension.columnId))
if truncation is not None:
if truncation_type == "top":
q = _orderby(q, columns_dict.get(dimension.columnId),
"asc")
if truncation_type == "bottom":
q = _orderby(q, columns_dict.get(dimension.columnId),
"desc")
if truncation_type == "none":
q = _orderby(q, columns_dict.get(dimension.columnId),
"none")
else:
q = _orderby(q, columns_dict.get(dimension.columnId), "none")
if truncation is not None:
q = _limit(q, count)
return q
def build_query_for_subject_chart(chart_id, report=None):
console_subject = load_console_subject_by_report_id(chart_id)
columns_dict = column_list_convert_dict(console_subject.dataset.columns)
if report is None:
report = load_report_by_id(chart_id)
if report.chart.type == ChartType.COUNT:
q = build_count_query_for_subject_chart(console_subject, columns_dict,
report)
else:
dataset = console_subject.dataset
q = _from(dataset.columns[0])
for join in dataset.joins:
q = _join(q, join)
if dataset.filters:
q = _filter(q, dataset.filters)
for indicator in report.indicators:
q = _indicator(q, indicator, columns_dict.get(indicator.columnId))
for dimension in report.dimensions:
q = _dimension(q, dimension, columns_dict.get(dimension.columnId))
q = _groupby(q, columns_dict.get(dimension.columnId))
q = _orderby(q, columns_dict.get(dimension.columnId))
return q
