def calculate_values(accounts_by_name, gl_entries_by_account, companies,
filters, fiscal_year):
start_date = (fiscal_year.year_start_date if filters.filter_based_on
== "Fiscal Year" else filters.period_start_date)
for entries in gl_entries_by_account.values():
for entry in entries:
if entry.account_number:
account_name = entry.account_number + " - " + entry.account_name
else:
account_name = entry.account_name
d = accounts_by_name.get(account_name)
if d:
debit, credit = 0, 0
for company in companies:
# check if posting date is within the period
if (entry.company == company
or (filters.get("accumulated_in_group_company"))
and entry.company in companies.get(company)):
parent_company_currency = erpnext.get_company_currency(
d.company)
child_company_currency = erpnext.get_company_currency(
entry.company)
debit, credit = flt(entry.debit), flt(entry.credit)
if (not filters.get("presentation_currency")
and entry.company != company
and parent_company_currency !=
child_company_currency and
filters.get("accumulated_in_group_company")):
debit = convert(debit, parent_company_currency,
child_company_currency,
filters.end_date)
credit = convert(credit, parent_company_currency,
child_company_currency,
filters.end_date)
d[company] = d.get(company,
0.0) + flt(debit) - flt(credit)
if entry.posting_date < getdate(start_date):
d["company_wise_opening_bal"][company] += flt(
debit) - flt(credit)
if entry.posting_date < getdate(start_date):
d["opening_balance"] = d.get(
"opening_balance", 0.0) + flt(debit) - flt(credit)
def get_opp_by_lead_source(from_date, to_date, company):
validate_filters(from_date, to_date, company)
opportunities = frappe.get_all("Opportunity", filters=[['status', 'in', ['Open', 'Quotation', 'Replied']], ['company', '=', company], ['transaction_date', 'Between', [from_date, to_date]]], fields=['currency', 'sales_stage', 'opportunity_amount', 'probability', 'source'])
if opportunities:
default_currency = frappe.get_cached_value('Global Defaults', 'None', 'default_currency')
cp_opportunities = [dict(x, **{'compound_amount': (convert(x['opportunity_amount'], x['currency'], default_currency, to_date) * x['probability']/100)}) for x in opportunities]
df = pd.DataFrame(cp_opportunities).groupby(['source', 'sales_stage'], as_index=False).agg({'compound_amount': 'sum'})
result = {}
result['labels'] = list(set(df.source.values))
result['datasets'] = []
for s in set(df.sales_stage.values):
result['datasets'].append({'name': s, 'values': [0]*len(result['labels']), 'chartType': 'bar'})
for row in df.itertuples():
source_index = result['labels'].index(row.source)
for dataset in result['datasets']:
if dataset['name'] == row.sales_stage:
dataset['values'][source_index] = row.compound_amount
return result
else:
return 'empty'
def get_opp_by_lead_source(from_date, to_date, company):
validate_filters(from_date, to_date, company)
opportunities = frappe.get_all(
"Opportunity",
filters=[
["status", "in", ["Open", "Quotation", "Replied"]],
["company", "=", company],
["transaction_date", "Between", [from_date, to_date]],
],
fields=[
"currency", "sales_stage", "opportunity_amount", "probability",
"source"
],
)
if opportunities:
default_currency = frappe.get_cached_value("Global Defaults", "None",
"default_currency")
cp_opportunities = [
dict(
x, **{
"compound_amount":
(convert(x["opportunity_amount"], x["currency"],
default_currency, to_date) * x["probability"] /
100)
}) for x in opportunities
]
df = (pd.DataFrame(cp_opportunities).groupby(["source", "sales_stage"],
as_index=False).agg({
"compound_amount":
"sum"
}))
result = {}
result["labels"] = list(set(df.source.values))
result["datasets"] = []
for s in set(df.sales_stage.values):
result["datasets"].append({
"name": s,
"values": [0] * len(result["labels"]),
"chartType": "bar"
})
for row in df.itertuples():
source_index = result["labels"].index(row.source)
for dataset in result["datasets"]:
if dataset["name"] == row.sales_stage:
dataset["values"][source_index] = row.compound_amount
return result
else:
return "empty"
def get_opp_by_lead_source(from_date, to_date, company):
validate_filters(from_date, to_date, company)
opportunities = frappe.get_all(
"Opportunity",
filters=[
["status", "in", ["Open", "Quotation", "Replied"]],
["company", "=", company],
["transaction_date", "Between", [from_date, to_date]],
],
fields=[
"currency", "sales_stage", "opportunity_amount", "probability",
"source"
],
)
if opportunities:
default_currency = frappe.get_cached_value("Global Defaults", "None",
"default_currency")
cp_opportunities = [
dict(
x, **{
"compound_amount":
(convert(x["opportunity_amount"], x["currency"],
default_currency, to_date) * x["probability"] /
100)
}) for x in opportunities
]
summary = {}
sales_stages = set()
group_key = lambda o: (o["source"], o["sales_stage"]) # noqa
for (source, sales_stage), rows in groupby(cp_opportunities,
group_key):
summary.setdefault(source,
{})[sales_stage] = sum(r["compound_amount"]
for r in rows)
sales_stages.add(sales_stage)
pivot_table = []
for sales_stage in sales_stages:
row = []
for source, sales_stage_values in summary.items():
row.append(flt(sales_stage_values.get(sales_stage)))
pivot_table.append({
"chartType": "bar",
"name": sales_stage,
"values": row
})
result = {"datasets": pivot_table, "labels": list(summary.keys())}
return result
else:
return "empty"
def get_pipeline_data(from_date, to_date, company):
validate_filters(from_date, to_date, company)
opportunities = frappe.get_all(
"Opportunity",
filters=[
["status", "in", ["Open", "Quotation", "Replied"]],
["company", "=", company],
["transaction_date", "Between", [from_date, to_date]],
],
fields=[
"currency", "sales_stage", "opportunity_amount", "probability"
],
)
if opportunities:
default_currency = frappe.get_cached_value("Global Defaults", "None",
"default_currency")
cp_opportunities = [
dict(
x, **{
"compound_amount":
(convert(x["opportunity_amount"], x["currency"],
default_currency, to_date) * x["probability"] /
100)
}) for x in opportunities
]
df = (pd.DataFrame(cp_opportunities).groupby(["sales_stage"],
as_index=True).agg({
"compound_amount":
"sum"
}).to_dict())
result = {}
result["labels"] = df["compound_amount"].keys()
result["datasets"] = []
result["datasets"].append({
"name": _("Total Amount"),
"values": df["compound_amount"].values(),
"chartType": "bar"
})
return result
else:
return "empty"
def get_pipeline_data(from_date, to_date, company):
validate_filters(from_date, to_date, company)
opportunities = frappe.get_all(
"Opportunity",
filters=[
["status", "in", ["Open", "Quotation", "Replied"]],
["company", "=", company],
["transaction_date", "Between", [from_date, to_date]],
],
fields=[
"currency", "sales_stage", "opportunity_amount", "probability"
],
)
if opportunities:
default_currency = frappe.get_cached_value("Global Defaults", "None",
"default_currency")
cp_opportunities = [
dict(
x, **{
"compound_amount":
(convert(x["opportunity_amount"], x["currency"],
default_currency, to_date) * x["probability"] /
100)
}) for x in opportunities
]
summary = {}
for sales_stage, rows in groupby(cp_opportunities,
lambda o: o["sales_stage"]):
summary[sales_stage] = sum(flt(r["compound_amount"]) for r in rows)
result = {
"labels":
list(summary.keys()),
"datasets": [{
"name": _("Total Amount"),
"values": list(summary.values()),
"chartType": "bar"
}],
}
return result
else:
return "empty"
def get_pipeline_data(from_date, to_date, company):
validate_filters(from_date, to_date, company)
opportunities = frappe.get_all(
"Opportunity",
filters=[['status', 'in', ['Open', 'Quotation', 'Replied']],
['company', '=', company],
['transaction_date', 'Between', [from_date, to_date]]],
fields=[
'currency', 'sales_stage', 'opportunity_amount', 'probability'
])
if opportunities:
default_currency = frappe.get_cached_value('Global Defaults', 'None',
'default_currency')
cp_opportunities = [
dict(
x, **{
'compound_amount':
(convert(x['opportunity_amount'], x['currency'],
default_currency, to_date) * x['probability'] /
100)
}) for x in opportunities
]
df = pd.DataFrame(cp_opportunities).groupby(['sales_stage'],
as_index=True).agg({
'compound_amount':
'sum'
}).to_dict()
result = {}
result['labels'] = df['compound_amount'].keys()
result['datasets'] = []
result['datasets'].append({
'name': _("Total Amount"),
'values': df['compound_amount'].values(),
'chartType': 'bar'
})
return result
else:
return 'empty'