gtl_erpnext/gtl_custom/gtl_customisation/report/tracker/tracker.py

# Copyright (c) 2013, Fafadia Tech and contributors
# For license information, please see license.txt

from __future__ import unicode_literals
import frappe
from frappe import _
import datetime

def get_po_total(currency, grand_total):
    currency_symbol = frappe.db.get_value("Currency", currency, "symbol")
    return currency_symbol + " " + str(grand_total)

def get_assigned_to(purchase_order):
    q = "SELECT _assign FROM `tabPurchase Order` WHERE name='{0}'".format(purchase_order)
    results = frappe.db.sql(q)
    if len(results) == 0 or len(results[0]) == 0 or not results[0][0]:
        return ""
    result = eval(results[0][0])
    if result:
        email = result[0]
        q = "SELECT full_name FROM `tabUser` WHERE email='{0}'".format(email)
        results = frappe.db.sql(q)

        if len(result) > 0:
            return results[0][0]
        return ""

def get_comment(comment):
    if comment:
        return comment.replace("\n","<br>").strip()
    return ""

def get_date_of_email(purchase_order):
    results = frappe.get_all("Communication",filters={"reference_name":purchase_order})
    if len(results) == 0:
        return ""

    # first time email doctype
    doc_name = results[-1]["name"]
    comm_date = frappe.get_value("Communication", doc_name, "communication_date")
    if comm_date:
        return comm_date

def get_so(so_values):
    so = None
    for sales_order in so_values:
        if sales_order[0]:
            return sales_order[0]

def execute(filters=None):
    columns, data = [], []
    columns = [
    {"fieldname": "date", "label": _("Date"), "fieldtype": "date",},
    {"fieldname": "gtl_so_ref", "label": _("GTL SO Ref"), "fieldtype": "data",},
    {"fieldname": "so_value", "label": _("SO Value"), "fieldtype":"data"},
    {"fieldname": "rfq_no", "label": _("RFQ/GTL Ref"), "fieldtype":"data"},
    {"fieldname": "delivery_date", "label": _("Delivery Date"), "fieldtype":"date"},
    {"fieldname": "po_rfq_no", "label": _("GTL PO to supplier/RFQ no"), "fieldtype":"data"},
    {"fieldname": "supplier", "label": _("Supplier"), "fieldtype":"data"},
    {"fieldname": "contact", "label": _("Product/Contact Person"), "fieldtype":"data"},
    {"fieldname": "transaction_date", "label": _("Date - PO sent to Supplier"), "fieldtype":"date"},
    {"fieldname": "po_grand_total", "label": _("PO Value to Supplier"), "fieldtype":"Currency/currency"},
    {"fieldname": "so_po", "label": _("RDD to Supplier"), "fieldtype":"date"},
    {"fieldname": "diff_so", "label": _("Current Delivery status"), "fieldtype":"data"},
    {"fieldname": "assigned_user", "label": _("Person Handling the order"), "fieldtype":"data"},
    {"fieldname": "gtl_pay_status", "label": _("PAID"), "fieldtype":"data"},
    {"fieldname": "workflow_status", "label": _("Category"), "fieldtype":"data"},
    {"fieldname": "gtl_status", "label": _("Status"), "fieldtype":"data"},
    {"fieldname": "gtl_comment", "label": _("Comments"), "fieldtype":"data"},
    ]

    for vro in frappe.get_all("Purchase Order", filters={"status":["not in",("Draft", "Cancelled")]}):
        so = get_so(frappe.db.sql(f"SELECT sales_order FROM `tabPurchase Order Item` WHERE parent='{vro.name}'"))
        if so:
            so_q = f"SELECT transaction_date, gtl_so_id, grand_total, rfq_no, delivery_date, status FROM `tabSales Order` WHERE name='{so}'"
            so_values = frappe.db.sql(so_q, as_dict=1)[0]
            po_q = f"SELECT gtl_po_no, supplier, contact_display, currency, grand_total, glt_status, gtl_po_paid, schedule_date, gtl_po_comment FROM `tabPurchase Order` WHERE name='{vro.name}'"
            po_values = frappe.db.sql(po_q, as_dict=1)[0]
            data.append({"date": frappe.utils.formatdate(so_values["transaction_date"], "MM-dd-YYYY"),
                 "gtl_so_ref":so_values["gtl_so_id"],
                 "so_value":so_values["grand_total"],
                 "rfq_no":so_values["rfq_no"],
                 "delivery_date": frappe.utils.formatdate(so_values["delivery_date"], "MM-dd-YYYY"),
                 "po_rfq_no":po_values["gtl_po_no"],
                 "supplier":po_values["supplier"],
                 "contact":po_values["contact_display"],
                 "transaction_date":frappe.utils.formatdate(get_date_of_email(vro.name), "dd-MMM-YYYY"),
                 "po_grand_total":get_po_total(po_values["currency"],po_values["grand_total"]),
                 "workflow_status":so_values["status"],
                 "gtl_status": po_values["glt_status"],
                 "gtl_pay_status":po_values["gtl_po_paid"],
                 "so_po":frappe.utils.formatdate(po_values["schedule_date"], "MM-dd-YYYY"),
                 "diff_so":(so_values["delivery_date"] - po_values["schedule_date"]).days,
                 "assigned_user": get_assigned_to(vro.name),
                 "gtl_comment": get_comment(po_values["gtl_po_comment"])
                 })
    return columns, data