aii: stock reconciliation with test case
diff --git a/accounts/doctype/account/account.py b/accounts/doctype/account/account.py
index 08bf80f..3cd131f 100644
--- a/accounts/doctype/account/account.py
+++ b/accounts/doctype/account/account.py
@@ -207,4 +207,4 @@
where group_or_ledger = 'Group' and docstatus != 2 and company = %s
and %s like %s order by name limit %s, %s""" %
("%s", searchfield, "%s", "%s", "%s"),
- (filters["company"], "%%%s%%" % txt, start, page_len), as_list=1)
+ (filters["company"], "%%%s%%" % txt, start, page_len), as_list=1)
\ No newline at end of file
diff --git a/accounts/doctype/fiscal_year/fiscal_year.txt b/accounts/doctype/fiscal_year/fiscal_year.txt
index 18f20dd..935b76f 100644
--- a/accounts/doctype/fiscal_year/fiscal_year.txt
+++ b/accounts/doctype/fiscal_year/fiscal_year.txt
@@ -1,8 +1,8 @@
[
{
- "creation": "2013-01-10 16:34:06",
+ "creation": "2013-01-22 16:50:25",
"docstatus": 0,
- "modified": "2013-01-22 14:46:59",
+ "modified": "2013-03-13 12:29:40",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -23,7 +23,6 @@
"permlevel": 0
},
{
- "amend": 0,
"cancel": 1,
"create": 1,
"doctype": "DocPerm",
@@ -43,22 +42,6 @@
"name": "Fiscal Year"
},
{
- "doctype": "DocField",
- "fieldname": "year_details",
- "fieldtype": "Section Break",
- "label": "Fiscal Year Details",
- "oldfieldtype": "Section Break"
- },
- {
- "doctype": "DocField",
- "fieldname": "trash_reason",
- "fieldtype": "Small Text",
- "label": "Trash Reason",
- "oldfieldname": "trash_reason",
- "oldfieldtype": "Small Text",
- "read_only": 1
- },
- {
"description": "For e.g. 2012, 2012-13",
"doctype": "DocField",
"fieldname": "year",
@@ -73,6 +56,7 @@
"fieldname": "year_start_date",
"fieldtype": "Date",
"label": "Year Start Date",
+ "no_copy": 1,
"oldfieldname": "year_start_date",
"oldfieldtype": "Date",
"reqd": 1
@@ -84,6 +68,7 @@
"fieldname": "is_fiscal_year_closed",
"fieldtype": "Select",
"label": "Year Closed",
+ "no_copy": 1,
"oldfieldname": "is_fiscal_year_closed",
"oldfieldtype": "Select",
"options": "\nNo\nYes",
diff --git a/accounts/doctype/purchase_invoice/purchase_invoice.js b/accounts/doctype/purchase_invoice/purchase_invoice.js
index f4a2e68..4a1cbba 100644
--- a/accounts/doctype/purchase_invoice/purchase_invoice.js
+++ b/accounts/doctype/purchase_invoice/purchase_invoice.js
@@ -238,8 +238,11 @@
refresh_field('entries');
}
-cur_frm.fields_dict['entries'].grid.get_field("cost_center").get_query = function(doc) {
- return 'SELECT `tabCost Center`.`name` FROM `tabCost Center` WHERE `tabCost Center`.`company_name` = "' +doc.company+'" AND `tabCost Center`.%(key)s LIKE "%s" AND `tabCost Center`.`group_or_ledger` = "Ledger" AND `tabCost Center`.docstatus != 2 ORDER BY `tabCost Center`.`name` ASC LIMIT 50';
+cur_frm.fields_dict["entries"].grid.get_field("cost_center").get_query = function(doc) {
+ return {
+ query: "accounts.utils.get_cost_center_list",
+ filters: { company_name: doc.company}
+ }
}
cur_frm.cscript.cost_center = function(doc, cdt, cdn){
diff --git a/accounts/doctype/sales_invoice/sales_invoice.js b/accounts/doctype/sales_invoice/sales_invoice.js
index a24e256..5cc09a0 100644
--- a/accounts/doctype/sales_invoice/sales_invoice.js
+++ b/accounts/doctype/sales_invoice/sales_invoice.js
@@ -271,8 +271,6 @@
if (doc.is_opening == 'Yes') unhide_field('aging_date');
}
-/* **************************** TRIGGERS ********************************** */
-
// Get Items based on SO or DN Selected
cur_frm.cscript.get_items = function(doc, dt, dn) {
var callback = function(r,rt) {
@@ -371,6 +369,18 @@
return 'SELECT tabAccount.name FROM tabAccount WHERE (tabAccount.debit_or_credit="Credit" OR tabAccount.account_type = "Income Account") AND tabAccount.group_or_ledger="Ledger" AND tabAccount.docstatus!=2 AND tabAccount.company="'+doc.company+'" AND tabAccount.%(key)s LIKE "%s"';
})
+// expense account
+cur_frm.fields_dict['entries'].grid.get_field('expense_account').get_query = function(doc) {
+ return {
+ "query": "accounts.utils.get_account_list",
+ "filters": {
+ "is_pl_account": "Yes",
+ "debit_or_credit": "Debit",
+ "company": doc.company
+ }
+ }
+}
+
// warehouse in detail table
//----------------------------
cur_frm.fields_dict['entries'].grid.get_field('warehouse').get_query= function(doc, cdt, cdn) {
@@ -380,8 +390,11 @@
// Cost Center in Details Table
// -----------------------------
-cur_frm.fields_dict.entries.grid.get_field("cost_center").get_query = function(doc) {
- return 'SELECT `tabCost Center`.`name` FROM `tabCost Center` WHERE `tabCost Center`.`company_name` = "' +doc.company+'" AND `tabCost Center`.%(key)s LIKE "%s" AND `tabCost Center`.`group_or_ledger` = "Ledger" AND `tabCost Center`.`docstatus`!= 2 ORDER BY `tabCost Center`.`name` ASC LIMIT 50';
+cur_frm.fields_dict["entries"].grid.get_field("cost_center").get_query = function(doc) {
+ return {
+ query: "accounts.utils.get_cost_center_list",
+ filters: { company_name: doc.company}
+ }
}
// Sales Order
diff --git a/accounts/doctype/sales_invoice/sales_invoice.py b/accounts/doctype/sales_invoice/sales_invoice.py
index de3ee95..b6b1f0b 100644
--- a/accounts/doctype/sales_invoice/sales_invoice.py
+++ b/accounts/doctype/sales_invoice/sales_invoice.py
@@ -804,8 +804,9 @@
item_buying_amount = 0
if stock_ledger_entries:
# is pos and update stock
- item_buying_amount = get_buying_amount(item.item_code, item.warehouse, item.qty,
+ item_buying_amount = get_buying_amount(item.item_code, item.warehouse, -1*item.qty,
self.doc.doctype, self.doc.name, item.name, stock_ledger_entries, item_sales_bom)
+ item.buying_amount = item_buying_amount > 0 and item_buying_amount or 0
elif item.delivery_note and item.dn_detail:
# against delivery note
dn_item = webnotes.conn.get_value("Delivery Note Item", item.dn_detail,
diff --git a/accounts/doctype/sales_invoice_item/sales_invoice_item.txt b/accounts/doctype/sales_invoice_item/sales_invoice_item.txt
index ca078b5..6f6ad39 100644
--- a/accounts/doctype/sales_invoice_item/sales_invoice_item.txt
+++ b/accounts/doctype/sales_invoice_item/sales_invoice_item.txt
@@ -2,7 +2,7 @@
{
"creation": "2013-03-07 11:42:55",
"docstatus": 0,
- "modified": "2013-03-11 14:58:50",
+ "modified": "2013-03-18 15:41:19",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -207,14 +207,16 @@
"width": "120px"
},
{
+ "depends_on": "eval:sys_defaults.auto_inventory_accounting",
"doctype": "DocField",
"fieldname": "expense_account",
"fieldtype": "Link",
- "hidden": 1,
+ "hidden": 0,
"in_filter": 1,
"label": "Expense Account",
"options": "Account",
- "print_hide": 1
+ "print_hide": 1,
+ "width": "120px"
},
{
"doctype": "DocField",
diff --git a/accounts/report/gross_profit/gross_profit.py b/accounts/report/gross_profit/gross_profit.py
index f1ae00e..2480e17 100644
--- a/accounts/report/gross_profit/gross_profit.py
+++ b/accounts/report/gross_profit/gross_profit.py
@@ -24,8 +24,10 @@
data = []
for row in delivery_note_items:
selling_amount = flt(row.amount)
- buying_amount = get_buying_amount(row.item_code, row.warehouse,
- row.qty, "Delivery Note", row.name, row.item_row, stock_ledger_entries, item_sales_bom)
+ buying_amount = get_buying_amount(row.item_code, row.warehouse, -1*row.qty,
+ "Delivery Note", row.name, row.item_row, stock_ledger_entries, item_sales_bom)
+ buying_amount = buying_amount > 0 and buying_amount or 0
+
if selling_amount:
gross_profit = selling_amount - buying_amount
gross_profit_percent = (gross_profit / selling_amount) * 100.0
diff --git a/controllers/accounts_controller.py b/controllers/accounts_controller.py
index 73d7608..f328473 100644
--- a/controllers/accounts_controller.py
+++ b/controllers/accounts_controller.py
@@ -18,6 +18,7 @@
import webnotes
from webnotes import msgprint, _
from webnotes.utils import flt
+
from utilities.transaction_base import TransactionBase
class AccountsController(TransactionBase):
@@ -70,7 +71,6 @@
def get_stock_in_hand_account(self):
stock_in_hand_account = webnotes.conn.get_value("Company", self.doc.company, "stock_in_hand_account")
-
if not stock_in_hand_account:
msgprint(_("Missing") + ": "
+ _(webnotes.get_doctype("company").get_label("stock_in_hand_account")
diff --git a/controllers/buying_controller.py b/controllers/buying_controller.py
index 2f3128c..03d2a92 100644
--- a/controllers/buying_controller.py
+++ b/controllers/buying_controller.py
@@ -24,9 +24,9 @@
from setup.utils import get_company_currency
from webnotes.model.utils import round_floats_in_doc
-from controllers.accounts_controller import AccountsController
+from controllers.stock_controller import StockController
-class BuyingController(AccountsController):
+class BuyingController(StockController):
def validate(self):
if self.meta.get_field("currency"):
self.company_currency = get_company_currency(self.doc.company)
diff --git a/setup/doctype/company/company.py b/setup/doctype/company/company.py
index 9cf722f..4051717 100644
--- a/setup/doctype/company/company.py
+++ b/setup/doctype/company/company.py
@@ -17,7 +17,7 @@
from __future__ import unicode_literals
import webnotes
-from webnotes.utils import cstr, set_default
+from webnotes.utils import cstr
from webnotes.model.doc import Document
from webnotes.model.code import get_obj
import webnotes.defaults
@@ -49,6 +49,8 @@
['Earnest Money','Securities and Deposits','Ledger','No','','Debit',self.doc.name,''],
['Stock In Hand','Current Assets','Group','No','','Debit',self.doc.name,''],
['Stock','Stock In Hand','Ledger','No','','Debit',self.doc.name,''],
+ ['Stock Delivered But Not Billed', 'Stock In Hand', 'Ledger',
+ 'No', '', 'Debit', self.doc.name, ''],
['Tax Assets','Current Assets','Group','No','','Debit',self.doc.name,''],
['Stock Delivered But Not Billed','Current Assets','Ledger','No','','Debit',self.doc.name,''],
['Fixed Assets','Application of Funds (Assets)','Group','No','','Debit',self.doc.name,''],
@@ -62,9 +64,9 @@
['Temporary Account (Assets)','Temporary Accounts (Assets)','Ledger','No','','Debit',self.doc.name,''],
['Expenses','','Group','Yes','Expense Account','Debit',self.doc.name,''],
['Direct Expenses','Expenses','Group','Yes','Expense Account','Debit',self.doc.name,''],
- ['Cost of Goods Sold','Direct Expenses','Ledger','Yes','Expense Account','Debit',self.doc.name,''],
- ['Expenses Included In Valuation','Direct Expenses','Ledger','Yes','Expense Account','Debit',self.doc.name,''],
- ['Stock Adjustment','Direct Expenses','Ledger','Yes','Expense Account','Debit',self.doc.name,''],
+ ['Stock Expenses','Direct Expenses','Group','Yes','Expense Account','Debit',self.doc.name,''],
+ ['Cost of Goods Sold','Stock Expenses','Ledger','Yes','Expense Account','Debit',self.doc.name,''],
+ ['Stock Adjustment','Stock Expenses','Ledger','Yes','Expense Account','Debit',self.doc.name,''],
['Indirect Expenses','Expenses','Group','Yes','Expense Account','Debit',self.doc.name,''],
['Advertising and Publicity','Indirect Expenses','Ledger','Yes','Chargeable','Debit',self.doc.name,''],
['Bad Debts Written Off','Indirect Expenses','Ledger','Yes','Expense Account','Debit',self.doc.name,''],
@@ -101,6 +103,9 @@
['Shareholders Funds','Capital Account','Group','No','','Credit',self.doc.name,''],
['Current Liabilities','Source of Funds (Liabilities)','Group','No','','Credit',self.doc.name,''],
['Accounts Payable','Current Liabilities','Group','No','','Credit',self.doc.name,''],
+ ['Stock Liabilities','Current Liabilities','Group','No','','Credit',self.doc.name,''],
+ ['Stock Received But Not Billed', 'Stock Liabilities', 'Ledger',
+ 'No', '', 'Credit', self.doc.name, ''],
['Duties and Taxes','Current Liabilities','Group','No','','Credit',self.doc.name,''],
['Loans (Liabilities)','Current Liabilities','Group','No','','Credit',self.doc.name,''],
['Secured Loans','Loans (Liabilities)','Group','No','','Credit',self.doc.name,''],
@@ -186,14 +191,30 @@
self.doc.letter_head = header
- # Set default AR and AP group
- # ---------------------------------------------------
- def set_default_groups(self):
- if not self.doc.receivables_group:
- webnotes.conn.set(self.doc, 'receivables_group', 'Accounts Receivable - '+self.doc.abbr)
- if not self.doc.payables_group:
- webnotes.conn.set(self.doc, 'payables_group', 'Accounts Payable - '+self.doc.abbr)
+ def set_default_accounts(self):
+ if not self.doc.receivables_group and webnotes.conn.exists('Account',
+ 'Accounts Receivable - ' + self.doc.abbr):
+ webnotes.conn.set(self.doc, 'receivables_group', 'Accounts Receivable - ' +
+ self.doc.abbr)
+
+ if not self.doc.payables_group and webnotes.conn.exists('Account',
+ 'Accounts Payable - ' + self.doc.abbr):
+ webnotes.conn.set(self.doc, 'payables_group', 'Accounts Payable - ' + self.doc.abbr)
+ if not self.doc.stock_delivered_but_not_billed and webnotes.conn.exists("Account",
+ "Stock Delivered But Not Billed - " + self.doc.abbr):
+ webnotes.conn.set(self.doc, "stock_delivered_but_not_billed",
+ "Stock Delivered But Not Billed - " + self.doc.abbr)
+
+ if not self.doc.stock_received_but_not_billed and webnotes.conn.exists("Account",
+ "Stock Received But Not Billed - " + self.doc.abbr):
+ webnotes.conn.set(self.doc, "stock_received_but_not_billed",
+ "Stock Received But Not Billed - " + self.doc.abbr)
+
+ if not self.doc.stock_adjustment_account and webnotes.conn.exists("Account",
+ "Stock Adjustment - " + self.doc.abbr):
+ webnotes.conn.set(self.doc, "stock_adjustment_account", "Stock Adjustment - " +
+ self.doc.abbr)
# Create default cost center
# ---------------------------------------------------
@@ -228,7 +249,7 @@
self.doc.name)
if not ac:
self.create_default_accounts()
- self.set_default_groups()
+ self.set_default_accounts()
cc = sql("select name from `tabCost Center` where cost_center_name = 'Root' and company_name = '%s'"%(self.doc.name))
if not cc:
self.create_default_cost_center()
@@ -258,9 +279,6 @@
#update value as blank for tabSingles Global Defaults
sql("update `tabSingles` set value = '' where doctype='Global Defaults' and field = 'default_company' and value = %s", self.doc.name)
-
- # on rename
- # ---------
def on_rename(self,newdn,olddn):
sql("update `tabCompany` set company_name = '%s' where name = '%s'" %(newdn,olddn))
sql("update `tabSingles` set value = %s where doctype='Global Defaults' and field = 'default_company' and value = %s", (newdn, olddn))
diff --git a/setup/doctype/company/company.txt b/setup/doctype/company/company.txt
index d97cfc3..d8c649f 100644
--- a/setup/doctype/company/company.txt
+++ b/setup/doctype/company/company.txt
@@ -1,8 +1,8 @@
[
{
- "creation": "2013-02-22 01:27:54",
+ "creation": "2013-02-27 09:38:05",
"docstatus": 0,
- "modified": "2013-02-26 10:57:39",
+ "modified": "2013-03-18 16:34:04",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -125,17 +125,6 @@
"options": "Account"
},
{
- "depends_on": "eval:!doc.__islocal",
- "description": "This account will be used to maintain value of available stock",
- "doctype": "DocField",
- "fieldname": "stock_in_hand_account",
- "fieldtype": "Link",
- "label": "Stock In Hand Account",
- "no_copy": 1,
- "options": "Account",
- "read_only": 0
- },
- {
"doctype": "DocField",
"fieldname": "column_break0",
"fieldtype": "Column Break",
@@ -182,6 +171,50 @@
"options": "\nWarn\nIgnore\nStop"
},
{
+ "depends_on": "eval:!doc.__islocal && sys_defaults.auto_inventory_accounting",
+ "doctype": "DocField",
+ "fieldname": "auto_inventory_accounting_settings",
+ "fieldtype": "Section Break",
+ "label": "Auto Inventory Accounting Settings"
+ },
+ {
+ "description": "This account will be used to maintain value of available stock",
+ "doctype": "DocField",
+ "fieldname": "stock_in_hand_account",
+ "fieldtype": "Link",
+ "label": "Stock In Hand Account",
+ "no_copy": 1,
+ "options": "Account",
+ "read_only": 0
+ },
+ {
+ "doctype": "DocField",
+ "fieldname": "stock_adjustment_account",
+ "fieldtype": "Link",
+ "label": "Stock Adjustment Account",
+ "options": "Account"
+ },
+ {
+ "doctype": "DocField",
+ "fieldname": "col_break23",
+ "fieldtype": "Column Break",
+ "width": "50%"
+ },
+ {
+ "doctype": "DocField",
+ "fieldname": "stock_delivered_but_not_billed",
+ "fieldtype": "Link",
+ "label": "Stock Delivered But Not Billed",
+ "options": "Account"
+ },
+ {
+ "doctype": "DocField",
+ "fieldname": "stock_received_but_not_billed",
+ "fieldtype": "Link",
+ "label": "Stock Received But Not Billed",
+ "options": "Account"
+ },
+ {
"description": "For reference only.",
"doctype": "DocField",
"fieldname": "company_info",
diff --git a/stock/doctype/delivery_note/delivery_note.py b/stock/doctype/delivery_note/delivery_note.py
index 229ec40..aef7939 100644
--- a/stock/doctype/delivery_note/delivery_note.py
+++ b/stock/doctype/delivery_note/delivery_note.py
@@ -24,7 +24,6 @@
sql = webnotes.conn.sql
-
from controllers.selling_controller import SellingController
class DocType(SellingController):
@@ -401,9 +400,10 @@
if stock_ledger_entries:
for item in self.doclist.get({"parentfield": "delivery_note_details"}):
- item.buying_amount = get_buying_amount(item.item_code, item.warehouse, item.qty,
+ buying_amount = get_buying_amount(item.item_code, item.warehouse, -1*item.qty,
self.doc.doctype, self.doc.name, item.name, stock_ledger_entries,
item_sales_bom)
+ item.buying_amount = buying_amount > 0 and buying_amount or 0
webnotes.conn.set_value("Delivery Note Item", item.name, "buying_amount",
item.buying_amount)
@@ -420,32 +420,11 @@
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()
-
+ against_stock_account = "Stock Delivered But Not Billed - %s" % (self.company_abbr,)
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)
-
+
+ super(DocType, self).make_gl_entries(against_stock_account, -1*total_buying_amount)
+
def get_total_buying_amount(self):
total_buying_amount = sum([item.buying_amount for item in
self.doclist.get({"parentfield": "delivery_note_details"})])
diff --git a/stock/doctype/purchase_receipt/purchase_receipt.py b/stock/doctype/purchase_receipt/purchase_receipt.py
index cce1493..e26c0a6 100644
--- a/stock/doctype/purchase_receipt/purchase_receipt.py
+++ b/stock/doctype/purchase_receipt/purchase_receipt.py
@@ -20,8 +20,7 @@
from webnotes.utils import cstr, flt, cint
from webnotes.model.bean import getlist
from webnotes.model.code import get_obj
-from webnotes.model.doc import Document
-from webnotes import msgprint, _
+from webnotes import msgprint
sql = webnotes.conn.sql
@@ -319,32 +318,10 @@
if not cint(webnotes.defaults.get_global_default("auto_inventory_accounting")):
return
- abbr = webnotes.conn.get_value("Company", self.doc.company, "abbr")
- stock_received_account = "Stock Received But Not Billed - %s" % (abbr,)
- stock_in_hand_account = self.get_stock_in_hand_account()
-
+ against_stock_account = "Stock Received But Not Billed - %s" % (self.company_abbr,)
total_valuation_amount = self.get_total_valuation_amount()
- if total_valuation_amount:
- gl_entries = [
- # debit stock in hand account
- self.get_gl_dict({
- "account": stock_in_hand_account,
- "against": stock_received_account,
- "debit": total_valuation_amount,
- "remarks": self.doc.remarks or "Accounting Entry for Stock",
- }, self.doc.docstatus == 2),
-
- # credit stock received but not billed account
- self.get_gl_dict({
- "account": stock_received_account,
- "against": stock_in_hand_account,
- "credit": total_valuation_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)
+ super(DocType, self).make_gl_entries(against_stock_account, total_valuation_amount)
def get_total_valuation_amount(self):
total_valuation_amount = 0.0
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.js b/stock/doctype/stock_reconciliation/stock_reconciliation.js
index f1508ac..fb4053c 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.js
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.js
@@ -18,6 +18,10 @@
wn.provide("erpnext.stock");
erpnext.stock.StockReconciliation = erpnext.stock.StockController.extend({
+ setup: function() {
+ this.frm.add_fetch("company", "stock_adjustment_account", "expense_account");
+ },
+
refresh: function() {
if(this.frm.doc.docstatus===0) {
this.show_download_template();
@@ -122,4 +126,15 @@
},
});
-cur_frm.cscript = new erpnext.stock.StockReconciliation({frm: cur_frm});
\ No newline at end of file
+cur_frm.cscript = new erpnext.stock.StockReconciliation({frm: cur_frm});
+
+cur_frm.fields_dict["expense_account"].get_query = function(doc) {
+ return {
+ "query": "accounts.utils.get_account_list",
+ "filters": {
+ "is_pl_account": "Yes",
+ "debit_or_credit": "Debit",
+ "company": doc.company
+ }
+ }
+}
\ No newline at end of file
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.py b/stock/doctype/stock_reconciliation/stock_reconciliation.py
index e0f7f09..c2f5a94 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.py
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.py
@@ -18,22 +18,26 @@
import webnotes
import json
from webnotes import msgprint, _
-from webnotes.utils import cstr, flt
-from webnotes.model.controller import DocListController
+from webnotes.utils import cstr, flt, cint
from stock.stock_ledger import update_entries_after
+from controllers.stock_controller import StockController
-class DocType(DocListController):
+class DocType(StockController):
def setup(self):
self.head_row = ["Item Code", "Warehouse", "Quantity", "Valuation Rate"]
+ self.entries = []
def validate(self):
self.validate_data()
def on_submit(self):
self.insert_stock_ledger_entries()
+ self.set_stock_value_difference()
+ self.make_gl_entries()
def on_cancel(self):
self.delete_stock_ledger_entries()
+ self.make_gl_entries()
def validate_data(self):
if not self.doc.reconciliation_json:
@@ -134,6 +138,7 @@
data = json.loads(self.doc.reconciliation_json)
for row_num, row in enumerate(data[data.index(self.head_row)+1:]):
row = webnotes._dict(zip(row_template, row))
+ row["row_num"] = row_num
previous_sle = get_previous_sle({
"item_code": row.item_code,
"warehouse": row.warehouse,
@@ -162,8 +167,7 @@
def sle_for_moving_avg(self, row, previous_sle, change_in_qty, change_in_rate):
"""Insert Stock Ledger Entries for Moving Average valuation"""
- def _get_incoming_rate(qty, valuation_rate, previous_qty,
- previous_valuation_rate):
+ def _get_incoming_rate(qty, valuation_rate, previous_qty, previous_valuation_rate):
if previous_valuation_rate == 0:
return flt(valuation_rate)
else:
@@ -177,9 +181,9 @@
incoming_rate = _get_incoming_rate(flt(row.qty), flt(row.valuation_rate),
flt(previous_sle.get("qty_after_transaction")),
flt(previous_sle.get("valuation_rate")))
-
- self.insert_entries({"actual_qty": change_in_qty,
- "incoming_rate": incoming_rate}, row)
+
+ row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Actual Entry"
+ self.insert_entries({"actual_qty": change_in_qty, "incoming_rate": incoming_rate}, row)
elif change_in_rate and flt(previous_sle.get("qty_after_transaction")) > 0:
# if no change in qty, but change in rate
@@ -190,9 +194,11 @@
flt(previous_sle.get("valuation_rate")))
# +1 entry
+ row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Valuation Adjustment +1"
self.insert_entries({"actual_qty": 1, "incoming_rate": incoming_rate}, row)
# -1 entry
+ row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Valuation Adjustment -1"
self.insert_entries({"actual_qty": -1}, row)
def sle_for_fifo(self, row, previous_sle, change_in_qty, change_in_rate):
@@ -206,14 +212,16 @@
if previous_stock_queue != [[row.qty, row.valuation_rate]]:
# make entry as per attachment
if row.qty:
+ row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Actual Entry"
self.insert_entries({"actual_qty": row.qty,
"incoming_rate": flt(row.valuation_rate)}, row)
# Make reverse entry
if previous_stock_qty:
+ row["voucher_detail_no"] = "Row: " + cstr(row.row_num) + "/Reverse Entry"
self.insert_entries({"actual_qty": -1 * previous_stock_qty,
- "incoming_rate": previous_stock_qty < 0 and \
- flt(row.valuation_rate) or 0}, row)
+ "incoming_rate": previous_stock_qty < 0 and
+ flt(row.valuation_rate) or 0}, row)
if change_in_qty:
@@ -221,8 +229,7 @@
# dont want change in valuation
if previous_stock_qty > 0:
# set valuation_rate as previous valuation_rate
- row.valuation_rate = \
- previous_stock_value / flt(previous_stock_qty)
+ row.valuation_rate = previous_stock_value / flt(previous_stock_qty)
_insert_entries()
@@ -234,8 +241,8 @@
_insert_entries()
def insert_entries(self, opts, row):
- """Insert Stock Ledger Entries"""
- args = {
+ """Insert Stock Ledger Entries"""
+ args = webnotes._dict({
"doctype": "Stock Ledger Entry",
"item_code": row.item_code,
"warehouse": row.warehouse,
@@ -243,9 +250,10 @@
"posting_time": self.doc.posting_time,
"voucher_type": self.doc.doctype,
"voucher_no": self.doc.name,
- "company": webnotes.conn.get_default("company"),
+ "company": self.doc.company,
"is_cancelled": "No",
- }
+ "voucher_detail_no": row.voucher_detail_no
+ })
args.update(opts)
# create stock ledger entry
sle_wrapper = webnotes.bean([args])
@@ -254,17 +262,18 @@
# update bin
webnotes.get_obj('Warehouse', row.warehouse).update_bin(args)
-
- return sle_wrapper
+
+ # append to entries
+ self.entries.append(args)
def delete_stock_ledger_entries(self):
""" Delete Stock Ledger Entries related to this Stock Reconciliation
and repost future Stock Ledger Entries"""
-
+
existing_entries = webnotes.conn.sql("""select item_code, warehouse
- from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation'
+ from `tabStock Ledger Entry` where voucher_type='Stock Reconciliation'
and voucher_no=%s""", self.doc.name, as_dict=1)
-
+
# delete entries
webnotes.conn.sql("""delete from `tabStock Ledger Entry`
where voucher_type='Stock Reconciliation' and voucher_no=%s""", self.doc.name)
@@ -277,7 +286,33 @@
"posting_date": self.doc.posting_date,
"posting_time": self.doc.posting_time
})
-
+
+ def set_stock_value_difference(self):
+ """stock_value_difference is the increment in the stock value"""
+ from stock.utils import get_buying_amount
+
+ item_list = [d.item_code for d in self.entries]
+ warehouse_list = [d.warehouse for d in self.entries]
+ stock_ledger_entries = self.get_stock_ledger_entries(item_list, warehouse_list)
+
+ self.doc.stock_value_difference = 0.0
+ for d in self.entries:
+ self.doc.stock_value_difference -= get_buying_amount(d.item_code, d.warehouse,
+ d.actual_qty, self.doc.doctype, self.doc.name, d.voucher_detail_no,
+ stock_ledger_entries)
+ webnotes.conn.set(self.doc, "stock_value_difference", self.doc.stock_value_difference)
+
+ def make_gl_entries(self):
+ if not cint(webnotes.defaults.get_global_default("auto_inventory_accounting")):
+ return
+
+ if not self.doc.expense_account:
+ msgprint(_("Please enter Expense Account"), raise_exception=1)
+
+ cost_center = "Auto Inventory Accounting - %s" % (self.company_abbr,)
+
+ super(DocType, self).make_gl_entries(self.doc.expense_account,
+ self.doc.stock_value_difference, cost_center)
@webnotes.whitelist()
def upload():
diff --git a/stock/doctype/stock_reconciliation/stock_reconciliation.txt b/stock/doctype/stock_reconciliation/stock_reconciliation.txt
index 094e903..9137cae 100644
--- a/stock/doctype/stock_reconciliation/stock_reconciliation.txt
+++ b/stock/doctype/stock_reconciliation/stock_reconciliation.txt
@@ -1,8 +1,8 @@
[
{
- "creation": "2013-01-19 10:23:35",
+ "creation": "2013-01-22 16:50:41",
"docstatus": 0,
- "modified": "2013-01-22 14:57:24",
+ "modified": "2013-03-18 12:48:42",
"modified_by": "Administrator",
"owner": "Administrator"
},
@@ -30,6 +30,7 @@
"permlevel": 0
},
{
+ "amend": 1,
"cancel": 1,
"create": 1,
"doctype": "DocPerm",
@@ -40,6 +41,7 @@
"permlevel": 0,
"read": 1,
"report": 1,
+ "role": "Material Manager",
"submit": 1,
"write": 1
},
@@ -81,6 +83,22 @@
},
{
"doctype": "DocField",
+ "fieldname": "company",
+ "fieldtype": "Link",
+ "label": "Company",
+ "options": "Company",
+ "reqd": 1
+ },
+ {
+ "depends_on": "eval:sys_defaults.auto_inventory_accounting",
+ "doctype": "DocField",
+ "fieldname": "expense_account",
+ "fieldtype": "Link",
+ "label": "Expense Account",
+ "options": "Account"
+ },
+ {
+ "doctype": "DocField",
"fieldname": "col1",
"fieldtype": "Column Break"
},
@@ -119,12 +137,14 @@
"read_only": 1
},
{
- "amend": 0,
- "doctype": "DocPerm",
- "role": "Material Manager"
+ "doctype": "DocField",
+ "fieldname": "stock_value_difference",
+ "fieldtype": "Currency",
+ "hidden": 1,
+ "label": "Stock Value Difference",
+ "print_hide": 1
},
{
- "doctype": "DocPerm",
- "role": "System Manager"
+ "doctype": "DocPerm"
}
]
\ No newline at end of file
diff --git a/stock/doctype/stock_reconciliation/test_stock_reconciliation.py b/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
index 80579ae..cebc6ff 100644
--- a/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
+++ b/stock/doctype/stock_reconciliation/test_stock_reconciliation.py
@@ -1,40 +1,15 @@
# 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/>.
-
+# For license information, please see license.txt
from __future__ import unicode_literals
-import unittest
-import webnotes
-from webnotes.tests import insert_test_data
+import webnotes, unittest
from webnotes.utils import flt
import json
-
-company = webnotes.conn.get_default("company")
+from accounts.utils import get_fiscal_year
class TestStockReconciliation(unittest.TestCase):
- def setUp(self):
- webnotes.conn.begin()
- self.insert_test_data()
-
- def tearDown(self):
- # print "Message Log:", "\n--\n".join(webnotes.message_log)
- # print "Debug Log:", "\n--\n".join(webnotes.debug_log)
- webnotes.conn.rollback()
-
def test_reco_for_fifo(self):
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
# [[qty, valuation_rate, posting_date,
# posting_time, expected_stock_value, bin_qty, bin_valuation]]
input_data = [
@@ -53,28 +28,32 @@
]
for d in input_data:
+ self.cleanup_data()
self.insert_existing_sle("FIFO")
-
- self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
+ stock_reco = self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
+ # check stock value
res = webnotes.conn.sql("""select stock_value from `tabStock Ledger Entry`
- where item_code = 'Android Jack D' and warehouse = 'Default Warehouse'
+ where item_code = '_Test Item' and warehouse = '_Test Warehouse'
and posting_date = %s and posting_time = %s order by name desc limit 1""",
(d[2], d[3]))
-
self.assertEqual(res and flt(res[0][0]) or 0, d[4])
+ # check bin qty and stock value
bin = webnotes.conn.sql("""select actual_qty, stock_value from `tabBin`
- where item_code = 'Android Jack D' and warehouse = 'Default Warehouse'""")
+ where item_code = '_Test Item' and warehouse = '_Test Warehouse'""")
self.assertEqual(bin and [flt(bin[0][0]), flt(bin[0][1])] or [], [d[5], d[6]])
+ # no gl entries
+ gl_entries = webnotes.conn.sql("""select name from `tabGL Entry`
+ where voucher_type = 'Stock Reconciliation' and voucher_no = %s""",
+ stock_reco.doc.name)
+ self.assertFalse(gl_entries)
- self.tearDown()
- self.setUp()
-
def test_reco_for_moving_average(self):
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
# [[qty, valuation_rate, posting_date,
# posting_time, expected_stock_value, bin_qty, bin_valuation]]
input_data = [
@@ -94,95 +73,194 @@
]
for d in input_data:
+ self.cleanup_data()
self.insert_existing_sle("Moving Average")
+ stock_reco = self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
- self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
-
+ # check stock value in sle
res = webnotes.conn.sql("""select stock_value from `tabStock Ledger Entry`
- where item_code = 'Android Jack D' and warehouse = 'Default Warehouse'
+ where item_code = '_Test Item' and warehouse = '_Test Warehouse'
and posting_date = %s and posting_time = %s order by name desc limit 1""",
(d[2], d[3]))
self.assertEqual(res and flt(res[0][0], 4) or 0, d[4])
+ # bin qty and stock value
bin = webnotes.conn.sql("""select actual_qty, stock_value from `tabBin`
- where item_code = 'Android Jack D' and warehouse = 'Default Warehouse'""")
+ where item_code = '_Test Item' and warehouse = '_Test Warehouse'""")
self.assertEqual(bin and [flt(bin[0][0]), flt(bin[0][1], 4)] or [],
[flt(d[5]), flt(d[6])])
-
- self.tearDown()
- self.setUp()
+
+ # no gl entries
+ gl_entries = webnotes.conn.sql("""select name from `tabGL Entry`
+ where voucher_type = 'Stock Reconciliation' and voucher_no = %s""",
+ stock_reco.doc.name)
+ self.assertFalse(gl_entries)
+ def test_reco_fifo_gl_entries(self):
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 1)
+
+ # [[qty, valuation_rate, posting_date,
+ # posting_time, stock_in_hand_debit]]
+ input_data = [
+ [50, 1000, "2012-12-26", "12:00", 38000],
+ [5, 1000, "2012-12-26", "12:00", -7000],
+ [15, 1000, "2012-12-26", "12:00", 3000],
+ [25, 900, "2012-12-26", "12:00", 10500],
+ [20, 500, "2012-12-26", "12:00", -2000],
+ ["", 1000, "2012-12-26", "12:05", 3000],
+ [20, "", "2012-12-26", "12:05", 4000],
+ [10, 2000, "2012-12-26", "12:10", 8000],
+ [0, "", "2012-12-26", "12:10", -12000],
+ [50, 1000, "2013-01-01", "12:00", 50000],
+ [5, 1000, "2013-01-01", "12:00", 5000],
+ [1, 1000, "2012-12-01", "00:00", 1000],
+
+ ]
+
+ for d in input_data:
+ self.cleanup_data()
+ self.insert_existing_sle("FIFO")
+ stock_reco = self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
+
+ # check gl_entries
+ self.check_gl_entries(stock_reco.doc.name, d[4])
+
+ # cancel
+ stock_reco.cancel()
+ self.check_gl_entries(stock_reco.doc.name, -d[4], True)
+
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
+
+ def test_reco_moving_average_gl_entries(self):
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 1)
+
+ # [[qty, valuation_rate, posting_date,
+ # posting_time, stock_in_hand_debit]]
+ input_data = [
+ [50, 1000, "2012-12-26", "12:00", 36500],
+ [5, 1000, "2012-12-26", "12:00", -8500],
+ [15, 1000, "2012-12-26", "12:00", 1500],
+ [25, 900, "2012-12-26", "12:00", 9000],
+ [20, 500, "2012-12-26", "12:00", -3500],
+ ["", 1000, "2012-12-26", "12:05", 1500],
+ [20, "", "2012-12-26", "12:05", 4500],
+ [10, 2000, "2012-12-26", "12:10", 6500],
+ [0, "", "2012-12-26", "12:10", -13500],
+ [50, 1000, "2013-01-01", "12:00", 50000],
+ [5, 1000, "2013-01-01", "12:00", 5000],
+ [1, 1000, "2012-12-01", "00:00", 1000],
+
+ ]
+
+ for d in input_data:
+ self.cleanup_data()
+ self.insert_existing_sle("Moving Average")
+ stock_reco = self.submit_stock_reconciliation(d[0], d[1], d[2], d[3])
+
+ # check gl_entries
+ self.check_gl_entries(stock_reco.doc.name, d[4])
+
+ # cancel
+ stock_reco.cancel()
+ self.check_gl_entries(stock_reco.doc.name, -d[4], True)
+
+ webnotes.defaults.set_global_default("auto_inventory_accounting", 0)
+
+
+ def cleanup_data(self):
+ webnotes.conn.sql("delete from `tabStock Ledger Entry`")
+ webnotes.conn.sql("delete from tabBin")
+
def submit_stock_reconciliation(self, qty, rate, posting_date, posting_time):
- return webnotes.bean([{
+ stock_reco = webnotes.bean([{
"doctype": "Stock Reconciliation",
- "name": "RECO-001",
- "__islocal": 1,
"posting_date": posting_date,
"posting_time": posting_time,
+ "fiscal_year": get_fiscal_year(posting_date)[0],
+ "company": "_Test Company",
+ "expense_account": "Stock Adjustment - _TC",
"reconciliation_json": json.dumps([
["Item Code", "Warehouse", "Quantity", "Valuation Rate"],
- ["Android Jack D", "Default Warehouse", qty, rate]
+ ["_Test Item", "_Test Warehouse", qty, rate]
]),
- }]).submit()
+ }])
+ stock_reco.insert()
+ stock_reco.submit()
+ return stock_reco
- def insert_test_data(self):
- # create default warehouse
- if not webnotes.conn.exists("Warehouse", "Default Warehouse"):
- webnotes.insert({"doctype": "Warehouse",
- "warehouse_name": "Default Warehouse",
- "warehouse_type": "Stores"})
-
- # create UOM: Nos.
- if not webnotes.conn.exists("UOM", "Nos"):
- webnotes.insert({"doctype": "UOM", "uom_name": "Nos"})
-
- # create item groups and items
- insert_test_data("Item Group",
- sort_fn=lambda ig: (ig[0].get('parent_item_group'), ig[0].get('name')))
- insert_test_data("Item")
+ def check_gl_entries(self, voucher_no, stock_value_diff, cancel=None):
+ stock_in_hand_account = webnotes.conn.get_value("Company", "_Test Company",
+ "stock_in_hand_account")
+ debit_amount = stock_value_diff > 0 and stock_value_diff or 0.0
+ credit_amount = stock_value_diff < 0 and abs(stock_value_diff) or 0.0
+
+ expected_gl_entries = sorted([
+ [stock_in_hand_account, debit_amount, credit_amount],
+ ["Stock Adjustment - _TC", credit_amount, debit_amount]
+ ])
+ if cancel:
+ expected_gl_entries = sorted([
+ [stock_in_hand_account, debit_amount, credit_amount],
+ ["Stock Adjustment - _TC", credit_amount, debit_amount],
+ [stock_in_hand_account, credit_amount, debit_amount],
+ ["Stock Adjustment - _TC", debit_amount, credit_amount]
+ ])
+
+ gl_entries = webnotes.conn.sql("""select account, debit, credit
+ from `tabGL Entry` where voucher_type='Stock Reconciliation' and voucher_no=%s
+ order by account asc, debit asc""", voucher_no, as_dict=1)
+ self.assertTrue(gl_entries)
+
+ for i, gle in enumerate(gl_entries):
+ self.assertEquals(expected_gl_entries[i][0], gle.account)
+ self.assertEquals(expected_gl_entries[i][1], gle.debit)
+ self.assertEquals(expected_gl_entries[i][2], gle.credit)
def insert_existing_sle(self, valuation_method):
- webnotes.conn.set_value("Item", "Android Jack D", "valuation_method", valuation_method)
+ webnotes.conn.set_value("Item", "_Test Item", "valuation_method", valuation_method)
webnotes.conn.set_default("allow_negative_stock", 1)
existing_ledgers = [
{
"doctype": "Stock Ledger Entry", "__islocal": 1,
"voucher_type": "Stock Entry", "voucher_no": "TEST",
- "item_code": "Android Jack D", "warehouse": "Default Warehouse",
+ "item_code": "_Test Item", "warehouse": "_Test Warehouse",
"posting_date": "2012-12-12", "posting_time": "01:00",
- "actual_qty": 20, "incoming_rate": 1000, "company": company
+ "actual_qty": 20, "incoming_rate": 1000, "company": "_Test Company"
},
{
"doctype": "Stock Ledger Entry", "__islocal": 1,
"voucher_type": "Stock Entry", "voucher_no": "TEST",
- "item_code": "Android Jack D", "warehouse": "Default Warehouse",
+ "item_code": "_Test Item", "warehouse": "_Test Warehouse",
"posting_date": "2012-12-15", "posting_time": "02:00",
- "actual_qty": 10, "incoming_rate": 700, "company": company
+ "actual_qty": 10, "incoming_rate": 700, "company": "_Test Company"
},
{
"doctype": "Stock Ledger Entry", "__islocal": 1,
"voucher_type": "Stock Entry", "voucher_no": "TEST",
- "item_code": "Android Jack D", "warehouse": "Default Warehouse",
+ "item_code": "_Test Item", "warehouse": "_Test Warehouse",
"posting_date": "2012-12-25", "posting_time": "03:00",
- "actual_qty": -15, "company": company
+ "actual_qty": -15, "company": "_Test Company"
},
{
"doctype": "Stock Ledger Entry", "__islocal": 1,
"voucher_type": "Stock Entry", "voucher_no": "TEST",
- "item_code": "Android Jack D", "warehouse": "Default Warehouse",
+ "item_code": "_Test Item", "warehouse": "_Test Warehouse",
"posting_date": "2012-12-31", "posting_time": "08:00",
- "actual_qty": -20, "company": company
+ "actual_qty": -20, "company": "_Test Company"
},
{
"doctype": "Stock Ledger Entry", "__islocal": 1,
"voucher_type": "Stock Entry", "voucher_no": "TEST",
- "item_code": "Android Jack D", "warehouse": "Default Warehouse",
+ "item_code": "_Test Item", "warehouse": "_Test Warehouse",
"posting_date": "2013-01-05", "posting_time": "07:00",
- "actual_qty": 15, "incoming_rate": 1200, "company": company
+ "actual_qty": 15, "incoming_rate": 1200, "company": "_Test Company"
},
]
- webnotes.get_obj("Stock Ledger").update_stock(existing_ledgers)
\ No newline at end of file
+ webnotes.get_obj("Stock Ledger").update_stock(existing_ledgers)
+
+
+test_dependencies = ["Item", "Warehouse"]
\ No newline at end of file