def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
columns = get_columns(additional_table_columns)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(
item_list,
columns,
doctype="Purchase Invoice",
tax_doctype="Purchase Taxes and Charges")
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
company_currency = frappe.db.get_value("Company", filters.company,
"default_currency")
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
for d in item_list:
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = d.expense_account or aii_account_map.get(d.company)
row = [
d.item_code, d.item_name, d.item_group, d.parent, d.posting_date,
d.supplier, d.supplier_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.credit_to, d.mode_of_payment, d.project, d.company,
d.purchase_order, purchase_receipt, expense_account, d.qty,
d.stock_uom, d.base_net_rate, d.base_net_amount
]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
total_tax += flt(item_tax.get("tax_amount"))
row += [total_tax, d.base_net_amount + total_tax, company_currency]
data.append(row)
return columns, data
def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters: filters = {}
filters.update({"from_date": filters.get("date_range")[0], "to_date": filters.get("date_range")[1]})
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency,
doctype="Purchase Invoice", tax_doctype="Purchase Taxes and Charges")
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
for d in item_list:
if not d.stock_qty:
continue
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = d.expense_account or aii_account_map.get(d.company)
row = [d.item_code, d.item_name, d.item_group, d.description, d.parent, d.posting_date, d.supplier,
d.supplier_name]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.credit_to, d.mode_of_payment, d.project, d.company, d.purchase_order,
purchase_receipt, expense_account, d.stock_qty, d.stock_uom, d.base_net_amount / d.stock_qty, d.base_net_amount
]
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row += [item_tax.get("tax_rate", 0), item_tax.get("tax_amount", 0)]
total_tax += flt(item_tax.get("tax_amount"))
row += [total_tax, d.base_net_amount + total_tax, company_currency]
data.append(row)
return columns, data
def get_itemised_taxes(self):
if self.entries:
self.itemised_tax, self.tax_columns = get_tax_accounts(
self.entries, [], self.company_currency, self.filters.doctype,
"Sales Taxes and Charges" if self.filters.party_type
== "Customer" else "Purchase Taxes and Charges")
self.tax_amount_fields = [
"tax_" + scrub(tax) for tax in self.tax_columns
]
self.tax_rate_fields = [
"tax_" + scrub(tax) + "_rate" for tax in self.tax_columns
]
else:
self.itemised_tax, self.tax_columns = {}, []
self.tax_amount_fields, self.tax_rate_fields = [], []
def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters: filters = {}
filters.update({"from_date": filters.get("date_range")[0], "to_date": filters.get("date_range")[1]})
columns = get_columns(additional_table_columns, filters)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency,
doctype='Purchase Invoice', tax_doctype='Purchase Taxes and Charges')
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
total_row_map = {}
skip_total_row = 0
prev_group_by_value = ''
if filters.get('group_by'):
grand_total = get_grand_total(filters, 'Purchase Invoice')
item_details = get_item_details()
for d in item_list:
if not d.stock_qty:
continue
item_record = item_details.get(d.item_code)
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = d.expense_account or aii_account_map.get(d.company)
row = {
'item_code': d.item_code,
'item_name': item_record.item_name if item_record else d.item_name,
'item_group': item_record.item_group if item_record else d.item_group,
'description': d.description,
'invoice': d.parent,
'posting_date': d.posting_date,
'supplier': d.supplier,
'supplier_name': d.supplier_name
}
if additional_query_columns:
for col in additional_query_columns:
row.update({
col: d.get(col)
})
row.update({
'credit_to': d.credit_to,
'mode_of_payment': d.mode_of_payment,
'project': d.project,
'company': d.company,
'purchase_order': d.purchase_order,
'purchase_receipt': d.purchase_receipt,
'expense_account': expense_account,
'stock_qty': d.stock_qty,
'stock_uom': d.stock_uom,
'rate': d.base_net_amount / d.stock_qty,
'amount': d.base_net_amount
})
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update({
frappe.scrub(tax + ' Rate'): item_tax.get('tax_rate', 0),
frappe.scrub(tax + ' Amount'): item_tax.get('tax_amount', 0),
})
total_tax += flt(item_tax.get('tax_amount'))
row.update({
'total_tax': total_tax,
'total': d.base_net_amount + total_tax,
'currency': company_currency
})
if filters.get('group_by'):
row.update({'percent_gt': flt(row['total']/grand_total) * 100})
group_by_field, subtotal_display_field = get_group_by_and_display_fields(filters)
data, prev_group_by_value = add_total_row(data, filters, prev_group_by_value, d, total_row_map,
group_by_field, subtotal_display_field, grand_total, tax_columns)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ''), tax_columns)
data.append(row)
if filters.get('group_by') and item_list:
total_row = total_row_map.get(prev_group_by_value or d.get('item_name'))
total_row['percent_gt'] = flt(total_row['total']/grand_total * 100)
data.append(total_row)
data.append({})
add_sub_total_row(total_row, total_row_map, 'total_row', tax_columns)
data.append(total_row_map.get('total_row'))
skip_total_row = 1
return columns, data, None, None, None, skip_total_row
def execute(filters=None):
if not filters:
filters = {}
from_date = filters.get("date_range")[0]
to_date = filters.get("date_range")[1]
patient = filters.get("patient")
columns, data = [], []
if patient:
sales_invoices = frappe.get_list(
'Sales Invoice',
filters=[['posting_date', ">=", from_date],
['posting_date', "<=", to_date], {
'patient': patient
}])
else:
sales_invoices = frappe.get_list(
'Sales Invoice',
filters=[['posting_date', ">=", from_date],
['posting_date', "<=", to_date]])
columns = [
'Date:Date:78',
'Invoice:Link/Sales Invoice:80',
'Procedure:Link/Clinical Procedure:80',
'Proceure Name:200',
'Doctor::150',
'Patient:Link/Patient:90',
'Patient Name::150',
'Family Name::100',
'Qty:20',
'Rate:Currency:100',
'Amount:Currency:100',
]
items_list = []
for si_name in sales_invoices:
si = frappe.get_doc('Sales Invoice', si_name)
if (not si) or (si.docstatus != 1):
continue
si_items = si.items
for item in si_items:
items_list.append(frappe._dict(vars(item)))
if items_list:
items_tax_list, tax_columns = get_tax_accounts(items_list, columns,
'INR')
for si_name in sales_invoices:
si = frappe.get_doc('Sales Invoice', si_name)
if (not si) or (si.docstatus != 1):
continue
posting_date = si.posting_date
si_items = si.items
for item in si_items:
reference_dt = item.reference_dt
if not reference_dt in billable_healtcare_doctypes:
continue
amount = item.amount
reference_dn = item.reference_dn
rate = item.rate
qty = item.qty
healthcare_doc = frappe.get_doc(reference_dt, reference_dn)
if healthcare_doc:
if reference_dt == 'Clinical Procedure' or reference_dt == 'Patient Encounter':
template = healthcare_doc.procedure_template if (
reference_dt
== 'Clinical Procedure') else 'Patient Encounter'
practitioner = healthcare_doc.practitioner
patient_id = healthcare_doc.patient
doctor_name = ''
doctor_full_name = ''
if practitioner:
doctor_name = practitioner
doctor = frappe.get_doc('Healthcare Practitioner',
doctor_name)
if doctor.first_name:
doctor_full_name += doctor.first_name
if doctor.last_name:
doctor_full_name += " " + doctor.last_name
patient = frappe.get_doc('Patient', patient_id)
patient_name = patient.patient_name
family_name = patient.family_name
if family_name == None:
family_name = 'N/A'
array_to_append = [
posting_date, si_name.name, healthcare_doc.name,
template, doctor_full_name, patient_id,
patient_name, family_name, qty, rate, amount
]
elif reference_dt == 'Procedure Prescription' or reference_dt == 'Lab Prescription':
pass
else:
continue
total_tax = 0
for tax in tax_columns:
item_tax = items_tax_list.get(item.name,
{}).get(tax, {})
array_to_append += [
item_tax.get("tax_rate", 0),
item_tax.get("tax_amount", 0)
]
total_tax += flt(item_tax.get("tax_amount"))
array_to_append += [
total_tax, item.base_net_amount + total_tax, "INR"
]
data.append(array_to_append)
return columns, data
def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters:
filters = {}
columns = get_columns(additional_table_columns, filters)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
aii_account_map = get_aii_accounts()
if item_list:
itemised_tax, tax_columns = get_tax_accounts(
item_list,
columns,
company_currency,
doctype="Purchase Invoice",
tax_doctype="Purchase Taxes and Charges",
)
po_pr_map = get_purchase_receipts_against_purchase_order(item_list)
data = []
total_row_map = {}
skip_total_row = 0
prev_group_by_value = ""
if filters.get("group_by"):
grand_total = get_grand_total(filters, "Purchase Invoice")
item_details = get_item_details()
for d in item_list:
if not d.stock_qty:
continue
item_record = item_details.get(d.item_code)
purchase_receipt = None
if d.purchase_receipt:
purchase_receipt = d.purchase_receipt
elif d.po_detail:
purchase_receipt = ", ".join(po_pr_map.get(d.po_detail, []))
expense_account = (d.unrealized_profit_loss_account
or d.expense_account
or aii_account_map.get(d.company))
row = {
"item_code": d.item_code,
"item_name": item_record.item_name if item_record else d.item_name,
"item_group":
item_record.item_group if item_record else d.item_group,
"description": d.description,
"invoice": d.parent,
"posting_date": d.posting_date,
"supplier": d.supplier,
"supplier_name": d.supplier_name,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update({
"credit_to": d.credit_to,
"mode_of_payment": d.mode_of_payment,
"project": d.project,
"company": d.company,
"purchase_order": d.purchase_order,
"purchase_receipt": d.purchase_receipt,
"expense_account": expense_account,
"stock_qty": d.stock_qty,
"stock_uom": d.stock_uom,
"rate": d.base_net_amount / d.stock_qty,
"amount": d.base_net_amount,
})
total_tax = 0
for tax in tax_columns:
item_tax = itemised_tax.get(d.name, {}).get(tax, {})
row.update({
frappe.scrub(tax + " Rate"):
item_tax.get("tax_rate", 0),
frappe.scrub(tax + " Amount"):
item_tax.get("tax_amount", 0),
})
total_tax += flt(item_tax.get("tax_amount"))
row.update({
"total_tax": total_tax,
"total": d.base_net_amount + total_tax,
"currency": company_currency
})
if filters.get("group_by"):
row.update({"percent_gt": flt(row["total"] / grand_total) * 100})
group_by_field, subtotal_display_field = get_group_by_and_display_fields(
filters)
data, prev_group_by_value = add_total_row(
data,
filters,
prev_group_by_value,
d,
total_row_map,
group_by_field,
subtotal_display_field,
grand_total,
tax_columns,
)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ""),
tax_columns)
data.append(row)
if filters.get("group_by") and item_list:
total_row = total_row_map.get(prev_group_by_value
or d.get("item_name"))
total_row["percent_gt"] = flt(total_row["total"] / grand_total * 100)
data.append(total_row)
data.append({})
add_sub_total_row(total_row, total_row_map, "total_row", tax_columns)
data.append(total_row_map.get("total_row"))
skip_total_row = 1
return columns, data, None, None, None, skip_total_row
