MultiSelectList in Script Report

1

i have multi select list from script report

frappe.query_reports["Close Stock Balance2"] = {

    "filters": [
        {
            'fieldname': 'brand',
            'label': 'Brand',
            "fieldtype": "MultiSelectList",
            "get_data": function(txt) {
            
                return frappe.db.get_link_options("Brand");
                    
                }
            }
        
    ]
};

when i choose brand from list there is no result in the table , i use this code for filter


sql_brand_filter = ""
if filters.get("brand"):
    escaped_input = frappe.db.escape(filters.get("brand"))
    sql_brand_filter = f"AND i.brand = {escaped_input}"

i’m really appreciate to any one help

2

@Osk brand is a list so try i.brand IN instead of equal

3

i changed code as below but there is an error


sql_brand_filter = ""
if filters.get("brand"):
    escaped_input = frappe.db.escape(filters.get("brand"))
    sql_brand_filter = f"AND i.brand in {escaped_input}"
4

@Osk try this :
escaped_input = str(tuple(filters.get("brand")))

5

i use this code


sql_brand_filter = ""
if filters.get("brand"):
    escaped_input = str(tuple(filters.get("brand")))
    sql_brand_filter = f"AND i.brand = {escaped_input}"

but give me an error message

pymysql.err.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 ‘)\n\n group by sli.item_code’ at line 20”)

6

@Osk it’s IN sir not =

7

iam sorry my friend i dont understand , you can see all script mybe you take good idea about my issue

    
sql_brand_filter = ""
if filters.get("brand"):
    escaped_input in str(tuple(filters.get("brand")))
    sql_brand_filter = f"AND i.brand in {escaped_input}"
 
 
res = frappe.db.sql(f"""SELECT

sli.item_code,
 i.item_name,
 i.item_master_barcode,
     sum(CASE WHEN sli.warehouse ='s1' THEN sli.actual_qty ELSE 0 END) AS A,
     sum(CASE WHEN sli.warehouse ='s2' THEN sli.actual_qty  ELSE 0 END) AS B
 
 from `tabBin` sli
 inner join `tabItem` i on  sli.item_code = i.item_code


where i.item_group in ('Discaount Group','With Out Discaount')

{sql_brand_filter}
{sql_item_barcode_filter}
 group by sli.item_code

   """, as_dict=True)


result = res
8

Check this out; it might be helpful for you.

image
image805×598 12.8 KB

For the above code, the SQL query generated will look like this.

SELECT `name`,`brand` FROM `tabItem` WHERE `brand` IN ('my_brand')

If you’re using raw query execution, then you’ll need to write the query like this

9

@Osk Check this out.

image
image1127×937 38.8 KB

I think it will satisfy your query and conditions. Replicate them and try this. Also, add your customizations like ‘barcode_master_filter’.

This code will run SQL like this

SELECT `tabBin`.`item_code`,`tabItem`.`item_name`,SUM(CASE WHEN `tabBin`.`warehouse`='s1' THEN `tabBin`.`actual_qty` ELSE 0 END) `A`,SUM(CASE WHEN `tabBin`.`warehouse`='s2' THEN `tabBin`.`actual_qty` ELSE 0 END) `B` FROM `tabBin` JOIN `tabItem` ON `tabBin`.`item_code`=`tabItem`.`name` WHERE `tabItem`.`brand` IN ('brand1','brand2') AND `tabItem`.`item_group` IN ('Discaount Group','With Out Discaount')

I believe this query is exactly what you need.

If Pypika seems confusing to you or you hesitate to use it, try using this query instead

10

Sorry, I’ve added my code as an image to make it easier to read. If you’d like to copy it, here it is


import frappe

def execute(filters=None):
	from frappe.query_builder import DocType, Case
	from pypika.functions import Sum
	item = DocType('Item')
	bin_ = DocType('Bin')
	case1 = Sum(Case().when(bin_.warehouse == 's1', bin_.actual_qty).else_(0)).as_('A')
	case2 = Sum(Case().when(bin_.warehouse == 's2', bin_.actual_qty).else_(0)).as_('B')
	query = (
		frappe.qb.from_(bin_)
			.select(bin_.item_code, item.item_name, case1, case2)
			.join(item).on(bin_.item_code == item.name)
			.where(item.brand.isin(filters.get('brand')))
			.where(item.item_group.isin(('Discaount Group','With Out Discaount')))
	)
	print(query)
	data = query.run(as_dict=1)
	columns = [
		{
			'fieldname': 'item_code',
			'fieldtype': 'Data',
			'label': 'Item'
		},
		{
			'fieldname':'item_name',
			'fieldtype':'Data',
			'label':'Brand'
		},
		{
			'fieldname':'a',
			'fieldtype':'Data',
			'label':'A'
		},
		{
			'fieldname':'b',
			'fieldtype':'Data',
			'label':'B'
		}
	]
	return columns, data
11

i use script report

where should i paste code?

in python script or java ?

12

In python

14

@osk You need to write code in your_custom_app/app_module/report/custom_report/custom_report.py. If you’re using a script field, you’ll need to write the code slightly differently

15

@Osk

Please refer to this tutorial for getting familiar with script reports and specifically using multiselect data (towards the end of the tutorial) [Tutorial] Script Report / Chart

16

Please check the default report example for a better understanding.

1 Like
17

thank you