def execute(filters=None): priority_map = {"High": 3, "Medium": 2, "Low": 1} todo_list = runreport( doctype="ToDo", fields=["name", "date", "description", "priority", "reference_type", "reference_name", "assigned_by", "owner"], filters=[["ToDo", "status", "=", "Open"]], ) todo_list.sort( key=lambda todo: ( priority_map.get(todo.priority, 0), todo.date and getdate(todo.date) or getdate("1900-01-01"), ), reverse=True, ) columns = [ _("ID") + ":Link/ToDo:90", _("Priority") + "::60", _("Date") + ":Date", _("Description") + "::150", _("Assigned To/Owner") + ":Data:120", _("Assigned By") + ":Data:120", _("Reference") + "::200", ] result = [] for todo in todo_list: if todo.owner == frappe.session.user or todo.assigned_by == frappe.session.user: if todo.reference_type: todo.reference = """<a href="#Form/%s/%s">%s: %s</a>""" % ( todo.reference_type, todo.reference_name, todo.reference_type, todo.reference_name, ) else: todo.reference = None result.append( [todo.name, todo.priority, todo.date, todo.description, todo.owner, todo.assigned_by, todo.reference] ) return columns, result
def execute(filters=None): priority_map = {"High": 3, "Medium": 2, "Low": 1} todo_list = runreport(doctype="ToDo", fields=[ "name", "date", "description", "priority", "reference_type", "reference_name", "assigned_by", "owner" ], filters=[["ToDo", "status", "=", "Open"]]) todo_list.sort(key=lambda todo: (priority_map.get(todo.priority, 0), todo.date and getdate( todo.date) or getdate("1900-01-01")), reverse=True) columns = [ _("ID") + ":Link/ToDo:90", _("Priority") + "::60", _("Date") + ":Date", _("Description") + "::150", _("Assigned To/Owner") + ":Data:120", _("Assigned By") + ":Data:120", _("Reference") + "::200" ] result = [] for todo in todo_list: if todo.owner == frappe.session.user or todo.assigned_by == frappe.session.user: if todo.reference_type: todo.reference = """<a href="#Form/%s/%s">%s: %s</a>""" % ( todo.reference_type, todo.reference_name, todo.reference_type, todo.reference_name) else: todo.reference = None result.append([ todo.name, todo.priority, todo.date, todo.description, todo.owner, todo.assigned_by, todo.reference ]) return columns, result
def execute(filters=None): if not filters: filters = {} employee_filters = filters.get("company") and \ [["Employee", "company", "=", filters.get("company")]] or None employees = runreport(doctype="Employee", fields=["name", "employee_name", "department"], filters=employee_filters, limit_page_length=None) if not employees: frappe.throw(_("No employee found!")) leave_types = frappe.db.sql_list("select name from `tabLeave Type`") if filters.get("fiscal_year"): fiscal_years = [filters["fiscal_year"]] else: fiscal_years = frappe.db.sql_list("select name from `tabFiscal Year` order by name desc") employee_names = [d.name for d in employees] allocations = frappe.db.sql("""select employee, fiscal_year, leave_type, total_leaves_allocated from `tabLeave Allocation` where docstatus=1 and employee in (%s)""" % ','.join(['%s']*len(employee_names)), employee_names, as_dict=True) applications = frappe.db.sql("""select employee, fiscal_year, leave_type, SUM(total_leave_days) as leaves from `tabLeave Application` where status="Approved" and docstatus = 1 and employee in (%s) group by employee, fiscal_year, leave_type""" % ','.join(['%s']*len(employee_names)), employee_names, as_dict=True) columns = [ _("Fiscal Year"), _("Employee") + ":Link/Employee:150", _("Employee Name") + "::200", _("Department") +"::150" ] for leave_type in leave_types: columns.append(_(leave_type) + " " + _("Allocated") + ":Float") columns.append(_(leave_type) + " " + _("Taken") + ":Float") columns.append(_(leave_type) + " " + _("Balance") + ":Float") data = {} for d in allocations: data.setdefault((d.fiscal_year, d.employee, d.leave_type), frappe._dict()).allocation = d.total_leaves_allocated for d in applications: data.setdefault((d.fiscal_year, d.employee, d.leave_type), frappe._dict()).leaves = d.leaves result = [] for fiscal_year in fiscal_years: for employee in employees: row = [fiscal_year, employee.name, employee.employee_name, employee.department] result.append(row) for leave_type in leave_types: tmp = data.get((fiscal_year, employee.name, leave_type), frappe._dict()) row.append(tmp.allocation or 0) row.append(tmp.leaves or 0) row.append((tmp.allocation or 0) - (tmp.leaves or 0)) return columns, result
def execute(filters=None): if not filters: filters = {} employee_filters = {"status": "Active"} if filters.get("company"): filters["company"] = filters.company employees = runreport(doctype="Employee", fields=["name", "employee_name", "department"], filters=employee_filters, limit_page_length=None) if not employees: frappe.throw(_("No employee found!")) leave_types = frappe.db.sql_list("select name from `tabLeave Type`") employee_names = [d.name for d in employees] allocations = frappe.db.sql( """select employee, leave_type, sum(new_leaves_allocated) as leaves_allocated from `tabLeave Allocation` where docstatus=1 and employee in (%s) and from_date >= '%s' and to_date <= '%s'""" % (','.join(['%s'] * len(employee_names)), filters.get("from_date"), filters.get("to_date")), employee_names, as_dict=True) applications = frappe.db.sql( """select employee, leave_type, SUM(total_leave_days) as leaves from `tabLeave Application` where status="Approved" and docstatus = 1 and employee in (%s) and from_date >= '%s' and to_date <= '%s' group by employee, leave_type""" % (','.join(['%s'] * len(employee_names)), filters.get("from_date"), filters.get("to_date")), employee_names, as_dict=True) columns = [ _("Employee") + ":Link/Employee:150", _("Employee Name") + "::200", _("Department") + "::150" ] for leave_type in leave_types: columns.append(_(leave_type) + " " + _("Opening") + ":Float") columns.append(_(leave_type) + " " + _("Taken") + ":Float") columns.append(_(leave_type) + " " + _("Balance") + ":Float") data = {} for d in allocations: data.setdefault((d.employee, d.leave_type), frappe._dict()).allocation = d.leaves_allocated for d in applications: data.setdefault((d.employee, d.leave_type), frappe._dict()).leaves = d.leaves result = [] for employee in employees: row = [employee.name, employee.employee_name, employee.department] result.append(row) for leave_type in leave_types: tmp = data.get((employee.name, leave_type), frappe._dict()) row.append(tmp.allocation or 0) row.append(tmp.leaves or 0) row.append((tmp.allocation or 0) - (tmp.leaves or 0)) return columns, result
def execute(filters=None): if not filters: filters = {} employee_filters = { "status": "Active" } if filters.get("company"): filters["company"] = filters.company employees = runreport(doctype="Employee", fields=["name", "employee_name", "department"], filters=employee_filters, limit_page_length=None) if not employees: frappe.throw(_("No employee found!")) leave_types = frappe.db.sql_list("select name from `tabLeave Type`") employee_names = [d.name for d in employees] allocations = frappe.db.sql("""select employee, leave_type, sum(new_leaves_allocated) as leaves_allocated from `tabLeave Allocation` where docstatus=1 and employee in (%s) and from_date >= '%s' and to_date <= '%s'""" % (','.join(['%s']*len(employee_names)), filters.get("from_date"), filters.get("to_date")), employee_names, as_dict=True) applications = frappe.db.sql("""select employee, leave_type, SUM(total_leave_days) as leaves from `tabLeave Application` where status="Approved" and docstatus = 1 and employee in (%s) and from_date >= '%s' and to_date <= '%s' group by employee, leave_type""" % (','.join(['%s']*len(employee_names)), filters.get("from_date"), filters.get("to_date")), employee_names, as_dict=True) columns = [ _("Employee") + ":Link/Employee:150", _("Employee Name") + "::200", _("Department") +"::150" ] for leave_type in leave_types: columns.append(_(leave_type) + " " + _("Opening") + ":Float") columns.append(_(leave_type) + " " + _("Taken") + ":Float") columns.append(_(leave_type) + " " + _("Balance") + ":Float") data = {} for d in allocations: data.setdefault((d.employee,d.leave_type), frappe._dict()).allocation = d.leaves_allocated for d in applications: data.setdefault((d.employee, d.leave_type), frappe._dict()).leaves = d.leaves result = [] for employee in employees: row = [employee.name, employee.employee_name, employee.department] result.append(row) for leave_type in leave_types: tmp = data.get((employee.name, leave_type), frappe._dict()) row.append(tmp.allocation or 0) row.append(tmp.leaves or 0) row.append((tmp.allocation or 0) - (tmp.leaves or 0)) return columns, result