def test_delivery_note_gl_entry_packing_item(self):
self.clear_stock_account_balance()
set_perpetual_inventory()
self._insert_purchase_receipt()
self._insert_purchase_receipt("_Test Item Home Desktop 100")
dn = webnotes.bean(copy=test_records[0])
dn.doclist[1].item_code = "_Test Sales BOM Item"
dn.doclist[1].qty = 1
stock_in_hand_account = webnotes.conn.get_value("Account", {"master_name": dn.doclist[1].warehouse})
from accounts.utils import get_balance_on
prev_bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
dn.insert()
dn.submit()
gl_entries = get_gl_entries("Delivery Note", dn.doc.name)
self.assertTrue(gl_entries)
expected_values = {stock_in_hand_account: [0.0, 525], "Cost of Goods Sold - _TC": [525.0, 0.0]}
for i, gle in enumerate(gl_entries):
self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account))
# check stock in hand balance
bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
self.assertEquals(bal, prev_bal - 525.0)
dn.cancel()
self.assertFalse(get_gl_entries("Delivery Note", dn.doc.name))
set_perpetual_inventory(0)
def test_save_journal_voucher(self):
expense_ac_balance = get_balance_on(get_name("Test Expense"), nowdate())
supplier_ac_balance = get_balance_on(get_name("Test Supplier"), nowdate())
dl = webnotes.model.insert(data["journal_voucher"])
dl.submit()
dl.load_from_db()
# test submitted jv
self.assertTrue(webnotes.conn.exists("Journal Voucher", dl.doclist[0].name))
for d in dl.doclist[1:]:
self.assertEquals(webnotes.conn.get_value("Journal Voucher Detail",
d.name, "parent"), dl.doclist[0].name)
# test gl entry
gle = webnotes.conn.sql("""select account, debit, credit
from `tabGL Entry` where voucher_no = %s order by account""",
dl.doclist[0].name)
self.assertEquals((gle[0][0], flt(gle[0][1]), flt(gle[0][2])),
('Test Expense - %s' % abbr, 5000.0, 0.0))
self.assertEquals((gle[1][0], flt(gle[1][1]), flt(gle[1][2])),
('Test Supplier - %s' % abbr, 0.0, 5000.0))
# check balance as on today
self.assertEqual(get_balance_on(get_name("Test Expense"), nowdate()),
expense_ac_balance + 5000)
self.assertEqual(get_balance_on(get_name("Test Supplier"), nowdate()),
supplier_ac_balance + 5000)
# check previous balance
self.assertEqual(get_balance_on(get_name("Test Expense"), add_days(nowdate(), -1)), 0)
def test_delivery_note_gl_entry(self):
self.clear_stock_account_balance()
set_perpetual_inventory()
self.assertEqual(cint(webnotes.defaults.get_global_default("auto_accounting_for_stock")), 1)
webnotes.conn.set_value("Item", "_Test Item", "valuation_method", "FIFO")
_insert_purchase_receipt()
dn = webnotes.bean(copy=test_records[0])
dn.doclist[1].expense_account = "Cost of Goods Sold - _TC"
dn.doclist[1].cost_center = "Main - _TC"
stock_in_hand_account = webnotes.conn.get_value("Account",
{"master_name": dn.doclist[1].warehouse})
from accounts.utils import get_balance_on
prev_bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
dn.insert()
dn.submit()
gl_entries = get_gl_entries("Delivery Note", dn.doc.name)
self.assertTrue(gl_entries)
expected_values = {
stock_in_hand_account: [0.0, 375.0],
"Cost of Goods Sold - _TC": [375.0, 0.0]
}
for i, gle in enumerate(gl_entries):
self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account))
# check stock in hand balance
bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
self.assertEquals(bal, prev_bal - 375.0)
# back dated purchase receipt
pr = webnotes.bean(copy=pr_test_records[0])
pr.doc.posting_date = "2013-01-01"
pr.doclist[1].import_rate = 100
pr.doclist[1].amount = 100
pr.insert()
pr.submit()
gl_entries = get_gl_entries("Delivery Note", dn.doc.name)
self.assertTrue(gl_entries)
expected_values = {
stock_in_hand_account: [0.0, 666.65],
"Cost of Goods Sold - _TC": [666.65, 0.0]
}
for i, gle in enumerate(gl_entries):
self.assertEquals([gle.debit, gle.credit], expected_values.get(gle.account))
dn.cancel()
self.assertFalse(get_gl_entries("Delivery Note", dn.doc.name))
set_perpetual_inventory(0)
def get_children():
args = webnotes.form_dict
ctype, company = args['ctype'], args['comp']
company_field = ctype=='Account' and 'company' or 'company_name'
# root
if args['parent'] == company:
acc = webnotes.conn.sql(""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = ''
and %s = %s and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), company_field, '%s'),
args['parent'], as_dict=1)
else:
# other
acc = webnotes.conn.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 = webnotes.conn.sql("select default_currency from `tabCompany` where name = %s", company)[0][0]
for each in acc:
bal = get_balance_on(each.get("value"))
each['balance'] = currency + ' ' + fmt_money(bal)
return acc
def get_opening_accounts(company):
"""get all balance sheet accounts for opening entry"""
from accounts.utils import get_balance_on
accounts = webnotes.conn.sql_list("""select name from tabAccount
where group_or_ledger='Ledger' and is_pl_account='No' and company=%s""", company)
return [{"account": a, "balance": get_balance_on(a)} for a in accounts]
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
data = get_entries(filters)
from accounts.utils import get_balance_on
balance_as_per_company = get_balance_on(filters["account"], filters["report_date"])
total_debit, total_credit = 0,0
for d in data:
total_debit += flt(d[4])
total_credit += flt(d[5])
if webnotes.conn.get_value("Account", filters["account"], "debit_or_credit") == 'Debit':
bank_bal = flt(balance_as_per_company) - flt(total_debit) + flt(total_credit)
else:
bank_bal = flt(balance_as_per_company) + flt(total_debit) - flt(total_credit)
data += [
["", "", "", "Balance as per company books", balance_as_per_company, ""],
["", "", "", "Amounts not reflected in bank", total_debit, total_credit],
["", "", "", "Balance as per bank", bank_bal, ""]
]
return columns, data
def execute(filters=None):
if not filters: filters = {}
columns = get_columns()
data = get_entries(filters)
from accounts.utils import get_balance_on
balance_as_per_company = get_balance_on(filters["account"],
filters["report_date"])
total_debit, total_credit = 0, 0
for d in data:
total_debit += flt(d[4])
total_credit += flt(d[5])
if webnotes.conn.get_value("Account", filters["account"],
"debit_or_credit") == 'Debit':
bank_bal = flt(balance_as_per_company) - flt(total_debit) + flt(
total_credit)
else:
bank_bal = flt(balance_as_per_company) + flt(total_debit) - flt(
total_credit)
data += [[
"", "", "", "Balance as per company books", balance_as_per_company, ""
], [
"", "", "", "Amounts not reflected in bank", total_debit, total_credit
], ["", "", "", "Balance as per bank", bank_bal, ""]]
return columns, data
def get_children():
args = webnotes.local.form_dict
ctype, company = args['ctype'], args['comp']
# root
if args['parent'] in ("Accounts", "Cost Centers"):
acc = webnotes.conn.sql(""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = ''
and `company` = %s and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'),
company, as_dict=1)
else:
# other
acc = webnotes.conn.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 = webnotes.conn.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_opening_accounts(company):
"""get all balance sheet accounts for opening entry"""
from accounts.utils import get_balance_on
accounts = webnotes.conn.sql_list("""select name from tabAccount
where group_or_ledger='Ledger' and is_pl_account='No' and company=%s""", company)
return [{"account": a, "balance": get_balance_on(a)} for a in accounts]
def get_children():
args = webnotes.form_dict
ctype, company = args['ctype'], args['comp']
company_field = ctype=='Account' and 'company' or 'company_name'
# root
if args['parent'] == company:
acc = webnotes.conn.sql(""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = ''
and %s = %s and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), company_field, '%s'),
args['parent'], as_dict=1)
else:
# other
acc = webnotes.conn.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 = webnotes.conn.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_default_bank_cash_account(company, voucher_type):
from accounts.utils import get_balance_on
account = webnotes.conn.get_value(
"Company", company,
voucher_type == "Bank Voucher" and "default_bank_account"
or "default_cash_account")
if account:
return {"account": account, "balance": get_balance_on(account)}
def get_default_bank_cash_account(company, voucher_type):
from accounts.utils import get_balance_on
account = webnotes.conn.get_value("Company", company,
voucher_type=="Bank Voucher" and "default_bank_account" or "default_cash_account")
if account:
return [{
"account": account,
"balance": get_balance_on(account)
}]
def test_delivery_note_gl_entry(self):
webnotes.conn.sql("""delete from `tabBin`""")
webnotes.conn.sql("delete from `tabStock Ledger Entry`")
webnotes.defaults.set_global_default("auto_inventory_accounting", 1)
self.assertEqual(
cint(
webnotes.defaults.get_global_default(
"auto_inventory_accounting")), 1)
self._insert_purchase_receipt()
dn = webnotes.bean(copy=test_records[0])
dn.doclist[1].expense_account = "Cost of Goods Sold - _TC"
dn.doclist[1].cost_center = "Main - _TC"
stock_in_hand_account = webnotes.conn.get_value(
"Company", dn.doc.company, "stock_in_hand_account")
from accounts.utils import get_balance_on
prev_bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
dn.insert()
dn.submit()
gl_entries = webnotes.conn.sql("""select account, debit, credit
from `tabGL Entry` where voucher_type='Delivery Note' and voucher_no=%s
order by account asc""",
dn.doc.name,
as_dict=1)
self.assertTrue(gl_entries)
expected_values = sorted([[stock_in_hand_account, 0.0, 375.0],
["Cost of Goods Sold - _TC", 375.0, 0.0]])
for i, gle in enumerate(gl_entries):
self.assertEquals(expected_values[i][0], gle.account)
self.assertEquals(expected_values[i][1], gle.debit)
self.assertEquals(expected_values[i][2], gle.credit)
# check stock in hand balance
bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
self.assertEquals(bal, prev_bal - 375.0)
webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
def get_period_balance(self, acc, pl):
ret, i = [], 0
for p in self.period_list:
period_end_date = self.period_end_date[p].strftime('%Y-%m-%d')
bal = get_balance_on(acc, period_end_date)
if pl == 'Yes':
bal = bal - sum(ret)
ret.append(bal)
return ret
def get_period_balance(self, acc, pl):
ret, i = [], 0
for p in self.period_list:
period_end_date = self.period_end_date[p].strftime("%Y-%m-%d")
bal = get_balance_on(acc, period_end_date)
if pl == "Yes":
bal = bal - sum(ret)
ret.append(bal)
return ret
def test_delivery_note_gl_entry(self):
webnotes.conn.sql("""delete from `tabBin`""")
webnotes.conn.sql("delete from `tabStock Ledger Entry`")
webnotes.defaults.set_global_default("auto_inventory_accounting", 1)
self.assertEqual(cint(webnotes.defaults.get_global_default("auto_inventory_accounting")), 1)
self._insert_purchase_receipt()
dn = webnotes.bean(copy=test_records[0])
dn.doclist[1].expense_account = "Cost of Goods Sold - _TC"
dn.doclist[1].cost_center = "Main - _TC"
stock_in_hand_account = webnotes.conn.get_value("Company", dn.doc.company,
"stock_in_hand_account")
from accounts.utils import get_balance_on
prev_bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
dn.insert()
dn.submit()
gl_entries = webnotes.conn.sql("""select account, debit, credit
from `tabGL Entry` where voucher_type='Delivery Note' and voucher_no=%s
order by account asc""", dn.doc.name, as_dict=1)
self.assertTrue(gl_entries)
expected_values = sorted([
[stock_in_hand_account, 0.0, 375.0],
["Cost of Goods Sold - _TC", 375.0, 0.0]
])
for i, gle in enumerate(gl_entries):
self.assertEquals(expected_values[i][0], gle.account)
self.assertEquals(expected_values[i][1], gle.debit)
self.assertEquals(expected_values[i][2], gle.credit)
# check stock in hand balance
bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
self.assertEquals(bal, prev_bal - 375.0)
webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
def test_delivery_note_gl_entry_packing_item(self):
self.clear_stock_account_balance()
set_perpetual_inventory()
_insert_purchase_receipt()
_insert_purchase_receipt("_Test Item Home Desktop 100")
dn = webnotes.bean(copy=test_records[0])
dn.doclist[1].item_code = "_Test Sales BOM Item"
dn.doclist[1].qty = 1
stock_in_hand_account = webnotes.conn.get_value(
"Account", {"master_name": dn.doclist[1].warehouse})
from accounts.utils import get_balance_on
prev_bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
dn.insert()
dn.submit()
gl_entries = get_gl_entries("Delivery Note", dn.doc.name)
self.assertTrue(gl_entries)
expected_values = {
stock_in_hand_account: [0.0, 525],
"Cost of Goods Sold - _TC": [525.0, 0.0]
}
for i, gle in enumerate(gl_entries):
self.assertEquals([gle.debit, gle.credit],
expected_values.get(gle.account))
# check stock in hand balance
bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
self.assertEquals(bal, prev_bal - 525.0)
dn.cancel()
self.assertFalse(get_gl_entries("Delivery Note", dn.doc.name))
set_perpetual_inventory(0)
def test_save_journal_voucher(self):
expense_ac_balance = get_balance_on(get_name("Test Expense"),
nowdate())
supplier_ac_balance = get_balance_on(get_name("Test Supplier"),
nowdate())
dl = webnotes.model.insert(data["journal_voucher"])
dl.submit()
dl.load_from_db()
# test submitted jv
self.assertTrue(
webnotes.conn.exists("Journal Voucher", dl.doclist[0].name))
for d in dl.doclist[1:]:
self.assertEquals(
webnotes.conn.get_value("Journal Voucher Detail", d.name,
"parent"), dl.doclist[0].name)
# test gl entry
gle = webnotes.conn.sql(
"""select account, debit, credit
from `tabGL Entry` where voucher_no = %s order by account""",
dl.doclist[0].name)
self.assertEquals((gle[0][0], flt(gle[0][1]), flt(gle[0][2])),
('Test Expense - %s' % abbr, 5000.0, 0.0))
self.assertEquals((gle[1][0], flt(gle[1][1]), flt(gle[1][2])),
('Test Supplier - %s' % abbr, 0.0, 5000.0))
# check balance as on today
self.assertEqual(get_balance_on(get_name("Test Expense"), nowdate()),
expense_ac_balance + 5000)
self.assertEqual(get_balance_on(get_name("Test Supplier"), nowdate()),
supplier_ac_balance + 5000)
# check previous balance
self.assertEqual(
get_balance_on(get_name("Test Expense"), add_days(nowdate(), -1)),
0)
def make_return_jv(stock_entry):
se = webnotes.bean("Stock Entry", stock_entry)
if not se.doc.purpose in ["Sales Return", "Purchase Return"]:
return
ref = get_return_doclist_and_details(se.doc.fields)
if ref.doclist[0].doctype == "Delivery Note":
result = make_return_jv_from_delivery_note(se, ref)
elif ref.doclist[0].doctype == "Sales Invoice":
result = make_return_jv_from_sales_invoice(se, ref)
elif ref.doclist[0].doctype == "Purchase Receipt":
result = make_return_jv_from_purchase_receipt(se, ref)
# create jv doclist and fetch balance for each unique row item
jv_list = [{
"__islocal":
1,
"doctype":
"Journal Voucher",
"posting_date":
se.doc.posting_date,
"voucher_type":
se.doc.purpose == "Sales Return" and "Credit Note" or "Debit Note",
"fiscal_year":
se.doc.fiscal_year,
"company":
se.doc.company
}]
from accounts.utils import get_balance_on
for r in result:
jv_list.append({
"__islocal":
1,
"doctype":
"Journal Voucher Detail",
"parentfield":
"entries",
"account":
r.get("account"),
"against_invoice":
r.get("against_invoice"),
"against_voucher":
r.get("against_voucher"),
"balance":
get_balance_on(r.get("account"), se.doc.posting_date)
})
return jv_list
def get_payment_entry_from_purchase_invoice(purchase_invoice):
from accounts.utils import get_balance_on
pi = webnotes.bean("Purchase Invoice", purchase_invoice)
jv = get_payment_entry(pi.doc)
jv.doc.remark = 'Payment against Purchase Invoice %(name)s. %(remarks)s' % pi.doc.fields
# credit supplier
jv.doclist[1].account = pi.doc.credit_to
jv.doclist[1].balance = get_balance_on(pi.doc.credit_to)
jv.doclist[1].debit = pi.doc.outstanding_amount
jv.doclist[1].against_voucher = pi.doc.name
# credit bank
jv.doclist[2].credit = pi.doc.outstanding_amount
return [d.fields for d in jv.doclist]
def get_payment_entry_from_sales_invoice(sales_invoice):
from accounts.utils import get_balance_on
si = webnotes.bean("Sales Invoice", sales_invoice)
jv = get_payment_entry(si.doc)
jv.doc.remark = 'Payment received against Sales Invoice %(name)s. %(remarks)s' % si.doc.fields
# credit customer
jv.doclist[1].account = si.doc.debit_to
jv.doclist[1].balance = get_balance_on(si.doc.debit_to)
jv.doclist[1].credit = si.doc.outstanding_amount
jv.doclist[1].against_invoice = si.doc.name
# debit bank
jv.doclist[2].debit = si.doc.outstanding_amount
return [d.fields for d in jv.doclist]
def get_payment_entry_from_sales_invoice(sales_invoice):
from accounts.utils import get_balance_on
si = webnotes.bean("Sales Invoice", sales_invoice)
jv = get_payment_entry(si.doc)
jv.doc.remark = 'Payment received against Sales Invoice %(name)s. %(remarks)s' % si.doc.fields
# credit customer
jv.doclist[1].account = si.doc.debit_to
jv.doclist[1].balance = get_balance_on(si.doc.debit_to)
jv.doclist[1].credit = si.doc.outstanding_amount
jv.doclist[1].against_invoice = si.doc.name
# debit bank
jv.doclist[2].debit = si.doc.outstanding_amount
return [d.fields for d in jv.doclist]
def get_payment_entry_from_purchase_invoice(purchase_invoice):
from accounts.utils import get_balance_on
pi = webnotes.bean("Purchase Invoice", purchase_invoice)
jv = get_payment_entry(pi.doc)
jv.doc.remark = 'Payment against Purchase Invoice %(name)s. %(remarks)s' % pi.doc.fields
# credit supplier
jv.doclist[1].account = pi.doc.credit_to
jv.doclist[1].balance = get_balance_on(pi.doc.credit_to)
jv.doclist[1].debit = pi.doc.outstanding_amount
jv.doclist[1].against_voucher = pi.doc.name
# credit bank
jv.doclist[2].credit = pi.doc.outstanding_amount
return [d.fields for d in jv.doclist]
def make_return_jv(stock_entry):
se = webnotes.bean("Stock Entry", stock_entry)
if not se.doc.purpose in ["Sales Return", "Purchase Return"]:
return
ref = get_return_doclist_and_details(se.doc.fields)
if ref.doclist[0].doctype == "Delivery Note":
result = make_return_jv_from_delivery_note(se, ref)
elif ref.doclist[0].doctype == "Sales Invoice":
result = make_return_jv_from_sales_invoice(se, ref)
elif ref.doclist[0].doctype == "Purchase Receipt":
result = make_return_jv_from_purchase_receipt(se, ref)
# create jv doclist and fetch balance for each unique row item
jv_list = [
{
"__islocal": 1,
"doctype": "Journal Voucher",
"posting_date": se.doc.posting_date,
"voucher_type": se.doc.purpose == "Sales Return" and "Credit Note" or "Debit Note",
"fiscal_year": se.doc.fiscal_year,
"company": se.doc.company,
}
]
from accounts.utils import get_balance_on
for r in result:
jv_list.append(
{
"__islocal": 1,
"doctype": "Journal Voucher Detail",
"parentfield": "entries",
"account": r.get("account"),
"against_invoice": r.get("against_invoice"),
"against_voucher": r.get("against_voucher"),
"balance": get_balance_on(r.get("account"), se.doc.posting_date) if r.get("account") else 0,
}
)
return jv_list
def get_invoice_account_jv_record(doc, children, fiscal_year, obj):
"""
Build customer/supplier account jv detail record
"""
# Calculate total return amount
total_amt = sum([(flt(ch.get("rate")) * flt(ch.get("returned_qty"))) for ch in children])
ret = {}
if doc.get("return_type") == "Sales Return":
account = obj.doc.debit_to
ret["against_invoice"] = doc.get("sales_invoice_no")
ret["credit"] = total_amt
else:
account = obj.doc.credit_to
ret["against_voucher"] = doc.get("purchase_invoice_no")
ret["debit"] = total_amt
ret.update({"account": account, "balance": get_balance_on(account, doc.get("return_date"))})
return ret
def get_item_accountwise_jv_record(doc, children, fiscal_year, obj):
"""
Build item accountwise jv detail records
"""
if doc.get('return_type')=='Sales Return':
amt_field = 'debit'
ac_field = 'income_account'
else:
amt_field = 'credit'
ac_field = 'expense_head'
inv_children = dict([[ic.fields.get('item_code'), ic] for ic in obj.doclist if ic.fields.get('item_code')])
accwise_list = []
for ch in children:
inv_ch = inv_children.get(ch.get('item_code'))
if not inv_ch: continue
amount = flt(ch.get('rate')) * flt(ch.get('returned_qty'))
accounts = [[jvd['account'], jvd['cost_center']] for jvd in accwise_list]
if [inv_ch.fields.get(ac_field), inv_ch.fields.get('cost_center')] not in accounts:
rec = {
'account': inv_ch.fields.get(ac_field),
'cost_center': inv_ch.fields.get('cost_center'),
'balance': get_balance_on(inv_ch.fields.get(ac_field),
doc.get("return_date"))
}
rec[amt_field] = amount
accwise_list.append(rec)
else:
rec = accwise_list[accounts.index([inv_ch.fields.get(ac_field), inv_ch.fields.get('cost_center')])]
rec[amt_field] = rec[amt_field] + amount
return accwise_list
def get_item_accountwise_jv_record(doc, children, fiscal_year, obj):
"""
Build item accountwise jv detail records
"""
if doc.get('return_type')=='Sales Return':
amt_field = 'debit'
ac_field = 'income_account'
else:
amt_field = 'credit'
ac_field = 'expense_head'
inv_children = dict([[ic.fields.get('item_code'), ic] for ic in obj.doclist if ic.fields.get('item_code')])
accwise_list = []
for ch in children:
inv_ch = inv_children.get(ch.get('item_code'))
if not inv_ch: continue
amount = flt(ch.get('rate')) * flt(ch.get('returned_qty'))
accounts = [[jvd['account'], jvd['cost_center']] for jvd in accwise_list]
if [inv_ch.fields.get(ac_field), inv_ch.fields.get('cost_center')] not in accounts:
rec = {
'account': inv_ch.fields.get(ac_field),
'cost_center': inv_ch.fields.get('cost_center'),
'balance': get_balance_on(inv_ch.fields.get(ac_field),
doc.get("return_date"))
}
rec[amt_field] = amount
accwise_list.append(rec)
else:
rec = accwise_list[accounts.index([inv_ch.fields.get(ac_field), inv_ch.fields.get('cost_center')])]
rec[amt_field] = rec[amt_field] + amount
return accwise_list
def get_item_accountwise_jv_record(doc, children, fiscal_year, obj):
"""
Build item accountwise jv detail records
"""
if doc.get("return_type") == "Sales Return":
amt_field = "debit"
ac_field = "income_account"
else:
amt_field = "credit"
ac_field = "expense_head"
inv_children = dict([[ic.fields.get("item_code"), ic] for ic in obj.doclist if ic.fields.get("item_code")])
accwise_list = []
for ch in children:
inv_ch = inv_children.get(ch.get("item_code"))
if not inv_ch:
continue
amount = flt(ch.get("rate")) * flt(ch.get("returned_qty"))
accounts = [[jvd["account"], jvd["cost_center"]] for jvd in accwise_list]
if [inv_ch.fields.get(ac_field), inv_ch.fields.get("cost_center")] not in accounts:
rec = {
"account": inv_ch.fields.get(ac_field),
"cost_center": inv_ch.fields.get("cost_center"),
"balance": get_balance_on(inv_ch.fields.get(ac_field), doc.get("return_date")),
}
rec[amt_field] = amount
accwise_list.append(rec)
else:
rec = accwise_list[accounts.index([inv_ch.fields.get(ac_field), inv_ch.fields.get("cost_center")])]
rec[amt_field] = rec[amt_field] + amount
return accwise_list
def get_invoice_account_jv_record(doc, children, fiscal_year, obj):
"""
Build customer/supplier account jv detail record
"""
# Calculate total return amount
total_amt = sum([(flt(ch.get('rate')) * flt(ch.get('returned_qty'))) for ch in children])
ret = {}
if doc.get('return_type')=='Sales Return':
account = obj.doc.debit_to
ret['against_invoice'] = doc.get('sales_invoice_no')
ret['credit'] = total_amt
else:
account = obj.doc.credit_to
ret['against_voucher'] = doc.get('purchase_invoice_no')
ret['debit'] = total_amt
ret.update({
'account': account,
'balance': get_balance_on(account, doc.get("return_date"))
})
return ret
def get_invoice_account_jv_record(doc, children, fiscal_year, obj):
"""
Build customer/supplier account jv detail record
"""
# Calculate total return amount
total_amt = sum([(flt(ch.get('rate')) * flt(ch.get('returned_qty'))) for ch in children])
ret = {}
if doc.get('return_type')=='Sales Return':
account = obj.doc.debit_to
ret['against_invoice'] = doc.get('sales_invoice_no')
ret['credit'] = total_amt
else:
account = obj.doc.credit_to
ret['against_voucher'] = doc.get('purchase_invoice_no')
ret['debit'] = total_amt
ret.update({
'account': account,
'balance': get_balance_on(account, doc.get("return_date"))
})
return ret
% query_where_clause)
for t in tot:
total_debit += t and flt(t[0]) or 0
total_credit += t and flt(t[1]) or 0
total_diff = total_debit - total_credit
# opening
account = filter_values.get('account')
if account:
acc_det = sql(
"select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'"
% account)
from accounts.utils import get_balance_on
opening_bal = get_balance_on(account, add_days(from_date, -1))
if acc_det[0][0] == 'Credit':
opening_bal = -1 * opening_bal
out = []
count = 0
for r in res:
count += 1
det = r[1].split('~~~')
if from_export == 1:
a = "Account: " + det[0] + NEWLINE + det[
1] + NEWLINE + "Against: " + det[
2] + NEWLINE + "Voucher No: " + det[4]
else:
a = "Account: <b>" + det[0] + "</b>" + NEWLINE + "<div class='comment'>" + det[
def get_opening_balance_row(filters): opening_balance = get_balance_on(filters["account"], add_days(filters["from_date"], -1)) return ["", "Opening Balance", opening_balance, 0.0, "", "", ""]
def get_closing_balance_row(filters): closing_balance = get_balance_on(filters["account"], filters["to_date"]) return ["", "Closing Balance", closing_balance, 0.0, "", "", ""]
raise Exception
else:
acc_name = filter_values.get('account')
if len(res) > 300 and from_export == 0:
msgprint(
"This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel"
)
raise Exception
acc = sql(
"select debit_or_credit, is_pl_account, lft, rgt from tabAccount where name = '%s'"
% acc_name)
from accounts.utils import get_balance_on
opening = get_balance_on(acc_name, to_date)
total_debit, total_credit = 0, 0
out = []
for r in res:
total_debit = flt(total_debit) + flt(r[col_idx['Debit']])
total_credit = flt(total_credit) + flt(r[col_idx['Credit']])
out.append(r)
if acc and acc[0][0] == 'Debit':
bank_bal = flt(opening) - flt(total_debit) + flt(total_credit)
else:
bank_bal = flt(opening) + flt(total_debit) - flt(total_credit)
out.append([
query_where_clause = q[0]
tot = sql('select sum(`tabGL Entry`.debit),sum(`tabGL Entry`.credit) from `tabGL Entry`, tabAccount where %s' % query_where_clause)
for t in tot:
total_debit += t and flt(t[0]) or 0
total_credit += t and flt(t[1]) or 0
total_diff = total_debit - total_credit
# opening
account = filter_values.get('account')
if account:
acc_det = sql("select debit_or_credit, is_pl_account, lft, rgt, group_or_ledger from tabAccount where name = '%s'" % account)
from accounts.utils import get_balance_on
opening_bal = get_balance_on(account, add_days(from_date, -1))
if acc_det[0][0] == 'Credit':
opening_bal = -1*opening_bal
out = []
count = 0
for r in res:
count +=1
det = r[1].split('~~~')
if from_export == 1:
a = "Account: " + det[0] + NEWLINE + det[1] + NEWLINE + "Against: " + det[2] + NEWLINE + "Voucher No: " + det[4]
else:
a = "Account: <b>" + det[0]+ "</b>" + NEWLINE + "<div class='comment'>" +det[1]+ "</div><div class = 'comment' style='padding-left:12px'>Against: <b>" + det[2] + "</b></div><div class = 'comment' style='padding-left:12px'>Voucher No: <span class='link_type' onclick='loaddoc(" + '"' + det[3] +'", ' + '"' + det[4] +'"' + ")'>" + det[4] + "</span></div>"
r[1] = a
def get_opening_balance_row(filters, debit_or_credit): opening_balance = get_balance_on(filters["account"], add_days(filters["from_date"], -1)) return get_balance_row(opening_balance, debit_or_credit, "Opening Balance")
# Account
if not filter_values.get('account'):
msgprint('Please select Account in filter section')
raise Exception
else:
acc_name = filter_values.get('account')
if len(res) > 300 and from_export == 0:
msgprint("This is a very large report and cannot be shown in the browser as it is likely to make your browser very slow.Please select Account or click on 'Export' to open in excel")
raise Exception
acc = sql("select debit_or_credit, is_pl_account, lft, rgt from tabAccount where name = '%s'" % acc_name)
from accounts.utils import get_balance_on
opening = get_balance_on(acc_name, to_date)
total_debit, total_credit = 0,0
out = []
for r in res:
total_debit = flt(total_debit) + flt(r[col_idx['Debit']])
total_credit = flt(total_credit) + flt(r[col_idx['Credit']])
out.append(r)
if acc and acc[0][0] == 'Debit':
bank_bal = flt(opening)-flt(total_debit)+flt(total_credit)
else:
bank_bal = flt(opening)+flt(total_debit)-flt(total_credit)
out.append(['','','','<font color = "#000"><b>Balance as per Company Books: </b></font>', opening,'', ''])
def get_closing_balance_row(filters, debit_or_credit): closing_balance = get_balance_on(filters["account"], filters["to_date"]) return get_balance_row(closing_balance, debit_or_credit, "Closing Balance")
def test_delivery_note_gl_entry(self):
self.clear_stock_account_balance()
set_perpetual_inventory()
self.assertEqual(
cint(
webnotes.defaults.get_global_default(
"auto_accounting_for_stock")), 1)
webnotes.conn.set_value("Item", "_Test Item", "valuation_method",
"FIFO")
_insert_purchase_receipt()
dn = webnotes.bean(copy=test_records[0])
dn.doclist[1].expense_account = "Cost of Goods Sold - _TC"
dn.doclist[1].cost_center = "Main - _TC"
stock_in_hand_account = webnotes.conn.get_value(
"Account", {"master_name": dn.doclist[1].warehouse})
from accounts.utils import get_balance_on
prev_bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
dn.insert()
dn.submit()
gl_entries = get_gl_entries("Delivery Note", dn.doc.name)
self.assertTrue(gl_entries)
expected_values = {
stock_in_hand_account: [0.0, 375.0],
"Cost of Goods Sold - _TC": [375.0, 0.0]
}
for i, gle in enumerate(gl_entries):
self.assertEquals([gle.debit, gle.credit],
expected_values.get(gle.account))
# check stock in hand balance
bal = get_balance_on(stock_in_hand_account, dn.doc.posting_date)
self.assertEquals(bal, prev_bal - 375.0)
# back dated purchase receipt
pr = webnotes.bean(copy=pr_test_records[0])
pr.doc.posting_date = "2013-01-01"
pr.doclist[1].import_rate = 100
pr.doclist[1].amount = 100
pr.insert()
pr.submit()
gl_entries = get_gl_entries("Delivery Note", dn.doc.name)
self.assertTrue(gl_entries)
expected_values = {
stock_in_hand_account: [0.0, 666.65],
"Cost of Goods Sold - _TC": [666.65, 0.0]
}
for i, gle in enumerate(gl_entries):
self.assertEquals([gle.debit, gle.credit],
expected_values.get(gle.account))
dn.cancel()
self.assertFalse(get_gl_entries("Delivery Note", dn.doc.name))
set_perpetual_inventory(0)