Auto email weekly attendance report to each employee

Hi
I want to send a weekly attendance report to each employee by email, for this, I have written a custom report script, the report is working fine and the data is valid.


and hers is the PDF version of this report:

Now next I have to do is that each employee will get his/her weekly attendance report by email each Saturday. I have tried “Auto Email Report list” and tried sending the email to my self and here is the email:


So what should I do next that each employee will get his own report by email via erpnext ? is there any built-in feature for this or how can I set up this report to auto email each employee?

1 Like

You need to look at auto email report doctype.

Create 1 auto email report per employee and thats all you need, If you have written a custom script report yourself then you can create a auto email report as it is available from GUI.

You can find the documentation of the said doctype here
https://docs.erpnext.com/docs/v12/user/manual/en/setting-up/email/auto-email-reports

@Muhammad_Ashraf I hope you don’t mind me asking but any chance you could share your script? been looking for a way to achieve this but I haven’t been successful.

Thank you @fkardame,
I hope I get your point correctly. here when I try to set up a new auto email report (providing the same weekly report) for the next employee, it says the “Auto email report already exists”.

@flexy2ky sure I’m sharing the script below, first I created the new report with type script then create the folder of the same name as your report you can create a copy of any existing report folder in “/home/frappe/frappe-bench/apps/erpnext/erpnext/hr/report/”, rename the folder and files same as report name.
.
testing_att_report.py

from future import unicode_literals
import frappe
from frappe import utils
from frappe.utils import cstr, cint, getdate
from frappe import msgprint, _
from calendar import monthrange
from datetime import datetime, timedelta
import calendar

def execute(filters=None):
if not filters: filters = {}

columns = get_columns()
data = get_data(filters)

return columns, data

def get_columns():
columns = [
_(“Date”) + “::120”, _(“Day”) + “::140”, _(“Status”)+ “::120”,
_(“Check In”) + “::120”, _(“Check Out”) + “::120”,
_(“Total Hours”) + “::120”
]
return columns

def get_data(filters):
detail = []
delta = timedelta(days=1)
todaydate = utils.today()
dt = datetime.strptime(todaydate, ‘%Y-%m-%d’)
start_date = dt - timedelta(days=dt.weekday())
end_date = start_date + timedelta(days=6)
total_weekly_hours = 0.0

while start_date <= end_date:
	attendace_list = frappe.db.sql(""" select status, working_hours, name from tabAttendance where employee=%s and attendance_date=%s """, (filters.employee,str(start_date).split(" ")[0]))
	
	att_status = ""
	att_working_hours = ""
	checkin = ""
	checkout = ""
	if attendace_list:
		att_status = attendace_list[0][0]
		att_working_hours = attendace_list[0][1]
		print(attendace_list[0][2])
		checkinout = frappe.db.sql(""" select time from `tabEmployee Checkin` where attendance=%s """, (attendace_list[0][2]))
		print("+++++++++++++++")
		print(checkinout)
		if checkinout:
			checkin = str(checkinout[0][0]).split(" ")[1]
			checkout = str(checkinout[-1][0]).split(" ")[1]

	total = [str(start_date).split(" ")[0], str(calendar.day_name[start_date.weekday()]), att_status, checkin, checkout, att_working_hours]
	detail.append(total)
	if (att_working_hours):
		total_weekly_hours = float(total_weekly_hours) + float(str(att_working_hours))
	start_date += delta
x=["Weekly Hours","","","","",total_weekly_hours]
detail.append(x)
return detail

@frappe.whitelist()
def get_attendance_years():
year_list = frappe.db.sql_list(“”“select distinct YEAR(attendance_date) from tabAttendance ORDER BY YEAR(attendance_date) DESC”“”)
if not year_list:
year_list = [getdate().year]

return "\n".join(str(year) for year in year_list)

testing_att_report.js
// Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors

// License: GNU General Public License v3. See license.txt

frappe.query_reports[“testing_att_report”] = {

"filters": [

    {

        "fieldname":"month",

        "label": __("Month"),

        "fieldtype": "Select",

        "options": "Jan\nFeb\nMar\nApr\nMay\nJun\nJul\nAug\nSep\nOct\nNov\nDec",

        "default": ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov",

            "Dec"][frappe.datetime.str_to_obj(frappe.datetime.get_today()).getMonth()],

    },

    // {

    //  "fieldname":"week",

    //  "label": __("Week"),

    //  "fieldtype": "Select",

    //  "options": "1\n2\n3\n4",

    //  "reqd": 1

    // },

    {

        "fieldname":"year",

        "label": __("Year"),

        "fieldtype": "Select",

        "reqd": 1

    },

    {

        "fieldname":"employee",

        "label": __("Employee"),

        "fieldtype": "Link",

        "options": "Employee",

        "reqd": 1

    },

    {

        "fieldname":"company",

        "label": __("Company"),

        "fieldtype": "Link",

        "options": "Company",

        "default": frappe.defaults.get_user_default("Company"),

        "reqd": 1

    }

],

"onload": function() {

    return  frappe.call({

        method: "erpnext.hr.report.testing_att_report.testing_att_report.get_attendance_years",

        callback: function(r) {

            var year_filter = frappe.query_report.get_filter('year');

            year_filter.df.options = r.message;

            year_filter.df.default = r.message.split("\n")[0];

            year_filter.refresh();

            year_filter.set_input(year_filter.df.default);

        }

    });

}

}

Please use markdown code function when sharing codes and many characters go missing when you just copy paste in the text editor.

Please check my screenshot carefully.

This needs common sense.

So I have to create a report for each employee and against each report, I have to set up “Auto email report” provided email and report filter for that employee, right?

1 Like

Does anyone have a alternate solution to this ???