Hi! I already added a filter where I can select company and view the stock balance but its not working. Any idea the next step i need to configure? Thanks
Hi Sheerland
I think you must add filter Company in
apps\erpnext\erpnext\stock\report\stock_balance\stock_balance.py
I notyet done this before. But I read quickly and I think you must add filter Company
This is example code, I notyet test, hope can help you. But this way you touch core you can’t update new version of erpnext.
sle.company = %s
,filters.get("company")
def get_stock_ledger_entries(filters):
conditions = get_conditions(filters)
join_table_query = ""
if filters.get("item_group"):
join_table_query = "inner join `tabItem` item on item.name = sle.item_code"
return frappe.db.sql("""
select
sle.item_code, warehouse, sle.posting_date, sle.actual_qty, sle.valuation_rate,
sle.company, sle.voucher_type, sle.qty_after_transaction, sle.stock_value_difference
from
`tabStock Ledger Entry` sle force index (posting_sort_index) %s
where sle.company = %s and sle.docstatus < 2 %s
order by sle.posting_date, sle.posting_time, sle.name""" %
(join_table_query, filters.get("company"), conditions), as_dict=1)
or you can add filter in def get_conditions(filters)
if filters.get("company"):
conditions += " and sle.company = '%s'" % frappe.db.escape(filters.get("company"))
Ohh i see. Thanks for the help and for the sample script you provided. I’ll try it later after lunch.
Traceback (most recent call last):
File “/home/frappe/frappe-bench/apps/frappe/frappe/app.py”, line 57, in application
response = frappe.handler.handle()
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 22, in handle
data = execute_cmd(cmd)
File “/home/frappe/frappe-bench/apps/frappe/frappe/handler.py”, line 53, in execute_cmd
return frappe.call(method, **frappe.form_dict)
File “/home/frappe/frappe-bench/apps/frappe/frappe/init.py”, line 923, in call
return fn(*args, **newargs)
File “/home/frappe/frappe-bench/apps/frappe/frappe/desk/query_report.py”, line 96, in run
res = frappe.get_attr(method_name)(frappe._dict(filters))
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/stock/report/stock_balance/stock_balance.py”, line 17, in execute
iwb_map = get_item_warehouse_map(filters)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/stock/report/stock_balance/stock_balance.py”, line 131, in get_item_warehouse_map
sle = get_stock_ledger_entries(filters)
File “/home/frappe/frappe-bench/apps/erpnext/erpnext/stock/report/stock_balance/stock_balance.py”, line 124, in get_stock_ledger_entries
(join_table_query, filters.get(“company”), conditions), as_dict=1)
File “/home/frappe/frappe-bench/apps/frappe/frappe/database.py”, line 165, in sql
self._cursor.execute(query)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/cursors.py”, line 250, in execute
self.errorhandler(self, exc, value)
File “/home/frappe/frappe-bench/env/local/lib/python2.7/site-packages/MySQLdb/connections.py”, line 50, in defaulterrorhandler
raise errorvalue
ProgrammingError: (1064, “You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘Mart and sle.docstatus < 2 and sle.posting_date <= ‘2017-09-28’ and sle.company’ at line 6”)
I tried your code. It’s not working. Pls check the traceback error. Thanks
you try this way?
Yes. Just copied it and pasting it.
Hi sheerland
I tested my side, It’s ok. Please check again.
stock_blance.js
frappe.query_reports["Stock Balance"] = {
"filters": [
{
"fieldname":"company",
"label": __("Company"),
"fieldtype": "Link",
"options": "Company",
"default": frappe.defaults.get_user_default("Company"),
"reqd": 1
},
{
"fieldname":"from_date",
"label": __("From Date"),
"fieldtype": "Date",
"width": "80",
"reqd": 1,
"default": frappe.sys_defaults.year_start_date,
},
stock_blance.py
def get_conditions(filters):
conditions = ""
if filters.get("company"):
conditions += " and sle.company = '%s'" % frappe.db.escape(filters.get("company"))
if not filters.get("from_date"):
frappe.throw(_("'From Date' is required"))
This is my stock_balance.js
// Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors and contributors
// For license information, please see license.txt
frappe.query_reports[“Stock Balance”] = {
“filters”: [
{
"fieldname": "company",
"label": __("Company"),
"fieldtype": "Link",
"options": "Company",
"default": frappe.defaults.get_user_default("Company"),
"reqd": 1
},
{
"fieldname":"from_date",
"label": __("From Date"),
"fieldtype": "Date",
"width": "80",
"reqd": 1,
"default": frappe.sys_defaults.year_start_date,
},
{
"fieldname":"to_date",
"label": __("To Date"),
"fieldtype": "Date",
"width": "80",
"reqd": 1,
"default": frappe.datetime.get_today()
},
{
"fieldname": "item_group",
"label": __("Item Group"),
"fieldtype": "Link",
"width": "80",
"options": "Item Group"
},
{
"fieldname": "item_code",
"label": __("Item"),
"fieldtype": "Link",
"width": "80",
"options": "Item"
},
{
"fieldname": "warehouse",
"label": __("Warehouse"),
"fieldtype": "Link",
"width": "80",
"options": "Warehouse"
},
]
}
Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
License: GNU General Public License v3. See license.txt
from future import unicode_literals
import frappe
from frappe import _
from frappe.utils import flt, cint, getdate
def execute(filters=None):
if not filters: filters = {}
validate_filters(filters)
columns = get_columns()
item_map = get_item_details(filters)
item_reorder_detail_map = get_item_reorder_details(filters)
iwb_map = get_item_warehouse_map(filters)
data = []
for (company, item, warehouse) in sorted(iwb_map):
qty_dict = iwb_map[(company, item, warehouse)]
item_reorder_level = 0
item_reorder_qty = 0
if item + warehouse in item_reorder_detail_map:
item_reorder_level = item_reorder_detail_map[item + warehouse]["warehouse_reorder_level"]
item_reorder_qty = item_reorder_detail_map[item + warehouse]["warehouse_reorder_qty"]
data.append([item, item_map[item]["item_name"],
company,
item_map[item]["item_group"],
item_map[item]["brand"],
item_map[item]["description"], warehouse,
item_map[item]["stock_uom"], qty_dict.opening_qty,
qty_dict.opening_val, qty_dict.in_qty,
qty_dict.in_val, qty_dict.out_qty,
qty_dict.out_val, qty_dict.bal_qty,
qty_dict.bal_val, qty_dict.val_rate,
item_reorder_level,
item_reorder_qty
])
return columns, data
def get_columns():
“”“return columns”“”
columns = [
_("Item")+":Link/Item:100",
_("Item Name")+"::150",
_("Company")+":Link/Company:100",
_("Item Group")+"::100",
_("Brand")+"::90",
_("Description")+"::140",
_("Warehouse")+":Link/Warehouse:100",
_("Stock UOM")+":Link/UOM:90",
_("Opening Qty")+":Float:100",
_("Opening Value")+":Float:110",
_("In Qty")+":Float:80",
_("In Value")+":Float:80",
_("Out Qty")+":Float:80",
_("Out Value")+":Float:80",
_("Balance Qty")+":Float:100",
_("Balance Value")+":Float:100",
_("Valuation Rate")+":Float:90",
_("Reorder Level")+":Float:80",
_("Reorder Qty")+":Float:80"
]
return columns
def get_conditions(filters):
conditions = “”
if filters.get("company"):
conditions += " and sle.company = '%s'" % frappe.db.escape(filters.get("company"))
if not filters.get("from_date"):
frappe.throw(_("'From Date' is required"))
if filters.get("to_date"):
conditions += " and sle.posting_date <= '%s'" % frappe.db.escape(filters.get("to_date"))
else:
frappe.throw(_("'To Date' is required"))
if filters.get("item_group"):
ig_details = frappe.db.get_value("Item Group", filters.get("item_group"),
["lft", "rgt"], as_dict=1)
if ig_details:
conditions += """
and exists (select name from `tabItem Group` ig
where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)
""" % (ig_details.lft, ig_details.rgt)
if filters.get("item_code"):
conditions += " and sle.item_code = '%s'" % frappe.db.escape(filters.get("item_code"), percent=False)
if filters.get("warehouse"):
warehouse_details = frappe.db.get_value("Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1)
if warehouse_details:
conditions += " and exists (select name from `tabWarehouse` wh \
where wh.lft >= %s and wh.rgt <= %s and sle.warehouse = wh.name)"%(warehouse_details.lft,
warehouse_details.rgt)
return conditions
def get_stock_ledger_entries(filters):
conditions = get_conditions(filters)
join_table_query = ""
if filters.get("item_group"):
join_table_query = "inner join `tabItem` item on item.name = sle.item_code"
return frappe.db.sql("""
select
sle.item_code, warehouse, sle.posting_date, sle.actual_qty, sle.valuation_rate,
sle.company, sle.voucher_type, sle.qty_after_transaction, sle.stock_value_difference
from
`tabStock Ledger Entry` sle force index (posting_sort_index) %s
where sle.company = %s and sle.docstatus < 2 %s
order by sle.posting_date, sle.posting_time, sle.name""" %
(join_table_query, filters.get("company"), conditions), as_dict=1)
def get_item_warehouse_map(filters):
iwb_map = {}
from_date = getdate(filters.get(“from_date”))
to_date = getdate(filters.get(“to_date”))
sle = get_stock_ledger_entries(filters)
for d in sle:
key = (d.company, d.item_code, d.warehouse)
if key not in iwb_map:
iwb_map[key] = frappe._dict({
"opening_qty": 0.0, "opening_val": 0.0,
"in_qty": 0.0, "in_val": 0.0,
"out_qty": 0.0, "out_val": 0.0,
"bal_qty": 0.0, "bal_val": 0.0,
"val_rate": 0.0
})
qty_dict = iwb_map[(d.company, d.item_code, d.warehouse)]
if d.voucher_type == "Stock Reconciliation":
qty_diff = flt(d.qty_after_transaction) - qty_dict.bal_qty
else:
qty_diff = flt(d.actual_qty)
value_diff = flt(d.stock_value_difference)
if d.posting_date < from_date:
qty_dict.opening_qty += qty_diff
qty_dict.opening_val += value_diff
elif d.posting_date >= from_date and d.posting_date <= to_date:
if qty_diff > 0:
qty_dict.in_qty += qty_diff
qty_dict.in_val += value_diff
else:
qty_dict.out_qty += abs(qty_diff)
qty_dict.out_val += abs(value_diff)
qty_dict.val_rate = d.valuation_rate
qty_dict.bal_qty += qty_diff
qty_dict.bal_val += value_diff
iwb_map = filter_items_with_no_transactions(iwb_map)
return iwb_map
def filter_items_with_no_transactions(iwb_map):
for (company, item, warehouse) in sorted(iwb_map):
qty_dict = iwb_map[(company, item, warehouse)]
no_transactions = True
float_precision = cint(frappe.db.get_default("float_precision")) or 3
for key, val in qty_dict.items():
val = flt(val, float_precision)
qty_dict[key] = val
if key != "val_rate" and val:
no_transactions = False
if no_transactions:
iwb_map.pop((company, item, warehouse))
return iwb_map
def get_item_details(filters):
condition = ‘’
value = ()
if filters.get(“item_code”):
condition = “where item_code=%s”
value = (filters.get(“item_code”),)
items = frappe.db.sql("""select name, item_name, stock_uom, item_group, brand, description
from tabItem {condition}""".format(condition=condition), value, as_dict=1)
return dict((d.name , d) for d in items)
def get_item_reorder_details(filters):
condition = ‘’
value = ()
if filters.get(“item_code”):
condition = “where parent=%s”
value = (filters.get(“item_code”),)
item_reorder_details = frappe.db.sql("""select parent,warehouse,warehouse_reorder_qty,warehouse_reorder_level
from `tabItem Reorder` {condition}""".format(condition=condition), value, as_dict=1)
return dict((d.parent + d.warehouse, d) for d in item_reorder_details)
def validate_filters(filters):
if not (filters.get(“item_code”) or filters.get(“warehouse”)):
sle_count = flt(frappe.db.sql(“”“select count(name) from tabStock Ledger Entry”“”)[0][0])
if sle_count > 500000:
frappe.throw(_(“Please set filter based on Item or Warehouse”))
Hi shherland
Pls backup again def get_stock_ledger_entries(filters). First way is disable.
Now we try to second way add filter in def get_conditions(filters)
Ok Im compiling it right now. removing those 2 you enclosed in box.
It still gives me error. Can you provide your whole script for the stock_balance.py? thanks. @vinhnguyent090
# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt
from __future__ import unicode_literals
import frappe
from frappe import _
from frappe.utils import flt, cint, getdate
def execute(filters=None):
if not filters: filters = {}
validate_filters(filters)
columns = get_columns()
item_map = get_item_details(filters)
item_reorder_detail_map = get_item_reorder_details(filters)
iwb_map = get_item_warehouse_map(filters)
data = []
for (company, item, warehouse) in sorted(iwb_map):
qty_dict = iwb_map[(company, item, warehouse)]
item_reorder_level = 0
item_reorder_qty = 0
if item + warehouse in item_reorder_detail_map:
item_reorder_level = item_reorder_detail_map[item + warehouse]["warehouse_reorder_level"]
item_reorder_qty = item_reorder_detail_map[item + warehouse]["warehouse_reorder_qty"]
data.append([item, item_map[item]["item_name"],
item_map[item]["item_group"],
item_map[item]["brand"],
item_map[item]["description"], warehouse,
item_map[item]["stock_uom"], qty_dict.opening_qty,
qty_dict.opening_val, qty_dict.in_qty,
qty_dict.in_val, qty_dict.out_qty,
qty_dict.out_val, qty_dict.bal_qty,
qty_dict.bal_val, qty_dict.val_rate,
item_reorder_level,
item_reorder_qty,
company
])
return columns, data
def get_columns():
"""return columns"""
columns = [
_("Item")+":Link/Item:100",
_("Item Name")+"::150",
_("Item Group")+"::100",
_("Brand")+"::90",
_("Description")+"::140",
_("Warehouse")+":Link/Warehouse:100",
_("Stock UOM")+":Link/UOM:90",
_("Opening Qty")+":Float:100",
_("Opening Value")+":Float:110",
_("In Qty")+":Float:80",
_("In Value")+":Float:80",
_("Out Qty")+":Float:80",
_("Out Value")+":Float:80",
_("Balance Qty")+":Float:100",
_("Balance Value")+":Float:100",
_("Valuation Rate")+":Float:90",
_("Reorder Level")+":Float:80",
_("Reorder Qty")+":Float:80",
_("Company")+":Link/Company:100"
]
return columns
def get_conditions(filters):
conditions = ""
if filters.get("company"):
conditions += " and sle.company = '%s'" % frappe.db.escape(filters.get("company"))
if not filters.get("from_date"):
frappe.throw(_("'From Date' is required"))
if filters.get("to_date"):
conditions += " and sle.posting_date <= '%s'" % frappe.db.escape(filters.get("to_date"))
else:
frappe.throw(_("'To Date' is required"))
if filters.get("item_group"):
ig_details = frappe.db.get_value("Item Group", filters.get("item_group"),
["lft", "rgt"], as_dict=1)
if ig_details:
conditions += """
and exists (select name from `tabItem Group` ig
where ig.lft >= %s and ig.rgt <= %s and item.item_group = ig.name)
""" % (ig_details.lft, ig_details.rgt)
if filters.get("item_code"):
conditions += " and sle.item_code = '%s'" % frappe.db.escape(filters.get("item_code"), percent=False)
if filters.get("warehouse"):
warehouse_details = frappe.db.get_value("Warehouse", filters.get("warehouse"), ["lft", "rgt"], as_dict=1)
if warehouse_details:
conditions += " and exists (select name from `tabWarehouse` wh \
where wh.lft >= %s and wh.rgt <= %s and sle.warehouse = wh.name)"%(warehouse_details.lft,
warehouse_details.rgt)
return conditions
def get_stock_ledger_entries(filters):
conditions = get_conditions(filters)
join_table_query = ""
if filters.get("item_group"):
join_table_query = "inner join `tabItem` item on item.name = sle.item_code"
return frappe.db.sql("""
select
sle.item_code, warehouse, sle.posting_date, sle.actual_qty, sle.valuation_rate,
sle.company, sle.voucher_type, sle.qty_after_transaction, sle.stock_value_difference
from
`tabStock Ledger Entry` sle force index (posting_sort_index) %s
where sle.docstatus < 2 %s
order by sle.posting_date, sle.posting_time, sle.name""" %
(join_table_query, conditions), as_dict=1)
def get_item_warehouse_map(filters):
iwb_map = {}
from_date = getdate(filters.get("from_date"))
to_date = getdate(filters.get("to_date"))
sle = get_stock_ledger_entries(filters)
for d in sle:
key = (d.company, d.item_code, d.warehouse)
if key not in iwb_map:
iwb_map[key] = frappe._dict({
"opening_qty": 0.0, "opening_val": 0.0,
"in_qty": 0.0, "in_val": 0.0,
"out_qty": 0.0, "out_val": 0.0,
"bal_qty": 0.0, "bal_val": 0.0,
"val_rate": 0.0
})
qty_dict = iwb_map[(d.company, d.item_code, d.warehouse)]
if d.voucher_type == "Stock Reconciliation":
qty_diff = flt(d.qty_after_transaction) - qty_dict.bal_qty
else:
qty_diff = flt(d.actual_qty)
value_diff = flt(d.stock_value_difference)
if d.posting_date < from_date:
qty_dict.opening_qty += qty_diff
qty_dict.opening_val += value_diff
elif d.posting_date >= from_date and d.posting_date <= to_date:
if qty_diff > 0:
qty_dict.in_qty += qty_diff
qty_dict.in_val += value_diff
else:
qty_dict.out_qty += abs(qty_diff)
qty_dict.out_val += abs(value_diff)
qty_dict.val_rate = d.valuation_rate
qty_dict.bal_qty += qty_diff
qty_dict.bal_val += value_diff
iwb_map = filter_items_with_no_transactions(iwb_map)
return iwb_map
def filter_items_with_no_transactions(iwb_map):
for (company, item, warehouse) in sorted(iwb_map):
qty_dict = iwb_map[(company, item, warehouse)]
no_transactions = True
float_precision = cint(frappe.db.get_default("float_precision")) or 3
for key, val in qty_dict.items():
val = flt(val, float_precision)
qty_dict[key] = val
if key != "val_rate" and val:
no_transactions = False
if no_transactions:
iwb_map.pop((company, item, warehouse))
return iwb_map
def get_item_details(filters):
condition = ''
value = ()
if filters.get("item_code"):
condition = "where item_code=%s"
value = (filters.get("item_code"),)
items = frappe.db.sql("""select name, item_name, stock_uom, item_group, brand, description
from tabItem {condition}""".format(condition=condition), value, as_dict=1)
return dict((d.name , d) for d in items)
def get_item_reorder_details(filters):
condition = ''
value = ()
if filters.get("item_code"):
condition = "where parent=%s"
value = (filters.get("item_code"),)
item_reorder_details = frappe.db.sql("""select parent,warehouse,warehouse_reorder_qty,warehouse_reorder_level
from `tabItem Reorder` {condition}""".format(condition=condition), value, as_dict=1)
return dict((d.parent + d.warehouse, d) for d in item_reorder_details)
def validate_filters(filters):
if not (filters.get("item_code") or filters.get("warehouse")):
sle_count = flt(frappe.db.sql("""select count(name) from `tabStock Ledger Entry`""")[0][0])
if sle_count > 500000:
frappe.throw(_("Please set filter based on Item or Warehouse"))
Tried your script and it gives no error but also it does not provide me the function. I have created 3 company. Only 1 company has a data while the 2 others dont. When I select the company without data? There will be no display because it does not contain any items. But why there are still items in the table displayed? @vinhnguyent090
Also checked the console with your code and there is a Type error saying:
TypeError: Cannot read property ‘id’ of undefined
at Class.make_data (report.min.js?ver=1506653732.4:1463)
at Class.make_results (report.min.js?ver=1506653732.4:1332)
at Object.callback (report.min.js?ver=1506653732.4:1283)
at Object.callback [as success_callback] (desk.min.js?ver=1506653732.4:1354)
at _ (desk.min.js?ver=1506653732.4:1378)
at Object. (desk.min.js?ver=1506653732.4:1478)
at i (jquery.min.js:2)