# 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(doc):
    currency_symbol = frappe.db.get_value("Currency", doc.currency, "symbol")
    return currency_symbol + " " + str(doc.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(".","<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 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")]}):
        po_doc = frappe.get_doc("Purchase Order",vro)
        for item in po_doc.items:
            if item.sales_order:
                so_doc = frappe.get_doc("Sales Order",item.sales_order)
                data.append({"date": frappe.utils.formatdate(so_doc.transaction_date, "MM-dd-YYYY"),
                     "gtl_so_ref":so_doc.gtl_so_id,
                     "so_value":so_doc.grand_total,"rfq_no":so_doc.rfq_no,
                     "delivery_date": frappe.utils.formatdate(so_doc.delivery_date, "MM-dd-YYYY"),
                     "po_rfq_no":po_doc.gtl_po_no,
                     "supplier":po_doc.supplier,"contact":po_doc.contact_display,
                     "transaction_date":frappe.utils.formatdate(get_date_of_email(po_doc.name), "dd-MMM-YYYY"),
                     "po_grand_total":get_po_total(po_doc),"workflow_status":so_doc.status,
                     "gtl_status":po_doc.glt_status,"gtl_pay_status":po_doc.gtl_po_paid,
                     "so_po":frappe.utils.formatdate(po_doc.schedule_date, "MM-dd-YYYY"),
                     "diff_so":(so_doc.delivery_date-po_doc.schedule_date).days,
                     "assigned_user": get_assigned_to(po_doc.name),
                     "gtl_comment": get_comment(po_doc.gtl_po_comment)
                     })
                break
    return columns, data