I need help creating a Script Report

ERPNext
,
1

Hello community

I’m having a bit of a coding issue. I’m trying to generate a report, that returns various entries on the database.

# Copyright (c) 2013, libracore AG and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe

def execute(filters=None):
        columns, data = [], get_data(filters)
        columns = [
	"Item Code:Link/Item:200",
	"Item Name::200"
    "Supplier Item Number::200",
	"Balance::200"]
        return columns, data
        
def get_data(filters):
        conditions = " 1 = 1"
        if filters.get('item'):
                conditions += " and `tabItem`.item_code = '{0}'".format(filters.get('item'))
                
        return frappe.db.sql("""SELECT `tabItem`.item_code, item_name, supplier_part_no from `tabItem`
                                left join `tabItem Supplier` on `tabItem Supplier`.parent = `tabItem`.item_code where {} """.format(conditions),as_list=1)

This code will return a report with Item Code, name and the Supplier Item number. Now I want to add the Stock Balance to the report. The problem: I also need to be able to filter a certain date, so I can narrow down how many of X item I had on X date.

What would be the best way to go about this? I’m aware that the Stock Balance report can already do this, but I can’t just copy the function there, since that code is much bigger and more complicated than what I can currently put up.

Kind Regards

2

Hi,
If you have given is_standard option as Yes in report, it will generate a js and py file. In js file you can write code for filters. For example,

frappe.query_reports[“Your Report”] = {
“filters”: [
{
“fieldname”:“from_date”,
“label”: __(“From Date”),
“fieldtype”: “Date”,
“width”: “80”,
“reqd”: 1,
“default”: frappe.datetime.add_months(frappe.datetime.get_today(), -1),
},
]
};

3

I’m sorry, I forgot to request to close this thread as I’ve found another solution by now.
Kind regards,
Vals

4

Do add the solution so it would help others as well.

5

Yeah fair point.

So contrary to what I said in my inital post, I got myself together and studied the libracore version of the Stock Balance report for over a week, trying to understand each line of code.

I then slightly altered the report, removed a few unnecessary columns and added the functionality that I need. It may sound not like much work, It’s only about 10-15 lines of code I added, but believe me it took me a long time to get the hang out of it. This report now includes the item supplier item number as well as the selling price.

You’ll find my report here: custom_reports/stock_and_sales_statistics.py at master · Lyefyre/custom_reports · GitHub

Edit: New Report link, with English Columns again. If you decide to use this report for your own ERP, please note that it will work only flawlessly for the system it was designed for.
Directly copy pasting my report without any changes will most likely throw an error!
If you want to know more, send me a message.

3 Likes