We are running ErpNext where 100s of invoices are generated per minute. As per the code in sales_invoice.py update_company_current_month_sales () method is called on_submit and on_cancel of the sales invoices.
The update_company_current_month_sales() is as follows
def update_current_month_sales(self):
if frappe.flags.in_test:
update_company_current_month_sales(self.company)
else:
frappe.enqueue('erpnext.setup.doctype.company.company.update_company_current_month_sales',
company=self.company)
although the function runs as a background job, It reads all the sales invoices of the current company and then calculates the monthly sales.
def update_company_current_month_sales(company):
current_month_year = formatdate(today(), "MM-yyyy")
results = frappe.db.sql('''
select
sum(base_grand_total) as total, date_format(posting_date, '%m-%Y') as month_year
from
`tabSales Invoice`
where
date_format(posting_date, '%m-%Y')="{0}"
and docstatus = 1
and company = "{1}"
group by
month_year
'''.format(current_month_year, frappe.db.escape(company)), as_dict = True)
monthly_total = results[0]['total'] if len(results) > 0 else 0
frappe.db.set_value("Company", company, "total_monthly_sales", monthly_total)
frappe.db.commit()
We think that doing this on every invoice update would degrade the overall performance. In case there are some back dated sales invoice entries made, ErpNext by default updates all the ledgers and invoices posted after that resulting in companyās monthly sale to be calculated for each invoice getting updated.
Instead of calculating monthly sale immediately after each invoice updation, we can do the same as a scheduled job running every hour or at the end of the day. We believe this would reduce the load on the system significantly.
What does the community think?