def get_result(data, timegrain, from_date, to_date): dates = get_dates_from_timegrain(from_date, to_date, timegrain) result = [[date, 0] for date in dates] data_index = 0 if data: for i, d in enumerate(result): while data_index < len(data) and getdate(data[data_index][0]) <= d[0]: d[1] += data[data_index][1] data_index += 1 return result
def prepare_chart_data(self, data): date_range = get_dates_from_timegrain(self.filters.from_date, self.filters.to_date, self.filters.range) if self.filters.range == "Monthly": date_range = [frappe.utils.add_days(dd, 1) for dd in date_range] labels = [] total_dataset = [] unique_dataset = [] def get_data_for_date(date): for item in data: item_date = getdate(item.get("date")) if item_date == date: return item return {'count': 0, 'unique_count': 0} for date in date_range: labels.append(date.strftime("%b %d %Y")) match = get_data_for_date(date) total_dataset.append(match.get('count', 0)) unique_dataset.append(match.get('unique_count', 0)) chart = { "data": { 'labels': labels, 'datasets': [{ 'name': "Total Views", 'type': 'line', 'values': total_dataset }, { 'name': "Unique Visits", 'type': 'line', 'values': unique_dataset }] }, "type": "axis-mixed", 'lineOptions': { 'regionFill': 1, }, 'axisOptions': { 'xIsSeries': 1 }, 'colors': ['#7cd6fd', '#5e64ff'] } return chart
def get_result(data, timegrain, from_date, to_date, chart_type): dates = get_dates_from_timegrain(from_date, to_date, timegrain) result = [[date, 0] for date in dates] data_index = 0 if data: for i, d in enumerate(result): count = 0 while data_index < len(data) and getdate(data[data_index][0]) <= d[0]: d[1] += data[data_index][1] count += data[data_index][2] data_index += 1 if chart_type == 'Average' and not count == 0: d[1] = d[1]/count if chart_type == 'Count': d[1] = count return result
def execute(filters=None): if getdate(filters.fromdate).year != getdate(filters.todate).year: frappe.throw(_("From Date and To Date should be in the same year")) return [], [] if filters.fromdate > filters.todate: frappe.throw(_("To Date Can Not be Less Then From Date")) return [], [] columns, data = [], [] #columns=["Ckin Date","Day Name","In Time","Out Time","Working Hours","Attendance Status","Pending Leave App","Pending Attendace Request"] columns = [ { "label": _("Ckeckin Date"), "fieldtype": "Date", "fieldname": "ckin_date" }, { "label": _("Day Name"), "fieldtype": "Data", "fieldname": "day_name", "width": 150 }, { "label": _("In Time"), "fieldtype": "Time", "fieldname": "in_iime" }, { "label": _("Out Time"), "fieldtype": "Time", "fieldname": "out_iime" }, { "label": _("Working Hours"), "fieldtype": "Time", "fieldname": "working_hours" }, { "label": _("Attendance Status"), "fieldtype": "Data", "fieldname": "attendance_status" }, { "label": _("Leave Application"), "fieldtype": "Link", "fieldname": "pending_leave_app", "options": "Leave Application", "width": 150 }, { "label": _("Attendace Request"), "fieldtype": "Link", "fieldname": "pending_attendace_request", "options": "Attendance Request", "width": 150 }, ] start_date = filters.get("fromdate") end_date = filters.get("todate") for d in get_dates_from_timegrain(filters.get("fromdate"), filters.get("todate")): row = [d, frappe.utils.data.get_weekday(d), '', '', '', '', '', ''] data.append(row) conditions = get_conditions(filters) sq = frappe.db.sql( """SELECT date(time) as ckin_date,time(min(time)) as in_time,time(max(time)) as out_time,TIMEDIFF(time(max(time)),time(min(time))) as working_hours FROM `tabEmployee Checkin` %s""" % conditions, as_dict=1) # frappe.throw("{0}".format(sq)) for at in sq: for dd in data: if at.ckin_date == dd[0]: dd[2] = at.in_time dd[4] = at.working_hours if at.in_time != at.out_time: dd[3] = at.out_time emp_hl_conditions = get_emp_hl_conditions(filters) ho_list = frappe.db.sql( """SELECT case when emp.holiday_list is null then shift.holiday_list else emp.holiday_list end as emp_holiday_list FROM tabEmployee emp left join `tabShift Type` shift on emp.default_shift=shift.name %s""" % emp_hl_conditions, as_dict=1) stremp_hl = ho_list[0].emp_holiday_list holidays_conditions = get_holidays_conditions(filters, stremp_hl) # holidays_conditions=get_holidays_conditions(filters) holidays_list = frappe.db.sql( """SELECT distinct(holiday_date)as holiday_date FROM tabHoliday %s""" % holidays_conditions, as_dict=1) for hdate in holidays_list: for dd5 in data: if hdate.holiday_date == dd5[0]: dd5[5] = 'Holiday' att_conditions = get_att_conditions(filters) emp_attendance = frappe.db.sql( """SELECT attendance_date,status FROM tabAttendance %s""" % att_conditions, as_dict=1) for at2 in emp_attendance: for dd2 in data: if at2.attendance_date == dd2[0]: dd2[5] = at2.status str_date = "" for dd3 in data: if (dd3[5] == '' or dd3[5] == 'Half Day') and dd3[1] != 'Friday' and dd3[1] != 'Saturday': # leave_conditions=get_leave_conditions(filters,parse_date(dd3[0])) str_date = dd3[0].strftime("%Y-%m-%d") leave_conditions = get_leave_conditions(filters, str_date) emp_leave = frappe.db.sql( """SELECT name FROM `tabLeave Application` %s""" % leave_conditions, as_dict=1) emp_att_req = frappe.db.sql( """SELECT name FROM `tabAttendance Request` %s""" % leave_conditions, as_dict=1) for le in emp_leave: dd3[6] = le.name for ar in emp_att_req: dd3[7] = ar.name total_p = 0.0 total_l = 0.0 total_a = 0.0 total_h = 0.0 total_hd = 0.0 total_unkown = 0.0 for dt in data: if dt[5] == 'Present': total_p += 1 elif dt[5] == 'Absent': total_a += 1 elif dt[5] == 'On Leave': total_l += 1 elif dt[5] == 'Half Day': total_hd += 1 elif dt[5] == 'Holiday': total_h += 1 elif dt[5] == '': total_unkown += 1 row = [None, 'Total Present', '', '', '', total_p, '', ''] data.append(row) row = [None, 'Total Absent', '', '', '', total_a, '', ''] data.append(row) row = [None, 'Total Leaves', '', '', '', total_l, '', ''] data.append(row) row = [None, 'Total Half Days', '', '', '', total_hd, '', ''] data.append(row) row = [None, 'Total Holidays', '', '', '', total_h, '', ''] data.append(row) row = [None, 'Total Not Submitted', '', '', '', total_unkown, '', ''] data.append(row) return columns, data