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 execute(filters=None):
columns, data, message, chart = [], [], [], []
if not filters.get('company'):
return columns, data, message, chart
period_list = get_period_list(filters.from_date, filters.to_date,
filters.periodicity,
filters.accumulated_in_group_company)
if not filters.get('cost_center'):
frappe.msgprint(_("Please select at least one cost center."))
if not filters.get('include_child_cost_centers'):
cost_centers = filters.cost_center
else:
cost_centers = get_cost_centers_with_children(filters.cost_center)
columns = get_columns(cost_centers, filters.periodicity, period_list)
if filters.get('report') == "Balance Sheet":
data, message, chart = get_balance_sheet_data(period_list,
cost_centers,
columns,
filters,
cost_center_wise=True)
elif filters.get('report') == "Profit and Loss Statement":
data, message, chart = get_profit_loss_data(period_list,
cost_centers,
columns,
filters,
cost_center_wise=True)
return columns, data, message, chart
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("customer_group"):
conditions.append(
"party in (select name from `tabCustomer` where customer_group ='%s')"
% filters.get("customer_group"))
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")
if filters.get("project"):
conditions.append("project in %(project)s")
company_finance_book = erpnext.get_default_finance_book(
filters.get("company"))
if not filters.get("finance_book") or (filters.get("finance_book")
== company_finance_book):
filters['finance_book'] = company_finance_book
conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
elif filters.get("finance_book"):
conditions.append("ifnull(finance_book, '') = %(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)
return "and {}".format(" and ".join(conditions)) if 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("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")
if filters.get("project"):
conditions.append("project in %(project)s")
company_finance_book = erpnext.get_default_finance_book(filters.get("company"))
if not filters.get("finance_book") or (filters.get("finance_book") == company_finance_book):
filters['finance_book'] = company_finance_book
conditions.append("ifnull(finance_book, '') in (%(finance_book)s, '')")
elif filters.get("finance_book"):
conditions.append("ifnull(finance_book, '') = %(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)
return "and {}".format(" and ".join(conditions)) if conditions else ""
def execute(filters=None):
columns, data, message, chart = [], [], [], []
if not filters.get('company'):
return columns, data, message, chart
period_dict = {}
period_dict.update({
'year_start_date': filters.get('from_date'),
'year_end_date': filters.get('to_date')
})
if not filters.get('cost_center'):
frappe.msgprint(_("Please select at least one cost center."));
cost_centers = get_cost_centers_with_children(filters.cost_center)
columns = get_columns(cost_centers)
if filters.get('report') == "Balance Sheet":
data, message, chart = get_balance_sheet_data(period_dict, cost_centers, columns, filters, cost_center_wise=True)
elif filters.get('report') == "Profit and Loss Statement":
data, message, chart = get_profit_loss_data(period_dict, cost_centers, columns, filters, cost_center_wise=True)
return columns, data, message, chart
def get_conditions(self):
conditions = []
if self.filters.get("customer"):
conditions.append("s.customer=%(customer)s")
if self.filters.get("customer_group"):
lft, rgt = frappe.db.get_value("Customer Group",
self.filters.customer_group,
["lft", "rgt"])
conditions.append(
"""s.customer_group in (select name from `tabCustomer Group`
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if self.filters.get("supplier"):
conditions.append("s.supplier=%(supplier)s")
if self.filters.get("supplier_group"):
lft, rgt = frappe.db.get_value("Supplier Group",
self.filters.supplier_group,
["lft", "rgt"])
conditions.append(
"""sup.supplier_group in (select name from `tabSupplier Group`
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if self.filters.get("item_code"):
conditions.append("i.item_code=%(item_code)s")
if self.filters.get("item_group"):
lft, rgt = frappe.db.get_value("Item Group",
self.filters.item_group,
["lft", "rgt"])
conditions.append(
"""i.item_group in (select name from `tabItem Group`
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if self.filters.get("brand"):
conditions.append("i.brand=%(brand)s")
if self.filters.get("territory"):
lft, rgt = frappe.db.get_value("Territory", self.filters.territory,
["lft", "rgt"])
conditions.append(
"""s.territory in (select name from `tabTerritory`
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if self.filters.get("sales_person"):
lft, rgt = frappe.db.get_value("Sales Person",
self.filters.sales_person,
["lft", "rgt"])
conditions.append(
"""sp.sales_person in (select name from `tabSales Person`
where lft>=%s and rgt<=%s and docstatus<2)""" % (lft, rgt))
if self.filters.get("order_type"):
conditions.append("s.order_type=%(order_type)s")
if self.filters.get("cost_center"):
self.filters.cost_center = get_cost_centers_with_children(
self.filters.get("cost_center"))
conditions.append("i.cost_center in %(cost_center)s")
if self.filters.get("project"):
projects = cstr(self.filters.get("project")).strip()
self.filters.project = [
d.strip() for d in projects.split(',') if d
]
conditions.append("i.project in %(project)s" if frappe.get_meta(
self.filters.doctype +
" Item").has_field("project") else "s.project in %(project)s")
return "and {}".format(" and ".join(conditions)) if 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("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"):
voucher_filter_method = filters.get("voucher_filter_method")
if voucher_filter_method == "Posted Against Voucher":
conditions.append("against_voucher=%(voucher_no)s")
elif voucher_filter_method == "Posted By and Against Voucher":
conditions.append(
"voucher_no=%(voucher_no)s or against_voucher=%(voucher_no)s")
else:
conditions.append("voucher_no=%(voucher_no)s")
if filters.get("against_voucher"):
conditions.append("against_voucher=%(against_voucher)s")
if filters.get("reference_no"):
conditions.append("reference_no=%(reference_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 filters.get("account") or filters.get("party") \
or filters.get("group_by") in [_("Group by Account"), _("Group by Party")]:
conditions.append(
"(posting_date <= %(to_date)s or is_opening = 'Yes')")
else:
conditions.append("posting_date between %(from_date)s and %(to_date)s")
if not filters.get('show_opening_entries'):
conditions.append("is_opening != 'Yes'")
if filters.get("project"):
conditions.append("project in %(project)s")
if filters.get("sales_person"):
lft, rgt = frappe.db.get_value("Sales Person",
filters.get("sales_person"),
["lft", "rgt"])
conditions.append(
"""exists(select name from `tabSales Team` steam where
steam.sales_person in (select name from `tabSales Person` where lft >= {0} and rgt <= {1})
and steam.parent = party and steam.parenttype = party_type)""".format(
lft, rgt))
if filters.get("finance_book"):
conditions.append("ifnull(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)
return "and {}".format(" and ".join(conditions)) if conditions else ""
def get_conditions(filters, party_type=None):
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"):
if filters["party_type"] == 'Customer Group' and filters.get(
"party") and party_type == None:
lft, rgt = frappe.db.get_value("Customer Group",
filters["party"][0], ['lft', 'rgt'])
get_parent_customer_groups = frappe.db.sql(
"""select name from `tabCustomer Group` where lft >= %s and rgt <= %s""",
(lft, rgt),
as_dict=1)
customer_groups = [
"%s" % (frappe.db.escape(d.name))
for d in get_parent_customer_groups
]
if customer_groups:
cond = "and 1=1"
customer_group_condition = ",".join(
['%s'] * len(customer_groups)) % (tuple(customer_groups))
condition = "{0} in ({1})".format(' and customer_group',
customer_group_condition)
cond += condition
customer_list = frappe.db.sql(
""" select name from `tabCustomer` where docstatus < 2 {cond} """
.format(cond=cond),
as_list=1)
if len(customer_list) > 0:
filters["party"] = customer_list
else:
filters["party"] = ['1']
filters["party_type"] = 'Customer'
conditions.append("party_type=%(party_type)s")
else:
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)")
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()
if accounting_dimensions:
for dimension in accounting_dimensions:
if filters.get(dimension):
conditions.append("{0} in (%({0})s)".format(dimension))
return "and {}".format(" and ".join(conditions)) if conditions else ""
