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