@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);
}
});
}
}
