def get_stock_ledger_entries(filters):
sle_filters = {"is_cancelled": 0}
if filters.warehouse:
children = get_descendants_of("Warehouse", filters.warehouse)
sle_filters["warehouse"] = ("in", children + [filters.warehouse])
if filters.item_code:
sle_filters["item_code"] = filters.item_code
elif filters.get("item_group"):
item_group = filters.get("item_group")
children = get_descendants_of("Item Group", item_group)
item_group_filter = {"item_group": ("in", children + [item_group])}
sle_filters["item_code"] = (
"in",
frappe.get_all("Item", filters=item_group_filter, pluck="name", order_by=None),
)
if filters.from_date:
sle_filters["posting_date"] = (">=", filters.from_date)
if filters.to_date:
sle_filters["posting_date"] = ("<=", filters.to_date)
return frappe.get_all(
"Stock Ledger Entry",
fields=SLE_FIELDS,
filters=sle_filters,
order_by="timestamp(posting_date, posting_time), creation",
)
def get_data(filters):
data = []
expenses = 0
income = 0
indent = 0
expense_accounts = get_descendants_of("Account", "Expenses")
expense_accounts.append("Expenses")
income_accounts = get_descendants_of("Account", "Income")
income_accounts.append("Income")
parent_income = {'indent': indent, 'account': "Income"}
data.append(parent_income)
indent += 1
for ledger_entry in frappe.get_all(
'Ledger Entry',
fields=['sum(debit) as amount', 'account'],
filters={'account': ["in", income_accounts]},
group_by='account'):
report_entry = {
'indent': indent,
'account': ledger_entry.account,
'amount': ledger_entry.amount,
'parent_account': parent_income['account']
}
data.append(report_entry)
income += ledger_entry.amount
indent = 0
parent_expense = {'indent': indent, 'account': "Expenses"}
data.append(parent_expense)
indent += 1
for ledger_entry in frappe.get_all(
'Ledger Entry',
fields=['sum(credit) as amount', 'account'],
filters={'account': ["in", expense_accounts]},
group_by='account'):
report_entry = {
'indent': indent,
'account': ledger_entry.account,
'amount': -(ledger_entry.amount),
'parent_account': parent_expense['account']
}
data.append(report_entry)
expenses += ledger_entry.amount
if income > expenses:
profit = income - expenses
data.append({'account': "Net Profit", 'amount': profit})
else:
loss = expenses - income
data.append({'account': "Net Loss", 'amount': loss})
return data
def getData(filters=None) -> List:
data = []
income, expense = 0, 0
expense_accounts = get_descendants_of("Account", "Expenses")
expense_accounts.append("Expenses")
income_accounts = get_descendants_of("Account", "Income - TB")
income_accounts.append("Income")
parent_incomes = {"indent": 0, "account": "Income - TB"}
data.append(parent_incomes)
incomes = frappe.get_all(
"General Ledger",
filters={"account": ["in", income_accounts]},
fields=["account", 'sum(debit_amount) as balance'],
group_by="account")
for inc in incomes:
report_entry = {
"indent": 1,
"account": inc.account,
"balance": inc.balance,
"parent_account": parent_incomes['account']
}
data.append(report_entry)
income += inc.balance
parent_expenses = {'indent': 0, 'account': "Expenses"}
data.append(parent_expenses)
expenses = frappe.get_all(
"General Ledger",
filters={"account": ["in", expense_accounts]},
fields=["account", "sum(credit_amount) as balance"],
group_by="account")
for expns in expenses:
report_entry = {
"indent": 1,
"account": expns.account,
"balance": expns.balance,
"parent_account": parent_expenses['account']
}
data.append(report_entry)
expense += expns.balance
if income > expense:
profit = income - expense
data.append({'account': "Net Profit", 'balance': profit})
else:
loss = expense - income
data.append({'account': "Net Loss", 'balance': loss})
return data
def update_account_number(name,
account_name,
account_number=None,
from_descendant=False):
account = frappe.db.get_value("Account", name, "company", as_dict=True)
if not account: return
old_acc_name, old_acc_number = frappe.db.get_value('Account', name, \
["account_name", "account_number"])
# check if account exists in parent company
ancestors = get_ancestors_of("Company", account.company)
allow_independent_account_creation = frappe.get_value(
"Company", account.company,
"allow_account_creation_against_child_company")
if ancestors and not allow_independent_account_creation:
for ancestor in ancestors:
if frappe.db.get_value("Account", {
'account_name': old_acc_name,
'company': ancestor
}, 'name'):
# same account in parent company exists
allow_child_account_creation = _(
"Allow Account Creation Against Child Company")
message = _(
"Account {0} exists in parent company {1}.").format(
frappe.bold(old_acc_name), frappe.bold(ancestor))
message += "<br>" + _(
"Renaming it is only allowed via parent company {0}, \
to avoid mismatch.").format(frappe.bold(ancestor)) + "<br><br>"
message += _(
"To overrule this, enable '{0}' in company {1}").format(
allow_child_account_creation,
frappe.bold(account.company))
frappe.throw(message, title=_("Rename Not Allowed"))
validate_account_number(name, account_number, account.company)
if account_number:
frappe.db.set_value("Account", name, "account_number",
account_number.strip())
else:
frappe.db.set_value("Account", name, "account_number", "")
frappe.db.set_value("Account", name, "account_name", account_name.strip())
if not from_descendant:
# Update and rename in child company accounts as well
descendants = get_descendants_of('Company', account.company)
if descendants:
sync_update_account_number_in_child(descendants, old_acc_name,
account_name, account_number,
old_acc_number)
new_name = get_account_autoname(account_number, account_name,
account.company)
if name != new_name:
frappe.rename_doc("Account", name, new_name, force=1)
return new_name
def get_designation_counts(designation, company):
if not designation:
return False
employee_counts = {}
company_set = get_descendants_of('Company', company)
company_set.append(company)
employee_counts["employee_count"] = frappe.db.get_value(
"Employee",
filters={
'designation': designation,
'status': 'Active',
'company': ('in', company_set)
},
fieldname=['count(name)'])
employee_counts['job_openings'] = frappe.db.get_value(
"Job Opening",
filters={
'designation': designation,
'status': 'Open',
'company': ('in', company_set)
},
fieldname=['count(name)'])
return employee_counts
def get_data(filters):
data = []
company_list = get_descendants_of("Company", filters.get("company"))
company_list.append(filters.get("company"))
customer_details = get_customer_details()
sales_order_records = get_sales_order_details(company_list, filters)
for record in sales_order_records:
customer_record = customer_details.get(record.customer)
row = {
"item_code": record.item_code,
"item_name": record.item_name,
"item_group": record.item_group,
"description": record.description,
"quantity": record.qty,
"uom": record.uom,
"rate": record.base_rate,
"amount": record.base_amount,
"sales_order": record.name,
"transaction_date": record.transaction_date,
"customer": record.customer,
"customer_name": customer_record.customer_name,
"customer_group": customer_record.customer_group,
"territory": record.territory,
"project": record.project,
"delivered_quantity": flt(record.delivered_qty),
"billed_amount": flt(record.billed_amt),
"company": record.company
}
data.append(row)
return data
def get_designation_counts(designation, company):
if not designation:
return False
employee_counts = {}
company_set = get_descendants_of("Company", company)
company_set.append(company)
employee_counts["employee_count"] = frappe.db.get_value(
"Employee",
filters={
"designation": designation,
"status": "Active",
"company": ("in", company_set)
},
fieldname=["count(name)"],
)
employee_counts["job_openings"] = frappe.db.get_value(
"Job Opening",
filters={
"designation": designation,
"status": "Open",
"company": ("in", company_set)
},
fieldname=["count(name)"],
)
return employee_counts
def validate_root_company_and_sync_account_to_children(self):
# ignore validation while creating new compnay or while syncing to child companies
if frappe.local.flags.ignore_root_company_validation or self.flags.ignore_root_company_validation:
return
ancestors = get_root_company(self.company)
if ancestors:
if frappe.get_value("Company", self.company, "allow_account_creation_against_child_company"):
return
frappe.throw(_("Please add the account to root level Company - %s" % ancestors[0]))
else:
descendants = get_descendants_of('Company', self.company)
if not descendants: return
acc_name_map = {}
acc_name = frappe.db.get_value('Account', self.parent_account, "account_name")
for d in frappe.db.get_values('Account',
{"company": ["in", descendants], "account_name": acc_name},
["company", "name"], as_dict=True):
acc_name_map[d["company"]] = d["name"]
if not acc_name_map: return
for company in descendants:
doc = frappe.copy_doc(self)
doc.flags.ignore_root_company_validation = True
doc.update({"company": company, "account_currency": None,
"parent": acc_name_map[company], "parent_account": acc_name_map[company]})
doc.save()
frappe.msgprint(_("Account {0} is added in the child company {1}")
.format(doc.name, company))
def validate_root_company_and_sync_account_to_children(self):
# ignore validation while creating new compnay or while syncing to child companies
if frappe.local.flags.ignore_root_company_validation or self.flags.ignore_root_company_validation:
return
ancestors = get_root_company(self.company)
if ancestors:
if frappe.get_value("Company", self.company, "allow_account_creation_against_child_company"):
return
if not frappe.db.get_value("Account",
{'account_name': self.account_name, 'company': ancestors[0]}, 'name'):
frappe.throw(_("Please add the account to root level Company - {}").format(ancestors[0]))
elif self.parent_account:
descendants = get_descendants_of('Company', self.company)
if not descendants: return
parent_acc_name_map = {}
parent_acc_name, parent_acc_number = frappe.db.get_value('Account', self.parent_account, \
["account_name", "account_number"])
filters = {
"company": ["in", descendants],
"account_name": parent_acc_name,
}
if parent_acc_number:
filters["account_number"] = parent_acc_number
for d in frappe.db.get_values('Account', filters=filters, fieldname=["company", "name"], as_dict=True):
parent_acc_name_map[d["company"]] = d["name"]
if not parent_acc_name_map: return
self.create_account_for_child_company(parent_acc_name_map, descendants, parent_acc_name)
def get_child_warehouses(warehouse):
from frappe.utils.nestedset import get_descendants_of
children = get_descendants_of("Warehouse",
warehouse,
ignore_permissions=True,
order_by="lft")
return children + [warehouse] # append self for backward compatibility
def getData(filters) -> List:
"""Return as a List a assets and liabilities
"""
data = []
asset_accounts = get_descendants_of("Account",
"Application of Funds (Assets) - TB")
liability_accounts = get_descendants_of(
"Account", "Source of Funds (Liabilities) - TB")
parent_assets = {
"indent": 0,
"account": "Application of Funds (Assets) - TB"
}
data.append(parent_assets)
assets = frappe.get_all(
"General Ledger",
filters={"account": ["in", asset_accounts]},
fields=["account", "sum(credit_amount) as balance"],
group_by="account")
for asset in assets:
report_entry = {
"indent": 1,
"account": asset.account,
"balance": asset.balance,
"parent_account": parent_assets['account']
}
data.append(report_entry)
parent_liabilities = {
'indent': 0,
'account': "Source of Funds (Liabilities) - TB"
}
data.append(parent_liabilities)
liabilities = frappe.get_all(
"General Ledger",
filters={"account": ["in", liability_accounts]},
fields=["account", 'sum(debit_amount) as balance'],
group_by="account")
for lblt in liabilities:
report_entry = {
"indent": 1,
"account": lblt.account,
"balance": lblt.balance,
"parent_account": parent_liabilities['account']
}
data.append(report_entry)
return data
def __init__(self, *args, **kwargs):
super(GLEntry, self).__init__(*args, **kwargs)
leaves = [
x.name for x in frappe.get_list('Account', filters={'is_group': 0})
]
self._assets = [
x for x in get_descendants_of('Account', 'Assets') if x in leaves
]
self._expenses = [
x for x in get_descendants_of('Account', 'Expenses') if x in leaves
]
self._income = [
x for x in get_descendants_of('Account', 'Income') if x in leaves
]
self._liabilities = [
x for x in get_descendants_of('Account', 'Liabilities')
if x in leaves
]
def get_gl_entries(account, to_date):
child_accounts = get_descendants_of('Account', account, ignore_permissions=True)
child_accounts.append(account)
return frappe.db.get_all('GL Entry',
fields = ['posting_date', 'debit', 'credit'],
filters = [
dict(posting_date = ('<', to_date)),
dict(account = ('in', child_accounts))
],
order_by = 'posting_date asc')
def set_item_locations(self, save=False):
self.validate_for_qty()
items = self.aggregate_item_qty()
self.item_location_map = frappe._dict()
from_warehouses = None
if self.parent_warehouse:
from_warehouses = get_descendants_of("Warehouse",
self.parent_warehouse)
# Create replica before resetting, to handle empty table on update after submit.
locations_replica = self.get("locations")
# reset
self.delete_key("locations")
for item_doc in items:
item_code = item_doc.item_code
self.item_location_map.setdefault(
item_code,
get_available_item_locations(
item_code, from_warehouses,
self.item_count_map.get(item_code), self.company),
)
locations = get_items_with_location_and_quantity(
item_doc, self.item_location_map, self.docstatus)
item_doc.idx = None
item_doc.name = None
for row in locations:
location = item_doc.as_dict()
location.update(row)
self.append("locations", location)
# If table is empty on update after submit, set stock_qty, picked_qty to 0 so that indicator is red
# and give feedback to the user. This is to avoid empty Pick Lists.
if not self.get("locations") and self.docstatus == 1:
for location in locations_replica:
location.stock_qty = 0
location.picked_qty = 0
self.append("locations", location)
frappe.msgprint(
_("Please Restock Items and Update the Pick List to continue. To discontinue, cancel the Pick List."
),
title=_("Out of Stock"),
indicator="red",
)
if save:
self.save()
def filter_territory(doctype, txt, searchfield, start, page_len, filters):
"""filter territory"""
territory_list = get_descendants_of("Territory", filters.get("region"))
territory_list.append(filters.get("region"))
return frappe.get_all('Territory',
filters={
'parent_territory': ('in', territory_list),
'territory_name': ("like", "%{0}%".format(txt))
},
fields=["name"],
as_list=1)
def validate_root_company_and_sync_account_to_children(self):
# ignore validation while creating new compnay or while syncing to child companies
if frappe.local.flags.ignore_root_company_validation or self.flags.ignore_root_company_validation:
return
ancestors = get_root_company(self.company)
if ancestors:
if frappe.get_value(
"Company", self.company,
"allow_account_creation_against_child_company"):
return
frappe.throw(
_("Please add the account to root level Company - %s" %
ancestors[0]))
else:
descendants = get_descendants_of('Company', self.company)
if not descendants: return
parent_acc_name_map = {}
parent_acc_name = frappe.db.get_value('Account',
self.parent_account,
"account_name")
for d in frappe.db.get_values('Account', {
"company": ["in", descendants],
"account_name": parent_acc_name
}, ["company", "name"],
as_dict=True):
parent_acc_name_map[d["company"]] = d["name"]
if not parent_acc_name_map: return
for company in descendants:
if not parent_acc_name_map.get(company):
frappe.throw(
_("While creating account for child Company {0}, parent account {1} not found. Please create the parent account in corresponding COA"
).format(company, parent_acc_name))
doc = frappe.copy_doc(self)
doc.flags.ignore_root_company_validation = True
doc.update({
"company": company,
# parent account's currency should be passed down to child account's curreny
# if it is None, it picks it up from default company currency, which might be unintended
"account_currency": self.account_currency,
"parent_account": parent_acc_name_map[company]
})
doc.save()
frappe.msgprint(
_("Account {0} is added in the child company {1}").format(
doc.name, company))
def get_gl_entries(account, to_date):
child_accounts = get_descendants_of("Account",
account,
ignore_permissions=True)
child_accounts.append(account)
return frappe.db.get_all(
"GL Entry",
fields=["posting_date", "debit", "credit"],
filters=[
dict(posting_date=("<", to_date)),
dict(account=("in", child_accounts)),
dict(voucher_type=("!=", "Period Closing Voucher")),
],
order_by="posting_date asc",
)
def get_designation_counts(designation, company, job_opening=None):
if not designation:
return False
company_set = get_descendants_of("Company", company)
company_set.append(company)
employee_count = frappe.db.count(
"Employee", {"designation": designation, "status": "Active", "company": ("in", company_set)}
)
filters = {"designation": designation, "status": "Open", "company": ("in", company_set)}
if job_opening:
filters["name"] = ("!=", job_opening)
job_openings = frappe.db.count("Job Opening", filters)
return {"employee_count": employee_count, "job_openings": job_openings}
def validate_root_company_and_sync_account_to_children(self):
# ignore validation while creating new compnay or while syncing to child companies
if frappe.local.flags.ignore_root_company_validation or self.flags.ignore_root_company_validation:
return
ancestors = get_root_company(self.company)
if ancestors:
if frappe.get_value(
"Company", self.company,
"allow_account_creation_against_child_company"):
return
frappe.throw(
_("Please add the account to root level Company - %s" %
ancestors[0]))
else:
descendants = get_descendants_of('Company', self.company)
if not descendants: return
acc_name_map = {}
acc_name = frappe.db.get_value('Account', self.parent_account,
"account_name")
for d in frappe.db.get_values('Account', {
"company": ["in", descendants],
"account_name": acc_name
}, ["company", "name"],
as_dict=True):
acc_name_map[d["company"]] = d["name"]
for company in descendants:
doc = frappe.copy_doc(self)
doc.flags.ignore_root_company_validation = True
doc.update({
"company": company,
"account_currency": None,
"parent": acc_name_map[company],
"parent_account": acc_name_map[company]
})
doc.save()
frappe.msgprint(
_("Account {0} is added in the child company {1}").format(
doc.name, company))
def get_data(filters):
data = []
company_list = get_descendants_of("Company", filters.get("company"))
company_list.append(filters.get("company"))
customer_details = get_customer_details()
item_details = get_item_details()
sales_order_records = get_sales_order_details(company_list, filters)
for record in sales_order_records:
customer_record = customer_details.get(record.customer)
item_record = item_details.get(record.item_code)
row = {
"item_code": record.get("item_code"),
"item_name": item_record.get("item_name"),
"item_group": item_record.get("item_group"),
"description": record.get("description"),
"quantity": record.get("qty"),
"uom": record.get("uom"),
"rate": record.get("base_rate"),
"amount": record.get("base_amount"),
"sales_order": record.get("name"),
"transaction_date": record.get("transaction_date"),
"customer": record.get("customer"),
"customer_name": customer_record.get("customer_name"),
"customer_group": customer_record.get("customer_group"),
"territory": record.get("territory"),
"project": record.get("project"),
"delivered_quantity": flt(record.get("delivered_qty")),
"billed_amount": flt(record.get("billed_amt")),
"company": record.get("company"),
}
data.append(row)
return data
if no_transactions:
iwb_map.pop((company, item, warehouse))
return iwb_map
def get_items(filters: StockBalanceFilter) -> List[str]:
"Get items based on item code, item group or brand."
if item_code := filters.get("item_code"):
return [item_code]
else:
item_filters = {}
if item_group := filters.get("item_group"):
children = get_descendants_of("Item Group",
item_group,
ignore_permissions=True)
item_filters["item_group"] = ("in", children + [item_group])
if brand := filters.get("brand"):
item_filters["brand"] = brand
return frappe.get_all("Item",
filters=item_filters,
pluck="name",
order_by=None)
def get_item_details(items: List[str], sle: List[SLEntry],
filters: StockBalanceFilter):
item_details = {}
if not items:
def get_departments(department, company):
departments_list = get_descendants_of("Department", department)
departments_list.append(department)
return departments_list