How to show various DocTypes in one Calendar view

ERPNext Customize ERPNext
7

Hi Pedro:

Once thing at a time :slight_smile:

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! :wink:

image
image946×585 24.2 KB

Play and try to custom it as your requirements: color, texts, etc …
Hope this helps.

10 Likes