New Report Employee Advance Summary (#13040)
* New Report Employee Advance Summary
* JSON style column definition
diff --git a/erpnext/hr/report/employee_advance_summary/__init__.py b/erpnext/hr/report/employee_advance_summary/__init__.py
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/erpnext/hr/report/employee_advance_summary/__init__.py
diff --git a/erpnext/hr/report/employee_advance_summary/employee_advance_summary.js b/erpnext/hr/report/employee_advance_summary/employee_advance_summary.js
new file mode 100644
index 0000000..528ae4c
--- /dev/null
+++ b/erpnext/hr/report/employee_advance_summary/employee_advance_summary.js
@@ -0,0 +1,41 @@
+// Copyright (c) 2016, Frappe Technologies Pvt. Ltd. and contributors
+// For license information, please see license.txt
+/* eslint-disable */
+
+frappe.query_reports["Employee Advance Summary"] = {
+ "filters": [
+ {
+ "fieldname":"employee",
+ "label": __("Employee"),
+ "fieldtype": "Link",
+ "options": "Employee",
+ "width": "80"
+ },
+ {
+ "fieldname":"from_date",
+ "label": __("From Date"),
+ "fieldtype": "Date",
+ "default": frappe.defaults.get_user_default("year_start_date"),
+ "width": "80"
+ },
+ {
+ "fieldname":"to_date",
+ "label": __("To Date"),
+ "fieldtype": "Date",
+ "default": frappe.datetime.get_today()
+ },
+ {
+ "fieldname":"company",
+ "label": __("Company"),
+ "fieldtype": "Link",
+ "options": "Company"
+ },
+ {
+ "fieldname":"status",
+ "label": __("Status"),
+ "fieldtype": "Select",
+ "options": "\nDraft\nPaid\nUnpaid\nClaimed\nCancelled"
+ }
+ ]
+};
+
diff --git a/erpnext/hr/report/employee_advance_summary/employee_advance_summary.json b/erpnext/hr/report/employee_advance_summary/employee_advance_summary.json
new file mode 100644
index 0000000..60afd59
--- /dev/null
+++ b/erpnext/hr/report/employee_advance_summary/employee_advance_summary.json
@@ -0,0 +1,26 @@
+{
+ "add_total_row": 1,
+ "apply_user_permissions": 1,
+ "creation": "2018-02-21 07:12:37.299923",
+ "disabled": 0,
+ "docstatus": 0,
+ "doctype": "Report",
+ "idx": 0,
+ "is_standard": "Yes",
+ "modified": "2018-02-22 13:33:41.532005",
+ "modified_by": "Administrator",
+ "module": "HR",
+ "name": "Employee Advance Summary",
+ "owner": "Administrator",
+ "ref_doctype": "Employee Advance",
+ "report_name": "Employee Advance Summary",
+ "report_type": "Script Report",
+ "roles": [
+ {
+ "role": "Employee"
+ },
+ {
+ "role": "Expense Approver"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/erpnext/hr/report/employee_advance_summary/employee_advance_summary.py b/erpnext/hr/report/employee_advance_summary/employee_advance_summary.py
new file mode 100644
index 0000000..363e31d
--- /dev/null
+++ b/erpnext/hr/report/employee_advance_summary/employee_advance_summary.py
@@ -0,0 +1,104 @@
+# Copyright (c) 2013, Frappe Technologies Pvt. Ltd. and contributors
+# For license information, please see license.txt
+
+from __future__ import unicode_literals
+import frappe
+from frappe import msgprint, _
+
+def execute(filters=None):
+ if not filters: filters = {}
+
+ advances_list = get_advances(filters)
+ columns = get_columns()
+
+ if not advances_list:
+ msgprint(_("No record found"))
+ return columns, advances_list
+
+ data = []
+ for advance in advances_list:
+ row = [advance.name, advance.employee, advance.company, advance.posting_date,
+ advance.advance_amount, advance.paid_amount, advance.claimed_amount, advance.status]
+ data.append(row)
+
+ return columns, data
+
+
+def get_columns():
+ return [
+ {
+ "label": _("Title"),
+ "fieldname": "title",
+ "fieldtype": "Link",
+ "options": "Employee Advance",
+ "width": 120
+ },
+ {
+ "label": _("Employee"),
+ "fieldname": "employee",
+ "fieldtype": "Link",
+ "options": "Employee",
+ "width": 120
+ },
+ {
+ "label": _("Company"),
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "options": "Company",
+ "width": 120
+ },
+ {
+ "label": _("Posting Date"),
+ "fieldname": "posting_date",
+ "fieldtype": "Date",
+ "width": 120
+ },
+ {
+ "label": _("Advance Amount"),
+ "fieldname": "advance_amount",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Paid Amount"),
+ "fieldname": "paid_amount",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Claimed Amount"),
+ "fieldname": "claimed_amount",
+ "fieldtype": "Currency",
+ "width": 120
+ },
+ {
+ "label": _("Status"),
+ "fieldname": "status",
+ "fieldtype": "Data",
+ "width": 120
+ }
+ ]
+
+def get_conditions(filters):
+ conditions = ""
+
+ if filters.get("employee"):
+ conditions += "and employee = %(employee)s"
+ if filters.get("company"):
+ conditions += " and company = %(company)s"
+ if filters.get("status"):
+ conditions += " and status = %(status)s"
+ if filters.get("from_date"):
+ conditions += " and posting_date>=%(from_date)s"
+ if filters.get("to_date"):
+ conditions += " and posting_date<=%(to_date)s"
+
+ return conditions
+
+def get_advances(filters):
+ conditions = get_conditions(filters)
+ return frappe.db.sql("""select name, employee, paid_amount, status, advance_amount, claimed_amount, company,
+ posting_date, purpose
+ from `tabEmployee Advance`
+ where docstatus<2 %s order by posting_date, name desc""" %
+ conditions, filters, as_dict=1)