def prepare_conditions(self, party_type):
conditions = [""]
values = [party_type]
party_type_field = scrub(party_type)
if self.filters.company:
conditions.append("company=%s")
values.append(self.filters.company)
company_finance_book = erpnext.get_default_finance_book(self.filters.company)
if not self.filters.finance_book or (self.filters.finance_book == company_finance_book):
conditions.append("ifnull(finance_book,'') in (%s, '')")
values.append(company_finance_book)
elif self.filters.finance_book:
conditions.append("ifnull(finance_book,'') = %s")
values.append(self.filters.finance_book)
if self.filters.get(party_type_field):
conditions.append("party=%s")
values.append(self.filters.get(party_type_field))
if party_type_field=="customer":
if self.filters.get("customer_group"):
lft, rgt = frappe.db.get_value("Customer Group",
self.filters.get("customer_group"), ["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1}
and name=tabCustomer.customer_group))""".format(lft, rgt))
if self.filters.get("territory"):
lft, rgt = frappe.db.get_value("Territory",
self.filters.get("territory"), ["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabTerritory` where lft >= {0} and rgt <= {1}
and name=tabCustomer.territory))""".format(lft, rgt))
if self.filters.get("payment_terms_template"):
conditions.append("party in (select name from tabCustomer where payment_terms=%s)")
values.append(self.filters.get("payment_terms_template"))
if self.filters.get("sales_partner"):
conditions.append("party in (select name from tabCustomer where default_sales_partner=%s)")
values.append(self.filters.get("sales_partner"))
if self.filters.get("sales_person"):
conditions.append("""party in (select parent
from `tabSales Team` where sales_person=%s and parenttype = 'Customer')""")
values.append(self.filters.get("sales_person"))
if party_type_field=="supplier":
if self.filters.get("supplier_group"):
conditions.append("""party in (select name from tabSupplier
where supplier_group=%s)""")
values.append(self.filters.get("supplier_group"))
return " and ".join(conditions), values
def get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
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):
projects = str(filters.get("project")).strip()
filters.project = [d.strip() for d in projects.split(',') if d]
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")
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):
additional_conditions.append("ifnull(finance_book, '') in (%s, '')" %
frappe.db.escape(company_finance_book))
elif filters.get("finance_book"):
additional_conditions.append("ifnull(finance_book, '') = %s " %
frappe.db.escape(filters.get("finance_book")))
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
def get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
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):
projects = frappe.safe_encode(filters.get("project"))
filters.project = [d.strip() for d in projects.strip().split(',') if d]
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")
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):
additional_conditions.append("ifnull(finance_book, '') in ('%s', '')" %
frappe.db.escape(company_finance_book))
elif filters.get("finance_book"):
additional_conditions.append("ifnull(finance_book, '') = '%s' " %
frappe.db.escape(filters.get("finance_book")))
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
def get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
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"):
additional_conditions.append(
"project = '%s'" % (frappe.db.escape(filters.get("project"))))
if filters.get("cost_center"):
additional_conditions.append(
get_cost_center_cond(filters.get("cost_center")))
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):
additional_conditions.append(
"ifnull(finance_book, '') in ('%s', '')" %
frappe.db.escape(company_finance_book))
elif filters.get("finance_book"):
additional_conditions.append(
"ifnull(finance_book, '') = '%s' " %
frappe.db.escape(filters.get("finance_book")))
return " and {}".format(
" and ".join(additional_conditions)) if additional_conditions else ""
def get_default_finance_book_idx(self):
if not self.get('default_finance_book') and self.company:
self.default_finance_book = erpnext.get_default_finance_book(self.company)
if self.get('default_finance_book'):
for d in self.get('finance_books'):
if d.finance_book == self.default_finance_book:
return cint(d.idx) - 1
def get_default_finance_book_idx(self):
if not self.get('default_finance_book') and self.company:
self.default_finance_book = erpnext.get_default_finance_book(self.company)
if self.get('default_finance_book'):
for d in self.get('finance_books'):
if d.finance_book == self.default_finance_book:
return cint(d.idx) - 1
def get_data(filters):
data = []
depreciation_accounts = frappe.db.sql_list(""" select name from tabAccount
where ifnull(account_type, '') = 'Depreciation' """)
filters_data = [["company", "=", filters.get('company')],
["posting_date", ">=", filters.get('from_date')],
["posting_date", "<=", filters.get('to_date')],
["against_voucher_type", "=", "Asset"],
["account", "in", depreciation_accounts]]
if filters.get("asset"):
filters_data.append(["against_voucher", "=", filters.get("asset")])
if filters.get("asset_category"):
assets = frappe.db.sql_list("""select name from tabAsset
where asset_category = %s and docstatus=1""", filters.get("asset_category"))
filters_data.append(["against_voucher", "in", assets])
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_data.append(["finance_book", "in", ['', filters.get('finance_book')]])
elif filters.get("finance_book"):
filters_data.append(["finance_book", "=", filters.get('finance_book')])
gl_entries = frappe.get_all('GL Entry',
filters= filters_data,
fields = ["against_voucher", "debit_in_account_currency as debit", "voucher_no", "posting_date"],
order_by= "against_voucher, posting_date")
if not gl_entries:
return data
assets = [d.against_voucher for d in gl_entries]
assets_details = get_assets_details(assets)
for d in gl_entries:
asset_data = assets_details.get(d.against_voucher)
if not asset_data.get("accumulated_depreciation_amount"):
asset_data.accumulated_depreciation_amount = d.debit
else:
asset_data.accumulated_depreciation_amount += d.debit
row = frappe._dict(asset_data)
row.update({
"depreciation_amount": d.debit,
"depreciation_date": d.posting_date,
"amount_after_depreciation": (flt(row.gross_purchase_amount) -
flt(row.accumulated_depreciation_amount)),
"depreciation_entry": d.voucher_no
})
data.append(row)
return data
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 prepare_conditions(self):
conditions = [""]
if self.filters.company:
conditions.append("gle.company=%(company)s")
self.filters.company_finance_book = erpnext.get_default_finance_book(self.filters.company)
if not self.filters.finance_book or (self.filters.finance_book == self.filters.company_finance_book):
conditions.append("ifnull(finance_book,'') in (%(company_finance_book)s, '')")
elif self.filters.finance_book:
conditions.append("ifnull(finance_book,'') = %(finance_book)s")
if self.filters.get("party"):
conditions.append("party=%(party)s")
if self.filters.party_type == "Customer":
if self.filters.get("customer_group"):
lft, rgt = frappe.db.get_value("Customer Group",
self.filters.get("customer_group"), ["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1}
and name=tabCustomer.customer_group))""".format(lft, rgt))
if self.filters.get("territory"):
lft, rgt = frappe.db.get_value("Territory",
self.filters.get("territory"), ["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabTerritory` where lft >= {0} and rgt <= {1}
and name=tabCustomer.territory))""".format(lft, rgt))
if self.filters.get("payment_terms_template"):
conditions.append("party in (select name from tabCustomer where payment_terms=%(payment_terms_template)s)")
if self.filters.get("sales_partner"):
conditions.append("party in (select name from tabCustomer where default_sales_partner=%(sales_partner)s)")
if self.filters.get("sales_person"):
lft, rgt = frappe.db.get_value("Sales Person",
self.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 = voucher_no and steam.parenttype = voucher_type)
or (steam.parent = against_voucher and steam.parenttype = against_voucher_type)
or (steam.parent = party and steam.parenttype = 'Customer')))""".format(lft, rgt))
if self.filters.party_type == "Supplier":
if self.filters.get("supplier_group"):
conditions.append("""party in (select name from tabSupplier
where supplier_group=%(supplier_group)s)""")
return " and ".join(conditions)
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 get_additional_conditions(from_date, ignore_closing_entries, filters):
additional_conditions = []
if ignore_closing_entries:
additional_conditions.append("ifnull(gl.voucher_type, '')!='Period Closing Voucher'")
if from_date:
additional_conditions.append("gl.posting_date >= %(from_date)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):
additional_conditions.append("ifnull(finance_book, '') in ('%s', '')" %
frappe.db.escape(company_finance_book))
elif filters.get("finance_book"):
additional_conditions.append("ifnull(finance_book, '') = '%s' " %
frappe.db.escape(filters.get("finance_book")))
return " and {}".format(" and ".join(additional_conditions)) if additional_conditions else ""
def get_filter_conditions(filters):
conditions = ""
if filters.get("asset"):
conditions += " and a.name = %(asset)s"
if filters.get("asset_category"):
conditions += " and a.asset_category = %(asset_category)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 += " and ifnull(ds.finance_book, '') in (%(finance_book)s, '') "
elif filters.get("finance_book"):
conditions += " and ifnull(ds.finance_book, '') = %(finance_book)s"
return conditions
def prepare_conditions(self, party_type):
conditions = [""]
values = [party_type]
party_type_field = scrub(party_type)
if self.filters.company:
conditions.append("company=%s")
values.append(self.filters.company)
company_finance_book = erpnext.get_default_finance_book(
self.filters.company)
if not self.filters.finance_book or (self.filters.finance_book
== company_finance_book):
conditions.append("ifnull(finance_book,'') in (%s, '')")
values.append(company_finance_book)
elif self.filters.finance_book:
conditions.append("ifnull(finance_book,'') = %s")
values.append(self.filters.finance_book)
if self.filters.get(party_type_field):
conditions.append("party=%s")
values.append(self.filters.get(party_type_field))
if party_type_field == "customer":
account_type = "Receivable"
if self.filters.get("customer_group"):
lft, rgt = frappe.db.get_value(
"Customer Group", self.filters.get("customer_group"),
["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1}
and name=tabCustomer.customer_group))""".format(lft, rgt))
if self.filters.get("territory"):
lft, rgt = frappe.db.get_value("Territory",
self.filters.get("territory"),
["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabTerritory` where lft >= {0} and rgt <= {1}
and name=tabCustomer.territory))""".format(lft, rgt))
if self.filters.get("payment_terms_template"):
conditions.append(
"party in (select name from tabCustomer where payment_terms=%s)"
)
values.append(self.filters.get("payment_terms_template"))
if self.filters.get("sales_partner"):
conditions.append(
"party in (select name from tabCustomer where default_sales_partner=%s)"
)
values.append(self.filters.get("sales_partner"))
if self.filters.get("sales_person"):
lft, rgt = frappe.db.get_value(
"Sales Person", self.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 = voucher_no and steam.parenttype = voucher_type)
or (steam.parent = against_voucher and steam.parenttype = against_voucher_type)
or (steam.parent = party and steam.parenttype = 'Customer')))""".format(
lft, rgt))
elif party_type_field == "supplier":
account_type = "Payable"
if self.filters.get("supplier_group"):
conditions.append("""party in (select name from tabSupplier
where supplier_group=%s)""")
values.append(self.filters.get("supplier_group"))
accounts = [
d.name for d in frappe.get_all("Account",
filters={
"account_type": account_type,
"company": self.filters.company
})
]
conditions.append("account in (%s)" % ','.join(['%s'] * len(accounts)))
values += accounts
return " and ".join(conditions), values
def get_data(filters):
data = []
depreciation_accounts = frappe.db.sql_list(""" select name from tabAccount
where ifnull(account_type, '') = 'Depreciation' """)
filters_data = [["company", "=", filters.get('company')],
["posting_date", ">=",
filters.get('from_date')],
["posting_date", "<=",
filters.get('to_date')],
["against_voucher_type", "=", "Asset"],
["account", "in", depreciation_accounts]]
if filters.get("asset"):
filters_data.append(["against_voucher", "=", filters.get("asset")])
if filters.get("asset_category"):
assets = frappe.db.sql_list(
"""select name from tabAsset
where asset_category = %s and docstatus=1""", filters.get("asset_category"))
filters_data.append(["against_voucher", "in", assets])
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_data.append(
["finance_book", "in", ['', filters.get('finance_book')]])
elif filters.get("finance_book"):
filters_data.append(["finance_book", "=", filters.get('finance_book')])
gl_entries = frappe.get_all('GL Entry',
filters=filters_data,
fields=[
"against_voucher",
"debit_in_account_currency as debit",
"voucher_no", "posting_date"
],
order_by="against_voucher, posting_date")
if not gl_entries:
return data
assets = [d.against_voucher for d in gl_entries]
assets_details = get_assets_details(assets)
for d in gl_entries:
asset_data = assets_details.get(d.against_voucher)
if not asset_data.get("accumulated_depreciation_amount"):
asset_data.accumulated_depreciation_amount = d.debit
else:
asset_data.accumulated_depreciation_amount += d.debit
row = frappe._dict(asset_data)
row.update({
"depreciation_amount":
d.debit,
"depreciation_date":
d.posting_date,
"amount_after_depreciation":
(flt(row.gross_purchase_amount) -
flt(row.accumulated_depreciation_amount)),
"depreciation_entry":
d.voucher_no
})
data.append(row)
return data
def prepare_conditions(self, party_type):
conditions = [""]
values = [party_type]
party_type_field = scrub(party_type)
if self.filters.company:
conditions.append("company=%s")
values.append(self.filters.company)
company_finance_book = erpnext.get_default_finance_book(self.filters.company)
if not self.filters.finance_book or (self.filters.finance_book == company_finance_book):
conditions.append("ifnull(finance_book,'') in (%s, '')")
values.append(company_finance_book)
elif self.filters.finance_book:
conditions.append("ifnull(finance_book,'') = %s")
values.append(self.filters.finance_book)
if self.filters.get(party_type_field):
conditions.append("party=%s")
values.append(self.filters.get(party_type_field))
if party_type_field=="customer":
account_type = "Receivable"
if self.filters.get("customer_group"):
lft, rgt = frappe.db.get_value("Customer Group",
self.filters.get("customer_group"), ["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabCustomer Group` where lft >= {0} and rgt <= {1}
and name=tabCustomer.customer_group))""".format(lft, rgt))
if self.filters.get("territory"):
lft, rgt = frappe.db.get_value("Territory",
self.filters.get("territory"), ["lft", "rgt"])
conditions.append("""party in (select name from tabCustomer
where exists(select name from `tabTerritory` where lft >= {0} and rgt <= {1}
and name=tabCustomer.territory))""".format(lft, rgt))
if self.filters.get("payment_terms_template"):
conditions.append("party in (select name from tabCustomer where payment_terms=%s)")
values.append(self.filters.get("payment_terms_template"))
if self.filters.get("sales_partner"):
conditions.append("party in (select name from tabCustomer where default_sales_partner=%s)")
values.append(self.filters.get("sales_partner"))
if self.filters.get("sales_person"):
lft, rgt = frappe.db.get_value("Sales Person",
self.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 = voucher_no and steam.parenttype = voucher_type)
or (steam.parent = against_voucher and steam.parenttype = against_voucher_type)
or (steam.parent = party and steam.parenttype = 'Customer')))""".format(lft, rgt))
elif party_type_field=="supplier":
account_type = "Payable"
if self.filters.get("supplier_group"):
conditions.append("""party in (select name from tabSupplier
where supplier_group=%s)""")
values.append(self.filters.get("supplier_group"))
accounts = [d.name for d in frappe.get_all("Account",
filters={"account_type": account_type, "company": self.filters.company})]
conditions.append("account in (%s)" % ','.join(['%s'] *len(accounts)))
values += accounts
return " and ".join(conditions), values