# Copyright (c) 2015, Frappe Technologies Pvt. Ltd. and Contributors
# License: GNU General Public License v3. See license.txt


import frappe
from frappe import _
from frappe.query_builder.functions import Sum
from frappe.utils import add_days, cstr, flt, formatdate, getdate

import erpnext
from erpnext.accounts.doctype.accounting_dimension.accounting_dimension import (
	get_accounting_dimensions,
	get_dimension_with_children,
)
from erpnext.accounts.report.financial_statements import (
	filter_accounts,
	filter_out_zero_value_rows,
	get_cost_centers_with_children,
	set_gl_entries_by_account,
)
from erpnext.accounts.report.utils import convert_to_presentation_currency, get_currency
from erpnext.accounts.utils import get_zero_cutoff

value_fields = (
	"opening_debit",
	"opening_credit",
	"debit",
	"credit",
	"closing_debit",
	"closing_credit",
)


def execute(filters=None):
	validate_filters(filters)
	data = get_data(filters)
	columns = get_columns()
	return columns, data


def validate_filters(filters):
	if not filters.fiscal_year:
		frappe.throw(_("Fiscal Year {0} is required").format(filters.fiscal_year))

	fiscal_year = frappe.get_cached_value(
		"Fiscal Year", filters.fiscal_year, ["year_start_date", "year_end_date"], as_dict=True
	)
	if not fiscal_year:
		frappe.throw(_("Fiscal Year {0} does not exist").format(filters.fiscal_year))
	else:
		filters.year_start_date = getdate(fiscal_year.year_start_date)
		filters.year_end_date = getdate(fiscal_year.year_end_date)

	if not filters.from_date:
		filters.from_date = filters.year_start_date

	if not filters.to_date:
		filters.to_date = filters.year_end_date

	filters.from_date = getdate(filters.from_date)
	filters.to_date = getdate(filters.to_date)

	if filters.from_date > filters.to_date:
		frappe.throw(_("From Date cannot be greater than To Date"))

	if (filters.from_date < filters.year_start_date) or (filters.from_date > filters.year_end_date):
		frappe.msgprint(
			_("From Date should be within the Fiscal Year. Assuming From Date = {0}").format(
				formatdate(filters.year_start_date)
			)
		)

		filters.from_date = filters.year_start_date

	if (filters.to_date < filters.year_start_date) or (filters.to_date > filters.year_end_date):
		frappe.msgprint(
			_("To Date should be within the Fiscal Year. Assuming To Date = {0}").format(
				formatdate(filters.year_end_date)
			)
		)
		filters.to_date = filters.year_end_date


def get_data(filters):
	accounts = frappe.db.sql(
		"""select name, account_number, parent_account, account_name, root_type, report_type, is_group, lft, rgt

		from `tabAccount` where company=%s order by lft""",
		filters.company,
		as_dict=True,
	)
	company_currency = filters.presentation_currency or erpnext.get_company_currency(filters.company)

	ignore_is_opening = frappe.get_single_value("Accounts Settings", "ignore_is_opening_check_for_reporting")

	if not accounts:
		return None

	accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)

	gl_entries_by_account = {}

	opening_balances = get_opening_balances(filters, ignore_is_opening)

	set_gl_entries_by_account(
		filters.company,
		filters.from_date,
		filters.to_date,
		filters,
		gl_entries_by_account,
		root_lft=None,
		root_rgt=None,
		ignore_closing_entries=not flt(filters.with_period_closing_entry_for_current_period),
		ignore_opening_entries=True,
		group_by_account=True,
	)

	calculate_values(
		accounts,
		gl_entries_by_account,
		opening_balances,
		filters.get("show_net_values"),
		ignore_is_opening=ignore_is_opening,
	)
	accumulate_values_into_parents(accounts, accounts_by_name)

	data = prepare_data(accounts, filters, parent_children_map, company_currency)
	data = filter_out_zero_value_rows(
		data, parent_children_map, show_zero_values=filters.get("show_zero_values")
	)

	return data


def get_opening_balances(filters, ignore_is_opening, exchange_rate=None, ignore_reporting_currency=True):
	balance_sheet_opening = get_rootwise_opening_balances(
		filters, "Balance Sheet", ignore_is_opening, exchange_rate, ignore_reporting_currency
	)
	pl_opening = get_rootwise_opening_balances(
		filters, "Profit and Loss", ignore_is_opening, exchange_rate, ignore_reporting_currency
	)

	balance_sheet_opening.update(pl_opening)
	return balance_sheet_opening


