Hi Pedro:
Once thing at a time
1. Sql query.
“field1”, “field2” … are suggested symbolic names … you have to use real field names. Take a look to your doctypes, and locate the tables and the field names desired. Just a sample:
Event doctype: The table is named tabEvent
(always tab+doctype name) … Event doctype has some good candidates: starts_on
, ends_on
, subject
… Go to console (awesome bar and search: system console) and execute this SQL query:
SELECT * FROM
tabEvent;
This query return the table content, and you can see what data you want to use for calendar view … let’s say that the fields mentioned above. Ok, our sql query should be
SELECT subject, starts_on, ends_on FROM
tabEvent;
Now … Task Doctype, same pathway. I think that subject
, exp_start_date
and exp_end_date
fields contains desired data … so SELECT subject, exp_start_date, exp_end_date FROM
tabTask;
Finally, Leave application … Probably I’d choose employee_name
, from_date
and to_date
… query should be: SELECT employee_name, from_date, to_date FROM
tabLeave Application`
Result UNION query:
SELECT CONCAT('Event: ', subject) as subject, ‘#FF5733’ as color, starts_on as exp_start_date, ends_on as exp_end_date FROM tabEvent
UNION SELECT CONCAT('Task: ', subject) as subject, ‘#86F95C’ as color, exp_start_date as exp_start_date, exp_end_date as exp_end_date FROM tabTask
UNION SELECT CONCAT('Leave application: ', employee_name) as subject, ‘#5CA8F9’ as color, from_date as exp_start_date, to_date as exp_end_date FROM tabLeave Application
This query returns all data merged, with a column showing text “Event”, “Task” or “Leave application” followed by the event/task name or employee name to show in calendar.
3. get_events method
Try get_events_method: "the_factory_suite.events.get_events.get_events"
. First get_events is the .py file … second one is the method inside the file.
3. Mapped fields in client script
id
, allday
and progress
are not included in the data returned by get_events
method (probably it not make sense), so map only returned data. Note that fields exp_start_date
, exp_end_date
and subject
are exactly the names used in sql query behind “as”.
Client script:
frappe.views.calendar["TFS Combi View"] = {
field_map: {
"start": "exp_start_date",
"end": "exp_end_date",
"title": "subject",
"eventColor": "color"
},
gantt: true,
get_events_method: "the_factory_suite.events.get_events.get_events"
}
get_events.py:
import frappe
@frappe.whitelist()
def get_events():
"""Returns events for Gantt / Calendar view rendering.
"""
data = frappe.db.sql("""
SELECT CONCAT('Event: ', subject) as subject, '#FF5733' as color, starts_on as exp_start_date, ends_on as exp_end_date FROM `tabEvent` UNION SELECT CONCAT('Task: ', subject) as subject, '#86F95C' as color, exp_start_date as exp_start_date, exp_end_date as exp_end_date FROM `tabTask` UNION SELECT CONCAT('Leave application: ', employee_name) as subject, '#5CA8F9' as color, from_date as exp_start_date, to_date as exp_end_date FROM `tabLeave Application`
""", as_dict=True)
return data
As @buildwithhussain says … cooool!
Play and try to custom it as your requirements: color, texts, etc …
Hope this helps.