def get(chart_name=None, from_date = None, to_date = None): chart = frappe.get_doc('Dashboard Chart', chart_name) timespan = chart.timespan timegrain = chart.time_interval filters = json.loads(chart.filters_json) account = filters.get("account") company = filters.get("company") if not to_date: to_date = nowdate() if not from_date: if timegrain in ('Monthly', 'Quarterly'): from_date = get_from_date_from_timespan(to_date, timespan) # fetch dates to plot dates = get_dates_from_timegrain(from_date, to_date, timegrain) # get all the entries for this account and its descendants gl_entries = get_gl_entries(account, get_period_ending(to_date, timegrain)) # compile balance values result = build_result(account, dates, gl_entries) return { "labels": [formatdate(r[0].strftime('%Y-%m-%d')) for r in result], "datasets": [{ "name": account, "values": [r[1] for r in result] }] }
def get(chart_name = None, chart = None, no_cache = None, from_date=None, to_date=None, refresh = None): if chart_name: chart = frappe.get_doc('Dashboard Chart', chart_name) else: chart = frappe._dict(frappe.parse_json(chart)) timespan = chart.timespan timegrain = chart.time_interval filters = frappe.parse_json(chart.filters_json) # don't include cancelled documents filters['docstatus'] = ('<', 2) if not from_date: from_date = get_from_date_from_timespan(to_date, timespan) if not to_date: to_date = datetime.datetime.now() # get conditions from filters conditions, values = frappe.db.build_conditions(filters) # query will return year, unit and aggregate value data = frappe.db.sql(''' select extract(year from {datefield}) as _year, {unit_function} as _unit, {aggregate_function}({value_field}) from `tab{doctype}` where {conditions} and {datefield} >= '{from_date}' and {datefield} <= '{to_date}' group by _year, _unit order by _year asc, _unit asc '''.format( unit_function = get_unit_function(chart.based_on, timegrain), datefield = chart.based_on, aggregate_function = get_aggregate_function(chart.chart_type), value_field = chart.value_based_on or '1', doctype = chart.document_type, conditions = conditions, from_date = from_date.strftime('%Y-%m-%d'), to_date = to_date ), values) # result given as year, unit -> convert it to end of period of that unit result = convert_to_dates(data, timegrain) # add missing data points for periods where there was no result result = add_missing_values(result, timegrain, from_date, to_date) return { "labels": [formatdate(r[0].strftime('%Y-%m-%d')) for r in result], "datasets": [{ "name": chart.name, "values": [r[1] for r in result] }] }
def get(chart_name=None, chart=None, no_cache=None, filters=None, from_date=None, to_date=None, timespan=None, time_interval=None): if chart_name: chart = frappe.get_doc('Dashboard Chart', chart_name) else: chart = frappe._dict(frappe.parse_json(chart)) timespan = chart.timespan if chart.timespan == 'Select Date Range': from_date = chart.from_date to_date = chart.to_date timegrain = chart.time_interval filters = frappe.parse_json(filters) or frappe.parse_json( chart.filters_json) account = filters.get("account") company = filters.get("company") if not account and chart_name: frappe.throw( _("Account is not set for the dashboard chart {0}").format( get_link_to_form("Dashboard Chart", chart_name))) if not frappe.db.exists("Account", account) and chart_name: frappe.throw( _("Account {0} does not exists in the dashboard chart {1}").format( account, get_link_to_form("Dashboard Chart", chart_name))) if not to_date: to_date = nowdate() if not from_date: if timegrain in ('Monthly', 'Quarterly'): from_date = get_from_date_from_timespan(to_date, timespan) # fetch dates to plot dates = get_dates_from_timegrain(from_date, to_date, timegrain) # get all the entries for this account and its descendants gl_entries = get_gl_entries(account, get_period_ending(to_date, timegrain)) # compile balance values result = build_result(account, dates, gl_entries) return { "labels": [formatdate(r[0].strftime('%Y-%m-%d')) for r in result], "datasets": [{ "name": account, "values": [r[1] for r in result] }] }
def get(chart_name=None, chart=None, no_cache=None, from_date=None, to_date=None): if chart_name: chart = frappe.get_doc('Dashboard Chart', chart_name) else: chart = frappe._dict(frappe.parse_json(chart)) timespan = chart.timespan timegrain = chart.time_interval filters = frappe.parse_json(chart.filters_json) account = filters.get("account") company = filters.get("company") if not to_date: to_date = nowdate() if not from_date: if timegrain in ('Daily', 'Weekly', 'Monthly', 'Quarterly'): from_date = get_from_date_from_timespan(to_date, timespan) # fetch dates to plot dates = get_dates_from_timegrain(from_date, to_date, timegrain) # Get balances on for set dates receivable = [] for date in dates: sql_query_str = '''SELECT sum(debit)-sum(credit) FROM `tabGL Entry` WHERE company="{0}" AND party_type="Customer" AND posting_date<="{1}"'''.format( company, date) sql_query = frappe.db.sql(sql_query_str) if sql_query[0][0] == None: receivable.append(0) else: receivable.append(sql_query[0][0]) payable = [] for date in dates: sql_query_str = '''SELECT (sum(debit)-sum(credit))*-1 FROM `tabGL Entry` WHERE company="{0}" AND party_type="Supplier" AND posting_date<="{1}"'''.format( company, date) sql_query = frappe.db.sql(sql_query_str) if sql_query[0][0] == None: payable.append(0) else: payable.append(sql_query[0][0]) return { "labels": [date for date in dates], "datasets": [{ "name": 'Receivable', "values": receivable }, { "name": 'Payable', "values": payable }] }
def get(card_name=None, card=None, no_cache=None, from_date=None, to_date=None, refresh=None): if card_name: card = frappe.get_doc('Dashboard Card', card_name) else: card = frappe._dict(frappe.parse_json(card)) card = frappe.parse_json(card) timespan = card.timespan filters = frappe.parse_json(card.filters_json) or {} # don't include cancelled documents filters['docstatus'] = ('<', 2) if not from_date: from_date = get_from_date_from_timespan(to_date, timespan) if not to_date: to_date = datetime.datetime.now() # get conditions from filters conditions, values = frappe.db.build_conditions(filters) # query will return year, unit and aggregate value data = frappe.db.sql(''' select {aggregate_function}({value_field}) from `tab{doctype}` where {conditions} and {datefield} >= '{from_date}' and {datefield} <= '{to_date}' '''.format(datefield=card.based_on, aggregate_function=get_aggregate_function(card.card_type), value_field=card.value_based_on or '1', doctype=card.document_type, conditions=conditions, from_date=from_date.strftime('%Y-%m-%d'), to_date=to_date), values, as_list=True) if len(data) > 0: return data[0][0] else: return None
def get_chart_config(chart, filters, timespan, timegrain, from_date, to_date): if not from_date: from_date = get_from_date_from_timespan(to_date, timespan) if not to_date: to_date = datetime.datetime.now() # get conditions from filters conditions, values = frappe.db.build_conditions(filters) # query will return year, unit and aggregate value data = frappe.db.sql( ''' select extract(year from {datefield}) as _year, {unit_function} as _unit, {aggregate_function}({value_field}) from `tab{doctype}` where {conditions} and {datefield} >= '{from_date}' and {datefield} <= '{to_date}' group by _year, _unit order by _year asc, _unit asc '''.format(unit_function=get_unit_function(chart.based_on, timegrain), datefield=chart.based_on, aggregate_function=get_aggregate_function(chart.chart_type), value_field=chart.value_based_on or '1', doctype=chart.document_type, conditions=conditions, from_date=from_date.strftime('%Y-%m-%d'), to_date=to_date), values) # result given as year, unit -> convert it to end of period of that unit result = convert_to_dates(data, timegrain) # add missing data points for periods where there was no result result = add_missing_values(result, timegrain, timespan, from_date, to_date) chart_config = { "labels": [r[0].strftime('%Y-%m-%d') for r in result], "datasets": [{ "name": chart.name, "values": [r[1] for r in result] }] } return chart_config
def get_chart_config(chart, filters, timespan, timegrain, from_date, to_date): if not from_date: from_date = get_from_date_from_timespan(to_date, timespan) if not to_date: to_date = datetime.datetime.now() # get conditions from filters conditions, values = frappe.db.build_conditions(filters) # query will return year, unit and aggregate value data = frappe.db.sql(''' select {unit} as _unit, {aggregate_function}({value_field}) from `tab{doctype}` where {conditions} and {datefield} BETWEEN '{from_date}' and '{to_date}' group by _unit order by _unit asc '''.format( unit = chart.based_on, datefield = chart.based_on, aggregate_function = get_aggregate_function(chart.chart_type), value_field = chart.value_based_on or '1', doctype = chart.document_type, conditions = conditions, from_date = from_date.strftime('%Y-%m-%d'), to_date = to_date ), values) # add missing data points for periods where there was no result result = get_result(data, timegrain, from_date, to_date) chart_config = { "labels": [formatdate(r[0].strftime('%Y-%m-%d')) for r in result], "datasets": [{ "name": chart.name, "values": [r[1] for r in result] }] } return chart_config