def get_rootwise_opening_balances(
	filters, report_type, ignore_is_opening, exchange_rate=None, ignore_reporting_currency=True
):
	gle = []

	last_period_closing_voucher = ""
	ignore_closing_balances = frappe.get_single_value("Accounts Settings", "ignore_account_closing_balance")

	if not ignore_closing_balances:
		last_period_closing_voucher = frappe.db.get_all(
			"Period Closing Voucher",
			filters={"docstatus": 1, "company": filters.company, "period_end_date": ("<", filters.from_date)},
			fields=["period_end_date", "name"],
			order_by="period_end_date desc",
			limit=1,
		)

	accounting_dimensions = get_accounting_dimensions(as_list=False)

	if last_period_closing_voucher:
		gle = get_opening_balance(
			"Account Closing Balance",
			filters,
			report_type,
			accounting_dimensions,
			period_closing_voucher=last_period_closing_voucher[0].name,
			ignore_is_opening=ignore_is_opening,
			ignore_reporting_currency=ignore_reporting_currency,
		)

		# Report getting generate from the mid of a fiscal year
		if getdate(last_period_closing_voucher[0].period_end_date) < getdate(add_days(filters.from_date, -1)):
			start_date = add_days(last_period_closing_voucher[0].period_end_date, 1)
			gle += get_opening_balance(
				"GL Entry",
				filters,
				report_type,
				accounting_dimensions,
				start_date=start_date,
				ignore_is_opening=ignore_is_opening,
				ignore_reporting_currency=ignore_reporting_currency,
			)
	else:
		gle = get_opening_balance(
			"GL Entry",
			filters,
			report_type,
			accounting_dimensions,
			ignore_is_opening=ignore_is_opening,
			ignore_reporting_currency=ignore_reporting_currency,
		)

	opening = frappe._dict()
	for d in gle:
		opening_dr_cr = {
			"account": d.account,
			"opening_debit": 0.0,
			"opening_credit": 0.0,
		}

		opening.setdefault(d.account, opening_dr_cr)

		if ignore_reporting_currency:
			opening[d.account]["opening_debit"] += flt(d.debit)
			opening[d.account]["opening_credit"] += flt(d.credit)

		else:
			if d.get("report_type") == "Balance Sheet" and not (
				d.get("root_type") == "Equity" or d.get("account_type") == "Equity"
			):
				opening[d.account]["opening_debit"] += flt(d.debit) * flt(exchange_rate)
				opening[d.account]["opening_credit"] += flt(d.credit) * flt(exchange_rate)
			else:
				opening[d.account]["opening_debit"] += flt(d.debit_in_reporting_currency)
				opening[d.account]["opening_credit"] += flt(d.credit_in_reporting_currency)

	return opening


