def get_data(filters):
    if (not filters.get('company')):
        frappe.throw(_('Select Company!'))
    if (not filters.get('from_date')):
        frappe.throw(_('Select From Date!'))
    if (not filters.get('to_date')):
        frappe.throw(_('Select To Date!'))
    query = "select po_number, sum(cust_total_box) as order_qty from `tabPurchase Order` \n\t\t\t\twhere company = '{0}' and transaction_date between '{1}' and '{2}' \n\t\t\t\tand po_number is not null and po_number != 'PENDING' \n\t\t\t\tand docstatus = 1".format(filters.get('company'), filters.get('from_date'), filters.get('to_date'))
    if filters.get('supplier'):
        query += " and supplier = '{0}'".format(filters.get('supplier'))
    query += ' group by po_number'
    po = frappe.db.sql(query, as_dict=True)
    data = []
    for res in po:
        query1 = "select sum(boxes) from `tabPurchase Invoice` as pi \n\t\t\t\t\tinner join `tabPurchase Invoice Item` as pii on pii.parent = pi.name \n\t\t\t\t\twhere company = '{0}' and pi.posting_date between '{1}' and '{2}' \n\t\t\t\t\tand pi.po_number = '{3}' \n\t\t\t\t\tand pi.docstatus = 1".format(filters.get('company'), filters.get('from_date'), filters.get('to_date'), res.po_number)
        if filters.get('supplier'):
            query1 += " and pi.supplier = '{0}'".format(filters.get('supplier'))
        pi = float((frappe.db.sql(query1)[0][0] or 0))
        data.append(frappe._dict({'po_number': res.po_number, 'ordered_qty': res.order_qty, 'received_qty': pi, 'pending_qty': (res.order_qty - pi)}))
    return data
def get_data(filters):
    if (not filters.get('company')):
        frappe.throw(_('Select Company!'))
    if (not filters.get('from_date')):
        frappe.throw(_('Select From Date!'))
    if (not filters.get('to_date')):
        frappe.throw(_('Select To Date!'))
    query = "select po_number, sum(cust_total_box) as order_qty from `tabPurchase Order` \n\t\t\t\twhere company = '{0}' and transaction_date between '{1}' and '{2}' \n\t\t\t\tand po_number is not null and po_number != 'PENDING' \n\t\t\t\tand docstatus = 1".format(filters.get('company'), filters.get('from_date'), filters.get('to_date'))
    if filters.get('supplier'):
        query += " and supplier = '{0}'".format(filters.get('supplier'))
    query += ' group by po_number'
    po = frappe.db.sql(query, as_dict=True)
    data = []
    for res in po:
        query1 = "select sum(boxes) from `tabPurchase Invoice` as pi \n\t\t\t\t\tinner join `tabPurchase Invoice Item` as pii on pii.parent = pi.name \n\t\t\t\t\twhere company = '{0}' and pi.posting_date between '{1}' and '{2}' \n\t\t\t\t\tand pi.po_number = '{3}' \n\t\t\t\t\tand pi.docstatus = 1".format(filters.get('company'), filters.get('from_date'), filters.get('to_date'), res.po_number)
        if filters.get('supplier'):
            query1 += " and pi.supplier = '{0}'".format(filters.get('supplier'))
        pi = float((frappe.db.sql(query1)[0][0] or 0))
        data.append(frappe._dict({'po_number': res.po_number, 'ordered_qty': res.order_qty, 'received_qty': pi, 'pending_qty': (res.order_qty - pi)}))
    return data