feat(uae vat 201): do not take zero and nil rated items in standard rated expense emiratewise
diff --git a/erpnext/regional/report/uae_vat_201/test_uae_vat_201.py b/erpnext/regional/report/uae_vat_201/test_uae_vat_201.py
index b0a21de..daa6976 100644
--- a/erpnext/regional/report/uae_vat_201/test_uae_vat_201.py
+++ b/erpnext/regional/report/uae_vat_201/test_uae_vat_201.py
@@ -53,7 +53,7 @@
"raw_amount": amount,
"raw_vat_amount": vat,
}
- self.assertEqual(amounts_by_emirate["Sharjah"]["raw_amount"],300)
+ self.assertEqual(amounts_by_emirate["Sharjah"]["raw_amount"],100)
self.assertEqual(amounts_by_emirate["Sharjah"]["raw_vat_amount"],5)
self.assertEqual(amounts_by_emirate["Dubai"]["raw_amount"],200)
self.assertEqual(amounts_by_emirate["Dubai"]["raw_vat_amount"],10)
diff --git a/erpnext/regional/report/uae_vat_201/uae_vat_201.py b/erpnext/regional/report/uae_vat_201/uae_vat_201.py
index c151c62..f2d5a5e 100644
--- a/erpnext/regional/report/uae_vat_201/uae_vat_201.py
+++ b/erpnext/regional/report/uae_vat_201/uae_vat_201.py
@@ -118,14 +118,23 @@
def get_total_emiratewise(filters):
"""Returns Emiratewise Amount and Taxes."""
- query_filters = get_filters(filters)
- query_filters['docstatus'] = ['=', 1]
- return frappe.db.get_all('Sales Invoice',
- filters = query_filters,
- fields = ['vat_emirate as emirate','sum(total)', 'sum(total_taxes_and_charges)'],
- group_by='vat_emirate',
- as_list=True
- )
+ conditions = get_conditions(filters)
+ try:
+ return frappe.db.sql("""
+ select
+ s.vat_emirate as emirate, sum(i.base_amount) as total, sum(s.total_taxes_and_charges)
+ from
+ `tabSales Invoice Item` i inner join `tabSales Invoice` s
+ on
+ i.parent = s.name
+ where
+ s.docstatus = 1 and i.is_exempt != 1 and i.is_zero_rated != 1
+ {where_conditions}
+ group by
+ s.vat_emirate;
+ """.format(where_conditions=conditions), filters)
+ except (IndexError, TypeError):
+ return 0
def get_emirates():
"""Returns a List of emirates in the order that they are to be displayed."""