Merge branch 'develop' into quo-status-fix
diff --git a/erpnext/patches.txt b/erpnext/patches.txt
index 0ea83fd..eaeebcf 100644
--- a/erpnext/patches.txt
+++ b/erpnext/patches.txt
@@ -673,3 +673,4 @@
erpnext.patches.v12_0.repost_stock_ledger_entries_for_target_warehouse
erpnext.patches.v12_0.update_end_date_and_status_in_email_campaign
erpnext.patches.v13_0.move_tax_slabs_from_payroll_period_to_income_tax_slab #123
+erpnext.patches.v12_0.fix_quotation_expired_status
diff --git a/erpnext/patches/v12_0/fix_quotation_expired_status.py b/erpnext/patches/v12_0/fix_quotation_expired_status.py
new file mode 100644
index 0000000..c8708d8
--- /dev/null
+++ b/erpnext/patches/v12_0/fix_quotation_expired_status.py
@@ -0,0 +1,34 @@
+import frappe
+
+def execute():
+ # fixes status of quotations which have status 'Expired' despite having valid sales order created
+
+ # filter out submitted expired quotations which has sales order created
+ cond = "qo.docstatus = 1 and qo.status = 'Expired'"
+ invalid_so_against_quo = """
+ SELECT
+ so.name FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so_item.docstatus = 1 and so.docstatus = 1
+ and so_item.parent = so.name
+ and so_item.prevdoc_docname = qo.name
+ and qo.valid_till < so.transaction_date""" # check if SO was created after quotation expired
+
+ frappe.db.sql(
+ """UPDATE `tabQuotation` qo SET qo.status = 'Expired' WHERE {cond} and exists({invalid_so_against_quo})"""
+ .format(cond=cond, invalid_so_against_quo=invalid_so_against_quo)
+ )
+
+ valid_so_against_quo = """
+ SELECT
+ so.name FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so_item.docstatus = 1 and so.docstatus = 1
+ and so_item.parent = so.name
+ and so_item.prevdoc_docname = qo.name
+ and qo.valid_till >= so.transaction_date""" # check if SO was created before quotation expired
+
+ frappe.db.sql(
+ """UPDATE `tabQuotation` qo SET qo.status = 'Closed' WHERE {cond} and exists({valid_so_against_quo})"""
+ .format(cond=cond, valid_so_against_quo=valid_so_against_quo)
+ )
diff --git a/erpnext/selling/doctype/quotation/quotation.py b/erpnext/selling/doctype/quotation/quotation.py
index 7c47b8a..7cfec5a 100644
--- a/erpnext/selling/doctype/quotation/quotation.py
+++ b/erpnext/selling/doctype/quotation/quotation.py
@@ -193,12 +193,23 @@
return doclist
def set_expired_status():
- frappe.db.sql("""
- UPDATE
- `tabQuotation` SET `status` = 'Expired'
- WHERE
- `status` not in ('Ordered', 'Expired', 'Lost', 'Cancelled') AND `valid_till` < %s
- """, (nowdate()))
+ # filter out submitted non expired quotations whose validity has been ended
+ cond = "qo.docstatus = 1 and qo.status != 'Expired' and qo.valid_till < %s"
+ # check if those QUO have SO against it
+ so_against_quo = """
+ SELECT
+ so.name FROM `tabSales Order` so, `tabSales Order Item` so_item
+ WHERE
+ so_item.docstatus = 1 and so.docstatus = 1
+ and so_item.parent = so.name
+ and so_item.prevdoc_docname = qo.name"""
+
+ # if not exists any SO, set status as Expired
+ frappe.db.sql(
+ """UPDATE `tabQuotation` qo SET qo.status = 'Expired' WHERE {cond} and not exists({so_against_quo})"""
+ .format(cond=cond, so_against_quo=so_against_quo),
+ (nowdate())
+ )
@frappe.whitelist()
def make_sales_invoice(source_name, target_doc=None):