def get_factor_value_by_subject_and_condition(console_subject,
factor_name_list, filter_list,
current_user):
query = build_query_for_subject(console_subject, current_user)
if filter_list:
query = _filter(query, filter_list)
conn = get_connection()
cur = conn.cursor()
sql = query.get_sql()
cur.execute(sql)
rows = cur.fetchall()
index_list = __find_factor_index(cur.description, factor_name_list)
results = []
if index_list:
for rw in rows:
row_data = []
for index in index_list:
row_data.append(rw[index])
results.append(row_data)
return results
else:
raise KeyError("factor_name :{0} can't find in subject {1}".format(
factor_name_list, console_subject.name))
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 get_factor_value_by_subject_and_condition(console_subject, factor_name, filter_list):
query = build_query_for_subject(console_subject)
if filter_list:
query = _filter(query, filter_list)
conn = get_connection()
cur = conn.cursor()
sql = query.get_sql()
# print(sql)
cur.execute(sql)
rows = cur.fetchall()
# print(rows)
# print(cur.description)
index = __find_factor_index(cur.description, factor_name)
# print(rows)
if index is not None:
results = []
for rw in rows:
results.append(rw[index])
return results
else:
raise KeyError("factor_name :{0} can't find in subject {1}".format(factor_name, console_subject.name))
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