def get_opening_balance(
	doctype,
	filters,
	report_type,
	accounting_dimensions,
	period_closing_voucher=None,
	start_date=None,
	ignore_is_opening=0,
	ignore_reporting_currency=True,
):
	closing_balance = frappe.qb.DocType(doctype)
	accounts = frappe.db.get_all("Account", filters={"report_type": report_type}, pluck="name")

	opening_balance = (
		frappe.qb.from_(closing_balance)
		.select(
			closing_balance.account,
			closing_balance.account_currency,
			Sum(closing_balance.debit).as_("debit"),
			Sum(closing_balance.credit).as_("credit"),
			Sum(closing_balance.debit_in_account_currency).as_("debit_in_account_currency"),
			Sum(closing_balance.credit_in_account_currency).as_("credit_in_account_currency"),
		)
		.where((closing_balance.company == filters.company) & (closing_balance.account.isin(accounts)))
		.groupby(closing_balance.account)
	)

	if not ignore_reporting_currency:
		opening_balance = opening_balance.select(
			Sum(closing_balance.debit_in_reporting_currency).as_("debit_in_reporting_currency"),
			Sum(closing_balance.credit_in_reporting_currency).as_("credit_in_reporting_currency"),
		)

	if period_closing_voucher:
		opening_balance = opening_balance.where(
			closing_balance.period_closing_voucher == period_closing_voucher
		)
	else:
		if start_date:
			opening_balance = opening_balance.where(
				(closing_balance.posting_date >= start_date)
				& (closing_balance.posting_date < filters.from_date)
			)

			if not ignore_is_opening:
				opening_balance = opening_balance.where(closing_balance.is_opening == "No")
		else:
			if not ignore_is_opening:
				opening_balance = opening_balance.where(
					(closing_balance.posting_date < filters.from_date) | (closing_balance.is_opening == "Yes")
				)
			else:
				opening_balance = opening_balance.where(closing_balance.posting_date < filters.from_date)

	if doctype == "GL Entry":
		opening_balance = opening_balance.where(closing_balance.is_cancelled == 0)

	if (
		not filters.show_unclosed_fy_pl_balances
		and report_type == "Profit and Loss"
		and doctype == "GL Entry"
	):
		opening_balance = opening_balance.where(closing_balance.posting_date >= filters.year_start_date)

	if not flt(filters.with_period_closing_entry_for_opening):
		if doctype == "Account Closing Balance":
			opening_balance = opening_balance.where(closing_balance.is_period_closing_voucher_entry == 0)
		else:
			opening_balance = opening_balance.where(closing_balance.voucher_type != "Period Closing Voucher")

	if filters.cost_center:
		opening_balance = opening_balance.where(
			closing_balance.cost_center.isin(get_cost_centers_with_children(filters.get("cost_center")))
		)

	if filters.project:
		opening_balance = opening_balance.where(closing_balance.project.isin(filters.project))

	if frappe.db.count("Finance Book"):
		if filters.get("include_default_book_entries"):
			company_fb = frappe.get_cached_value("Company", filters.company, "default_finance_book")

			if filters.finance_book and company_fb and cstr(filters.finance_book) != cstr(company_fb):
				frappe.throw(
					_("To use a different finance book, please uncheck 'Include Default FB Entries'")
				)

			opening_balance = opening_balance.where(
				(closing_balance.finance_book.isin([cstr(filters.finance_book), cstr(company_fb), ""]))
				| (closing_balance.finance_book.isnull())
			)
		else:
			opening_balance = opening_balance.where(
				(closing_balance.finance_book.isin([cstr(filters.finance_book), ""]))
				| (closing_balance.finance_book.isnull())
			)

	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)
					)
					opening_balance = opening_balance.where(
						closing_balance[dimension.fieldname].isin(filters[dimension.fieldname])
					)
				else:
					opening_balance = opening_balance.where(
						closing_balance[dimension.fieldname].isin(filters[dimension.fieldname])
					)

	gle = opening_balance.run(as_dict=1)

	if filters and filters.get("presentation_currency") and ignore_reporting_currency:
		convert_to_presentation_currency(gle, get_currency(filters))

	return gle


def calculate_values(
	accounts,
	gl_entries_by_account,
	opening_balances,
	show_net_values,
	ignore_is_opening=0,
	exchange_rate=None,
	ignore_reporting_currency=True,
):
	init = {
		"opening_debit": 0.0,
		"opening_credit": 0.0,
		"debit": 0.0,
		"credit": 0.0,
		"closing_debit": 0.0,
		"closing_credit": 0.0,
	}

	for d in accounts:
		d.update(init.copy())

		# add opening
		d["opening_debit"] = opening_balances.get(d.name, {}).get("opening_debit", 0)
		d["opening_credit"] = opening_balances.get(d.name, {}).get("opening_credit", 0)

		for entry in gl_entries_by_account.get(d.name, []):
			if cstr(entry.is_opening) != "Yes" or ignore_is_opening:
				if ignore_reporting_currency:
					d["debit"] += flt(entry.debit)
					d["credit"] += flt(entry.credit)
				else:
					if d.report_type == "Balance Sheet" and not (
						d.root_type == "Equity" or d.account_type == "Equity"
					):
						d["debit"] += flt(entry.debit) * flt(exchange_rate)
						d["credit"] += flt(entry.credit) * flt(exchange_rate)
					else:
						d["debit"] += flt(entry.debit_in_reporting_currency)
						d["credit"] += flt(entry.credit_in_reporting_currency)

		d["closing_debit"] = d["opening_debit"] + d["debit"]
		d["closing_credit"] = d["opening_credit"] + d["credit"]

		if show_net_values:
			prepare_opening_closing(d)


