text: # Copyright (c) 2013, Aakvatech and contributors
  # For license information, please see license.txt
  
  from __future__ import unicode_literals
  import frappe
  from erpnext import get_company_currency, get_default_company
  from erpnext.accounts.report.utils import get_currency, convert_to_presentation_currency
  from frappe.utils import getdate, cstr, flt
  from frappe import _, _dict
  from erpnext.accounts.utils import get_account_currency
  from erpnext.accounts.report.financial_statements import get_cost_centers_with_children
  from six import iteritems
  from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import get_accounting_dimensions, get_dimension_with_children
  from collections import OrderedDict
  # from csf_tz import console
  
  def execute(filters=None):
  	if not filters:
  		return [], []
  
  	account_details = {}
  
  	if filters and filters.get('print_in_account_currency') and \
  		not filters.get('account'):
  		frappe.throw(_("Select an account to print in account currency"))
  
  	for acc in frappe.db.sql("""select name, is_group from tabAccount""", as_dict=1):
  		account_details.setdefault(acc.name, acc)
  
  	if filters.get('party'):
  		filters.party = frappe.parse_json(filters.get("party"))
  
  	validate_filters(filters, account_details)
  
  	validate_party(filters)
  
  	filters = set_account_currency(filters)
  
  	columns = get_columns(filters)
  
  	res = get_result(filters)
  
  	return columns, res
  
  
  def validate_filters(filters, account_details):
  	if not filters.get('company'):
  		frappe.throw(_('{0} is mandatory').format(_('Company')))
  
  	if filters.get("account") and not account_details.get(filters.account):
  		frappe.throw(_("Account {0} does not exists").format(filters.account))
  
  	if (filters.get("account") and filters.get("group_by") == _('Group by Account')
  		and account_details[filters.account].is_group == 0):
  		frappe.throw(_("Can not filter based on Account, if grouped by Account"))
  
  	if (filters.get("voucher_no")
  		and filters.get("group_by") in [_('Group by Voucher')]):
  		frappe.throw(_("Can not filter based on Voucher No, if grouped by Voucher"))
  
  	if filters.from_date > filters.to_date:
  		frappe.throw(_("From Date must be before To Date"))
  
  	if filters.get('project'):
  		filters.project = frappe.parse_json(filters.get('project'))
  
  	if filters.get('cost_center'):
  		filters.cost_center = frappe.parse_json(filters.get('cost_center'))
  
  
  def validate_party(filters):
  	party_type, party = filters.get("party_type"), filters.get("party")
  
  	if party:
  		if not party_type:
  			frappe.throw(_("To filter based on Party, select Party Type first"))
  		else:
  			for d in party:
  				if not frappe.db.exists(party_type, d):
  					frappe.throw(_("Invalid {0}: {1}").format(party_type, d))
  
  def set_account_currency(filters):
  	if filters.get("account") or (filters.get('party') and len(filters.party) == 1):
  		filters["company_currency"] = frappe.get_cached_value('Company',  filters.company,  "default_currency")
  		account_currency = None
  
  		if filters.get("account"):
  			account_currency = get_account_currency(filters.account)
  		elif filters.get("party"):
  			gle_currency = frappe.db.get_value(
  				"GL Entry", {
  					"party_type": filters.party_type, "party": filters.party[0], "company": filters.company
  				},
  				"account_currency"
  			)
  
  			if gle_currency:
  				account_currency = gle_currency
  			else:
  				account_currency = (None if filters.party_type in ["Employee", "Student", "Shareholder", "Member"] else
  					frappe.db.get_value(filters.party_type, filters.party[0], "default_currency"))
  
  		filters["account_currency"] = account_currency or filters.company_currency
  		if filters.account_currency != filters.company_currency and not filters.presentation_currency:
  			filters.presentation_currency = filters.account_currency
  
  	return filters
  
  def get_result(filters):
  	gl_entries = get_gl_entries(filters)
  
  	data = get_data_with_opening_closing(filters, gl_entries)
  
  	result = get_result_as_list(data, filters)
  
  	return result
  
  def get_gl_entries(filters):
  	currency_map = get_currency(filters)
  	select_fields = """, debit, credit, debit_in_account_currency,
  		credit_in_account_currency """
  
  	order_by_statement = "order by posting_date, account"
  
  	if filters.get("group_by") == _("Group by Voucher"):
  		order_by_statement = "order by posting_date, voucher_type, voucher_no"
  
  	if filters.get("include_default_book_entries"):
  		filters['company_fb'] = frappe.db.get_value("Company",
  			filters.get("company"), 'default_finance_book')
  
  	gl_entries = frappe.db.sql(
  		"""
  		select
  			posting_date, account, party_type, party,
  			voucher_type, voucher_no, cost_center, project,
  			against_voucher_type, against_voucher, account_currency,
  			remarks, against, is_opening {select_fields}
  		from `tabGL Entry`
  		where company=%(company)s {conditions}
  		{order_by_statement}
  		""".format(
  			select_fields=select_fields, conditions=get_conditions(filters),
  			order_by_statement=order_by_statement
  		),
  		filters, as_dict=1)
  
  	converted_gl_list = []
  
  	company_currency = currency_map['company_currency']
  
  	for entry in gl_entries:
  		docktype = entry['voucher_type']
  		docname = entry['voucher_no']
  		entry_currency = entry['account_currency']
  		doc_currency = company_currency
  		items_list = ""
  
  		if docktype == "Payment Entry":
  			doc = frappe.db.sql(
  				"""
  				select
  					payment_type, paid_to_account_currency, paid_from_account_currency, received_amount, paid_amount 
  				from `tabPayment Entry`
  				where name=%(docname)s
  				limit 1
  				""",{'docname':docname},as_dict=1)
  			if doc[0].payment_type == "Receive":
  				doc_currency = doc[0].paid_to_account_currency
  				doc_amount = doc[0].received_amount
  			elif doc[0].payment_type == "Pay":
  				doc_currency = doc[0].paid_from_account_currency
  				doc_amount = doc[0].paid_amount
  			elif doc[0].payment_type == "Internal Transfer":
  				if entry.get('credit'):
  					doc_currency = doc[0].paid_from_account_currency
  					doc_amount = doc[0].paid_amount
  				else:
  					doc_currency = doc[0].paid_to_account_currency
  					doc_amount = doc[0].received_amount
  
  		elif docktype == "Sales Invoice":
  			if entry["party"]:
  				doc = frappe.db.sql(
  				"""
  				select
  					name, currency, grand_total ,rounded_total
  				from `tabSales Invoice`
  				where name=%(docname)s
  				limit 1
  				""",{'docname':entry['voucher_no']},as_dict=1)
  
  				doc_currency = doc[0].currency
  				doc_amount = doc[0].rounded_total or doc[0].grand_total
  				
  				items = frappe.db.sql(
  				"""
  				select
  					item_name, service_start_date, service_end_date
  				from `tabSales Invoice Item`
  				where parent=%(docname)s
  				""",{'docname':entry['voucher_no']},as_dict=1)
  		
  				for item in items:
  					items_list += "({0}".format(item.item_name) 
  					if item.service_start_date:
  						items_list += " , {0} ".format(item.service_start_date)
  					if item.service_end_date:
  						items_list += ", {0}".format(item.service_end_date)
  					items_list += ") "
  			
  		elif docktype == "Purchase Invoice":
  			if entry["party"]:
  				doc = frappe.db.sql(
  				"""
  				select
  					name, currency, grand_total, rounded_total
  				from `tabPurchase Invoice`
  				where name=%(docname)s
  				limit 1
  				""",{'docname':entry['voucher_no']},as_dict=1)
  
  				doc_currency = doc[0].currency
  				doc_amount = doc[0].rounded_total or doc[0].grand_total
  				
  				items = frappe.db.sql(
  				"""
  				select
  					item_name, service_start_date, service_end_date
  				from `tabPurchase Invoice Item`
  				where parent=%(docname)s
  				""",{'docname':entry['voucher_no']},as_dict=1)
  		
  				for item in items:
  					items_list += "({0}".format(item.item_name) 
  					if item.service_start_date:
  						items_list += " , {0} ".format(item.service_start_date)
  					if item.service_end_date:
  						items_list += ", {0}".format(item.service_end_date)
  					items_list += ") "
  
  		elif docktype == "Journal Entry":
  			doc_currency = entry_currency
  			if entry.get('credit'):
  				doc_amount = entry['credit_in_account_currency']
  			else:
  				doc_amount = entry['debit_in_account_currency']
  
  		if company_currency != doc_currency:
  
  			if entry.get('debit'):
  				entry['debit_foreign'] = doc_amount
  				entry['exchange_rate'] = entry.get('debit') / doc_amount
  
  			if entry.get('credit'):
  				entry['credit_foreign'] = doc_amount
  				entry['exchange_rate'] = entry.get('credit') / doc_amount
  
  			entry['foreign_currency'] = doc_currency
  		
  		
  		entry['items'] = str(items_list) if items_list else  ""
  		converted_gl_list.append(entry)
  
  
  	if filters.get('presentation_currency'):
  		return convert_to_presentation_currency(converted_gl_list, currency_map)
  	else:
  		return converted_gl_list
  
  
  def get_conditions(filters):
  	conditions = []
  	if filters.get("account"):
  		lft, rgt = frappe.db.get_value("Account", filters["account"], ["lft", "rgt"])
  		conditions.append("""account in (select name from tabAccount
  			where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
  
  	if filters.get("cost_center"):
  		filters.cost_center = get_cost_centers_with_children(filters.cost_center)
  		conditions.append("cost_center in %(cost_center)s")
  
  	if filters.get("voucher_no"):
  		conditions.append("voucher_no=%(voucher_no)s")
  
  	if filters.get("group_by") == "Group by Party" and not filters.get("party_type"):
  		conditions.append("party_type in ('Customer', 'Supplier')")
  
  	if filters.get("party_type"):
  		conditions.append("party_type=%(party_type)s")
  
  	if filters.get("party"):
  		conditions.append("party in %(party)s")
  
  	if not (filters.get("account") or filters.get("party") or
  		filters.get("group_by") in ["Group by Account", "Group by Party"]):
  		conditions.append("posting_date >=%(from_date)s")
  
  	conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')")
  
  	if filters.get("project"):
  		conditions.append("project in %(project)s")
  
  	if filters.get("finance_book"):
  		if filters.get("include_default_book_entries"):
  			conditions.append("finance_book in (%(finance_book)s, %(company_fb)s)")
  		else:
  			conditions.append("finance_book in (%(finance_book)s)")
  
  	from frappe.desk.reportview import build_match_conditions
  	match_conditions = build_match_conditions("GL Entry")
  
  	if match_conditions:
  		conditions.append(match_conditions)
  
  	accounting_dimensions = get_accounting_dimensions(as_list=False)
  
  	if accounting_dimensions:
  		for dimension in accounting_dimensions:
  			if filters.get(dimension.fieldname):
  				if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
  					filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
  						filters.get(dimension.fieldname))
  				conditions.append("{0} in %({0})s".format(dimension.fieldname))
  
  	return "and {}".format(" and ".join(conditions)) if conditions else ""
  
  
  def get_data_with_opening_closing(filters, gl_entries):
  	data = []
  
  	gle_map = initialize_gle_map(gl_entries, filters)
  
  	totals, entries = get_accountwise_gle(filters, gl_entries, gle_map)
  
  	# Opening for filtered account
  	data.append(totals.opening)
  
  	if filters.get("group_by") != _('Group by Voucher (Consolidated)'):
  		for acc, acc_dict in iteritems(gle_map):
  			# acc
  			if acc_dict.entries:
  				# opening
  				data.append({})
  				if filters.get("group_by") != _("Group by Voucher"):
  					data.append(acc_dict.totals.opening)
  
  				data += acc_dict.entries
  
  				# totals
  				data.append(acc_dict.totals.total)
  
  				# closing
  				if filters.get("group_by") != _("Group by Voucher"):
  					data.append(acc_dict.totals.closing)
  		data.append({})
  	else:
  		data += entries
  
  	# totals
  	data.append(totals.total)
  
  	# closing
  	data.append(totals.closing)
  
  	return data
  
  def get_totals_dict():
  	def _get_debit_credit_dict(label):
  		return _dict(
  			account="'{0}'".format(label),
  			debit=0.0,
  			credit=0.0,
  			debit_in_account_currency=0.0,
  			credit_in_account_currency=0.0
  		)
  	return _dict(
  		opening = _get_debit_credit_dict(_('Opening')),
  		total = _get_debit_credit_dict(_('Total')),
  		closing = _get_debit_credit_dict(_('Closing (Opening + Total)'))
  	)
  
  def group_by_field(group_by):
  	if group_by == _('Group by Party'):
  		return 'party'
  	elif group_by in [_('Group by Voucher (Consolidated)'), _('Group by Account')]:
  		return 'account'
  	else:
  		return 'voucher_no'
  
  def initialize_gle_map(gl_entries, filters):
  	gle_map = OrderedDict()
  	group_by = group_by_field(filters.get('group_by'))
  
  	for gle in gl_entries:
  		gle_map.setdefault(gle.get(group_by), _dict(totals=get_totals_dict(), entries=[]))
  	return gle_map
  
  
  def get_accountwise_gle(filters, gl_entries, gle_map):
  	totals = get_totals_dict()
  	entries = []
  	consolidated_gle = OrderedDict()
  	group_by = group_by_field(filters.get('group_by'))
  
  	def update_value_in_dict(data, key, gle):
  		data[key].debit += flt(gle.debit)
  		data[key].credit += flt(gle.credit)
  
  		data[key].debit_in_account_currency += flt(gle.debit_in_account_currency)
  		data[key].credit_in_account_currency += flt(gle.credit_in_account_currency)
  
  	from_date, to_date = getdate(filters.from_date), getdate(filters.to_date)
  	for gle in gl_entries:
  		if (gle.posting_date < from_date or
  			(cstr(gle.is_opening) == "Yes" and not filters.get("show_opening_entries"))):
  			update_value_in_dict(gle_map[gle.get(group_by)].totals, 'opening', gle)
  			update_value_in_dict(totals, 'opening', gle)
  
  			update_value_in_dict(gle_map[gle.get(group_by)].totals, 'closing', gle)
  			update_value_in_dict(totals, 'closing', gle)
  
  		elif gle.posting_date <= to_date:
  			update_value_in_dict(gle_map[gle.get(group_by)].totals, 'total', gle)
  			update_value_in_dict(totals, 'total', gle)
  			if filters.get("group_by") != _('Group by Voucher (Consolidated)'):
  				gle_map[gle.get(group_by)].entries.append(gle)
  			elif filters.get("group_by") == _('Group by Voucher (Consolidated)'):
  				key = (gle.get("voucher_type"), gle.get("voucher_no"),
  					gle.get("account"), gle.get("cost_center"))
  				if key not in consolidated_gle:
  					consolidated_gle.setdefault(key, gle)
  				else:
  					update_value_in_dict(consolidated_gle, key, gle)
  
  			update_value_in_dict(gle_map[gle.get(group_by)].totals, 'closing', gle)
  			update_value_in_dict(totals, 'closing', gle)
  
  	for key, value in consolidated_gle.items():
  		entries.append(value)
  
  	return totals, entries
  
  def get_result_as_list(data, filters):
  	balance = 0
  	inv_details = get_supplier_invoice_details()
  	updated_data = []
  
  	for d in data:
  		if not d.get('posting_date'):
  			balance = 0
  
  		balance = get_balance(d, balance, 'debit', 'credit')
  		d['balance'] = balance
  
  		d['account_currency'] = filters.account_currency
  		d['bill_no'] = inv_details.get(d.get('against_voucher'), '')
  
  		if not ((d.get("credit_foreign") and d.get("debit_foreign")) and (d.get("credit_foreign") == d.get("debit_foreign"))):
  			updated_data.append(d)
  
  			if d.get("voucher_type") == "Journal Entry" and filters.get("account"):
  				jv_doc = frappe.get_doc(d["voucher_type"],d["voucher_no"])
  				for row in jv_doc.accounts:
  					if row.account != d["account"]:
  						new_entry = {}
  						new_entry["posting_date"] = d["posting_date"]
  						new_entry["against_acount"] = row.account
  						new_entry["against_debit"] = row.debit_in_account_currency
  						new_entry["against_credit"] = row.credit_in_account_currency
  						new_entry["against_currency"] = row.account_currency
  						updated_data.append(new_entry)
  
  	return updated_data
  
  def get_supplier_invoice_details():
  	inv_details = {}
  	for d in frappe.db.sql(""" select name, bill_no from `tabPurchase Invoice`
  		where docstatus = 1 and bill_no is not null and bill_no != '' """, as_dict=1):
  		inv_details[d.name] = d.bill_no
  	return inv_details
  
  def get_balance(row, balance, debit_field, credit_field):
  	balance += (row.get(debit_field, 0) - row.get(credit_field, 0))
  	return balance
  
  def get_columns(filters):
  	if filters.get("presentation_currency"):
  		currency = filters["presentation_currency"]
  	else:
  		if filters.get("company"):
  			currency = get_company_currency(filters["company"])
  		else:
  			company = get_default_company()
  			currency = get_company_currency(company)
  
  	columns = [
  		{
  			"label": _("Posting Date"),
  			"fieldname": "posting_date",
  			"fieldtype": "Date",
  			"width": 90
  		},
  		{
  			"label": _("Account"),
  			"fieldname": "account",
  			"fieldtype": "Link",
  			"options": "Account",
  			"width": 180
  		},
  		{
  			"label": _("Debit ({0})".format(currency)),
  			"fieldname": "debit",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Credit ({0})".format(currency)),
  			"fieldname": "credit",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Debit Foreign"),
  			"fieldname": "debit_foreign",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Credit Foreign"),
  			"fieldname": "credit_foreign",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Foreign Currency"),
  			"fieldname": "foreign_currency",
  			# "fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Exchange Rate"),
  			"fieldname": "exchange_rate",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Balance ({0})".format(currency)),
  			"fieldname": "balance",
  			"fieldtype": "Float",
  			"width": 130
  		}
  	]
  
  	columns.extend([
  		{
  			"label": _("Voucher Type"),
  			"fieldname": "voucher_type",
  			"width": 120
  		},
  		{
  			"label": _("Voucher No"),
  			"fieldname": "voucher_no",
  			"fieldtype": "Dynamic Link",
  			"options": "voucher_type",
  			"width": 180
  		},
  		{
  			"label": _("Against Account"),
  			"fieldname": "against",
  			"width": 120
  		},
  		{
  			"label": _("Against AC Name"),
  			"fieldname": "against_acount",
  			"width": 120
  		},
  		{
  			"label": _("Against Debit"),
  			"fieldname": "against_debit",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Against Credit"),
  			"fieldname": "against_credit",
  			"fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Against Currency"),
  			"fieldname": "against_currency",
  			# "fieldtype": "Float",
  			"width": 100
  		},
  		{
  			"label": _("Party Type"),
  			"fieldname": "party_type",
  			"width": 100
  		},
  		{
  			"label": _("Party"),
  			"fieldname": "party",
  			"width": 100
  		},
  		{
  			"label": _("Project"),
  			"options": "Project",
  			"fieldname": "project",
  			"width": 100
  		},
  		{
  			"label": _("Cost Center"),
  			"options": "Cost Center",
  			"fieldname": "cost_center",
  			"width": 100
  		},
  		{
  			"label": _("Against Voucher Type"),
  			"fieldname": "against_voucher_type",
  			"width": 100
  		},
  		{
  			"label": _("Against Voucher"),
  			"fieldname": "against_voucher",
  			"fieldtype": "Dynamic Link",
  			"options": "against_voucher_type",
  			"width": 100
  		},
  		{
  			"label": _("Supplier Invoice No"),
  			"fieldname": "bill_no",
  			"fieldtype": "Data",
  			"width": 100
  		},
  		{
  			"label": _("Remarks"),
  			"fieldname": "remarks",
  			"width": 400
  		},
  		{
  			"label": _("Items"),
  			"fieldname": "items",
  			"width": 400
  		}
  	])
  
  	return columns
  