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