def calculate_total_row(data, company_currency, show_group_accounts=True):
	total_row = {
		"account": "'" + _("Total") + "'",
		"account_name": "'" + _("Total") + "'",
		"warn_if_negative": True,
		"opening_debit": 0.0,
		"opening_credit": 0.0,
		"debit": 0.0,
		"credit": 0.0,
		"closing_debit": 0.0,
		"closing_credit": 0.0,
		"parent_account": None,
		"indent": 0,
		"has_value": True,
		"currency": company_currency,
	}

	def sum_value_fields(row):
		for field in value_fields:
			total_row[field] += row[field]

	for d in data:
		if not show_group_accounts:
			sum_value_fields(d)

		elif show_group_accounts and not d.get("parent_account"):
			sum_value_fields(d)

	return total_row


def accumulate_values_into_parents(accounts, accounts_by_name):
	for d in reversed(accounts):
		if d.parent_account:
			for key in value_fields:
				accounts_by_name[d.parent_account][key] += d[key]


def prepare_data(accounts, filters, parent_children_map, company_currency):
	data = []

	for d in accounts:
		# Prepare opening closing for group account
		if parent_children_map.get(d.account) and filters.get("show_net_values"):
			prepare_opening_closing(d)

		has_value = False
		row = {
			"account": d.name,
			"parent_account": d.parent_account,
			"indent": d.indent,
			"from_date": filters.from_date,
			"to_date": filters.to_date,
			"currency": company_currency,
			"is_group_account": d.is_group,
			"acc_name": d.account_name,
			"acc_number": d.account_number,
			"account_name": (
				f"{d.account_number} - {d.account_name}" if d.account_number else d.account_name
			),
		}

		for key in value_fields:
			row[key] = flt(d.get(key, 0.0))

			if abs(row[key]) >= get_zero_cutoff(company_currency):
				# ignore zero values
				has_value = True

		row["has_value"] = has_value
		data.append(row)

	if not filters.get("show_group_accounts"):
		data = hide_group_accounts(data)

	total_row = calculate_total_row(
		data, company_currency, show_group_accounts=filters.get("show_group_accounts")
	)

	data.extend([{}, total_row])

	return data


def get_columns():
	return [
		{
			"fieldname": "account",
			"label": _("Account"),
			"fieldtype": "Link",
			"options": "Account",
			"width": 300,
		},
		{
			"fieldname": "acc_name",
			"label": _("Account Name"),
			"fieldtype": "Data",
			"hidden": 1,
			"width": 250,
		},
		{
			"fieldname": "acc_number",
			"label": _("Account Number"),
			"fieldtype": "Data",
			"hidden": 1,
			"width": 120,
		},
		{
			"fieldname": "currency",
			"label": _("Currency"),
			"fieldtype": "Link",
			"options": "Currency",
			"hidden": 1,
		},
		{
			"fieldname": "opening_debit",
			"label": _("Opening (Dr)"),
			"fieldtype": "Currency",
			"options": "currency",
			"width": 120,
		},
		{
			"fieldname": "opening_credit",
			"label": _("Opening (Cr)"),
			"fieldtype": "Currency",
			"options": "currency",
			"width": 120,
		},
		{
			"fieldname": "debit",
			"label": _("Debit"),
			"fieldtype": "Currency",
			"options": "currency",
			"width": 120,
		},
		{
			"fieldname": "credit",
			"label": _("Credit"),
			"fieldtype": "Currency",
			"options": "currency",
			"width": 120,
		},
		{
			"fieldname": "closing_debit",
			"label": _("Closing (Dr)"),
			"fieldtype": "Currency",
			"options": "currency",
			"width": 120,
		},
		{
			"fieldname": "closing_credit",
			"label": _("Closing (Cr)"),
			"fieldtype": "Currency",
			"options": "currency",
			"width": 120,
		},
	]


def prepare_opening_closing(row):
	dr_or_cr = "debit" if row["root_type"] in ["Asset", "Equity", "Expense"] else "credit"
	reverse_dr_or_cr = "credit" if dr_or_cr == "debit" else "debit"

	for col_type in ["opening", "closing"]:
		valid_col = col_type + "_" + dr_or_cr
		reverse_col = col_type + "_" + reverse_dr_or_cr
		row[valid_col] -= row[reverse_col]
		if row[valid_col] < 0:
			row[reverse_col] = abs(row[valid_col])
			row[valid_col] = 0.0
		else:
			row[reverse_col] = 0.0


