refactor(UAE VAT 201): replace cartesian product to inner joins
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 744eb87..ba6ff69 100644
--- a/erpnext/regional/report/uae_vat_201/uae_vat_201.py
+++ b/erpnext/regional/report/uae_vat_201/uae_vat_201.py
@@ -9,7 +9,6 @@
columns = get_columns()
data, emirates, amounts_by_emirate = get_data(filters)
chart = get_chart(emirates, amounts_by_emirate)
-
return columns, data, None, chart
def get_columns():
@@ -50,7 +49,6 @@
append_vat_on_expenses(data, filters)
return data, emirates, amounts_by_emirate
-
def get_chart(emirates, amounts_by_emirate):
"""Returns chart data."""
labels = []
@@ -167,16 +165,6 @@
'Fujairah'
]
-def get_conditions(filters):
- """The conditions to be used to filter data to calculate the total sale."""
- conditions = ""
- for opts in (("company", " and company=%(company)s"),
- ("from_date", " and posting_date>=%(from_date)s"),
- ("to_date", " and posting_date<=%(to_date)s")):
- if filters.get(opts[0]):
- conditions += opts[1]
- return conditions
-
def get_filters(filters):
"""The conditions to be used to filter data to calculate the total sale."""
query_filters = {}
@@ -215,16 +203,6 @@
{where_conditions} ;
""".format(where_conditions=conditions), filters)[0][0] or 0
-def get_conditions_join(filters):
- """The conditions to be used to filter data to calculate the total vat."""
- conditions = ""
- for opts in (("company", " and `tabPurchase Invoice`.company=%(company)s"),
- ("from_date", " and `tabPurchase Invoice`.posting_date>=%(from_date)s"),
- ("to_date", " and `tabPurchase Invoice`.posting_date<=%(to_date)s")):
- if filters.get(opts[0]):
- conditions += opts[1]
- return conditions
-
def get_reverse_charge_recoverable_total(filters):
"""Returns the sum of the total of each Purchase invoice made with recoverable reverse charge."""
query_filters = get_filters(filters)
@@ -254,6 +232,16 @@
{where_conditions} ;
""".format(where_conditions=conditions), filters)[0][0] or 0
+def get_conditions_join(filters):
+ """The conditions to be used to filter data to calculate the total vat."""
+ conditions = ""
+ for opts in (("company", " and `tabPurchase Invoice`.company=%(company)s"),
+ ("from_date", " and `tabPurchase Invoice`.posting_date>=%(from_date)s"),
+ ("to_date", " and `tabPurchase Invoice`.posting_date<=%(to_date)s")):
+ if filters.get(opts[0]):
+ conditions += opts[1]
+ return conditions
+
def get_standard_rated_expenses_total(filters):
"""Returns the sum of the total of each Purchase invoice made with recoverable reverse charge."""
query_filters = get_filters(filters)
@@ -307,8 +295,9 @@
conditions = get_conditions(filters)
return frappe.db.sql("""
select sum(i.base_amount) as total from
- `tabSales Invoice Item` i, `tabSales Invoice` s
- where s.docstatus = 1 and i.parent = s.name and i.is_zero_rated = 1
+ `tabSales Invoice Item` i inner join `tabSales Invoice` s
+ on i.parent = s.name
+ where s.docstatus = 1 and i.is_zero_rated = 1
{where_conditions} ;
""".format(where_conditions=conditions), filters)[0][0] or 0
@@ -317,7 +306,18 @@
conditions = get_conditions(filters)
return frappe.db.sql("""
select sum(i.base_amount) as total from
- `tabSales Invoice Item` i, `tabSales Invoice` s
- where s.docstatus = 1 and i.parent = s.name and i.is_exempt = 1
+ `tabSales Invoice Item` i inner join `tabSales Invoice` s
+ on i.parent = s.name
+ where s.docstatus = 1 and i.is_exempt = 1
{where_conditions} ;
- """.format(where_conditions=conditions), filters)[0][0] or 0
\ No newline at end of file
+ """.format(where_conditions=conditions), filters)[0][0] or 0
+
+def get_conditions(filters):
+ """The conditions to be used to filter data to calculate the total sale."""
+ conditions = ""
+ for opts in (("company", " and company=%(company)s"),
+ ("from_date", " and posting_date>=%(from_date)s"),
+ ("to_date", " and posting_date<=%(to_date)s")):
+ if filters.get(opts[0]):
+ conditions += opts[1]
+ return conditions
\ No newline at end of file