Hi,
I was working on custom app for Job Card Calendar and I seek for proper way to convert filters that come from view to Query Builder feature with .where()
Here what I’ve got
hook.py
doctype_calendar_js = {"Job Card" : "public/js/job_card_calendar.js"}
public/js/job_card_calendar.js
frappe.views.Calendar = frappe.views.Calendar.extend({
init: function(options) {
$.extend(true, this, options);
this.get_events_method='jobcard_planning.controllers.jobcard_planning.get_jobcard_planning_details';
this.field_map= {
"start": "from_time",
"end": "to_time",
"id": "name",
"title": "subject",
"color": "color",
"allDay": "allDay",
"progress": "progress"
};
this.filters= [
{
"fieldtype": "Link",
"fieldname": "planned_employee",
"options": "Employee",
"label": __("Planned Employee")
}
];
}
this.get_default_options();
},
})
jobcard_planning.py
@frappe.whitelist()
def get_jobcard_planning_details(start, end, filters=None):
JobCard = frappe.qb.DocType("Job Card")
JobCardTimeLog = frappe.qb.DocType("Job Card Time Log")
job_cards_query = (
frappe.qb.from_(JobCard)
.inner_join(JobCardTimeLog)
.on(JobCard.name == JobCardTimeLog.parent)
.groupby(JobCard.name)
.having(Min(JobCardTimeLog.from_time) >= start)
.having(Max(JobCardTimeLog.from_time) <= end)
.select(
JobCard.name,
JobCard.work_order,
JobCard.status,
JobCard.remarks,
JobCard.planned_start_date,
JobCard.planned_end_date,
Min(JobCardTimeLog.from_time).as_('initial_start_date'),
)
)
job_cards = job_cards_query.run(as_dict=1)
In the original method job_cat.get_job_details there was
from frappe.desk.reportview import get_filters_cond
conditions = get_filters_cond("Job Card", filters, [])
job_cards = frappe.db.sql(
""" SELECT `tabJob Card`.name, `tabJob Card`.work_order,
`tabJob Card`.status, ifnull(`tabJob Card`.remarks, ''),
min(`tabJob Card Time Log`.from_time) as from_time,
max(`tabJob Card Time Log`.to_time) as to_time
FROM `tabJob Card` , `tabJob Card Time Log`
WHERE
`tabJob Card`.name = `tabJob Card Time Log`.parent {0}
group by `tabJob Card`.name""".format(
conditions
),
as_dict=1,
)
Here conditions are build from reportview.get_filters_cond and return a string.
Is there somewhere a way to get .where() conditions ?
I’ve search into develop branch and the only things I’ve found is into stock.report.stock_balance.apply_conditions(query, filters) where there is manual things like
if filter=to_date then .where(sle.to_date <= todate)...
Well I’m sure I didn’t dig enough but my eyes are tired and start to go away. I send a bottle in the sea
Thanks