Recently I paid a developer to build a report like below… Can I post the source here?
My vote is yes - contributions are welcome and encouraged I would venture!
But uploads are limited to screenshot graphics files that are typically malware-free.
So if your Pivot Table is in Excel spreadsheet form I can’t say how to post that here since those lack source code form, say to copy and paste here?
If you have made changes to the code, then send a Pull request on GitHub - frappe/erpnext: Free and Open Source Enterprise Resource Planning (ERP) . The maintainers will review the code, if required they may suggest some changes, and if it is good they’ll merge it.
So please ask your developer to send a Pull Request.
thank you
5 Likes
from __future__ import unicode_literals
import frappe
from frappe import _
from frappe.utils import flt, cint, getdate
from erpnext.stock.report.stock_balance.stock_balance import get_item_details, get_item_reorder_details, get_item_warehouse_map
from erpnext.stock.report.stock_ageing.stock_ageing import get_fifo_queue, get_average_age
from dlt.utils import get_user_permissions_for
def execute(filters=None):
if not filters: filters = {}
validate_filters(filters)
columns = get_columns(filters)
item_map = get_item_details(filters)
iwb_map = get_item_warehouse_map(filters)
warehouse_list = get_warehouse_list(filters)
item_ageing = get_fifo_queue(filters)
data = []
item_balance = {}
item_value = {}
for (company, item, warehouse) in sorted(iwb_map):
row = []
qty_dict = iwb_map[(company, item, warehouse)]
item_balance.setdefault((item, item_map[item]["item_group"]), [])
total_stock_value = 0.00
for wh in warehouse_list:
row += [qty_dict.bal_qty] if wh.name in warehouse else [0.00]
total_stock_value += qty_dict.bal_val if wh.name in warehouse else 0.00
item_balance[(item, item_map[item]["item_group"])].append(row)
item_value.setdefault((item, item_map[item]["item_group"]),[])
item_value[(item, item_map[item]["item_group"])].append(total_stock_value)
#sum bal_qty by item
for (item, item_group), wh_balance in item_balance.items():
total_stock_value = sum(item_value[(item, item_group)])
row = [item, item_group, total_stock_value]
fifo_queue = item_ageing[item]["fifo_queue"]
average_age = 0.00
if fifo_queue:
average_age = get_average_age(fifo_queue, filters["to_date"])
row += [average_age]
bal_qty = [sum(bal_qty) for bal_qty in zip(*wh_balance)]
total_qty = sum(bal_qty)
if len(warehouse_list) > 1:
row += [total_qty]
row += bal_qty
if total_qty > 0:
data.append(row)
elif not filters.get("filter_total_zero_qty"):
data.append(row)
add_warehouse_column(columns, warehouse_list)
return columns, data
def get_columns(filters):
"""return columns"""
columns = [
_("Item")+":Link/Item:180",
_("Item Group")+"::100",
_("Value")+":Currency:100",
_("Age")+":Float:60",
]
return columns
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"))
if not filters.get("company"):
filters["company"] = frappe.defaults.get_user_default("Company")
def get_warehouse_list(filters):
condition = ''
user_permitted_warehouse = get_user_permissions_for("Warehouse")
value = ()
if user_permitted_warehouse:
condition = "and name in %s"
value = set(user_permitted_warehouse)
elif not user_permitted_warehouse and filters.get("warehouse"):
condition = "and name = %s"
value = filters.get("warehouse")
return frappe.db.sql("""select name, ifnull(abbr, name) as abbr
from `tabWarehouse` where is_group = 0
{condition}""".format(condition=condition), value, as_dict=1)
def add_warehouse_column(columns, warehouse_list):
if len(warehouse_list) > 1:
columns += [_("Total")+":Int:40"]
for wh in warehouse_list:
columns += [_(wh.abbr)+":Int:54"]
4 Likes
Thanks for the code, but here it will be lost in forum, ideal place for getting it merged in the frappe code is github.
1 Like
please help me how to do that!
I set up a developer server in developer mode, then create this report then how can I merge it with GitHub ?
First step is to register on github.com
Then I suggest you understand the github workflow . There are a lot of tutorials for this online.
Then fork the erpnext or frappe project on github, add your changes to it and send a Pull Request. Here is a guideline for contributing in erpnext.
Let me know if you get stuck anywhere.
P.S. The advantage of going through all this is that it will be easier to track and fix in future and also as a bonus will have your name as a contributor to frappe and erpnext.
1 Like
I have created a PR Warehouse wise Item Balance, Age and Value by riyasrawther · Pull Request #12879 · frappe/erpnext · GitHub
3 Likes
Thank you very much, @nabinhait had already replied to it. please add a screenshot to the pr and continue the discussion there.
Congrats!! on your first PR 
thank you for your contribution
it’s very useful