Merge pull request #16487 from netchampfaris/fix-gst-pos-invoice
fix: Dont show 0 amount taxes and tax breakup
diff --git a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
index 7814b08..9172762 100644
--- a/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
+++ b/erpnext/accounts/doctype/bank_reconciliation/bank_reconciliation.py
@@ -26,7 +26,7 @@
select
"Journal Entry" as payment_document, t1.name as payment_entry,
t1.cheque_no as cheque_number, t1.cheque_date,
- t2.debit_in_account_currency as debit, t2.credit_in_account_currency as credit,
+ sum(t2.debit_in_account_currency) as debit, sum(t2.credit_in_account_currency) as credit,
t1.posting_date, t2.against_account, t1.clearance_date, t2.account_currency
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
@@ -34,6 +34,7 @@
t2.parent = t1.name and t2.account = %s and t1.docstatus=1
and t1.posting_date >= %s and t1.posting_date <= %s
and ifnull(t1.is_opening, 'No') = 'No' {0}
+ group by t2.account, t1.name
order by t1.posting_date ASC, t1.name DESC
""".format(condition), (self.bank_account, self.from_date, self.to_date), as_dict=1)
diff --git a/erpnext/accounts/doctype/sales_invoice/pos.py b/erpnext/accounts/doctype/sales_invoice/pos.py
index 287da08..1ef7b54 100755
--- a/erpnext/accounts/doctype/sales_invoice/pos.py
+++ b/erpnext/accounts/doctype/sales_invoice/pos.py
@@ -333,10 +333,12 @@
itemwise_bin_data = {}
cond = "1=1"
if pos_profile.get('warehouse'):
- cond = "warehouse = '{0}'".format(pos_profile.get('warehouse'))
+ cond = "warehouse = %(warehouse)s"
bin_data = frappe.db.sql(""" select item_code, warehouse, actual_qty from `tabBin`
- where actual_qty > 0 and {cond}""".format(cond=cond), as_dict=1)
+ where actual_qty > 0 and {cond}""".format(cond=cond), {
+ 'warehouse': frappe.db.escape(pos_profile.get('warehouse'))
+ }, as_dict=1)
for bins in bin_data:
if bins.item_code not in itemwise_bin_data:
diff --git a/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py b/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py
index 13424db..0861b20 100644
--- a/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py
+++ b/erpnext/accounts/report/bank_clearance_summary/bank_clearance_summary.py
@@ -36,8 +36,8 @@
def get_entries(filters):
conditions = get_conditions(filters)
journal_entries = frappe.db.sql("""SELECT
- "Journal Entry", jv.name, jv.posting_date, jv.cheque_no, jv.clearance_date, jvd.against_account,
- if((jvd.debit - jvd.credit) < 0, (jvd.debit - jvd.credit) * -1, (jvd.debit - jvd.credit))
+ "Journal Entry", jv.name, jv.posting_date, jv.cheque_no,
+ jv.clearance_date, jvd.against_account, jvd.debit - jvd.credit
FROM
`tabJournal Entry Account` jvd, `tabJournal Entry` jv
WHERE
@@ -46,7 +46,7 @@
payment_entries = frappe.db.sql("""SELECT
"Payment Entry", name, posting_date, reference_no, clearance_date, party,
- if(paid_from=%(account)s, paid_amount, received_amount)
+ if(paid_from=%(account)s, paid_amount * -1, received_amount)
FROM
`tabPayment Entry`
WHERE
diff --git a/erpnext/stock/dashboard/item_dashboard.py b/erpnext/stock/dashboard/item_dashboard.py
index f95daaf..d817e5f 100644
--- a/erpnext/stock/dashboard/item_dashboard.py
+++ b/erpnext/stock/dashboard/item_dashboard.py
@@ -1,43 +1,37 @@
from __future__ import unicode_literals
import frappe
+from frappe.model.db_query import DatabaseQuery
@frappe.whitelist()
def get_data(item_code=None, warehouse=None, item_group=None,
start=0, sort_by='actual_qty', sort_order='desc'):
'''Return data to render the item dashboard'''
- conditions = []
- values = []
+ filters = []
if item_code:
- conditions.append('b.item_code=%s')
- values.append(item_code)
+ filters.append(['item_code', '=', item_code])
if warehouse:
- conditions.append('b.warehouse=%s')
- values.append(warehouse)
+ filters.append(['warehouse', '=', warehouse])
if item_group:
- conditions.append('i.item_group=%s')
- values.append(item_group)
+ filters.append(['item_group', '=', item_group])
+ try:
+ # check if user has any restrictions based on user permissions on warehouse
+ if DatabaseQuery('Warehouse', user=frappe.session.user).build_match_conditions():
+ filters.append(['warehouse', 'in', [w.name for w in frappe.get_list('Warehouse')]])
+ except frappe.PermissionError:
+ # user does not have access on warehouse
+ return []
- if conditions:
- conditions = ' and ' + ' and '.join(conditions)
- else:
- conditions = ''
-
- return frappe.db.sql('''
- select
- b.item_code, b.warehouse, b.projected_qty, b.reserved_qty,
- b.reserved_qty_for_production, b.reserved_qty_for_sub_contract, b.actual_qty, b.valuation_rate, i.item_name
- from
- tabBin b, tabItem i
- where
- b.item_code = i.name
- and
- (b.projected_qty != 0 or b.reserved_qty != 0 or b.reserved_qty_for_production != 0
- or b.reserved_qty_for_sub_contract != 0 or b.actual_qty != 0)
- {conditions}
- order by
- {sort_by} {sort_order}
- limit
- {start}, 21
- '''.format(conditions=conditions, sort_by=sort_by, sort_order=sort_order,
- start=start), values, as_dict=True)
+ return frappe.db.get_all('Bin', fields=['item_code', 'warehouse', 'projected_qty',
+ 'reserved_qty', 'reserved_qty_for_production', 'reserved_qty_for_sub_contract', 'actual_qty', 'valuation_rate'],
+ or_filters={
+ 'projected_qty': ['!=', 0],
+ 'reserved_qty': ['!=', 0],
+ 'reserved_qty_for_production': ['!=', 0],
+ 'reserved_qty_for_sub_contract': ['!=', 0],
+ 'actual_qty': ['!=', 0],
+ },
+ filters=filters,
+ order_by=sort_by + ' ' + sort_order,
+ limit_start=start,
+ limit_page_length='21')