Ejemplo n.º 1
0
	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
Ejemplo n.º 2
0
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
Ejemplo n.º 3
0
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
Ejemplo n.º 4
0
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
Ejemplo n.º 5
0
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
Ejemplo n.º 6
0
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
Ejemplo n.º 7
0
    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)
Ejemplo n.º 8
0
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
Ejemplo n.º 9
0
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
Ejemplo n.º 10
0
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
Ejemplo n.º 11
0
    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
Ejemplo n.º 13
0
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