feat: add reverse charge to uae vat report
diff --git a/erpnext/regional/report/uae_vat/uae_vat.py b/erpnext/regional/report/uae_vat/uae_vat.py
index 6e9565b..50d1ddd 100644
--- a/erpnext/regional/report/uae_vat/uae_vat.py
+++ b/erpnext/regional/report/uae_vat/uae_vat.py
@@ -3,6 +3,7 @@
from __future__ import unicode_literals
import frappe
+from erpnext.regional.united_arab_emirates.utils import get_tax_accounts
def execute(filters=None):
columns = get_columns()
@@ -70,19 +71,21 @@
"vat_amount": 0
}
)
+ data.append(
+ {
+ "no": '3',
+ "legend": f'Supplies subject to the reverse charge provision',
+ "amount": get_reverse_charge_total(filters),
+ "vat_amount": get_reverse_charge_tax(filters)
+ }
+ )
return data
def get_total_emiratewise(filters):
- conditions = get_conditions(filters)
- print(f"""
- select emirate, sum(total), sum(total_taxes_and_charges) from `tabSales Invoice`
- where docstatus = 1 {conditions}
- group by `tabSales Invoice`.emirate;
- """)
return frappe.db.sql(f"""
select emirate, sum(total), sum(total_taxes_and_charges) from `tabSales Invoice`
- where docstatus = 1 {conditions}
+ where docstatus = 1 {get_conditions(filters)}
group by `tabSales Invoice`.emirate;
""", filters)
@@ -99,10 +102,40 @@
def get_conditions(filters):
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
- for opts in (("company", " and company=%(company)s"),
- ("from_date", " and posting_date>=%(from_date)s"),
- ("to_date", " and posting_date<=%(to_date)s")):
+def get_reverse_charge_tax(filters):
+ return frappe.db.sql(f"""
+ select sum(debit) 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 `tabGL Entry`.docstatus = 1 {get_conditions_join(filters)}
+ and account in ("{'", "'.join(get_tax_accounts(filters['company']))}");
+ """)[0][0]
+
+
+def get_reverse_charge_total(filters):
+ return frappe.db.sql(f"""
+ select sum(total) from
+ `tabPurchase Invoice`
+ where
+ reverse_charge = "Y"
+ and docstatus = 1 {get_conditions(filters)} ;
+ """)[0][0]
+
+def get_conditions_join(filters):
+ conditions = ""
+ for opts in (("company", f' and `tabPurchase Invoice`.company="{filters.get("company")}"'),
+ ("from_date", f' and `tabPurchase Invoice`.posting_date>="{filters.get("from_date")}"'),
+ ("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
diff --git a/erpnext/regional/united_arab_emirates/setup.py b/erpnext/regional/united_arab_emirates/setup.py
index a2938bb..d38d647 100644
--- a/erpnext/regional/united_arab_emirates/setup.py
+++ b/erpnext/regional/united_arab_emirates/setup.py
@@ -20,7 +20,7 @@
insert_after='group_same_items', print_hide=1, collapsible=1),
dict(fieldname='permit_no', label='Permit Number',
fieldtype='Data', insert_after='vat_section', print_hide=1),
- dict(fieldname='reverse_charge_applicable', label='Reverse Charge Applicable',
+ dict(fieldname='reverse_charge', label='Reverse Charge Applicable',
fieldtype='Select', insert_after='permit_no', print_hide=1,
options='Y\nN', default='N')
]
@@ -42,7 +42,7 @@
fieldtype='Read Only', insert_after='customer_name',
fetch_from='customer.customer_name_in_arabic', print_hide=1),
dict(fieldname='emirate', label='Emirate', insert_after='customer_address',
- fetch_from='customer_address.emirates'),
+ fieldtype='Read Only', fetch_from='customer_address.emirates'),
]
invoice_item_fields = [
@@ -79,8 +79,8 @@
fieldtype='Data', insert_after='supplier_name'),
],
'Address': [
- dict(fieldname='emirates', label='Emirates',
- fieldtype='Data', insert_after='state'),
+ dict(fieldname='emirates', label='Emirates', fieldtype='Select', insert_after='state',
+ options='Abu Dhabi\nAjman\nDubai\nFujairah\nRas Al Khaimah\nSharjah\nUmm Al Quwain')
],
'Purchase Invoice': purchase_invoice_fields + invoice_fields,
'Purchase Order': purchase_invoice_fields + invoice_fields,