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
