aii: gl entries for delivery note
diff --git a/accounts/report/gross_profit/gross_profit.py b/accounts/report/gross_profit/gross_profit.py
index de8651b..fa926da 100644
--- a/accounts/report/gross_profit/gross_profit.py
+++ b/accounts/report/gross_profit/gross_profit.py
@@ -1,15 +1,13 @@
from __future__ import unicode_literals
import webnotes
from webnotes.utils import flt
-
-stock_ledger_entries = None
-item_sales_bom = None
+from stock.utils import get_buying_amount, get_sales_bom
def execute(filters=None):
if not filters: filters = {}
- get_stock_ledger_entries(filters)
- get_sales_bom()
+ stock_ledger_entries = get_stock_ledger_entries(filters)
+ item_sales_bom = get_sales_bom()
delivery_note_items = webnotes.conn.sql("""select dn.name, dn.posting_date, dn.posting_time,
dn.project_name, item.item_code, item.item_name, item.description, item.warehouse,
@@ -26,7 +24,8 @@
data = []
for row in delivery_note_items:
selling_amount = flt(row.amount)
- buying_amount = get_buying_amount(row)
+ buying_amount = get_buying_amount(row.item_code, row.warehouse,
+ row.qty, row.name, row.item_row, stock_ledger_entries, item_sales_bom)
if selling_amount:
gross_profit = selling_amount - buying_amount
gross_profit_percent = (gross_profit / selling_amount) * 100.0
@@ -38,42 +37,8 @@
gross_profit, gross_profit_percent, row.project])
return columns, data
-
-def get_buying_amount(row):
- if item_sales_bom.get(row.item_code):
- # sales bom item
- buying_amount = 0.0
- for bom_item in item_sales_bom[row.item_code]:
- buying_amount += _get_buying_amount(row.name, "[** No Item Row **]",
- bom_item.item_code, row.warehouse, bom_item.qty * row.qty)
- return buying_amount
- else:
- # doesn't have sales bom
- return _get_buying_amount(row.name, row.item_row, row.item_code, row.warehouse, row.qty)
-
-def _get_buying_amount(voucher_no, item_row, item_code, warehouse, qty):
- for i, sle in enumerate(stock_ledger_entries):
- if sle.voucher_type == "Delivery Note" and sle.voucher_no == voucher_no:
- if (sle.voucher_detail_no == item_row) or \
- (sle.item_code == item_code and sle.warehouse == warehouse and \
- abs(flt(sle.qty)) == qty):
- buying_amount = flt(stock_ledger_entries[i+1].stock_value) - flt(sle.stock_value)
-
- return buying_amount
-
- return 0.0
-
-def get_sales_bom():
- global item_sales_bom
- item_sales_bom = {}
-
- for r in webnotes.conn.sql("""select parent, item_code, qty from `tabSales BOM Item`""", as_dict=1):
- item_sales_bom.setdefault(r.parent, []).append(r)
-
-def get_stock_ledger_entries(filters):
- global stock_ledger_entries
-
+def get_stock_ledger_entries(filters):
query = """select item_code, voucher_type, voucher_no,
voucher_detail_no, posting_date, posting_time, stock_value,
warehouse, actual_qty as qty
@@ -84,4 +49,4 @@
query += " order by item_code desc, warehouse desc, posting_date desc, posting_time desc, name desc"
- stock_ledger_entries = webnotes.conn.sql(query, filters, as_dict=True)
+ return webnotes.conn.sql(query, filters, as_dict=True)
diff --git a/stock/doctype/delivery_note/delivery_note.py b/stock/doctype/delivery_note/delivery_note.py
index 35d008b..c0b9916 100644
--- a/stock/doctype/delivery_note/delivery_note.py
+++ b/stock/doctype/delivery_note/delivery_note.py
@@ -17,7 +17,7 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import cstr, flt, getdate
+from webnotes.utils import cstr, flt, getdate, cint
from webnotes.model.bean import getlist
from webnotes.model.code import get_obj
from webnotes import msgprint
@@ -251,6 +251,8 @@
self.update_stock_ledger(update_stock = 1)
self.credit_limit()
+
+ self.make_gl_entries()
# set DN status
webnotes.conn.set(self.doc, 'status', 'Submitted')
@@ -293,6 +295,8 @@
self.update_stock_ledger(update_stock = -1)
webnotes.conn.set(self.doc, 'status', 'Cancelled')
self.cancel_packing_slips()
+
+ self.make_gl_entries()
def check_next_docstatus(self):
@@ -389,4 +393,71 @@
self.doclist = get_obj('Sales Common').make_packing_list(self,'delivery_note_details')
sl = get_obj('Stock Ledger')
sl.scrub_serial_nos(self)
- sl.scrub_serial_nos(self, 'packing_details')
\ No newline at end of file
+ sl.scrub_serial_nos(self, 'packing_details')
+
+ def make_gl_entries(self):
+ if not cint(webnotes.defaults.get_global_default("auto_inventory_accounting")):
+ return
+
+ abbr = webnotes.conn.get_value("Company", self.doc.company, "abbr")
+ stock_delivered_account = "Stock Delivered But Not Billed - %s" % (abbr,)
+ stock_in_hand_account = self.get_stock_in_hand_account()
+
+ total_buying_amount = self.get_total_buying_amount()
+ if total_buying_amount:
+ gl_entries = [
+ # credit stock in hand account
+ self.get_gl_dict({
+ "account": stock_in_hand_account,
+ "against": stock_delivered_account,
+ "credit": total_buying_amount,
+ "remarks": self.doc.remarks or "Accounting Entry for Stock",
+ }, self.doc.docstatus == 2),
+
+ # debit stock received but not billed account
+ self.get_gl_dict({
+ "account": stock_delivered_account,
+ "against": stock_in_hand_account,
+ "debit": total_buying_amount,
+ "remarks": self.doc.remarks or "Accounting Entry for Stock",
+ }, self.doc.docstatus == 2),
+ ]
+ from accounts.general_ledger import make_gl_entries
+ make_gl_entries(gl_entries, cancel=self.doc.docstatus == 2)
+
+ def get_total_buying_amount(self):
+ from stock.utils import get_buying_amount, get_sales_bom
+ stock_ledger_entries = self.get_stock_ledger_entries()
+ item_sales_bom = get_sales_bom()
+ total_buying_amount = 0
+
+ if stock_ledger_entries:
+ for item in self.doclist.get({"parentfield": "delivery_note_details"}):
+ buying_amount = get_buying_amount(item.item_code, item.warehouse, item.qty,
+ self.doc.name, item.name, stock_ledger_entries, item_sales_bom)
+ total_buying_amount += buying_amount
+
+ return total_buying_amount
+
+ def get_stock_ledger_entries(self):
+ item_list, warehouse_list = self.get_distinct_item_warehouse()
+ if item_list and warehouse_list:
+ return webnotes.conn.sql("""select item_code, voucher_type, voucher_no,
+ voucher_detail_no, posting_date, posting_time, stock_value,
+ warehouse, actual_qty as qty from `tabStock Ledger Entry`
+ where ifnull(`is_cancelled`, "No") = "No" and company = %s
+ and item_code in (%s) and warehouse in (%s)
+ order by item_code desc, warehouse desc, posting_date desc,
+ posting_time desc, name desc""" %
+ ('%s', ', '.join(['%s']*len(item_list)), ', '.join(['%s']*len(warehouse_list))),
+ tuple([self.doc.company] + item_list + warehouse_list), as_dict=1)
+
+ def get_distinct_item_warehouse(self):
+ item_list = []
+ warehouse_list = []
+ for item in self.doclist.get({"parentfield": "delivery_note_details"}) \
+ + self.doclist.get({"parentfield": "packing_details"}):
+ item_list.append(item.item_code)
+ warehouse_list.append(item.warehouse)
+
+ return list(set(item_list)), list(set(warehouse_list))
\ No newline at end of file
diff --git a/stock/doctype/delivery_note/test_delivery_note.py b/stock/doctype/delivery_note/test_delivery_note.py
index 4666360..c2bb5d0 100644
--- a/stock/doctype/delivery_note/test_delivery_note.py
+++ b/stock/doctype/delivery_note/test_delivery_note.py
@@ -1,3 +1,89 @@
+# ERPNext - web based ERP (http://erpnext.com)
+# Copyright (C) 2012 Web Notes Technologies Pvt Ltd
+#
+# This program is free software: you can redistribute it and/or modify
+# it under the terms of the GNU General Public License as published by
+# the Free Software Foundation, either version 3 of the License, or
+# (at your option) any later version.
+#
+# This program is distributed in the hope that it will be useful,
+# but WITHOUT ANY WARRANTY; without even the implied warranty of
+# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+# GNU General Public License for more details.
+#
+# You should have received a copy of the GNU General Public License
+# along with this program. If not, see <http://www.gnu.org/licenses/>.
+
+
+from __future__ import unicode_literals
+import unittest
+import webnotes
+import webnotes.defaults
+from webnotes.utils import cint
+
+class TestDeliveryNote(unittest.TestCase):
+ def _insert_purchase_receipt(self):
+ from stock.doctype.purchase_receipt.test_purchase_receipt import test_records as pr_test_records
+ pr = webnotes.bean(copy=pr_test_records[0])
+ pr.run_method("calculate_taxes_and_totals")
+ pr.insert()
+ pr.submit()
+
+ def test_delivery_note_no_gl_entry(self):
+ webnotes.conn.sql("""delete from `tabBin`""")
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
+ self.assertEqual(cint(webnotes.defaults.get_global_default("auto_inventory_accounting")), 0)
+
+ self._insert_purchase_receipt()
+
+ dn = webnotes.bean(copy=test_records[0])
+ 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 desc""", dn.doc.name, as_dict=1)
+
+ self.assertTrue(not gl_entries)
+
+ def test_delivery_note_gl_entry(self):
+ webnotes.conn.sql("""delete from `tabBin`""")
+ 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])
+ 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],
+ ["Stock Delivered But Not Billed - _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)
+
test_records = [
[
{
@@ -15,18 +101,19 @@
"price_list_name": "_Test Price List",
"status": "Draft",
"territory": "_Test Territory",
+ "net_total": 500.0,
"grand_total": 500.0,
"grand_total_export": 500.0,
},
{
"description": "CPU",
"doctype": "Delivery Note Item",
- "item_code": "_Test Item Home Desktop 100",
+ "item_code": "_Test Item",
"item_name": "CPU",
"parentfield": "delivery_note_details",
- "qty": 4.0,
- "basic_rate": 50.0,
- "export_rate": 50.0,
+ "qty": 5.0,
+ "basic_rate": 100.0,
+ "export_rate": 100.0,
"amount": 500.0,
"warehouse": "_Test Warehouse",
"stock_uom": "No."
diff --git a/stock/doctype/item/item.txt b/stock/doctype/item/item.txt
index 1a48368..036b9d5 100644
--- a/stock/doctype/item/item.txt
+++ b/stock/doctype/item/item.txt
@@ -2,7 +2,7 @@
{
"creation": "2013-02-21 14:54:43",
"docstatus": 0,
- "modified": "2013-02-28 10:43:02",
+ "modified": "2013-03-06 16:02:47",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -404,18 +404,6 @@
},
{
"depends_on": "eval:doc.is_purchase_item==\"Yes\"",
- "description": "Buying Cost will be updated from Purchase Orders and Purchase Receipts. <br>The buying cost will calculated by moving average method.",
- "doctype": "DocField",
- "fieldname": "buying_cost",
- "fieldtype": "Float",
- "label": "Buying Cost",
- "no_copy": 1,
- "oldfieldname": "buying_cost",
- "oldfieldtype": "Currency",
- "read_only": 1
- },
- {
- "depends_on": "eval:doc.is_purchase_item==\"Yes\"",
"doctype": "DocField",
"fieldname": "last_purchase_rate",
"fieldtype": "Float",
diff --git a/stock/utils.py b/stock/utils.py
index 6fac0fd..9055cee 100644
--- a/stock/utils.py
+++ b/stock/utils.py
@@ -163,4 +163,37 @@
elif webnotes.session.user in warehouse_users:
wlist.append([w])
return wlist
-
\ No newline at end of file
+
+def get_buying_amount(item_code, warehouse, qty, voucher_no, voucher_detail_no,
+ stock_ledger_entries, item_sales_bom):
+ if item_sales_bom.get(item_code):
+ # sales bom item
+ buying_amount = 0.0
+ for bom_item in item_sales_bom[item_code]:
+ buying_amount += _get_buying_amount(voucher_no, "[** No Item Row **]",
+ item_code, warehouse, bom_item.qty * qty, stock_ledger_entries)
+ return buying_amount
+ else:
+ # doesn't have sales bom
+ return _get_buying_amount(voucher_no, voucher_detail_no, item_code, warehouse, qty,
+ stock_ledger_entries)
+
+def _get_buying_amount(voucher_no, item_row, item_code, warehouse, qty, stock_ledger_entries):
+ for i, sle in enumerate(stock_ledger_entries):
+ if sle.voucher_type == "Delivery Note" and sle.voucher_no == voucher_no:
+ if (sle.voucher_detail_no == item_row) or \
+ (sle.item_code == item_code and sle.warehouse == warehouse and \
+ abs(flt(sle.qty)) == qty):
+ buying_amount = flt(stock_ledger_entries[i+1].stock_value) - flt(sle.stock_value)
+
+ return buying_amount
+
+ return 0.0
+
+def get_sales_bom():
+ item_sales_bom = {}
+ for r in webnotes.conn.sql("""select parent, item_code, qty from `tabSales BOM Item`""",
+ as_dict=1):
+ item_sales_bom.setdefault(r.parent, []).append(r)
+
+ return item_sales_bom
\ No newline at end of file