def hide_group_accounts(data):
	non_group_accounts_data = []
	for d in data:
		if not d.get("is_group_account"):
			d.update(indent=0)
			non_group_accounts_data.append(d)
	return non_group_accounts_data


def execute_snapshot_report(filters):
	from frappe.database.duckdb.database import get_latest_sync

	if conn := get_latest_sync("GL Entry"):
		validate_filters(filters)
		columns = get_columns()
		data = get_data_duckdb(filters, conn)
		return columns, data
	else:
		frappe.throw(_("Trial Balance requires {0} to be synced to DuckDB").format(frappe.bold("GL Entry")))


def get_data_duckdb(filters, conn):
	# accounts and all metadata via frappe.db — only GL Entry comes from DuckDB
	accounts = frappe.db.sql(
		"""select name, account_number, parent_account, account_name, root_type, report_type, is_group, lft, rgt
		from `tabAccount` where company=%s order by lft""",
		filters.company,
		as_dict=True,
	)
	if not accounts:
		return None

	company_currency = filters.presentation_currency or erpnext.get_company_currency(filters.company)
	ignore_is_opening = frappe.get_single_value("Accounts Settings", "ignore_is_opening_check_for_reporting")
	accounts, accounts_by_name, parent_children_map = filter_accounts(accounts)

	gl_entries_by_account = get_period_gl_entries_duckdb(conn, filters, ignore_is_opening)
	opening_balances = get_opening_balances_duckdb(conn, filters, ignore_is_opening)

	calculate_values(
		accounts,
		gl_entries_by_account,
		opening_balances,
		filters.get("show_net_values"),
		ignore_is_opening=ignore_is_opening,
	)
	accumulate_values_into_parents(accounts, accounts_by_name)

	data = prepare_data(accounts, filters, parent_children_map, company_currency)
	return filter_out_zero_value_rows(
		data, parent_children_map, show_zero_values=filters.get("show_zero_values")
	)


def _extra_gl_conditions(filters):
	"""Returns (conditions, params) for optional shared GL Entry filters."""
	conditions, params = [], []

	if filters.get("cost_center"):
		cc = get_cost_centers_with_children(filters.get("cost_center"))
		conditions.append(f"cost_center IN ({', '.join(['?'] * len(cc))})")
		params.extend(cc)

	if filters.get("project"):
		proj = filters.project if isinstance(filters.project, list) else [filters.project]
		conditions.append(f"project IN ({', '.join(['?'] * len(proj))})")
		params.extend(proj)

	if frappe.db.count("Finance Book"):
		company_fb = frappe.get_cached_value("Company", filters.company, "default_finance_book")
		if filters.get("include_default_book_entries"):
			if filters.get("finance_book") and company_fb and cstr(filters.finance_book) != cstr(company_fb):
				frappe.throw(
					_("To use a different finance book, please uncheck 'Include Default FB Entries'")
				)
			fb_list = [cstr(filters.get("finance_book")), cstr(company_fb), ""]
		else:
			fb_list = [cstr(filters.get("finance_book")), ""]
		conditions.append(f"(finance_book IN ({', '.join(['?'] * len(fb_list))}) OR finance_book IS NULL)")
		params.extend(fb_list)

	for dim in get_accounting_dimensions(as_list=False):
		if filters.get(dim.fieldname):
			if frappe.get_cached_value("DocType", dim.document_type, "is_tree"):
				filters[dim.fieldname] = get_dimension_with_children(
					dim.document_type, filters.get(dim.fieldname)
				)
			vals = (
				filters[dim.fieldname]
				if isinstance(filters[dim.fieldname], list)
				else [filters[dim.fieldname]]
			)
			conditions.append(f"{dim.fieldname} IN ({', '.join(['?'] * len(vals))})")
			params.extend(vals)

	return conditions, params


def _fetch_gl_rows_duckdb(conn, conditions, params):
	cols = [
		"account",
		"debit",
		"credit",
		"debit_in_account_currency",
		"credit_in_account_currency",
		"account_currency",
	]
	sql = f"""SELECT account, SUM(debit), SUM(credit),
	                 SUM(debit_in_account_currency), SUM(credit_in_account_currency), account_currency
	          FROM "tabGL Entry" WHERE {" AND ".join(conditions)}
	          GROUP BY account, account_currency"""
	return [frappe._dict(zip(cols, row, strict=False)) for row in conn.execute(sql, params).fetchall()]


