feat(UAE VAT 21): Add rows for inputs and tourists
diff --git a/erpnext/regional/report/uae_vat/uae_vat.py b/erpnext/regional/report/uae_vat/uae_vat.py
index 8eeff51..b3e52be 100644
--- a/erpnext/regional/report/uae_vat/uae_vat.py
+++ b/erpnext/regional/report/uae_vat/uae_vat.py
@@ -61,6 +61,7 @@
Dict: Dictionary containing chart data
"""
data = []
+ data.append({"legend": f'VAT on Sales and All Other Outputs',})
total_emiratewise = get_total_emiratewise(filters)
emirates = get_emirates()
amounts_by_emirate = {}
@@ -88,6 +89,16 @@
"vat_amount": 0
}
)
+
+ data.append(
+ {
+ "no": '2',
+ "legend": f'Tax Refunds provided to Tourists under the Tax Refunds for Tourists Scheme',
+ "amount": (-1) * get_tourist_tax_return_total(filters),
+ "vat_amount": (-1) * get_tourist_tax_return_tax(filters)
+ }
+ )
+
data.append(
{
"no": '3',
@@ -96,6 +107,25 @@
"vat_amount": get_reverse_charge_tax(filters)
}
)
+
+ data.append({"legend": f'VAT on Expenses and All Other Inputs'})
+ data.append(
+ {
+ "no": '9',
+ "legend": f'Standard Rated Expenses',
+ "amount": get_standard_rated_expenses_total(filters),
+ "vat_amount": get_standard_rated_expenses_tax(filters)
+ }
+ )
+ data.append(
+ {
+ "no": '10',
+ "legend": f'Supplies subject to the reverse charge provision',
+ "amount": get_reverse_charge_recoverable_total(filters),
+ "vat_amount": get_reverse_charge_recoverable_tax(filters)
+ }
+ )
+
return data, chart
@@ -235,4 +265,140 @@
("to_date", f' and `tabPurchase Invoice`.posting_date<="{filters.get("to_date")}"')):
if filters.get(opts[0]):
conditions += opts[1]
- return conditions
\ No newline at end of file
+ return conditions
+
+
+def get_reverse_charge_recoverable_total(filters):
+ """Returns the sum of the total of each Purchase invoice made with claimable reverse charge
+
+ Args:
+ filters (Dict, optional): Dictionary consisting of the filters selected by the user. Defaults to None.
+
+ Returns:
+ Float: sum of the total of each Purchase invoice made with claimable reverse charge
+ """
+ conditions = """
+ for opts in (("company", f' and company="{filters.get("company")}"'),
+ ("from_date", f' and posting_date>="{filters.get("from_date")}"'),
+ ("to_date", f' and posting_date<="{filters.get("to_date")}"')):
+ if filters.get(opts[0]):
+ conditions += opts[1]
+ return conditions
+ """
+ return frappe.db.sql(f"""
+ select sum(total) from
+ `tabPurchase Invoice`
+ where
+ reverse_charge = "Y"
+ and claimable_reverse_charge > 0
+ and docstatus = 1 {get_conditions(filters)} ;
+ """)[0][0]
+
+
+def get_reverse_charge_recoverable_tax(filters):
+ """Returns the sum of the tax of each Purchase invoice made
+
+ Args:
+ filters (Dict, optional): Dictionary consisting of the filters selected by the user. Defaults to None.
+
+ Returns:
+ Float: sum of the tax of each Purchase invoice made
+ """
+ return frappe.db.sql(f"""
+ select sum(debit * `tabPurchase Invoice`.claimable_reverse_charge / 100) from
+ `tabPurchase Invoice` inner join `tabGL Entry`
+ on `tabGL Entry`.voucher_no = `tabPurchase Invoice`.name
+ where
+ `tabPurchase Invoice`.reverse_charge = "Y"
+ and `tabPurchase Invoice`.docstatus = 1
+ and `tabPurchase Invoice`.claimable_reverse_charge > 0
+ and `tabGL Entry`.docstatus = 1 {get_conditions_join(filters)}
+ and account in ("{'", "'.join(get_tax_accounts(filters['company']))}");
+ """)[0][0]
+
+
+def get_standard_rated_expenses_total(filters):
+ """Returns the sum of the total of each Purchase invoice made with claimable reverse charge
+
+ Args:
+ filters (Dict, optional): Dictionary consisting of the filters selected by the user. Defaults to None.
+
+ Returns:
+ Float: sum of the total of each Purchase invoice made with claimable reverse charge
+ """
+ conditions = """
+ for opts in (("company", f' and company="{filters.get("company")}"'),
+ ("from_date", f' and posting_date>="{filters.get("from_date")}"'),
+ ("to_date", f' and posting_date<="{filters.get("to_date")}"')):
+ if filters.get(opts[0]):
+ conditions += opts[1]
+ return conditions
+ """
+ return frappe.db.sql(f"""
+ select sum(total) from
+ `tabSales Invoice`
+ where
+ standard_rated_expenses > 0
+ and docstatus = 1 {get_conditions(filters)} ;
+ """)[0][0]
+
+
+def get_standard_rated_expenses_tax(filters):
+ """Returns the sum of the tax of each Purchase invoice made
+
+ Args:
+ filters (Dict, optional): Dictionary consisting of the filters selected by the user. Defaults to None.
+
+ Returns:
+ Float: sum of the tax of each Purchase invoice made
+ """
+ return frappe.db.sql(f"""
+ select sum(standard_rated_expenses) from
+ `tabSales Invoice`
+ where
+ standard_rated_expenses > 0
+ and docstatus = 1 {get_conditions(filters)} ;
+ """)[0][0]
+
+def get_tourist_tax_return_total(filters):
+ """Returns the sum of the total of each Sales invoice with non zero tourist_tax_return
+
+ Args:
+ filters (Dict, optional): Dictionary consisting of the filters selected by the user. Defaults to None.
+
+ Returns:
+ Float: sum of the total of each Sales invoice with non zero tourist_tax_return
+ """
+ conditions = """
+ for opts in (("company", f' and company="{filters.get("company")}"'),
+ ("from_date", f' and posting_date>="{filters.get("from_date")}"'),
+ ("to_date", f' and posting_date<="{filters.get("to_date")}"')):
+ if filters.get(opts[0]):
+ conditions += opts[1]
+ return conditions
+ """
+ return frappe.db.sql(f"""
+ select sum(total) from
+ `tabSales Invoice`
+ where
+ tourist_tax_return > 0
+ and docstatus = 1 {get_conditions(filters)} ;
+ """)[0][0]
+
+
+def get_tourist_tax_return_tax(filters):
+ """Returns the sum of the tax of each Sales invoice with non zero tourist_tax_return
+
+ Args:
+ filters (Dict, optional): Dictionary consisting of the filters selected by the user. Defaults to None.
+
+ Returns:
+ Float: sum of the tax of each Sales invoice with non zero tourist_tax_return
+ """
+ return frappe.db.sql(f"""
+ select sum(tourist_tax_return) from
+ `tabSales Invoice`
+ where
+ tourist_tax_return > 0
+ and docstatus = 1 {get_conditions(filters)} ;
+ """)[0][0]
\ No newline at end of file
diff --git a/erpnext/regional/united_arab_emirates/setup.py b/erpnext/regional/united_arab_emirates/setup.py
index f116303..0176340 100644
--- a/erpnext/regional/united_arab_emirates/setup.py
+++ b/erpnext/regional/united_arab_emirates/setup.py
@@ -33,7 +33,8 @@
fieldtype='Select', insert_after='permit_no', print_hide=1,
options='Y\nN', default='N'),
dict(fieldname='claimable_reverse_charge', label='Claimable Reverse Charge (Percentage)',
- insert_after='reverse_charge', fieldtype='Percent'),
+ insert_after='reverse_charge', fieldtype='Percent', print_hide=1,
+ depends_on="eval:doc.reverse_charge=='Y'", default='100.000'),
]
sales_invoice_fields = [
@@ -45,12 +46,12 @@
fetch_from='customer.customer_name_in_arabic', print_hide=1),
dict(fieldname='emirate', label='Emirate', insert_after='customer_address',
fieldtype='Read Only', fetch_from='customer_address.emirates'),
- dict(fieldname='returns_column_break', fieldtype='Column Break',
- insert_after='select_print_heading'),
+ # dict(fieldname='returns_column_break', fieldtype='Column Break',
+ # insert_after='select_print_heading'),
dict(fieldname='tourist_tax_return', label='Tax Refund provided to Tourists (AED)',
- insert_after='returns_column_break', fieldtype='Currency',),
+ insert_after='permit_no', fieldtype='Currency', print_hide=1, default='0'),
dict(fieldname='standard_rated_expenses', label='Standard Rated Expenses (AED)',
- insert_after='tourist_tax_return', fieldtype='Currency',),
+ insert_after='tourist_tax_return', fieldtype='Currency', print_hide=1, default='0'),
]
invoice_item_fields = [