Hi guys,
i am trying to create a script report with filters… 1.Project filter 2. Building_name Filter
Project is doctype so it can be link field but Building_name is field in a seperate table so how it can be used as a link filter because we can’t give options attribute to it as it is compulsory…
script runs when page is loaded but when i select filters it dont return data because it is not executing the report.py file
Here is Js code:
frappe.query_reports[“Modify Property Price”] = {
“filters”: [
{
fieldname:“project”,
label: __(“Project”),
fieldtype: “Link”,
width: “300”,
options:“Project”,
“on_change”: function() {
var project = frappe.query_report.get_filter_value('project');
if(project=="")
{
frappe.query_report.filters[1].value="";
frappe.query_report.filters[1].refresh();
}
frappe.call({
method: "property_sales.property_sales.report.modify_property_price.modify_property_price.get_building_names",
args: {
'project':project
},
callback: function(r) {
var Building = frappe.query_report.get_filter_value('building_name');
frappe.query_report.filters[1].value="";
$.each(r.message, function(i, data) {
$('.input-with-feedback').append(new Option(data.building_name))
});
frappe.query_report.filters[1].refresh();
}
});
}
},
{
fieldname:"building_name",
label: __("Building Name"),
fieldtype: "Select",
width: "300",
"on_change": function() {
var project = frappe.query_report.get_filter_value('project');
var building_name = frappe.query_report.get_filter_value('building_name');
console.log(building_name)
if(project==""){
frappe.query_report.filters[1].value="";
frappe.query_report.filters[1].refresh();
}
}
]
}
Here is py code:
@frappe.whitelist()
def execute(filters):
columns = get_columns(filters)
data = get_data(filters)
return columns, data
@frappe.whitelist()
def get_columns(filters):
columns = [
{
"label": _("Plot Number"),
"fieldtype": "Data",
"fieldname": "plot_number",
"width": 100
},
{
"label": _("Bumi Lot"),
"fieldtype": "Float",
"fieldname": "bumi_lot",
"width": 100
},
{
"label": _("Unit Price"),
"fieldtype": "Float",
"fieldname": "unit_price",
"width": 100
},
{
"label": _("Min Unit Price"),
"fieldtype": "Float",
"fieldname": "min_unit_price",
"width": 100
},
{
"label": _("Land Area"),
"fieldtype": "Float",
"fieldname": "land_area",
"width": 100
},
{
"label": _("Land Area Measuurement"),
"fieldtype": "Data",
"fieldname": "land_area_measurement",
"width": 100
},
{
"label": _("Built Up Area"),
"fieldtype": "Float",
"fieldname": "built_up_area",
"width": 100
},
{
"label": _("Measurement"),
"fieldtype": "Data",
"fieldname": "measurement",
"width": 100
},
{
"label": _("Floor Level"),
"fieldtype": "Data",
"fieldname": "floor_level",
"width": 100
},
{
"label": _("Final Serial Number"),
"fieldtype": "Data",
"fieldname": "final_serial_number",
"width": 200
},
{
"label": _("Building Design"),
"fieldtype": "Data",
"fieldname": "building_design",
"width": 100
},
{
"label": _("House Number"),
"fieldtype": "Data",
"fieldname": "house_number",
"width": 100
},
{
"label": _("House Address"),
"fieldtype": "Data",
"fieldname": "house_address",
"width": 200
},
{
"label": _("Selling Properties"),
"fieldtype": "Data",
"fieldname": "modify_price",
"width": 110
}
]
return columns
@frappe.whitelist()
def get_data(filters):
sel_building_name = filters.get('building_name')
project_name = filters.get('project')
frappe.msgprint(project_name)
frappe.msgprint(sel_building_name)
modify_property_price = []
result_query = frappe.db.sql("""SELECT a.plot_number,a.bumi_lot,a.unit_price,a.min_unit_price,a.land_area, a.land_area_measurement, a.built_up_area,a.measurement,a.floor_level,a.final_serial_number,a.building_design,a.house_number,a.house_address,
CONCAT('<button type=''button'' data=''' , a.plot_number,''' onClick=''consoleerp_hi(this.getAttribute("data"))''>Modify Price</button>') as Button
from `tabPlot Unit Price` a
inner join `tabGenerate Sales Unit` b on b.name= a.parent where a.docstatus=1 and b.sel_building_name = '{sel_building_name}' and project_name='{project_name}'""".format(project_name=project_name,sel_building_name=sel_building_name),as_dict=True)
for rows in result_query:
row_object={
"plot_number":rows.plot_number,
"bumi_lot" : rows.bumi_lot,
"unit_price" : rows.unit_price,
"min_unit_price" : rows.min_unit_price,
"land_area" : rows.land_area,
"land_area_measurement" : rows.land_area_measurement,
"built_up_area" : rows.built_up_area,
"measurement" : rows.measurement,
"floor_level":rows.floor_level,
"final_serial_number":rows.final_serial_number,
"building_design" : rows.building_design,
"house_number":rows.house_number,
"house_address":rows.house_address,
"modify_price": rows.Button
}
modify_property_price.append(row_object)
return modify_property_price
ANY help will be highly appreciatable