def get_period_gl_entries_duckdb(conn, filters, ignore_is_opening):
	conditions = ["company = ?", "is_cancelled = 0", "posting_date >= ?", "posting_date <= ?"]
	params = [filters.company, filters.from_date, filters.to_date]

	if not ignore_is_opening:
		conditions.append("is_opening = 'No'")
	if not flt(filters.get("with_period_closing_entry_for_current_period")):
		conditions.append("voucher_type != 'Period Closing Voucher'")

	extra_cond, extra_params = _extra_gl_conditions(filters)
	conditions.extend(extra_cond)
	params.extend(extra_params)

	entries = _fetch_gl_rows_duckdb(conn, conditions, params)
	if filters.get("presentation_currency"):
		convert_to_presentation_currency(entries, get_currency(filters))

	gl_entries_by_account = {}
	for entry in entries:
		gl_entries_by_account.setdefault(entry.account, []).append(entry)
	return gl_entries_by_account


def get_opening_balances_duckdb(conn, filters, ignore_is_opening):
	bs = _get_rootwise_opening_duckdb(conn, filters, "Balance Sheet", ignore_is_opening)
	pl = _get_rootwise_opening_duckdb(conn, filters, "Profit and Loss", ignore_is_opening)
	bs.update(pl)
	return bs


def _get_rootwise_opening_duckdb(conn, filters, report_type, ignore_is_opening):
	accounting_dimensions = get_accounting_dimensions(as_list=False)
	ignore_closing_balances = frappe.get_single_value("Accounts Settings", "ignore_account_closing_balance")
	last_pcv = ""

	if not ignore_closing_balances:
		last_pcv = frappe.db.get_all(
			"Period Closing Voucher",
			filters={"docstatus": 1, "company": filters.company, "period_end_date": ("<", filters.from_date)},
			fields=["period_end_date", "name"],
			order_by="period_end_date desc",
			limit=1,
		)

	if last_pcv:
		# Account Closing Balance fetched via frappe (not GL Entry)
		gle = get_opening_balance(
			"Account Closing Balance",
			filters,
			report_type,
			accounting_dimensions,
			period_closing_voucher=last_pcv[0].name,
			ignore_is_opening=ignore_is_opening,
		)
		if getdate(last_pcv[0].period_end_date) < getdate(add_days(filters.from_date, -1)):
			start_date = add_days(last_pcv[0].period_end_date, 1)
			gle += _get_gl_entry_opening_duckdb(
				conn, filters, report_type, ignore_is_opening, start_date=start_date
			)
	else:
		gle = _get_gl_entry_opening_duckdb(conn, filters, report_type, ignore_is_opening)

	opening = frappe._dict()
	for d in gle:
		opening.setdefault(d.account, {"account": d.account, "opening_debit": 0.0, "opening_credit": 0.0})
		opening[d.account]["opening_debit"] += flt(d.debit)
		opening[d.account]["opening_credit"] += flt(d.credit)
	return opening


def _get_gl_entry_opening_duckdb(conn, filters, report_type, ignore_is_opening, start_date=None):
	accounts = frappe.db.get_all("Account", filters={"report_type": report_type}, pluck="name")
	if not accounts:
		return []

	conditions = ["company = ?", f"account IN ({', '.join(['?'] * len(accounts))})", "is_cancelled = 0"]
	params = [filters.company, *accounts]

	if start_date:
		conditions.append("posting_date >= ? AND posting_date < ?")
		params.extend([start_date, filters.from_date])
		if not ignore_is_opening:
			conditions.append("is_opening = 'No'")
	elif not ignore_is_opening:
		conditions.append("(posting_date < ? OR is_opening = 'Yes')")
		params.append(filters.from_date)
	else:
		conditions.append("posting_date < ?")
		params.append(filters.from_date)

	if not filters.get("show_unclosed_fy_pl_balances") and report_type == "Profit and Loss":
		conditions.append("posting_date >= ?")
		params.append(filters.year_start_date)

	if not flt(filters.get("with_period_closing_entry_for_opening")):
		conditions.append("voucher_type != 'Period Closing Voucher'")

	extra_cond, extra_params = _extra_gl_conditions(filters)
	conditions.extend(extra_cond)
	params.extend(extra_params)

	gle = _fetch_gl_rows_duckdb(conn, conditions, params)
	if filters.get("presentation_currency"):
		convert_to_presentation_currency(gle, get_currency(filters))
	return gle
