def get_children():
args = frappe.local.form_dict
ctype, company = args['ctype'], args['comp']
# root
if args['parent'] in ("Accounts", "Cost Centers"):
select_cond = ", root_type, report_type" if args["parent"]=="Accounts" else ""
acc = frappe.db.sql(""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable %s
from `tab%s`
where ifnull(parent_%s,'') = ''
and `company` = %s and docstatus<2
order by name""" % (select_cond, ctype, ctype.lower().replace(' ','_'), '%s'),
company, as_dict=1)
if args["parent"]=="Accounts":
sort_root_accounts(acc)
else:
# other
acc = frappe.db.sql("""select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = %s
and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'),
args['parent'], as_dict=1)
if ctype == 'Account':
currency = frappe.db.sql("select default_currency from `tabCompany` where name = %s", company)[0][0]
for each in acc:
bal = get_balance_on(each.get("value"))
each["currency"] = currency
each["balance"] = flt(bal)
return acc
def get_children():
args = frappe.local.form_dict
ctype, company = args['ctype'], args['comp']
# root
if args['parent'] in ("Accounts", "Cost Centers"):
select_cond = ", root_type, report_type, account_currency" if ctype=="Account" else ""
acc = frappe.db.sql(""" select
name as value, is_group as expandable %s
from `tab%s`
where ifnull(parent_%s,'') = ''
and `company` = %s and docstatus<2
order by name""" % (select_cond, ctype, ctype.lower().replace(' ','_'), '%s'),
company, as_dict=1)
if args["parent"]=="Accounts":
sort_root_accounts(acc)
else:
# other
select_cond = ", account_currency" if ctype=="Account" else ""
acc = frappe.db.sql("""select
name as value, is_group as expandable %s
from `tab%s`
where ifnull(parent_%s,'') = %s
and docstatus<2
order by name""" % (select_cond, ctype, ctype.lower().replace(' ','_'), '%s'),
args['parent'], as_dict=1)
if ctype == 'Account':
for each in acc:
each["balance"] = flt(get_balance_on(each.get("value")))
return acc
def get_children():
args = frappe.local.form_dict
doctype, company = args["doctype"], args["company"]
fieldname = frappe.db.escape(doctype.lower().replace(" ", "_"))
doctype = frappe.db.escape(doctype)
# root
if args["parent"] in ("Accounts", "Cost Centers"):
fields = ", root_type, report_type, account_currency" if doctype == "Account" else ""
acc = frappe.db.sql(
""" select
name as value, is_group as expandable {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = ''
and `company` = %s and docstatus<2
order by name""".format(
fields=fields, fieldname=fieldname, doctype=doctype
),
company,
as_dict=1,
)
if args["parent"] == "Accounts":
sort_root_accounts(acc)
else:
# other
fields = ", account_currency" if doctype == "Account" else ""
acc = frappe.db.sql(
"""select
name as value, is_group as expandable, parent_{fieldname} as parent {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = %s
and docstatus<2
order by name""".format(
fields=fields, fieldname=fieldname, doctype=doctype
),
args["parent"],
as_dict=1,
)
if doctype == "Account":
company_currency = frappe.db.get_value("Company", company, "default_currency")
for each in acc:
each["company_currency"] = company_currency
each["balance"] = flt(get_balance_on(each.get("value"), in_account_currency=False))
if each.account_currency != company_currency:
each["balance_in_account_currency"] = flt(get_balance_on(each.get("value")))
return acc
def get_children():
from erpnext.accounts.report.financial_statements import sort_root_accounts
args = frappe.local.form_dict
doctype, company = args['doctype'], args['company']
fieldname = frappe.db.escape(doctype.lower().replace(' ', '_'))
doctype = frappe.db.escape(doctype)
# root
if args['parent'] in ("Accounts", "Cost Centers"):
fields = ", root_type, report_type, account_currency" if doctype == "Account" else ""
acc = frappe.db.sql(""" select
name as value, is_group as expandable {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = ''
and `company` = %s and docstatus<2
order by name""".format(fields=fields, fieldname=fieldname,
doctype=doctype),
company,
as_dict=1)
if args["parent"] == "Accounts":
sort_root_accounts(acc)
else:
# other
fields = ", account_currency" if doctype == "Account" else ""
acc = frappe.db.sql("""select
name as value, is_group as expandable, parent_{fieldname} as parent {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = %s
and docstatus<2
order by name""".format(fields=fields, fieldname=fieldname,
doctype=doctype),
args['parent'],
as_dict=1)
if doctype == 'Account':
company_currency = frappe.db.get_value("Company", company,
"default_currency")
for each in acc:
each["company_currency"] = company_currency
each["balance"] = flt(
get_balance_on(each.get("value"), in_account_currency=False))
if each.account_currency != company_currency:
each["balance_in_account_currency"] = flt(
get_balance_on(each.get("value")))
return acc
def get_children():
args = frappe.local.form_dict
ctype, company = args['ctype'], args['comp']
# root
if args['parent'] in ("Accounts", "Cost Centers"):
select_cond = ", root_type, report_type, account_currency" if ctype == "Account" else ""
acc = frappe.db.sql(
""" select
name as value, is_group as expandable %s
from `tab%s`
where ifnull(`parent_%s`,'') = ''
and `company` = %s and docstatus<2
order by name""" %
(select_cond, frappe.db.escape(ctype),
frappe.db.escape(ctype.lower().replace(' ', '_')), '%s'),
company,
as_dict=1)
if args["parent"] == "Accounts":
sort_root_accounts(acc)
else:
# other
select_cond = ", account_currency" if ctype == "Account" else ""
acc = frappe.db.sql(
"""select
name as value, is_group as expandable %s
from `tab%s`
where ifnull(`parent_%s`,'') = %s
and docstatus<2
order by name""" %
(select_cond, frappe.db.escape(ctype),
frappe.db.escape(ctype.lower().replace(' ', '_')), '%s'),
args['parent'],
as_dict=1)
if ctype == 'Account':
for each in acc:
each["balance"] = flt(get_balance_on(each.get("value")))
return acc
def get_children(doctype, parent, company, is_root=False):
from erpnext.accounts.report.financial_statements import sort_root_accounts
fieldname = frappe.db.escape(doctype.lower().replace(' ','_'))
doctype = frappe.db.escape(doctype)
# root
if is_root:
fields = ", root_type, report_type, account_currency" if doctype=="Account" else ""
acc = frappe.db.sql(""" select
name as value, is_group as expandable {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = ''
and `company` = %s and docstatus<2
order by name""".format(fields=fields, fieldname = fieldname, doctype=doctype),
company, as_dict=1)
if parent=="Accounts":
sort_root_accounts(acc)
else:
# other
fields = ", account_currency" if doctype=="Account" else ""
acc = frappe.db.sql("""select
name as value, is_group as expandable, parent_{fieldname} as parent {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = %s
and docstatus<2
order by name""".format(fields=fields, fieldname=fieldname, doctype=doctype),
parent, as_dict=1)
if doctype == 'Account':
company_currency = frappe.db.get_value("Company", company, "default_currency")
for each in acc:
each["company_currency"] = company_currency
each["balance"] = flt(get_balance_on(each.get("value"), in_account_currency=False))
if each.account_currency != company_currency:
each["balance_in_account_currency"] = flt(get_balance_on(each.get("value")))
return acc
def generate_account_number(doctype, parent, company, is_root=False):
from erpnext.accounts.report.financial_statements import sort_root_accounts
fieldname = frappe.db.escape(doctype.lower().replace(' ', '_'))
doctype = frappe.db.escape(doctype)
# root
if is_root:
fields = ", root_type, report_type, account_currency" if doctype == "Account" else ""
acc = frappe.db.sql(""" select
name as value, is_group as expandable {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = ''
and `company` = %s and docstatus<2
order by name""".format(fields=fields, fieldname=fieldname,
doctype=doctype),
company,
as_dict=1)
if parent == "Accounts":
sort_root_accounts(acc)
else:
# other
fields = ", account_currency" if doctype == "Account" else ""
acc = frappe.db.sql("""select
name as value, is_group as expandable, parent_{fieldname} as parent {fields}
from `tab{doctype}`
where ifnull(`parent_{fieldname}`,'') = %s
and docstatus<2
order by name""".format(fields=fields, fieldname=fieldname,
doctype=doctype),
parent,
as_dict=1)
#auto account number
root_seed = 0
child_seed = 0
leaf_seed = 0
last_acct_num = 0
for each in acc:
parent_account = frappe.db.get_value("Account", each.get("value"),
"parent_account")
parent_acct_num = frappe.db.get_value("Account", parent_account,
"account_number")
account_number = frappe.db.get_value("Account", each.get("value"),
"account_number")
#account_number = frappe.db.get_value("Account", each.get("value"), "account_number")
#root
if ((parent_account == None) and (each.get("expandable") == 1)):
if (account_number == None or account_number == ''):
account_name = frappe.db.get_value("Account",
each.get("value"),
"account_name")
if (account_name == 'Application of Funds (Assets)'):
root_seed = 1
elif (account_name == 'Source of Funds (Liabilities)'):
root_seed = 2
elif (account_name == 'Equity'):
root_seed = 3
elif (account_name == 'Income'):
root_seed = 4
elif (account_name == 'Expenses'):
root_seed = 5
#frappe.db.set_value('Account',each.get("value"),'account_number',root_seed)
update_account_number(each.get("value"), root_seed)
#child
if ((parent_account != None) and (each.get("expandable") == 1)):
last_acct_num = frappe.get_all('Account',
fields=['account_number'],
filters={
'parent_account':
parent_account,
'is_group': 1
},
order_by='account_number desc',
limit=1)[0]['account_number']
if (account_number == None or account_number == ''):
if (last_acct_num != None and last_acct_num != ''):
child_seed = (int(last_acct_num) % 10) + 1
else:
child_seed = 1
new_account_number = str(parent_acct_num) + str(child_seed)
account_with_same_number = frappe.db.get_value(
"Account", {
"account_number": new_account_number,
"company": company,
"name": ["!=", each.get("value")]
})
if account_with_same_number:
child_seed = child_seed + 1
new_account_number = str(parent_acct_num) + str(child_seed)
#frappe.db.set_value('Account',each.get("value"),'account_number',new_account_number)
update_account_number(each.get("value"), new_account_number)
#leaf
if ((parent_account != None) and (each.get("expandable") == 0)):
last_acct_num = frappe.get_all('Account',
fields=['account_number'],
filters={
'parent_account':
parent_account,
'is_group': 0
},
order_by='account_number desc',
limit=1)[0]['account_number']
leaf_node_count = frappe.db.count('Account',
filters={
'parent_account':
parent_account,
'is_group': 0
})
if (account_number == None or account_number == ''):
if (last_acct_num != None and last_acct_num != ''):
if (leaf_node_count > 9):
str_num = str(last_acct_num)
if (str_num.endswith('0',
len(str_num) - 2,
len(str_num) - 1)):
leaf_seed = (int(last_acct_num) % 10) + 1
new_account_number = str(parent_acct_num) + str(
"{0:0>2}".format(leaf_seed))
else:
leaf_seed = (int(last_acct_num) % 100) + 1
new_account_number = str(parent_acct_num) + str(
leaf_seed)
else:
leaf_seed = (int(last_acct_num) % 10) + 1
new_account_number = str(parent_acct_num) + str(
"{0:0>2}".format(leaf_seed))
else:
leaf_seed = 1
new_account_number = str(parent_acct_num) + str(
"{0:0>2}".format(leaf_seed))
account_with_same_number = frappe.db.get_value(
"Account", {
"account_number": new_account_number,
"company": company,
"name": ["!=", each.get("value")]
})
if account_with_same_number:
leaf_seed = leaf_seed + 1
new_account_number = str(parent_acct_num) + str(
"{0:0>2}".format(leaf_seed))
#frappe.db.set_value('Account',each.get("value"),'account_number',new_account_number)
update_account_number(each.get("value"), new_account_number)
#auto account number
return acc
