fix order labels and add purchase order items overdue (#15436)
diff --git a/erpnext/setup/doctype/email_digest/email_digest.json b/erpnext/setup/doctype/email_digest/email_digest.json
index 12f275c..da95b25 100644
--- a/erpnext/setup/doctype/email_digest/email_digest.json
+++ b/erpnext/setup/doctype/email_digest/email_digest.json
@@ -4,7 +4,7 @@
"allow_rename": 0,
"autoname": "Prompt",
"beta": 0,
- "creation": "2013-02-21 14:15:31",
+ "creation": "2018-09-16 22:00:00",
"custom": 0,
"description": "Send regular summary reports via Email.",
"docstatus": 0,
@@ -568,6 +568,90 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "fieldname": "work_in_progress",
+ "fieldtype": "Column Break",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Work in Progress",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "sales_orders_to_bill",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Sales Orders to Bill",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "purchase_orders_to_bill",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Purchase Orders to Bill",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"fieldname": "operation",
"fieldtype": "Section Break",
"hidden": 0,
@@ -651,34 +735,6 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
- "fieldname": "pending_sales_orders",
- "fieldtype": "Check",
- "hidden": 0,
- "ignore_user_permissions": 0,
- "ignore_xss_filter": 0,
- "in_filter": 0,
- "in_list_view": 0,
- "in_standard_filter": 0,
- "label": "Pending Sales Orders",
- "length": 0,
- "no_copy": 0,
- "permlevel": 0,
- "precision": "",
- "print_hide": 0,
- "print_hide_if_no_value": 0,
- "read_only": 0,
- "remember_last_selected_value": 0,
- "report_hide": 0,
- "reqd": 0,
- "search_index": 0,
- "set_only_once": 0,
- "unique": 0
- },
- {
- "allow_on_submit": 0,
- "bold": 0,
- "collapsible": 0,
- "columns": 0,
"fieldname": "purchase_order",
"fieldtype": "Check",
"hidden": 0,
@@ -686,7 +742,7 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
- "in_standard_filter": 0,
+ "in_standard_filter": 0,
"label": "New Purchase Orders",
"length": 0,
"no_copy": 0,
@@ -707,15 +763,15 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
- "fieldname": "pending_purchase_orders",
+ "fieldname": "sales_orders_to_deliver",
"fieldtype": "Check",
"hidden": 0,
"ignore_user_permissions": 0,
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
- "in_standard_filter": 0,
- "label": "Pending Purchase Orders",
+ "in_standard_filter": 0,
+ "label": "Sales Orders to Deliver",
"length": 0,
"no_copy": 0,
"permlevel": 0,
@@ -735,6 +791,34 @@
"bold": 0,
"collapsible": 0,
"columns": 0,
+ "fieldname": "purchase_orders_to_receive",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Purchase Orders to Receive",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
"fieldname": "column_break_operation",
"fieldtype": "Column Break",
"hidden": 0,
@@ -798,8 +882,8 @@
"ignore_xss_filter": 0,
"in_filter": 0,
"in_list_view": 0,
- "in_standard_filter": 0,
- "label": "Pending Quotations",
+ "in_standard_filter": 0,
+ "label": "Open Quotations",
"length": 0,
"no_copy": 0,
"permlevel": 0,
@@ -869,6 +953,34 @@
"search_index": 0,
"set_only_once": 0,
"unique": 0
+ },
+ {
+ "allow_on_submit": 0,
+ "bold": 0,
+ "collapsible": 0,
+ "columns": 0,
+ "fieldname": "purchase_orders_items_overdue",
+ "fieldtype": "Check",
+ "hidden": 0,
+ "ignore_user_permissions": 0,
+ "ignore_xss_filter": 0,
+ "in_filter": 0,
+ "in_list_view": 0,
+ "in_standard_filter": 0,
+ "label": "Purchase Orders Items Overdue",
+ "length": 0,
+ "no_copy": 0,
+ "permlevel": 0,
+ "precision": "",
+ "print_hide": 0,
+ "print_hide_if_no_value": 0,
+ "read_only": 0,
+ "remember_last_selected_value": 0,
+ "report_hide": 0,
+ "reqd": 0,
+ "search_index": 0,
+ "set_only_once": 0,
+ "unique": 0
},
{
"allow_on_submit": 0,
@@ -1079,7 +1191,7 @@
"istable": 0,
"max_attachments": 0,
"menu_index": 0,
- "modified": "2016-11-07 05:10:32.190134",
+ "modified": "2018-09-16 22:00:00.000000",
"modified_by": "Administrator",
"module": "Setup",
"name": "Email Digest",
diff --git a/erpnext/setup/doctype/email_digest/email_digest.py b/erpnext/setup/doctype/email_digest/email_digest.py
index e2189a1..d309e88 100644
--- a/erpnext/setup/doctype/email_digest/email_digest.py
+++ b/erpnext/setup/doctype/email_digest/email_digest.py
@@ -96,7 +96,13 @@
quote = get_random_quote()
context.quote = {"text": quote[0], "author": quote[1]}
- if not (context.events or context.todo_list or context.notifications or context.cards):
+ if self.get("purchase_orders_items_overdue"):
+ context.purchase_order_list, context.purchase_orders_items_overdue_list = self.get_purchase_orders_items_overdue_list()
+ if not context.purchase_order_list:
+ frappe.throw(_("No items to be received are overdue"))
+
+ if not (context.events or context.todo_list or context.notifications or context.cards
+ or context.purchase_orders_items_overdue_list):
return None
frappe.flags.ignore_account_permission = False
@@ -230,9 +236,10 @@
cache = frappe.cache()
context.cards = []
- for key in ("income", "expenses_booked", "income_year_to_date","expense_year_to_date",
- "new_quotations","pending_quotations","sales_order","purchase_order","pending_sales_orders","pending_purchase_orders",
- "invoiced_amount", "payables", "bank_balance", "credit_balance"):
+ for key in ("income", "expenses_booked", "income_year_to_date", "expense_year_to_date",
+ "bank_balance", "credit_balance", "invoiced_amount", "payables",
+ "sales_orders_to_bill", "purchase_orders_to_bill", "sales_order", "purchase_order",
+ "sales_orders_to_deliver", "purchase_orders_to_receive", "new_quotations", "pending_quotations"):
if self.get(key):
cache_key = "email_digest:card:{0}:{1}:{2}:{3}".format(self.company, self.frequency, key, self.from_date)
card = cache.get(cache_key)
@@ -346,6 +353,62 @@
return balance, past_balance, count
+ def get_sales_orders_to_bill(self):
+ """Get value not billed"""
+
+ value, count = frappe.db.sql("""select ifnull((sum(grand_total)) - (sum(grand_total*per_billed/100)),0),
+ count(*) from `tabSales Order`
+ where (transaction_date <= %(to_date)s) and billing_status != "Fully Billed"
+ and status not in ('Closed','Cancelled', 'Completed') """, {"to_date": self.future_to_date})[0]
+
+ return {
+ "label": self.meta.get_label("sales_orders_to_bill"),
+ "value": value,
+ "count": count
+ }
+
+ def get_sales_orders_to_deliver(self):
+ """Get value not delivered"""
+
+ value, count = frappe.db.sql("""select ifnull((sum(grand_total)) - (sum(grand_total*per_delivered/100)),0),
+ count(*) from `tabSales Order`
+ where (transaction_date <= %(to_date)s) and delivery_status != "Fully Delivered"
+ and status not in ('Closed','Cancelled', 'Completed') """, {"to_date": self.future_to_date})[0]
+
+ return {
+ "label": self.meta.get_label("sales_orders_to_deliver"),
+ "value": value,
+ "count": count
+ }
+
+ def get_purchase_orders_to_receive(self):
+ """Get value not received"""
+
+ value, count = frappe.db.sql("""select ifnull((sum(grand_total))-(sum(grand_total*per_received/100)),0),
+ count(*) from `tabPurchase Order`
+ where (transaction_date <= %(to_date)s) and per_received < 100
+ and status not in ('Closed','Cancelled', 'Completed') """, {"to_date": self.future_to_date})[0]
+
+ return {
+ "label": self.meta.get_label("purchase_orders_to_receive"),
+ "value": value,
+ "count": count
+ }
+
+ def get_purchase_orders_to_bill(self):
+ """Get purchase not billed"""
+
+ value, count = frappe.db.sql("""select ifnull((sum(grand_total)) - (sum(grand_total*per_billed/100)),0),
+ count(*) from `tabPurchase Order`
+ where (transaction_date <= %(to_date)s) and per_billed < 100
+ and status not in ('Closed','Cancelled', 'Completed') """, {"to_date": self.future_to_date})[0]
+
+ return {
+ "label": self.meta.get_label("purchase_orders_to_bill"),
+ "value": value,
+ "count": count
+ }
+
def get_type_balance(self, fieldname, account_type, root_type=None):
if root_type:
@@ -529,6 +592,30 @@
else:
return fmt_money(value, currency=self.currency)
+ def get_purchase_orders_items_overdue_list(self):
+ fields_po = "distinct `tabPurchase Order Item`.parent as po"
+ fields_poi = "`tabPurchase Order Item`.parent, `tabPurchase Order Item`.schedule_date, item_code," \
+ "received_qty, qty - received_qty as missing_qty, rate, amount"
+
+ sql_po = """select {fields} from `tabPurchase Order Item`
+ left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
+ where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
+ and received_qty < qty order by `tabPurchase Order Item`.parent DESC,
+ `tabPurchase Order Item`.schedule_date DESC""".format(fields=fields_po)
+
+ sql_poi = """select {fields} from `tabPurchase Order Item`
+ left join `tabPurchase Order` on `tabPurchase Order`.name = `tabPurchase Order Item`.parent
+ where status<>'Closed' and `tabPurchase Order Item`.docstatus=1 and curdate() > `tabPurchase Order Item`.schedule_date
+ and received_qty < qty order by `tabPurchase Order Item`.idx""".format(fields=fields_poi)
+ purchase_order_list = frappe.db.sql(sql_po, as_dict=True)
+ purchase_order_items_overdue_list = frappe.db.sql(sql_poi, as_dict=True)
+
+ for t in purchase_order_items_overdue_list:
+ t.link = get_url_to_form("Purchase Order", t.parent)
+ t.rate = fmt_money(t.rate, 2, t.currency)
+ t.amount = fmt_money(t.amount, 2, t.currency)
+ return purchase_order_list, purchase_order_items_overdue_list
+
def send():
now_date = now_datetime().date()
diff --git a/erpnext/setup/doctype/email_digest/templates/default.html b/erpnext/setup/doctype/email_digest/templates/default.html
index 5a657d2..4ee4b0f 100644
--- a/erpnext/setup/doctype/email_digest/templates/default.html
+++ b/erpnext/setup/doctype/email_digest/templates/default.html
@@ -1,6 +1,6 @@
{% macro show_card(card) %}
-<div style="width: 50%; float:left; min-height: 80px; padding-top: 20px;">
- <h6 style="color: {{ text_muted }}; font-size: 12px; margin-bottom: 0px; margin-top: 0px;">{{ _(card.label) }}
+<div style="width: 49%; display:inline-block; vertical-align: top; min-height: 80px; padding-top: 20px;">
+ <h6 style="color: {{ text_muted }}; font-size: 12px; margin-bottom: 0px; margin-top: 0px;">{{ card.label }}
{% if card.count %}
<span class="badge">({{ card.count }})</span>
{% endif %}</h6>
@@ -180,5 +180,80 @@
<br>
</div>
{% endif %}
-
+
+<!-- Purchase Order Items Overdue -->
+{% if purchase_orders_items_overdue_list %}
+<h4 style="{{ section_head }}" class="text-center">{{ _("Purchase Order Items not received on time") }}</h4>
+<div>
+ <div style="background-color: #fafbfc;">
+ <hr>
+ <table style="width: 100%;">
+ <tr>
+ <th style="width: 40%;">
+ <span style="padding: 3px 7px; margin-right: 7px; font-weight: bold; {{ link_css }}">Item Code</span>
+ </th>
+ <th style="width: 20%; text-align: right">
+ <span style="padding: 3px 7px; margin-right: 7px; font-weight: bold; {{ link_css }}">Quantity</span>
+ </th>
+ <th style="width: 20%; text-align: right">
+ <span style="padding: 3px 7px; margin-right: 7px; font-weight: bold; {{ link_css }}">Rate</span>
+ </th>
+ <th style="width: 20%; text-align: right">
+ <span style="padding: 3px 7px; margin-right: 7px; font-weight: bold; {{ link_css }}">Amount</span>
+ </th>
+ </tr>
+ </table>
+ <hr>
+ </div>
+ <div>
+ {% for po in purchase_order_list %}
+ <div style="{{ line_item }}">
+ <table style="width: 100%;">
+ <tr>
+ <th>
+ <span style="padding: 3px 7px; margin-right: 7px; font-weight: bold;">{{ po.po }}</span>
+ </th>
+ </tr>
+ <tr>
+ <td>
+ {% for t in purchase_orders_items_overdue_list %}
+ {% if t.parent == po.po %}
+ <div >
+ <table style="width: 100%;">
+ <tr>
+ <td style="padding-left: 7px;">
+ <a style="width: 40%; {{ link_css }}" href="{{ t.link }}">{{ _(t.item_code) }}</a>
+ </td>
+ <td style="width: 20%; text-align: right">
+ <span style="{{ label_css }}">
+ {{ t.missing_qty }}
+ </span>
+ </td>
+ <td style="width: 20%; text-align: right">
+ <span style="{{ label_css }}">
+ {{ t.rate }}
+ </span>
+ </td>
+ <td style="width: 20%; text-align: right">
+ <span style="{{ label_css }}">
+ {{ t.amount }}
+ </span>
+ </td>
+ </tr>
+ </table>
+ </div>
+ {% endif %}
+ {% endfor %}
+ </td>
+ </tr>
+ </table>
+ </div>
+ {% endfor %}
+ </div>
+</div>
+<div class="text-center">
+ <br><br><span class="text-danger">Please take necessary action</span>
+</div>
+{% endif %}
+
</div>