def concat_mode_of_payments(pos_entries):
mode_of_payments = get_mode_of_payments(
set([d.pos_invoice for d in pos_entries]))
for entry in pos_entries:
if mode_of_payments.get(entry.pos_invoice):
entry.mode_of_payment = ", ".join(
mode_of_payments.get(entry.pos_invoice, []))
def _execute(filters=None,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [
d.item_code, d.item_name, d.item_group, d.parent, d.posting_date,
d.customer, d.customer_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to,
", ".join(mode_of_payments.get(d.parent, [])), d.territory,
d.project, d.company, d.sales_order, delivery_note,
d.income_account, d.cost_center, d.stock_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 = {}
columns = get_columns(additional_table_columns)
last_col = len(columns)
item_list = get_items(filters, additional_query_columns)
if item_list:
item_row_tax, tax_accounts = get_tax_accounts(item_list, columns)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
company_currency = frappe.db.get_value("Company", filters.company,
"default_currency")
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(
frappe.db.sql_list(
"""select distinct parent
from `tabDelivery Note Item` where docstatus=1 and so_detail=%s""",
d.so_detail))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [
d.item_code, d.item_name, d.item_group, d.parent, d.posting_date,
d.customer, d.customer_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to,
", ".join(mode_of_payments.get(d.parent,
[])), d.territory, d.project,
d.company, d.sales_order, delivery_note, d.income_account,
d.cost_center, d.qty, d.base_net_rate, d.base_net_amount
]
for tax in tax_accounts:
row.append(item_row_tax.get(d.name, {}).get(tax, 0))
total_tax = sum(row[last_col:])
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") and filters.get("date_range")[0], "to_date": filters.get("date_range") and filters.get("date_range")[1]})
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.get('company'))
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [d.item_code, d.item_name, d.item_group, d.description, d.parent, d.posting_date, d.customer, d.customer_name]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to, ", ".join(mode_of_payments.get(d.parent, [])),
d.territory, d.project, d.company, d.sales_order,
delivery_note, d.income_account, d.cost_center, d.stock_qty, d.stock_uom
]
if d.stock_uom != d.uom and d.stock_qty:
row += [(d.base_net_rate * d.qty)/d.stock_qty, d.base_net_amount]
else:
row += [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 = {}
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
# if item_list:
# itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
# columns.append({
# "fieldname": "currency",
# "label": _("Currency"),
# "fieldtype": "Data",
# "width": 80
# })
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
# delivery_note = None
# if d.delivery_note:
# delivery_note = d.delivery_note
# elif d.so_detail:
# delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
# if not delivery_note and d.update_stock:
# delivery_note = d.parent
row = [
d.posting_date, d.cost_center, d.item_code, d.item_name,
d.item_group
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [d.stock_qty, d.base_net_amount]
# row += [(d.base_net_rate * d.qty)/d.stock_qty, d.base_net_amount] \
# if d.stock_uom != d.uom else [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 += [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 = {}
columns = get_columns(additional_table_columns)
last_col = len(columns)
item_list = get_items(filters, additional_query_columns)
if item_list:
item_row_tax, tax_accounts = get_tax_accounts(item_list, columns)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
company_currency = frappe.db.get_value("Company", filters.get("company"), "default_currency")
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(frappe.db.sql_list("""select distinct parent
from `tabDelivery Note Item` where docstatus=1 and so_detail=%s""", d.so_detail))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [d.item_code, d.item_name, d.item_group, d.parent, d.posting_date, d.customer, d.customer_name]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to, ", ".join(mode_of_payments.get(d.parent, [])),
d.territory, d.project, d.company, d.sales_order,
delivery_note, d.income_account, d.cost_center, d.qty, d.base_net_rate, d.base_net_amount
]
for tax in tax_accounts:
row.append(item_row_tax.get(d.name, {}).get(tax, 0))
total_tax = sum(row[last_col:])
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") and filters.get("date_range")[0],
"to_date":
filters.get("date_range") and filters.get("date_range")[1]
})
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.get('company'))
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [d.posting_date, d.item_code, d.item_name, d.parent, d.customer]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [d.stock_qty, d.stock_uom]
row += [(d.base_net_rate * d.qty)/d.stock_qty, d.base_net_amount] \
if d.stock_uom != d.uom and d.stock_qty != 0 else [d.base_net_rate, d.base_net_amount]
data.append(row)
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 = frappe.get_cached_value('Company',
filters.get('company'),
'default_currency')
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_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, 'Sales Invoice')
customer_details = get_customer_details()
item_details = get_item_details()
for d in item_list:
customer_record = customer_details.get(d.customer)
item_record = item_details.get(d.item_code)
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
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,
'customer': d.customer,
'customer_name': customer_record.customer_name,
'customer_group': customer_record.customer_group,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update({
'debit_to':
d.debit_to,
'mode_of_payment':
", ".join(mode_of_payments.get(d.parent, [])),
'territory':
d.territory,
'project':
d.project,
'company':
d.company,
'sales_order':
d.sales_order,
'delivery_note':
d.delivery_note,
'income_account':
d.unrealized_profit_loss_account or d.income_account,
'cost_center':
d.cost_center,
'stock_qty':
d.stock_qty,
'stock_uom':
d.stock_uom
})
if d.stock_uom != d.uom and d.stock_qty:
row.update({
'rate': (d.base_net_rate * d.qty) / d.stock_qty,
'amount': d.base_net_amount
})
else:
row.update({'rate': d.base_net_rate, '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,
additional_table_columns=None,
additional_query_columns=None):
# if not filters: filters = {}
filters.update({
"from_date": filters.get("from_date"),
"to_date": filters.get("to_date")
})
columns = get_columns(additional_table_columns)
company_currency = frappe.get_cached_value('Company',
filters.get("company"),
"default_currency")
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [
d.item_code, d.item_name, d.item_group, d.description, d.parent,
d.posting_date, d.customer, d.customer_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.customer_group, d.debit_to,
", ".join(mode_of_payments.get(d.parent, [])), d.territory,
d.project, d.company, d.sales_order, delivery_note,
d.income_account, d.cost_center, d.stock_qty, d.stock_uom
]
if d.stock_uom != d.uom and d.stock_qty:
row += [(d.base_net_rate * d.qty) / d.stock_qty, d.base_net_amount]
else:
row += [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 sum([i[columns.index("Total:Currency/currency:100")] for i in data])
def _execute(filters=None, additional_table_columns=None, additional_query_columns=None):
if not filters: filters = {}
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.company)
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
for i in item_list:
item_list[item_list.index(i)]["posting_time"]=frappe.utils.get_time(i.posting_time).strftime("%H:%M")
# columns.append({
# "fieldname": "currency",
# "label": _("Currency"),
# "fieldtype": "Data",
# "width": 80
# })
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
data = []
for d in item_list:
row = [d.item_code,d.parent,d.customer , d.posting_date,d.posting_time, d.vehicle_no]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
row += [
d.stock_qty,
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]
print("row",row)
data.append(row)
total_net_qty=0.00
total_net_rate=0.00
total_net_amt8=0.00
total_net_tax9=0.00
Nett_Total=0.00
# print "data",data
for i in data:
print("HIIIIIII")
total_net_qty +=i[6]
total_net_tax9 += i[9]
total_net_amt8 += i[8]
Nett_Total += i[10]
a = list(sorted(data,key=itemgetter(0)))
my_new_list = []
for k, items in groupby(a, itemgetter(0)):
grand = 0.00
total_amt8 = 0.00
total_qty = 0.00
total_rate= 0.00
total_tax9 = 0.00
for i in items:
print("i[0]=",i,"\n")
total_qty += float(i[6])
# total_rate += i[6]
total_amt8 +=i[8]
total_tax9 +=i[9]
grand +=i[10]
my_new_list.append([i[0],i[1],i[2],i[3],i[4],i[6],i[7],i[8],i[9],i[10]])
# my_new_list.append(["","","","","","",grand,paid,""])
# my_new_list.append(["","","","","<b>Total</b>","","",total_qty,total_tax,total_amt])
my_new_list.append(["","","","","Total",str(total_qty),"",total_amt8,total_tax9,grand])
# my_new_list.append(["","","","","<b>Total</b>",total_qty,total_rate,total_amt,total_tax,grand])
# my_new_list.append(["","","",""])
# ,"<b> Net Total</b>","","",total_net_qty,total_net_tax,total_net_amt,""])
my_new_list.append(["","","","","Net Total",total_net_qty,"",total_net_amt8,total_net_tax9,Nett_Total])
my_new_list.append(["<b> ITEM</b>","<b> QTY</b>","<b> AMOUNT</b>"])
for k, items in groupby(a, itemgetter(0)):
print "k=",k
qty = 0.00
amount = 0.00
# paid = 0.00
for i in items:
qty += i[6]
amount += i[8]
print("qty",qty)
print qty,"qt"
my_new_list.append([k,"<div class=text-right>"+str("%.2f" % qty)+"</div>","<div class=text-right>"+str("%.2f" % amount)+"</div>","","","",""])
my_new_list.append(["<b>Total</b>","<div class=text-right><b>"+str("%.2f" % total_net_qty)+"</b></div>","<div class=text-right><b>"+str("%.2f" % total_net_amt8)+"</b></div>","","","",""])
return columns, my_new_list
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 = frappe.get_cached_value("Company", filters.get("company"), "default_currency")
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns, company_currency)
mode_of_payments = get_mode_of_payments(set(d.parent for d in item_list))
so_dn_map = get_delivery_notes_against_sales_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, "Sales Invoice")
customer_details = get_customer_details()
item_details = get_item_details()
for d in item_list:
customer_record = customer_details.get(d.customer)
item_record = item_details.get(d.item_code)
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
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,
"customer": d.customer,
"customer_name": customer_record.customer_name,
"customer_group": customer_record.customer_group,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update(
{
"debit_to": d.debit_to,
"mode_of_payment": ", ".join(mode_of_payments.get(d.parent, [])),
"territory": d.territory,
"project": d.project,
"company": d.company,
"sales_order": d.sales_order,
"delivery_note": d.delivery_note,
"income_account": d.unrealized_profit_loss_account
if d.is_internal_customer == 1
else d.income_account,
"cost_center": d.cost_center,
"stock_qty": d.stock_qty,
"stock_uom": d.stock_uom,
}
)
if d.stock_uom != d.uom and d.stock_qty:
row.update({"rate": (d.base_net_rate * d.qty) / d.stock_qty, "amount": d.base_net_amount})
else:
row.update({"rate": d.base_net_rate, "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,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
columns = get_columns(additional_table_columns, filters)
company_currency = frappe.get_cached_value('Company',
filters.get("company"),
"default_currency")
item_list = get_items(filters, additional_query_columns)
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_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, 'Sales Invoice')
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = {
'posting_date': d.posting_date,
'item_name': d.item_name,
'customer': d.customer,
}
if additional_query_columns:
for col in additional_query_columns:
row.update({col: d.get(col)})
row.update({
'territory': d.territory,
'stock_qty': d.stock_qty,
'stock_uom': d.stock_uom
})
if d.stock_uom != d.uom and d.stock_qty:
row.update({
'rate': (d.base_net_rate * d.qty) / d.stock_qty,
'amount': d.base_net_amount
})
else:
row.update({'rate': d.base_net_rate, 'amount': d.base_net_amount})
row.update({
'batch_no': d.batch_no,
'package_tag': d.package_tag,
'sales_order': d.sales_order,
})
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)
add_sub_total_row(row, total_row_map, d.get(group_by_field, ''))
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')
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,
additional_table_columns=None,
additional_query_columns=None):
if not filters: filters = {}
filters.update({
"from_date":
filters.get("date_range") and filters.get("date_range")[0],
"to_date":
filters.get("date_range") and filters.get("date_range")[1]
})
columns = get_columns(additional_table_columns)
company_currency = erpnext.get_company_currency(filters.get('company'))
item_list = get_items(filters, additional_query_columns)
if item_list:
itemised_tax, tax_columns = get_tax_accounts(item_list, columns,
company_currency)
columns.append({
"fieldname": "currency",
"label": _("Currency"),
"fieldtype": "Data",
"width": 80
})
mode_of_payments = get_mode_of_payments(set([d.parent for d in item_list]))
so_dn_map = get_delivery_notes_against_sales_order(item_list)
bundle_list = frappe.db.sql(
"""select parent,item_code,qty from `tabProduct Bundle Item` where item_code="LPG-20180001";""",
as_list=1)
bundle = {}
for row in bundle_list:
bundle[row[0]] = row[2]
data = []
for d in item_list:
delivery_note = None
if d.delivery_note:
delivery_note = d.delivery_note
elif d.so_detail:
delivery_note = ", ".join(so_dn_map.get(d.so_detail, []))
if not delivery_note and d.update_stock:
delivery_note = d.parent
row = [
d.item_code,
d.item_name,
d.item_group,
# d.description,
d.parent,
d.posting_date,
d.customer,
d.customer_name
]
if additional_query_columns:
for col in additional_query_columns:
row.append(d.get(col))
in_kg = d.stock_qty
if d.item_code in bundle:
in_kg = flt(bundle[d.item_code]) * d.stock_qty
row += [
d.customer_group,
#d.debit_to,
", ".join(mode_of_payments.get(d.parent, [])),
d.territory,
# d.project, d.company,
d.sales_order,
delivery_note,
# d.income_account,
d.cost_center,
d.stock_qty,
d.stock_uom,
in_kg,
d.owner
]
if d.stock_uom != d.uom and d.stock_qty:
row += [(d.base_net_rate * d.qty) / d.stock_qty, d.base_net_amount]
else:
row += [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
