[fix] Update monthly sales value on Company (#10417)
* [fix] Update monthly sales value on cancellation of invoice, also update modified datestamp
* Company monthly sales: patches and test cases
diff --git a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
index 9dfacbd..96d617e 100644
--- a/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/sales_invoice.py
@@ -19,6 +19,7 @@
import get_disposal_account_and_cost_center, get_gl_entries_on_asset_disposal
from erpnext.stock.doctype.batch.batch import set_batch_nos
from erpnext.stock.doctype.serial_no.serial_no import get_serial_nos, get_delivery_note_serial_no
+from erpnext.setup.doctype.company.company import update_company_current_month_sales
form_grid_templates = {
"items": "templates/form_grid/item_grid.html"
@@ -140,7 +141,7 @@
self.update_time_sheet(self.name)
- frappe.enqueue('erpnext.setup.doctype.company.company.update_company_current_month_sales', company=self.company)
+ self.update_current_month_sales()
def validate_pos_paid_amount(self):
if len(self.payments) == 0 and self.is_pos:
@@ -178,6 +179,15 @@
self.make_gl_entries_on_cancel()
frappe.db.set(self, 'status', 'Cancelled')
+ self.update_current_month_sales()
+
+ 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)
+
def update_status_updater_args(self):
if cint(self.update_stock):
self.status_updater.extend([{
diff --git a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
index 806b489..e0a453c 100644
--- a/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
+++ b/erpnext/accounts/doctype/sales_invoice/test_sales_invoice.py
@@ -1272,6 +1272,17 @@
si.insert()
return si
+ def test_company_monthly_sales(self):
+ existing_current_month_sales = frappe.db.get_value("Company", "_Test Company", "total_monthly_sales")
+
+ si = create_sales_invoice()
+ current_month_sales = frappe.db.get_value("Company", "_Test Company", "total_monthly_sales")
+ self.assertEqual(current_month_sales, existing_current_month_sales + si.base_grand_total)
+
+ si.cancel()
+ current_month_sales = frappe.db.get_value("Company", "_Test Company", "total_monthly_sales")
+ self.assertEqual(current_month_sales, existing_current_month_sales)
+
def create_sales_invoice(**args):
si = frappe.new_doc("Sales Invoice")
args = frappe._dict(args)
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 60caf0f..6b660e1 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -421,7 +421,7 @@
erpnext.patches.v8_1.add_indexes_in_transaction_doctypes
erpnext.patches.v8_3.set_restrict_to_domain_for_module_def
erpnext.patches.v8_1.update_expense_claim_status
-erpnext.patches.v8_3.update_company_total_sales
+erpnext.patches.v8_3.update_company_total_sales #2017-08-16
erpnext.patches.v8_4.make_scorecard_records
erpnext.patches.v8_1.set_delivery_date_in_so_item #2017-07-28
erpnext.patches.v8_5.fix_tax_breakup_for_non_invoice_docs
diff --git a/erpnext/setup/doctype/company/company.py b/erpnext/setup/doctype/company/company.py
index 29630ee..b945ee4 100644
--- a/erpnext/setup/doctype/company/company.py
+++ b/erpnext/setup/doctype/company/company.py
@@ -5,7 +5,7 @@
import frappe, os
from frappe import _
-from frappe.utils import cint
+from frappe.utils import cint, today, formatdate
import frappe.defaults
@@ -312,39 +312,35 @@
return " - ".join(parts)
def update_company_current_month_sales(company):
- from frappe.utils import today, formatdate
current_month_year = formatdate(today(), "MM-yyyy")
- results = frappe.db.sql(('''
+ 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
- company = "{1}"
+ 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)
+ 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.sql(('''
- update tabCompany set total_monthly_sales = %s where name=%s
- '''), (monthly_total, frappe.db.escape(company)))
+ frappe.db.set_value("Company", company, "total_monthly_sales", monthly_total)
frappe.db.commit()
-
def update_company_monthly_sales(company):
'''Cache past year monthly sales of every company based on sales invoices'''
from frappe.utils.goal import get_monthly_results
import json
- filter_str = "company = '{0}' and status != 'Draft'".format(frappe.db.escape(company))
- month_to_value_dict = get_monthly_results("Sales Invoice", "base_grand_total", "posting_date", filter_str, "sum")
+ filter_str = "company = '{0}' and status != 'Draft' and docstatus=1".format(frappe.db.escape(company))
+ month_to_value_dict = get_monthly_results("Sales Invoice", "base_grand_total",
+ "posting_date", filter_str, "sum")
- frappe.db.sql(('''
- update tabCompany set sales_monthly_history = %s where name=%s
- '''), (json.dumps(month_to_value_dict), frappe.db.escape(company)))
+ frappe.db.set_value("Company", company, "sales_monthly_history", json.dumps(month_to_value_dict))
frappe.db.commit()
def cache_companies_monthly_sales_history():
diff --git a/erpnext/setup/doctype/company/delete_company_transactions.py b/erpnext/setup/doctype/company/delete_company_transactions.py
index f1fb0cf..d975a9e 100644
--- a/erpnext/setup/doctype/company/delete_company_transactions.py
+++ b/erpnext/setup/doctype/company/delete_company_transactions.py
@@ -27,6 +27,10 @@
"Purchase Taxes and Charges Template", "POS Profile", 'BOM'):
delete_for_doctype(doctype, company_name)
+ # reset company values
+ doc.total_monthly_sales = 0
+ doc.sales_monthly_history = None
+ doc.save()
# Clear notification counts
clear_notifications()