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, '') OR finance_book IS NULL)")
else:
conditions.append("finance_book in (%(finance_book)s)")
if not filters.get("show_cancelled_entries"):
conditions.append("is_cancelled = 0")
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))
else:
conditions.append("{0} in (%({0})s)".format(dimension.fieldname))
return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
accounting_dimensions = get_accounting_dimensions(as_list=False)
if filters:
if filters.get("project"):
if not isinstance(filters.get("project"), list):
filters.project = frappe.parse_json(filters.get("project"))
additional_conditions.append("b.project in %(project)s")
if filters.get("cost_center"):
filters.cost_center = get_cost_centers_with_children(
filters.cost_center)
additional_conditions.append("b.cost_center in %(cost_center)s")
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))
additional_conditions.append("{0} in %({0})s".format(
dimension.fieldname))
else:
additional_conditions.append("{0} in (%({0})s)".format(
dimension.fieldname))
return " and {}".format(
" and ".join(additional_conditions)) if additional_conditions else ""
def get_conditions(filters):
conditions = ""
if filters.get("company"): conditions += " and company=%(company)s"
if filters.get("customer"): conditions += " and customer = %(customer)s"
if filters.get("from_date"):
conditions += " and posting_date >= %(from_date)s"
if filters.get("to_date"): conditions += " and posting_date <= %(to_date)s"
if filters.get("owner"): conditions += " and owner = %(owner)s"
if filters.get("mode_of_payment"):
conditions += """ and exists(select name from `tabSales Invoice Payment`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Payment`.mode_of_payment, '') = %(mode_of_payment)s)"""
if filters.get("cost_center"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.cost_center, '') = %(cost_center)s)"""
if filters.get("warehouse"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.warehouse, '') = %(warehouse)s)"""
if filters.get("brand"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.brand, '') = %(brand)s)"""
if filters.get("item_group"):
conditions += """ and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
and ifnull(`tabSales Invoice Item`.item_group, '') = %(item_group)s)"""
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
common_condition = """
and exists(select name from `tabSales Invoice Item`
where parent=`tabSales Invoice`.name
"""
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 += common_condition + "and ifnull(`tabSales Invoice Item`.{0}, '') in %({0})s)".format(
dimension.fieldname)
else:
conditions += common_condition + "and ifnull(`tabSales Invoice Item`.{0}, '') in (%({0})s))".format(
dimension.fieldname)
return conditions
def add_accounting_dimensions_filters(self, conditions, values):
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
if self.filters.get(dimension.fieldname):
if frappe.get_cached_value('DocType', dimension.document_type, 'is_tree'):
self.filters[dimension.fieldname] = get_dimension_with_children(dimension.document_type,
self.filters.get(dimension.fieldname))
conditions.append("{0} in %s".format(dimension.fieldname))
values.append(tuple(self.filters.get(dimension.fieldname)))
def get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
accounting_dimensions = get_accounting_dimensions(as_list=False)
if ignore_closing_entries:
additional_conditions.append(
"ifnull(voucher_type, '')!='Period Closing Voucher'")
if from_date:
additional_conditions.append("posting_date >= %(from_date)s")
if filters:
if filters.get("project"):
if not isinstance(filters.get("project"), list):
filters.project = frappe.parse_json(filters.get("project"))
additional_conditions.append("project in %(project)s")
if filters.get("cost_center"):
filters.cost_center = get_cost_centers_with_children(
filters.cost_center)
additional_conditions.append("cost_center in %(cost_center)s")
if filters.get("include_default_book_entries"):
additional_conditions.append(
"(finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
)
else:
additional_conditions.append(
"(finance_book in (%(finance_book)s, '') OR finance_book IS NULL)"
)
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))
additional_conditions.append("{0} in %({0})s".format(
dimension.fieldname))
else:
additional_conditions.append("{0} in (%({0})s)".format(
dimension.fieldname))
return " and {}".format(
" and ".join(additional_conditions)) if additional_conditions else ""
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("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")):
conditions.append("posting_date >=%(from_date)s")
conditions.append("(posting_date <=%(to_date)s or is_opening = 'Yes')")
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))
else:
conditions.append("{0} in (%({0})s)".format(
dimension.fieldname))
return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_rootwise_opening_balances(filters, report_type):
additional_conditions = ""
if not filters.show_unclosed_fy_pl_balances:
additional_conditions = (" and posting_date >= %(year_start_date)s"
if report_type == "Profit and Loss" else "")
if not flt(filters.with_period_closing_entry):
additional_conditions += " and ifnull(voucher_type, '')!='Period Closing Voucher'"
if filters.cost_center:
lft, rgt = frappe.db.get_value("Cost Center", filters.cost_center,
["lft", "rgt"])
additional_conditions += """ and cost_center in (select name from `tabCost Center`
where lft >= %s and rgt <= %s)""" % (
lft,
rgt,
)
if filters.project:
additional_conditions += " and project = %(project)s"
if filters.finance_book:
fb_conditions = " AND finance_book = %(finance_book)s"
if filters.include_default_book_entries:
fb_conditions = (
" AND (finance_book in (%(finance_book)s, %(company_fb)s, '') OR finance_book IS NULL)"
)
additional_conditions += fb_conditions
accounting_dimensions = get_accounting_dimensions(as_list=False)
query_filters = {
"company":
filters.company,
"from_date":
filters.from_date,
"report_type":
report_type,
"year_start_date":
filters.year_start_date,
"project":
filters.project,
"finance_book":
filters.finance_book,
"company_fb":
frappe.db.get_value("Company", filters.company,
"default_finance_book"),
}
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))
additional_conditions += " and {0} in %({0})s".format(
dimension.fieldname)
else:
additional_conditions += " and {0} in %({0})s".format(
dimension.fieldname)
query_filters.update(
{dimension.fieldname: filters.get(dimension.fieldname)})
gle = frappe.db.sql(
"""
select
account, sum(debit) as opening_debit, sum(credit) as opening_credit
from `tabGL Entry`
where
company=%(company)s
{additional_conditions}
and (posting_date < %(from_date)s or ifnull(is_opening, 'No') = 'Yes')
and account in (select name from `tabAccount` where report_type=%(report_type)s)
and is_cancelled = 0
group by account""".format(additional_conditions=additional_conditions),
query_filters,
as_dict=True,
)
opening = frappe._dict()
for d in gle:
opening.setdefault(d.account, d)
return opening
def get_data(filters, conditions):
data = []
inc, cond = "", ""
query_details = conditions["based_on_select"] + conditions[
"period_wise_select"]
posting_date = "t1.transaction_date"
if conditions.get("trans") in [
"Sales Invoice",
"Purchase Invoice",
"Purchase Receipt",
"Delivery Note",
]:
posting_date = "t1.posting_date"
if filters.period_based_on:
posting_date = "t1." + filters.period_based_on
accounting_dimensions = get_accounting_dimensions(as_list=False)
if accounting_dimensions:
for dimension in accounting_dimensions:
if not dimension.disabled and 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))
cond += " and t1.{0} in %({0})s".format(
dimension.fieldname)
else:
cond += " and t1.{0} = %({0})s".format(
dimension.fieldname)
if conditions["based_on_select"] in ["t1.project,", "t2.project,"]:
cond = " and " + conditions["based_on_select"][:-1] + " IS Not NULL"
if conditions.get("trans") in ["Sales Order", "Purchase Order"]:
cond += " and t1.status != 'Closed'"
if conditions.get("trans") == "Quotation" and filters.get(
"group_by") == "Customer":
cond += " and t1.quotation_to = 'Customer'"
year_start_date, year_end_date = frappe.db.get_value(
"Fiscal Year", filters.get("fiscal_year"),
["year_start_date", "year_end_date"])
if filters.get("group_by"):
sel_col = ""
ind = conditions["columns"].index(conditions["grbc"][0])
if filters.get("group_by") == "Item":
sel_col = "t2.item_code"
elif filters.get("group_by") == "Customer":
sel_col = ("t1.party_name" if conditions.get("trans")
== "Quotation" else "t1.customer")
elif filters.get("group_by") == "Supplier":
sel_col = "t1.supplier"
if filters.get("based_on") in ["Item", "Customer", "Supplier"]:
inc = 2
else:
inc = 1
data1 = frappe.db.sql(
"""
select {query_details}
from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables}
where t2.parent = t1.name
and t1.company = %(company)s
and {posting_date} between %(year_start_date)s and %(year_end_date)s
and t1.docstatus = 1
{cond}
{addl_tables_relational_cond}
group by {group_by}
""".format(
query_details=query_details,
trans=conditions["trans"],
addl_tables=conditions["addl_tables"],
posting_date=posting_date,
cond=cond,
addl_tables_relational_cond=conditions.get(
"addl_tables_relational_cond"),
group_by=conditions["group_by"],
),
values=filters.update({
"year_start_date": year_start_date,
"year_end_date": year_end_date
}),
as_list=1,
)
for d in range(len(data1)):
# to add blanck column
dt = data1[d]
dt.insert(ind, "")
data.append(dt)
# to get distinct value of col specified by group_by in filter
row = frappe.db.sql(
"""
select DISTINCT({sel_col})
from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables}
where t2.parent = t1.name
and t1.company = %(company)s
and {posting_date} between %(year_start_date)s and %(year_end_date)s
and t1.docstatus = 1
and {group_by} = %(group_by)s
{cond}
{addl_tables_relational_cond}
""".format(
sel_col=sel_col,
trans=conditions["trans"],
addl_tables=conditions["addl_tables"],
posting_date=posting_date,
cond=cond,
addl_tables_relational_cond=conditions.get(
"addl_tables_relational_cond"),
group_by=conditions["group_by"],
),
values=filters.update({
"year_start_date": year_start_date,
"year_end_date": year_end_date,
"group_by": data1[d][0],
}),
as_list=1,
)
for i in range(len(row)):
des = ["" for q in range(len(conditions["columns"]))]
# get data for group_by filter
row1 = frappe.db.sql(
"""
select {sel_col} , {period_wise_select}
from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables}
where t2.parent = t1.name
and t1.company = %(company)s
and {posting_date} between %(year_start_date)s and %(year_end_date)s
and t1.docstatus = 1
and {sel_col} = %(sel_col)s
and {group_by} = %(group_by)s
{cond}
{addl_tables_relational_cond}
""".format(
sel_col=sel_col,
period_wise_select=conditions["period_wise_select"],
trans=conditions["trans"],
addl_tables=conditions["addl_tables"],
posting_date=posting_date,
cond=cond,
addl_tables_relational_cond=conditions.get(
"addl_tables_relational_cond"),
group_by=conditions["group_by"],
),
values=filters.update({
"year_start_date": year_start_date,
"year_end_date": year_end_date,
"sel_col": row[i][0],
"group_by": data1[d][0],
}),
as_list=1,
)
des[ind] = row[i][0]
for j in range(1, len(conditions["columns"]) - inc):
des[j + inc] = row1[0][j]
data.append(des)
else:
data = frappe.db.sql(
"""
select {query_details}
from `tab{trans}` t1, `tab{trans} Item` t2 {addl_tables}
where t2.parent = t1.name
and t1.company = %(company)s
and {posting_date} between %(year_start_date)s and %(year_end_date)s
and t1.docstatus = 1
{cond}
{addl_tables_relational_cond}
group by {group_by}
""".format(
query_details=query_details,
trans=conditions["trans"],
addl_tables=conditions["addl_tables"],
posting_date=posting_date,
cond=cond,
addl_tables_relational_cond=conditions.get(
"addl_tables_relational_cond", ""),
group_by=conditions["group_by"],
),
values=filters.update({
"year_start_date": year_start_date,
"year_end_date": year_end_date
}),
as_list=1,
)
return data
