def validate_warehouse_account(self):
'''If perpetual inventory is set, and warehouse is linked,
the account balance and stock balance as of now must always match.
'''
from erpnext.accounts.utils import get_balance_on
from erpnext.stock.utils import get_stock_value_on
if not cint(frappe.defaults.get_global_default("auto_accounting_for_stock")):
return
if self.account_type == "Stock":
if self.is_group == 0 and not self.warehouse:
frappe.throw(_("Warehouse is mandatory for non group Accounts of type Stock"))
if self.warehouse:
# company must be same
if frappe.get_value('Warehouse', self.warehouse, 'company') != self.company:
frappe.throw(_("Warehouse company must be same as Account company"))
# balance must be same
stock_balance = get_stock_value_on(self.warehouse)
if self.is_new():
account_balance = 0.0
else:
account_balance = get_balance_on(self.name)
if account_balance != stock_balance:
frappe.throw(_('Account balance ({0}) and stock value ({1}) must be same')\
.format(fmt_money(account_balance, currency=self.account_currency),
fmt_money(stock_balance, currency=self.account_currency)))
elif self.warehouse:
self.warehouse = None
def get_children():
from erpnext.stock.utils import get_stock_value_on
doctype = frappe.local.form_dict.get("doctype")
company = frappe.local.form_dict.get("company")
parent_field = "parent_" + doctype.lower().replace(" ", "_")
parent = frappe.form_dict.get("parent") or ""
if parent == "Warehouses":
parent = ""
warehouses = frappe.db.sql(
"""select name as value,
is_group as expandable
from `tab{doctype}`
where docstatus < 2
and ifnull(`{parent_field}`,'') = %s
and (`company` = %s or company is null or company = '')
order by name""".format(
doctype=frappe.db.escape(doctype), parent_field=frappe.db.escape(parent_field)
),
(parent, company),
as_dict=1,
)
# return warehouses
for wh in warehouses:
wh["balance"] = get_stock_value_on(warehouse=wh.value)
return warehouses
def get_stock_and_account_balance(account=None,
posting_date=None,
company=None):
if not posting_date: posting_date = nowdate()
warehouse_account = get_warehouse_account_map(company)
account_balance = get_balance_on(account,
posting_date,
in_account_currency=False)
related_warehouses = [
wh for wh, wh_details in warehouse_account.items()
if wh_details.account == account and not wh_details.is_group
]
total_stock_value = 0.0
for warehouse in related_warehouses:
value = get_stock_value_on(warehouse, posting_date)
total_stock_value += value
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
return flt(account_balance, precision), flt(total_stock_value,
precision), related_warehouses
def get_children():
from erpnext.stock.utils import get_stock_value_on
doctype = frappe.local.form_dict.get('doctype')
company = frappe.local.form_dict.get('company')
parent_field = 'parent_' + doctype.lower().replace(' ', '_')
parent = frappe.form_dict.get("parent") or ""
if parent == "Warehouses":
parent = ""
warehouses = frappe.db.sql("""select name as value,
is_group as expandable
from `tab{doctype}`
where docstatus < 2
and ifnull(`{parent_field}`,'') = %s
and (`company` = %s or company is null or company = '')
order by name""".format(doctype=frappe.db.escape(doctype),
parent_field=frappe.db.escape(parent_field)),
(parent, company),
as_dict=1)
# return warehouses
for wh in warehouses:
wh["balance"] = get_stock_value_on(warehouse=wh.value)
return warehouses
def get_stock_and_account_difference(account_list=None, posting_date=None):
from erpnext.stock.utils import get_stock_value_on
if not posting_date:
posting_date = nowdate()
difference = {}
account_warehouse = dict(
frappe.db.sql(
"""select name, warehouse from tabAccount
where account_type = 'Warehouse' and (warehouse is not null and warehouse != '')
and name in (%s)"""
% ", ".join(["%s"] * len(account_list)),
account_list,
)
)
for account, warehouse in account_warehouse.items():
account_balance = get_balance_on(account, posting_date, in_account_currency=False)
stock_value = get_stock_value_on(warehouse, posting_date)
if abs(flt(stock_value) - flt(account_balance)) > 0.005:
difference.setdefault(account, flt(stock_value) - flt(account_balance))
return difference
def get_stock_item_details(warehouse, date, item=None, barcode=None):
out = {}
if barcode:
out["item"] = frappe.db.get_value("Item Barcode",
filters={"barcode": barcode},
fieldname=["parent"])
if not out["item"]:
frappe.throw(
_("Invalid Barcode. There is no Item attached to this barcode."
))
else:
out["item"] = item
barcodes = frappe.db.get_values("Item Barcode",
filters={"parent": out["item"]},
fieldname=["barcode"])
out["selling_price"] = get_item_rate(out["item"])
out["last_buying_rate"] = get_last_buying_rate(out["item"])
out["barcodes"] = [x[0] for x in barcodes]
out["qty"] = get_stock_balance(out["item"], warehouse, date)
out["value"] = get_stock_value_on(warehouse, date, out["item"])
out["image"] = frappe.db.get_value("Item",
filters={"name": out["item"]},
fieldname=["image"])
return out
def test_stock_reco_for_batch_item(self):
set_perpetual_inventory()
to_delete_records = []
to_delete_serial_nos = []
# Add new serial nos
item_code = "Stock-Reco-batch-Item-1"
warehouse = "_Test Warehouse for Stock Reco2 - _TC"
sr = create_stock_reconciliation(item_code=item_code,
warehouse=warehouse,
qty=5,
rate=200,
do_not_submit=1)
sr.save(ignore_permissions=True)
sr.submit()
self.assertTrue(sr.items[0].batch_no)
to_delete_records.append(sr.name)
sr1 = create_stock_reconciliation(item_code=item_code,
warehouse=warehouse,
qty=6,
rate=300,
batch_no=sr.items[0].batch_no)
args = {
"item_code": item_code,
"warehouse": warehouse,
"posting_date": nowdate(),
"posting_time": nowtime(),
}
valuation_rate = get_incoming_rate(args)
self.assertEqual(valuation_rate, 300)
to_delete_records.append(sr1.name)
sr2 = create_stock_reconciliation(item_code=item_code,
warehouse=warehouse,
qty=0,
rate=0,
batch_no=sr.items[0].batch_no)
stock_value = get_stock_value_on(warehouse, nowdate(), item_code)
self.assertEqual(stock_value, 0)
to_delete_records.append(sr2.name)
to_delete_records.reverse()
for d in to_delete_records:
stock_doc = frappe.get_doc("Stock Reconciliation", d)
stock_doc.cancel()
frappe.delete_doc("Batch", sr.items[0].batch_no)
for d in to_delete_records:
frappe.delete_doc("Stock Reconciliation", d)
def get_stock_and_account_difference(account_list=None, posting_date=None):
from erpnext.stock.utils import get_stock_value_on
from erpnext.stock import get_warehouse_account_map
if not posting_date: posting_date = nowdate()
difference = {}
warehouse_account = get_warehouse_account_map()
for warehouse, account_data in warehouse_account.items():
if account_data.get('account') in account_list:
account_balance = get_balance_on(account_data.get('account'), posting_date, in_account_currency=False)
stock_value = get_stock_value_on(warehouse, posting_date)
if abs(flt(stock_value) - flt(account_balance)) > 0.005:
difference.setdefault(account_data.get('account'), flt(stock_value) - flt(account_balance))
return difference
def get_stock_and_account_difference(account_list=None, posting_date=None):
from erpnext.stock.utils import get_stock_value_on
if not posting_date: posting_date = nowdate()
difference = {}
account_warehouse = dict(frappe.db.sql("""select name, warehouse from tabAccount
where account_type = 'Warehouse' and (warehouse is not null and warehouse != '')
and name in (%s)""" % ', '.join(['%s']*len(account_list)), account_list))
for account, warehouse in account_warehouse.items():
account_balance = get_balance_on(account, posting_date, in_account_currency=False)
stock_value = get_stock_value_on(warehouse, posting_date)
if abs(flt(stock_value) - flt(account_balance)) > 0.005:
difference.setdefault(account, flt(stock_value) - flt(account_balance))
return difference
def get_children(doctype, parent=None, company=None, is_root=False):
from erpnext.stock.utils import get_stock_value_on
if is_root:
parent = ""
warehouses = frappe.db.sql("""select name as value,
is_group as expandable
from `tabWarehouse`
where docstatus < 2
and ifnull(`parent_warehouse`,'') = %s
and (`company` = %s or company is null or company = '')
order by name""", (parent, company), as_dict=1)
# return warehouses
for wh in warehouses:
wh["balance"] = get_stock_value_on(warehouse=wh.value)
return warehouses
def validate_warehouse_account(self):
'''If perpetual inventory is set, and warehouse is linked,
the account balance and stock balance as of now must always match.
'''
from erpnext.accounts.utils import get_balance_on
from erpnext.stock.utils import get_stock_value_on
if not cint(
frappe.defaults.get_global_default(
"auto_accounting_for_stock")):
return
if self.account_type == "Stock":
if self.is_group == 0 and not self.warehouse:
frappe.throw(
_("Warehouse is mandatory for non group Accounts of type Stock"
))
if self.warehouse:
# company must be same
if frappe.get_value('Warehouse', self.warehouse,
'company') != self.company:
frappe.throw(
_("Warehouse company must be same as Account company"))
# balance must be same
stock_balance = get_stock_value_on(self.warehouse)
if self.is_new():
account_balance = 0.0
else:
account_balance = get_balance_on(self.name)
if account_balance != stock_balance:
frappe.throw(
_('Account balance ({0}) for {1} and stock value ({2}) for warehouse {3} must be same'
).format(
fmt_money(account_balance,
currency=self.account_currency),
self.name,
fmt_money(stock_balance,
currency=self.account_currency),
self.warehouse))
elif self.warehouse:
self.warehouse = None
def get_data(report_filters):
from_date = get_unsync_date(report_filters)
if not from_date:
return []
result = []
voucher_wise_dict = {}
data = frappe.db.sql(
"""
SELECT
name, posting_date, posting_time, voucher_type, voucher_no,
stock_value_difference, stock_value, warehouse, item_code
FROM
`tabStock Ledger Entry`
WHERE
posting_date
= %s and company = %s
and is_cancelled = 0
ORDER BY timestamp(posting_date, posting_time) asc, creation asc
""",
(from_date, report_filters.company),
as_dict=1,
)
for d in data:
voucher_wise_dict.setdefault((d.item_code, d.warehouse), []).append(d)
closing_date = add_days(from_date, -1)
for key, stock_data in iteritems(voucher_wise_dict):
prev_stock_value = get_stock_value_on(posting_date=closing_date,
item_code=key[0],
warehouse=key[1])
for data in stock_data:
expected_stock_value = prev_stock_value + data.stock_value_difference
if abs(data.stock_value - expected_stock_value) > 0.1:
data.difference_value = abs(data.stock_value -
expected_stock_value)
data.expected_stock_value = expected_stock_value
result.append(data)
return result
def get_children(doctype, parent=None, company=None, is_root=False):
from erpnext.stock.utils import get_stock_value_on
if is_root:
parent = ""
fields = ['name as value', 'is_group as expandable']
filters = [['docstatus', '<', '2'],
['ifnull(`parent_warehouse`, "")', '=', parent],
['company', 'in', (company, None, '')]]
warehouses = frappe.get_list(doctype,
fields=fields,
filters=filters,
order_by='name')
# return warehouses
for wh in warehouses:
wh["balance"] = get_stock_value_on(warehouse=wh.value,
posting_date=nowdate())
if company:
wh["company_currency"] = frappe.db.get_value(
'Company', company, 'default_currency')
return warehouses
