def get_last_purchase_details(item_code, doc_name=None, conversion_rate=1.0):
"""returns last purchase details in stock uom"""
# get last purchase order item details
last_purchase_order = frappe.db.sql("""\
select po.name, po.transaction_date, po.conversion_rate,
po_item.conversion_factor, po_item.base_price_list_rate,
po_item.discount_percentage, po_item.base_rate
from `tabPurchase Order` po, `tabPurchase Order Item` po_item
where po.docstatus = 1 and po_item.item_code = %s and po.name != %s and
po.name = po_item.parent
order by po.transaction_date desc, po.name desc
limit 1""", (item_code, cstr(doc_name)), as_dict=1)
# get last purchase receipt item details
last_purchase_receipt = frappe.db.sql("""\
select pr.name, pr.posting_date, pr.posting_time, pr.conversion_rate,
pr_item.conversion_factor, pr_item.base_price_list_rate, pr_item.discount_percentage,
pr_item.base_rate
from `tabPurchase Receipt` pr, `tabPurchase Receipt Item` pr_item
where pr.docstatus = 1 and pr_item.item_code = %s and pr.name != %s and
pr.name = pr_item.parent
order by pr.posting_date desc, pr.posting_time desc, pr.name desc
limit 1""", (item_code, cstr(doc_name)), as_dict=1)
purchase_order_date = getdate(last_purchase_order and last_purchase_order[0].transaction_date \
or "1900-01-01")
purchase_receipt_date = getdate(last_purchase_receipt and \
last_purchase_receipt[0].posting_date or "1900-01-01")
if (purchase_order_date > purchase_receipt_date) or \
(last_purchase_order and not last_purchase_receipt):
# use purchase order
last_purchase = last_purchase_order[0]
purchase_date = purchase_order_date
elif (purchase_receipt_date > purchase_order_date) or \
(last_purchase_receipt and not last_purchase_order):
# use purchase receipt
last_purchase = last_purchase_receipt[0]
purchase_date = purchase_receipt_date
else:
return frappe._dict()
conversion_factor = flt(last_purchase.conversion_factor)
out = frappe._dict({
"base_price_list_rate": flt(last_purchase.base_price_list_rate) / conversion_factor,
"base_rate": flt(last_purchase.base_rate) / conversion_factor,
"discount_percentage": flt(last_purchase.discount_percentage),
"purchase_date": purchase_date
})
conversion_rate = flt(conversion_rate) or 1.0
out.update({
"price_list_rate": out.base_price_list_rate / conversion_rate,
"rate": out.base_rate / conversion_rate,
"base_rate": out.base_rate
})
return out
def get_fifo_queue(filters):
item_details = {}
for d in get_stock_ledger_entries(filters):
item_details.setdefault(d.name, {"details": d, "fifo_queue": []})
fifo_queue = item_details[d.name]["fifo_queue"]
if d.voucher_type == "Stock Reconciliation":
d.actual_qty = flt(d.qty_after_transaction) - flt(item_details[d.name].get("qty_after_transaction", 0))
if d.actual_qty > 0:
fifo_queue.append([d.actual_qty, d.posting_date])
else:
qty_to_pop = abs(d.actual_qty)
while qty_to_pop:
batch = fifo_queue[0] if fifo_queue else [0, None]
if 0 < batch[0] <= qty_to_pop:
# if batch qty > 0
# not enough or exactly same qty in current batch, clear batch
qty_to_pop -= batch[0]
fifo_queue.pop(0)
else:
# all from current batch
batch[0] -= qty_to_pop
qty_to_pop = 0
item_details[d.name]["qty_after_transaction"] = d.qty_after_transaction
return item_details
def get_pending_raw_materials(self):
"""
issue (item quantity) that is pending to issue or desire to transfer,
whichever is less
"""
item_dict = self.get_bom_raw_materials(1)
issued_item_qty = self.get_issued_qty()
max_qty = flt(self.pro_doc.qty)
for item in item_dict:
pending_to_issue = (max_qty * item_dict[item]["qty"]) - issued_item_qty.get(item, 0)
desire_to_transfer = flt(self.fg_completed_qty) * item_dict[item]["qty"]
if desire_to_transfer <= pending_to_issue:
item_dict[item]["qty"] = desire_to_transfer
elif pending_to_issue > 0:
item_dict[item]["qty"] = pending_to_issue
else:
item_dict[item]["qty"] = 0
# delete items with 0 qty
for item in item_dict.keys():
if not item_dict[item]["qty"]:
del item_dict[item]
# show some message
if not len(item_dict):
frappe.msgprint(_("""All items have already been transferred for this Production Order."""))
return item_dict
def get_exchange_rate(from_currency, to_currency):
exchange = "%s-%s" % (from_currency, to_currency)
value = flt(frappe.db.get_value("Currency Exchange", exchange, "exchange_rate"))
if not value:
try:
cache = frappe.cache()
key = "currency_exchange_rate:{0}:{1}".format(from_currency, to_currency)
value = cache.get(key)
if not value:
import requests
response = requests.get("http://api.fixer.io/latest", params={
"base": from_currency,
"symbols": to_currency
})
# expire in 6 hours
response.raise_for_status()
value = response.json()["rates"][to_currency]
cache.setex(key, value, 6 * 60 * 60)
return flt(value)
except:
frappe.msgprint(_("Unable to find exchange rate for {0} to {1}").format(from_currency, to_currency))
return 0.0
else:
return value
def add_to_stock_entry_detail(self, item_dict, bom_no=None):
expense_account, cost_center = frappe.db.get_values("Company", self.company, \
["default_expense_account", "cost_center"])[0]
for d in item_dict:
se_child = self.append('items')
se_child.s_warehouse = item_dict[d].get("from_warehouse")
se_child.t_warehouse = item_dict[d].get("to_warehouse")
se_child.item_code = cstr(d)
se_child.item_name = item_dict[d]["item_name"]
se_child.description = item_dict[d]["description"]
se_child.uom = item_dict[d]["stock_uom"]
se_child.stock_uom = item_dict[d]["stock_uom"]
se_child.qty = flt(item_dict[d]["qty"])
se_child.expense_account = item_dict[d]["expense_account"] or expense_account
se_child.cost_center = item_dict[d]["cost_center"] or cost_center
if se_child.s_warehouse==None:
se_child.s_warehouse = self.from_warehouse
if se_child.t_warehouse==None:
se_child.t_warehouse = self.to_warehouse
# in stock uom
se_child.transfer_qty = flt(item_dict[d]["qty"])
se_child.conversion_factor = 1.00
# to be assigned for finished item
se_child.bom_no = bom_no
def update_against_document_in_jv(self):
"""
Links invoice and advance voucher:
1. cancel advance voucher
2. split into multiple rows if partially adjusted, assign against voucher
3. submit advance voucher
"""
lst = []
for d in self.get('advances'):
if flt(d.allocated_amount) > 0:
args = {
'voucher_no' : d.journal_entry,
'voucher_detail_no' : d.jv_detail_no,
'against_voucher_type' : 'Purchase Invoice',
'against_voucher' : self.name,
'account' : self.credit_to,
'party_type': 'Supplier',
'party': self.supplier,
'is_advance' : 'Yes',
'dr_or_cr' : 'debit',
'unadjusted_amt' : flt(d.advance_amount),
'allocated_amt' : flt(d.allocated_amount)
}
lst.append(args)
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
def update_stock_ledger(self):
sl_entries = []
for d in self.get('items'):
if cstr(d.s_warehouse) and self.docstatus == 1:
sl_entries.append(self.get_sl_entries(d, {
"warehouse": cstr(d.s_warehouse),
"actual_qty": -flt(d.transfer_qty),
"incoming_rate": 0
}))
if cstr(d.t_warehouse):
sl_entries.append(self.get_sl_entries(d, {
"warehouse": cstr(d.t_warehouse),
"actual_qty": flt(d.transfer_qty),
"incoming_rate": flt(d.incoming_rate)
}))
# On cancellation, make stock ledger entry for
# target warehouse first, to update serial no values properly
if cstr(d.s_warehouse) and self.docstatus == 2:
sl_entries.append(self.get_sl_entries(d, {
"warehouse": cstr(d.s_warehouse),
"actual_qty": -flt(d.transfer_qty),
"incoming_rate": 0
}))
self.make_sl_entries(sl_entries, self.amended_from and 'Yes' or 'No')
def get_leave_details(self, joining_date=None, relieving_date=None, lwp=None):
if not self.fiscal_year:
self.fiscal_year = frappe.db.get_default("fiscal_year")
if not self.month:
self.month = "%02d" % getdate(nowdate()).month
if not joining_date:
joining_date, relieving_date = frappe.db.get_value("Employee", self.employee,
["date_of_joining", "relieving_date"])
m = get_month_details(self.fiscal_year, self.month)
holidays = self.get_holidays_for_employee(m['month_start_date'], m['month_end_date'])
working_days = m["month_days"]
if not cint(frappe.db.get_value("HR Settings", None, "include_holidays_in_total_working_days")):
working_days -= len(holidays)
if working_days < 0:
frappe.throw(_("There are more holidays than working days this month."))
if not lwp:
lwp = self.calculate_lwp(holidays, m)
self.total_days_in_month = working_days
self.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(m, joining_date, relieving_date)) - flt(lwp)
self.payment_days = payment_days > 0 and payment_days or 0
def get_stock_rbnb_difference(posting_date, company):
stock_items = frappe.db.sql_list("""select distinct item_code
from `tabStock Ledger Entry` where company=%s""", company)
pr_valuation_amount = frappe.db.sql("""
select sum(pr_item.valuation_rate * pr_item.qty * pr_item.conversion_factor)
from `tabPurchase Receipt Item` pr_item, `tabPurchase Receipt` pr
where pr.name = pr_item.parent and pr.docstatus=1 and pr.company=%s
and pr.posting_date <= %s and pr_item.item_code in (%s)""" %
('%s', '%s', ', '.join(['%s']*len(stock_items))), tuple([company, posting_date] + stock_items))[0][0]
pi_valuation_amount = frappe.db.sql("""
select sum(pi_item.valuation_rate * pi_item.qty * pi_item.conversion_factor)
from `tabPurchase Invoice Item` pi_item, `tabPurchase Invoice` pi
where pi.name = pi_item.parent and pi.docstatus=1 and pi.company=%s
and pi.posting_date <= %s and pi_item.item_code in (%s)""" %
('%s', '%s', ', '.join(['%s']*len(stock_items))), tuple([company, posting_date] + stock_items))[0][0]
# Balance should be
stock_rbnb = flt(pr_valuation_amount, 2) - flt(pi_valuation_amount, 2)
# Balance as per system
stock_rbnb_account = "Stock Received But Not Billed - " + frappe.db.get_value("Company", company, "abbr")
sys_bal = get_balance_on(stock_rbnb_account, posting_date, in_account_currency=False)
# Amount should be credited
return flt(stock_rbnb) + flt(sys_bal)
def get_cost_center_account_month_map(filters):
import datetime
cost_center_target_details = get_cost_center_target_details(filters)
tdd = get_target_distribution_details(filters)
cam_map = {}
for ccd in cost_center_target_details:
actual_details = get_actual_details(ccd.cost_center, filters.fiscal_year)
for month_id in range(1, 13):
month = datetime.date(2013, month_id, 1).strftime('%B')
cam_map.setdefault(ccd.cost_center, {}).setdefault(ccd.account, {})\
.setdefault(month, frappe._dict({
"target": 0.0, "actual": 0.0
}))
tav_dict = cam_map[ccd.cost_center][ccd.account][month]
month_percentage = tdd.get(ccd.monthly_distribution, {}).get(month, 0) \
if ccd.monthly_distribution else 100.0/12
tav_dict.target = flt(ccd.budget_amount) * month_percentage / 100
for ad in actual_details.get(ccd.account, []):
if ad.month_name == month:
tav_dict.actual += flt(ad.debit) - flt(ad.credit)
return cam_map
def get_leave_details(self, joining_date=None, relieving_date=None, lwp=None):
if not self.fiscal_year:
# if default fiscal year is not set, get from nowdate
self.fiscal_year = get_fiscal_year(nowdate())[0]
if not self.month:
self.month = "%02d" % getdate(nowdate()).month
self.set_month_dates()
if not joining_date:
joining_date, relieving_date = frappe.db.get_value("Employee", self.employee,
["date_of_joining", "relieving_date"])
holidays = self.get_holidays_for_employee(self.start_date, self.end_date)
working_days = date_diff(self.end_date, self.start_date) + 1
if not cint(frappe.db.get_value("HR Settings", None, "include_holidays_in_total_working_days")):
working_days -= len(holidays)
if working_days < 0:
frappe.throw(_("There are more holidays than working days this month."))
if not lwp:
lwp = self.calculate_lwp(holidays, working_days)
self.total_days_in_month = working_days
self.leave_without_pay = lwp
payment_days = flt(self.get_payment_days(joining_date, relieving_date)) - flt(lwp)
self.payment_days = payment_days > 0 and payment_days or 0
def get_outstanding_invoices(self):
self.set('accounts', [])
total = 0
for d in self.get_values():
total += flt(d.outstanding_amount, self.precision("credit", "accounts"))
jd1 = self.append('accounts', {})
jd1.account = d.account
jd1.party = d.party
if self.write_off_based_on == 'Accounts Receivable':
jd1.party_type = "Customer"
jd1.credit = flt(d.outstanding_amount, self.precision("credit", "accounts"))
jd1.reference_type = "Sales Invoice"
jd1.reference_name = cstr(d.name)
elif self.write_off_based_on == 'Accounts Payable':
jd1.party_type = "Supplier"
jd1.debit = flt(d.outstanding_amount, self.precision("debit", "accounts"))
jd1.reference_type = "Purchase Invoice"
jd1.reference_name = cstr(d.name)
jd2 = self.append('accounts', {})
if self.write_off_based_on == 'Accounts Receivable':
jd2.debit = total
elif self.write_off_based_on == 'Accounts Payable':
jd2.credit = total
self.validate_total_debit_and_credit()
def execute(filters=None): columns = get_columns(filters) consumed_details = get_consumed_details(filters) supplier_details = get_suppliers_details(filters) material_transfer_vouchers = get_material_transfer_vouchers() data = [] for item_code, suppliers in supplier_details.items(): consumed_qty = consumed_amount = delivered_qty = delivered_amount = 0.0 total_qty = total_amount = 0.0 if consumed_details.get(item_code): for cd in consumed_details.get(item_code): if (cd.voucher_no not in material_transfer_vouchers): if cd.voucher_type=="Delivery Note": delivered_qty += abs(flt(cd.actual_qty)) delivered_amount += abs(flt(cd.stock_value_difference)) elif cd.voucher_type!="Delivery Note": consumed_qty += abs(flt(cd.actual_qty)) consumed_amount += abs(flt(cd.stock_value_difference)) if consumed_qty or consumed_amount or delivered_qty or delivered_amount: total_qty += delivered_qty + consumed_qty total_amount += delivered_amount + consumed_amount row = [cd.item_code, cd.item_name, cd.description, cd.stock_uom, \ consumed_qty, consumed_amount, delivered_qty, delivered_amount, \ total_qty, total_amount, list(set(suppliers))] data.append(row) return columns, data
def create_remarks(self):
r = []
if self.cheque_no:
if self.cheque_date:
r.append(_('Reference #{0} dated {1}').format(self.cheque_no, formatdate(self.cheque_date)))
else:
msgprint(_("Please enter Reference date"), raise_exception=frappe.MandatoryError)
for d in self.get('accounts'):
if d.reference_type=="Sales Invoice" and d.credit:
r.append(_("{0} against Sales Invoice {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if d.reference_type=="Sales Order" and d.credit:
r.append(_("{0} against Sales Order {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if d.reference_type == "Purchase Invoice" and d.debit:
bill_no = frappe.db.sql("""select bill_no, bill_date
from `tabPurchase Invoice` where name=%s""", d.reference_name)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append(_('{0} against Bill {1} dated {2}').format(fmt_money(flt(d.debit), currency=self.company_currency), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d'))))
if d.reference_type == "Purchase Order" and d.debit:
r.append(_("{0} against Purchase Order {1}").format(fmt_money(flt(d.credit), currency = self.company_currency), \
d.reference_name))
if self.user_remark:
r.append(_("Note: {0}").format(self.user_remark))
if r:
self.remark = ("\n").join(r) #User Remarks is not mandatory
def make_gl_entries(self, cancel=0, adv_adj=0):
from erpnext.accounts.general_ledger import make_gl_entries
gl_map = []
for d in self.get("accounts"):
if d.debit or d.credit:
gl_map.append(
self.get_gl_dict({
"account": d.account,
"party_type": d.party_type,
"party": d.party,
"against": d.against_account,
"debit": flt(d.debit, d.precision("debit")),
"credit": flt(d.credit, d.precision("credit")),
"account_currency": d.account_currency,
"debit_in_account_currency": flt(d.debit_in_account_currency, d.precision("debit_in_account_currency")),
"credit_in_account_currency": flt(d.credit_in_account_currency, d.precision("credit_in_account_currency")),
"against_voucher_type": d.reference_type,
"against_voucher": d.reference_name,
"remarks": self.remark,
"cost_center": d.cost_center,
"project": d.project
})
)
if gl_map:
make_gl_entries(gl_map, cancel=cancel, adv_adj=adv_adj)
def validate_against_jv(self):
for d in self.get('accounts'):
if d.reference_type=="Journal Entry":
account_root_type = frappe.db.get_value("Account", d.account, "root_type")
if account_root_type == "Asset" and flt(d.debit) > 0:
frappe.throw(_("For {0}, only credit accounts can be linked against another debit entry")
.format(d.account))
elif account_root_type == "Liability" and flt(d.credit) > 0:
frappe.throw(_("For {0}, only debit accounts can be linked against another credit entry")
.format(d.account))
if d.reference_name == self.name:
frappe.throw(_("You can not enter current voucher in 'Against Journal Entry' column"))
against_entries = frappe.db.sql("""select * from `tabJournal Entry Account`
where account = %s and docstatus = 1 and parent = %s
and (reference_type is null or reference_type in ("", "Sales Order", "Purchase Order"))
""", (d.account, d.reference_name), as_dict=True)
if not against_entries:
frappe.throw(_("Journal Entry {0} does not have account {1} or already matched against other voucher")
.format(d.reference_name, d.account))
else:
dr_or_cr = "debit" if d.credit > 0 else "credit"
valid = False
for jvd in against_entries:
if flt(jvd[dr_or_cr]) > 0:
valid = True
if not valid:
frappe.throw(_("Against Journal Entry {0} does not have any unmatched {1} entry")
.format(d.reference_name, dr_or_cr))
def validate_orders(self):
"""Validate totals, closed and docstatus for orders"""
for reference_name, total in self.reference_totals.iteritems():
reference_type = self.reference_types[reference_name]
account = self.reference_accounts[reference_name]
if reference_type in ("Sales Order", "Purchase Order"):
order = frappe.get_doc(reference_type, reference_name)
if order.docstatus != 1:
frappe.throw(_("{0} {1} is not submitted").format(reference_type, reference_name))
if flt(order.per_billed) >= 100:
frappe.throw(_("{0} {1} is fully billed").format(reference_type, reference_name))
if cstr(order.status) == "Closed":
frappe.throw(_("{0} {1} is closed").format(reference_type, reference_name))
account_currency = get_account_currency(account)
if account_currency == self.company_currency:
voucher_total = order.base_grand_total
formatted_voucher_total = fmt_money(voucher_total, order.precision("base_grand_total"),
currency=account_currency)
else:
voucher_total = order.grand_total
formatted_voucher_total = fmt_money(voucher_total, order.precision("grand_total"),
currency=account_currency)
if flt(voucher_total) < (flt(order.advance_paid) + total):
frappe.throw(_("Advance paid against {0} {1} cannot be greater \
than Grand Total {2}").format(reference_type, reference_name, formatted_voucher_total))
def validate_multiple_billing(self, ref_dt, item_ref_dn, based_on, parentfield):
from erpnext.controllers.status_updater import get_tolerance_for
item_tolerance = {}
global_tolerance = None
for item in self.get("items"):
if item.get(item_ref_dn):
ref_amt = flt(frappe.db.get_value(ref_dt + " Item",
item.get(item_ref_dn), based_on), self.precision(based_on, item))
if not ref_amt:
frappe.msgprint(_("Warning: System will not check overbilling since amount for Item {0} in {1} is zero").format(item.item_code, ref_dt))
else:
already_billed = frappe.db.sql("""select sum(%s) from `tab%s`
where %s=%s and docstatus=1 and parent != %s""" %
(based_on, self.doctype + " Item", item_ref_dn, '%s', '%s'),
(item.get(item_ref_dn), self.name))[0][0]
total_billed_amt = flt(flt(already_billed) + flt(item.get(based_on)),
self.precision(based_on, item))
tolerance, item_tolerance, global_tolerance = get_tolerance_for(item.item_code,
item_tolerance, global_tolerance)
max_allowed_amt = flt(ref_amt * (100 + tolerance) / 100)
if total_billed_amt - max_allowed_amt > 0.01:
frappe.throw(_("Cannot overbill for Item {0} in row {1} more than {2}. To allow overbilling, please set in Stock Settings").format(item.item_code, item.idx, max_allowed_amt))
def set_total_advance_paid(self):
if self.doctype == "Sales Order":
dr_or_cr = "credit"
against_field = "against_sales_order"
else:
dr_or_cr = "debit"
against_field = "against_purchase_order"
advance_paid = frappe.db.sql("""
select
sum(ifnull({dr_or_cr}, 0))
from
`tabJournal Entry Account`
where
{against_field} = %s and docstatus = 1 and is_advance = "Yes" """.format(dr_or_cr=dr_or_cr, \
against_field=against_field), self.name)
if advance_paid:
advance_paid = flt(advance_paid[0][0], self.precision("advance_paid"))
if flt(self.base_grand_total) >= advance_paid:
frappe.db.set_value(self.doctype, self.name, "advance_paid", advance_paid)
else:
frappe.throw(_("Total advance ({0}) against Order {1} cannot be greater \
than the Grand Total ({2})")
.format(advance_paid, self.name, self.base_grand_total))
def get_moving_average_values(self, sle): actual_qty = flt(sle.actual_qty) new_stock_qty = flt(self.qty_after_transaction) + actual_qty if new_stock_qty >= 0: if actual_qty > 0: if flt(self.qty_after_transaction) <= 0: self.valuation_rate = sle.incoming_rate else: new_stock_value = (self.qty_after_transaction * self.valuation_rate) + \ (actual_qty * sle.incoming_rate) self.valuation_rate = new_stock_value / new_stock_qty elif sle.outgoing_rate: if new_stock_qty: new_stock_value = (self.qty_after_transaction * self.valuation_rate) + \ (actual_qty * sle.outgoing_rate) self.valuation_rate = new_stock_value / new_stock_qty else: self.valuation_rate = self.outgoing_rate else: if flt(self.qty_after_transaction) >= 0 and sle.outgoing_rate: self.valuation_rate = sle.outgoing_rate if not self.valuation_rate and actual_qty > 0: self.valuation_rate = sle.incoming_rate
def get_advances(self, account_head, party_type, party, child_doctype, parentfield, dr_or_cr, against_order_field):
so_list = list(set([d.get(against_order_field) for d in self.get("items") if d.get(against_order_field)]))
cond = ""
if so_list:
cond = "or (ifnull(t2.%s, '') in (%s))" % ("against_" + against_order_field, ', '.join(['%s']*len(so_list)))
res = frappe.db.sql("""
select
t1.name as jv_no, t1.remark, t2.{0} as amount, t2.name as jv_detail_no, `against_{1}` as against_order
from
`tabJournal Entry` t1, `tabJournal Entry Account` t2
where
t1.name = t2.parent and t2.account = %s
and t2.party_type=%s and t2.party=%s
and t2.is_advance = 'Yes' and t1.docstatus = 1
and ((
ifnull(t2.against_voucher, '') = ''
and ifnull(t2.against_invoice, '') = ''
and ifnull(t2.against_jv, '') = ''
and ifnull(t2.against_sales_order, '') = ''
and ifnull(t2.against_purchase_order, '') = ''
) {2})
order by t1.posting_date""".format(dr_or_cr, against_order_field, cond),
[account_head, party_type, party] + so_list, as_dict=1)
self.set(parentfield, [])
for d in res:
self.append(parentfield, {
"doctype": child_doctype,
"journal_entry": d.jv_no,
"jv_detail_no": d.jv_detail_no,
"remarks": d.remark,
"advance_amount": flt(d.amount),
"allocated_amount": flt(d.amount) if d.against_order else 0
})
def apply_shipping_rule(self):
if self.shipping_rule:
shipping_rule = frappe.get_doc("Shipping Rule", self.shipping_rule)
value = self.base_net_total
# TODO
# shipping rule calculation based on item's net weight
shipping_amount = 0.0
for condition in shipping_rule.get("conditions"):
if not condition.to_value or (flt(condition.from_value) <= value <= flt(condition.to_value)):
shipping_amount = condition.shipping_amount
break
shipping_charge = {
"doctype": "Sales Taxes and Charges",
"charge_type": "Actual",
"account_head": shipping_rule.account,
"cost_center": shipping_rule.cost_center
}
existing_shipping_charge = self.get("taxes", filters=shipping_charge)
if existing_shipping_charge:
# take the last record found
existing_shipping_charge[-1].tax_amount = shipping_amount
else:
shipping_charge["tax_amount"] = shipping_amount
shipping_charge["description"] = shipping_rule.label
self.append("taxes", shipping_charge)
self.calculate_taxes_and_totals()
def get_distinct_items_and_boms(self):
""" Club similar BOM and item for processing
bom_dict {
bom_no: ['sales_order', 'qty']
}
"""
item_dict, bom_dict = {}, {}
for d in self.get("items"):
if d.bom_no:
bom_dict.setdefault(d.bom_no, []).append([d.sales_order, flt(d.planned_qty)])
if frappe.db.get_value("Item", d.item_code, "is_pro_applicable"):
item_dict[(d.item_code, d.sales_order, d.warehouse)] = {
"production_item" : d.item_code,
"sales_order" : d.sales_order,
"qty" : flt(item_dict.get((d.item_code, d.sales_order, d.warehouse),
{}).get("qty")) + flt(d.planned_qty),
"bom_no" : d.bom_no,
"description" : d.description,
"stock_uom" : d.stock_uom,
"company" : self.company,
"wip_warehouse" : "",
"fg_warehouse" : d.warehouse,
"status" : "Draft",
}
return bom_dict, item_dict
def set_transfer_qty(self):
for item in self.get("items"):
if not flt(item.qty):
frappe.throw(_("Row {0}: Qty is mandatory").format(item.idx))
if not flt(item.conversion_factor):
frappe.throw(_("Row {0}: UOM Conversion Factor is mandatory").format(item.idx))
item.transfer_qty = flt(item.qty * item.conversion_factor, self.precision("transfer_qty", item))
def make_gle_for_change_amount(self, gl_entries):
if cint(self.is_pos) and self.change_amount:
if self.account_for_change_amount:
gl_entries.append(
self.get_gl_dict({
"account": self.debit_to,
"party_type": "Customer",
"party": self.customer,
"against": self.account_for_change_amount,
"debit": flt(self.base_change_amount),
"debit_in_account_currency": flt(self.base_change_amount) \
if self.party_account_currency==self.company_currency else flt(self.change_amount),
"against_voucher": self.return_against if cint(self.is_return) else self.name,
"against_voucher_type": self.doctype
}, self.party_account_currency)
)
gl_entries.append(
self.get_gl_dict({
"account": self.account_for_change_amount,
"against": self.customer,
"credit": self.base_change_amount
})
)
else:
frappe.throw(_("Select change amount account"), title="Mandatory Field")
def validate_invoice(self):
if not self.get("invoices"):
frappe.throw(_("No records found in the Invoice table"))
if not self.get("payments"):
frappe.throw(_("No records found in the Payment table"))
unreconciled_invoices = frappe._dict()
for d in self.get("invoices"):
unreconciled_invoices.setdefault(d.invoice_type, {}).setdefault(d.invoice_number, d.outstanding_amount)
invoices_to_reconcile = []
for p in self.get("payments"):
if p.invoice_type and p.invoice_number and p.allocated_amount:
invoices_to_reconcile.append(p.invoice_number)
if p.invoice_number not in unreconciled_invoices.get(p.invoice_type, {}):
frappe.throw(_("{0}: {1} not found in Invoice Details table")
.format(p.invoice_type, p.invoice_number))
if flt(p.allocated_amount) > flt(p.amount):
frappe.throw(_("Row {0}: Allocated amount {1} must be less than or equals to JV amount {2}")
.format(p.idx, p.allocated_amount, p.amount))
invoice_outstanding = unreconciled_invoices.get(p.invoice_type, {}).get(p.invoice_number)
if abs(flt(p.allocated_amount) - invoice_outstanding) > 0.009:
frappe.throw(_("Row {0}: Allocated amount {1} must be less than or equals to invoice outstanding amount {2}")
.format(p.idx, p.allocated_amount, unreconciled_invoices.get(p.invoice_type, {}).get(p.invoice_number)))
if not invoices_to_reconcile:
frappe.throw(_("Please select Allocated Amount, Invoice Type and Invoice Number in atleast one row"))
def set_basic_rate_for_finished_goods(self, raw_material_cost):
if self.purpose in ["Manufacture", "Repack"]:
number_of_fg_items = len([t.t_warehouse for t in self.get("items") if t.t_warehouse])
for d in self.get("items"):
if d.bom_no or (d.t_warehouse and number_of_fg_items == 1):
d.basic_rate = flt(raw_material_cost / flt(d.transfer_qty), d.precision("basic_rate"))
d.basic_amount = flt(raw_material_cost, d.precision("basic_amount"))
def validate_selling_price(self):
def throw_message(item_name, rate, ref_rate_field):
frappe.throw(_("""Selling rate for item {0} is lower than its {1}. Selling rate should be atleast {2}""")
.format(item_name, ref_rate_field, rate))
if not frappe.db.get_single_value("Selling Settings", "validate_selling_price"):
return
for it in self.get("items"):
if not it.item_code:
continue
last_purchase_rate, is_stock_item = frappe.db.get_value("Item", it.item_code, ["last_purchase_rate", "is_stock_item"])
last_purchase_rate_in_sales_uom = last_purchase_rate / (it.conversion_factor or 1)
if flt(it.base_rate) < flt(last_purchase_rate_in_sales_uom):
throw_message(it.item_name, last_purchase_rate_in_sales_uom, "last purchase rate")
last_valuation_rate = frappe.db.sql("""
SELECT valuation_rate FROM `tabStock Ledger Entry` WHERE item_code = %s
AND warehouse = %s AND valuation_rate > 0
ORDER BY posting_date DESC, posting_time DESC, name DESC LIMIT 1
""", (it.item_code, it.warehouse))
if last_valuation_rate:
last_valuation_rate_in_sales_uom = last_valuation_rate[0][0] / (it.conversion_factor or 1)
if is_stock_item and flt(it.base_rate) < flt(last_valuation_rate_in_sales_uom):
throw_message(it.name, last_valuation_rate_in_sales_uom, "valuation rate")
def reconcile(self, args):
self.get_invoice_entries()
self.validate_invoice()
dr_or_cr = "credit" if self.party_type == "Customer" else "debit"
lst = []
for e in self.get('payments'):
if e.invoice_type and e.invoice_number and e.allocated_amount:
lst.append({
'voucher_no' : e.journal_entry,
'voucher_detail_no' : e.voucher_detail_number,
'against_voucher_type' : e.invoice_type,
'against_voucher' : e.invoice_number,
'account' : self.receivable_payable_account,
'party_type': self.party_type,
'party': self.party,
'is_advance' : e.is_advance,
'dr_or_cr' : dr_or_cr,
'unadjusted_amt' : flt(e.amount),
'allocated_amt' : flt(e.allocated_amount)
})
if lst:
from erpnext.accounts.utils import reconcile_against_document
reconcile_against_document(lst)
msgprint(_("Successfully Reconciled"))
self.get_unreconciled_entries()
def get_serialized_values(self, sle):
incoming_rate = flt(sle.incoming_rate)
actual_qty = flt(sle.actual_qty)
serial_no = cstr(sle.serial_no).split("\n")
if incoming_rate < 0:
# wrong incoming rate
incoming_rate = self.valuation_rate
stock_value_change = 0
if incoming_rate:
stock_value_change = actual_qty * incoming_rate
elif actual_qty < 0:
# In case of delivery/stock issue, get average purchase rate
# of serial nos of current entry
stock_value_change = -1 * flt(frappe.db.sql("""select sum(purchase_rate)
from `tabSerial No` where name in (%s)""" % (", ".join(["%s"]*len(serial_no))),
tuple(serial_no))[0][0])
new_stock_qty = self.qty_after_transaction + actual_qty
if new_stock_qty > 0:
new_stock_value = (self.qty_after_transaction * self.valuation_rate) + stock_value_change
if new_stock_value > 0:
# calculate new valuation rate only if stock value is positive
# else it remains the same as that of previous entry
self.valuation_rate = new_stock_value / new_stock_qty
def update_item(source, target, source_parent): target.base_amount = (flt(source.qty) - flt(source.delivered_qty)) * flt(source.base_rate) target.amount = (flt(source.qty) - flt(source.delivered_qty)) * flt(source.rate) target.qty = flt(source.qty) - flt(source.delivered_qty)
def make_accrual_jv_entry(self):
self.check_permission('write')
earnings = self.get_salary_component_total(
component_type="earnings") or {}
deductions = self.get_salary_component_total(
component_type="deductions") or {}
default_payroll_payable_account = self.get_default_payroll_payable_account(
)
loan_details = self.get_loan_details()
jv_name = ""
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounts = []
payable_amount = 0
# Earnings
for item in earnings:
payable_amount += flt(flt(item["amount"]), precision)
accounts.append({
"account":
item["default_account"],
"party_type":
"Employee",
"party":
item["employee"],
"debit_in_account_currency":
flt(flt(item["amount"]), precision),
"cost_center":
get_employee_cost_center(item["employee"])
or self.cost_center,
"project":
self.project
})
# Deductions
for item in deductions:
payable_amount -= flt(flt(item["amount"]), precision)
accounts.append({
"account":
item["default_account"],
"party_type":
"Employee",
"party":
item["employee"],
"credit_in_account_currency":
flt(flt(item["amount"]), precision),
"cost_center":
get_employee_cost_center(item["employee"])
or self.cost_center,
"project":
self.project
})
# Loan
for data in loan_details:
accounts.append({
"account": data.loan_account,
"credit_in_account_currency": data.principal_amount,
"party_type": "Employee",
"party": data.employee
})
if data.interest_amount and not data.interest_income_account:
frappe.throw(
_("Select interest income account in loan {0}").format(
data.loan))
if data.interest_income_account and data.interest_amount:
accounts.append({
"account": data.interest_income_account,
"credit_in_account_currency": data.interest_amount,
"cost_center": self.cost_center,
"project": self.project,
"party_type": "Employee",
"party": data.employee
})
payable_amount -= flt(data.total_payment, precision)
# Payable amount
accounts.append({
"account":
default_payroll_payable_account,
"credit_in_account_currency":
flt(payable_amount, precision),
"party_type":
'',
})
journal_entry.set("accounts", accounts)
journal_entry.title = default_payroll_payable_account
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name=jv_name)
except Exception as e:
frappe.msgprint(e)
return jv_name
def make_accural_jv_entry(self):
#self.check_permission('write')
earnings = get_salary_component_total(self,component_type = "earnings") or {}
deductions = get_salary_component_total(self,component_type = "deductions") or {}
default_payroll_payable_account = get_default_payroll_payable_account(self)
loan_details = get_loan_details(self)
jv_name = ""
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accural Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = nowdate()
accounts = []
payable_amount = 0
#HELKYDS
contasal = 0
empresa = frappe.get_doc('Company',self.company)
contaseg_soc = frappe.db.sql(""" SELECT name from `tabAccount` where company = %s and name like '7252%%' """,(empresa.name),as_dict=False)
print contaseg_soc
if (contaseg_soc == ()):
print "VAZIO"
print "VAZIO"
print "VAZIO"
print "VAZIO"
contaseg_soc = frappe.db.sql(""" SELECT name from `tabAccount` where company = %s and name like '5.10.80.10.10.20.90%%' """,(empresa.name),as_dict=False)
print contaseg_soc
ss_list = get_sal_slip_list(self,ss_status=1)
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print ss_list
saliliquido = 0
for x in ss_list:
print x
ss_obj = frappe.get_doc("Salary Slip",x[0])
print ss_obj.salario_iliquido
saliliquido = saliliquido + flt(ss_obj.salario_iliquido)
# =============
# Earnings
for acc, amount in earnings.items():
payable_amount += flt(amount, precision)
accounts.append({
"account": acc,
"debit_in_account_currency": flt(amount, precision),
"cost_center": self.cost_center,
"project": contasal_prj
})
#HELKYDS
conta = acc;
# 72210000 or 5.10.80.10.10.20.90
print "VEvervegtertasdfasfdsafsadf"
print "VEvervegtertasdfasfdsafsadf"
print "EARNINGS"
print acc
print conta.find('72210000')
print conta.find('5.10.80.10.10.20.10')
if (conta.find('72210000') !=-1):
contasal = acc
contasal_amt = round(saliliquido * 0.08) # round(amt * 0.08)
# contasal_prj = self['project']
print "CONTA 72210000"
print contaseg_soc[0][0]
print amount
print contasal_amt
print payable_amount
elif (conta.find('5.10.80.10.10.20.10') !=-1):
contasal = acc
contasal_amt = round(saliliquido * 0.08) # round(amt * 0.08)
print "CONTA 5.10.80.10.10.20.10"
print contaseg_soc[0][0]
print amount
print contasal_amt
print payable_amount
#Acrescenta a conta da Seguranca Social
print "CENTRO CUSTO SEG. SOCIAL "
segsocial = frappe.db.sql(""" SELECT name from `tabCost Center` where company = %s and cost_center_name = 'seguranca social' """,(empresa.name),as_dict=False)
print "Seg. social"
print segsocial[0][0]
if (contasal != 0):
print "ADIICINAEIIII o DEBITO "
print contasal_amt
accounts.append({
"account": contaseg_soc[0][0],
"debit_in_account_currency": contasal_amt,
"cost_center": segsocial[0][0], #contasal_cent, # segsocial[0][0],
"project": contasal_prj
})
payable_amount = payable_amount+contasal_amt
#contasal = 0
print payable_amount
# ============
# Deductions
for acc, amount in deductions.items():
payable_amount -= flt(amount, precision)
accounts.append({
"account": acc,
"credit_in_account_currency": flt(amount, precision),
"cost_center": self.cost_center,
"project": contasal_prj
})
#HELKYDS
conta = acc;
# 34610000 or 2.80.40.20 2.80.20.20.20
if (conta.find('34610000') !=-1):
contasal = acc
#contasal_amt = round(amt * 0.08)
# contasal_cent = self['cost_center']
# contasal_prj = self['project']
print "CONTA 34610000"
print acc
print amount
print contasal_amt
print payable_amount
elif (conta.find('2.80.20.20.20') !=-1):
contasal = acc
#contasal_amt = round(amt * 0.08)
# contasal_cent = self['cost_center']
# contasal_prj = self['project']
print "CONTA 2.80.20.20.20"
print acc
print amount
print contasal_amt
print payable_amount
#Acrescenta a conta do DEBITO da Seguranca Social
if (contasal != 0):
accounts.append({
"account": contasal,
"credit_in_account_currency": contasal_amt,
"cost_center": self.cost_center,
"project": contasal_prj
})
payable_amount = payable_amount-contasal_amt
contasal = 0
print "ADIICINAEIIII o CREDITO "
print contasal_amt
print payable_amount
# ==========
# Employee loan
for data in loan_details:
accounts.append({
"account": data.employee_loan_account,
"credit_in_account_currency": data.principal_amount
})
accounts.append({
"account": data.interest_income_account,
"credit_in_account_currency": data.interest_amount,
"cost_center": contasal_cent,
"project": contasal_prj
})
payable_amount -= flt(data.total_payment, precision)
# Payable amount
accounts.append({
"account": default_payroll_payable_account,
"credit_in_account_currency": flt(payable_amount, precision)
})
journal_entry.set("accounts", accounts)
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
update_salary_slip_status(self,jv_name = jv_name)
except Exception as e:
frappe.msgprint(e)
return jv_name
def get_data(item_code=None,
warehouse=None,
item_group=None,
start=0,
sort_by='actual_qty',
sort_order='desc'):
'''Return data to render the item dashboard'''
filters = []
if item_code:
filters.append(['item_code', '=', item_code])
if warehouse:
filters.append(['warehouse', '=', warehouse])
if item_group:
lft, rgt = frappe.db.get_value("Item Group", item_group,
["lft", "rgt"])
items = frappe.db.sql_list(
"""
select i.name from `tabItem` i
where exists(select name from `tabItem Group`
where name=i.item_group and lft >=%s and rgt<=%s)
""", (lft, rgt))
filters.append(['item_code', 'in', items])
try:
# check if user has any restrictions based on user permissions on warehouse
if DatabaseQuery('Warehouse',
user=frappe.session.user).build_match_conditions():
filters.append([
'warehouse', 'in',
[w.name for w in frappe.get_list('Warehouse')]
])
except frappe.PermissionError:
# user does not have access on warehouse
return []
items = frappe.db.get_all('Bin',
fields=[
'item_code', 'warehouse', 'projected_qty',
'reserved_qty',
'reserved_qty_for_production',
'reserved_qty_for_sub_contract',
'actual_qty', 'valuation_rate'
],
or_filters={
'projected_qty': ['!=', 0],
'reserved_qty': ['!=', 0],
'reserved_qty_for_production': ['!=', 0],
'reserved_qty_for_sub_contract': ['!=', 0],
'actual_qty': ['!=', 0],
},
filters=filters,
order_by=sort_by + ' ' + sort_order,
limit_start=start,
limit_page_length='21')
precision = cint(
frappe.db.get_single_value("System Settings", "float_precision"))
for item in items:
item.update({
'item_name':
frappe.get_cached_value("Item", item.item_code, 'item_name'),
'disable_quick_entry':
frappe.get_cached_value("Item", item.item_code, 'has_batch_no') or
frappe.get_cached_value("Item", item.item_code, 'has_serial_no'),
'projected_qty':
flt(item.projected_qty, precision),
'reserved_qty':
flt(item.reserved_qty, precision),
'reserved_qty_for_production':
flt(item.reserved_qty_for_production, precision),
'reserved_qty_for_sub_contract':
flt(item.reserved_qty_for_sub_contract, precision),
'actual_qty':
flt(item.actual_qty, precision),
})
return items
def get_ink_amount(self):
return flt(self.ink_rate) \
* flt(self.ink_usage)
def update_item(source, target, source_parent):
target.schedule_date = source.delivery_date
target.qty = flt(source.qty) - flt(source.ordered_qty)
target.stock_qty = (flt(source.qty) - flt(source.ordered_qty)) * flt(
source.conversion_factor)
def update_item(source, target, source_parent): target.amount = flt(source.amount) - flt(source.billed_amt) target.base_amount = target.amount * flt(source_parent.conversion_rate) target.qty = target.amount / flt(source.rate) if (source.rate and source.billed_amt) else source.qty
def get_pro_rata_amt(row, depreciation_amount, from_date, to_date):
days = date_diff(to_date, from_date)
months = month_diff(to_date, from_date)
total_days = get_total_days(to_date, row.frequency_of_depreciation)
return (depreciation_amount * flt(days)) / flt(total_days), days, months
def make_depreciation_schedule(self):
if 'Manual' not in [d.depreciation_method for d in self.finance_books]:
self.schedules = []
if self.get("schedules") or not self.available_for_use_date:
return
for d in self.get('finance_books'):
self.validate_asset_finance_books(d)
value_after_depreciation = (
flt(self.gross_purchase_amount) -
flt(self.opening_accumulated_depreciation))
d.value_after_depreciation = value_after_depreciation
number_of_pending_depreciations = cint(d.total_number_of_depreciations) - \
cint(self.number_of_depreciations_booked)
has_pro_rata = self.check_is_pro_rata(d)
if has_pro_rata:
number_of_pending_depreciations += 1
skip_row = False
for n in range(number_of_pending_depreciations):
# If depreciation is already completed (for double declining balance)
if skip_row: continue
depreciation_amount = self.get_depreciation_amount(
value_after_depreciation, d.total_number_of_depreciations,
d)
if not has_pro_rata or n < cint(
number_of_pending_depreciations) - 1:
schedule_date = add_months(
d.depreciation_start_date,
n * cint(d.frequency_of_depreciation))
# schedule date will be a year later from start date
# so monthly schedule date is calculated by removing 11 months from it
monthly_schedule_date = add_months(
schedule_date, -d.frequency_of_depreciation + 1)
# For first row
if has_pro_rata and n == 0:
depreciation_amount, days, months = get_pro_rata_amt(
d, depreciation_amount, self.available_for_use_date,
d.depreciation_start_date)
# For first depr schedule date will be the start date
# so monthly schedule date is calculated by removing month difference between use date and start date
monthly_schedule_date = add_months(
d.depreciation_start_date, -months + 1)
# For last row
elif has_pro_rata and n == cint(
number_of_pending_depreciations) - 1:
to_date = add_months(self.available_for_use_date,
n * cint(d.frequency_of_depreciation))
depreciation_amount, days, months = get_pro_rata_amt(
d, depreciation_amount, schedule_date, to_date)
monthly_schedule_date = add_months(schedule_date, 1)
schedule_date = add_days(schedule_date, days)
last_schedule_date = schedule_date
if not depreciation_amount: continue
value_after_depreciation -= flt(
depreciation_amount,
self.precision("gross_purchase_amount"))
# Adjust depreciation amount in the last period based on the expected value after useful life
if d.expected_value_after_useful_life and (
(n == cint(number_of_pending_depreciations) - 1
and value_after_depreciation !=
d.expected_value_after_useful_life)
or value_after_depreciation <
d.expected_value_after_useful_life):
depreciation_amount += (value_after_depreciation -
d.expected_value_after_useful_life)
skip_row = True
if depreciation_amount > 0:
# With monthly depreciation, each depreciation is divided by months remaining until next date
if self.allow_monthly_depreciation:
# month range is 1 to 12
# In pro rata case, for first and last depreciation, month range would be different
month_range = months \
if (has_pro_rata and n==0) or (has_pro_rata and n == cint(number_of_pending_depreciations) - 1) \
else d.frequency_of_depreciation
for r in range(month_range):
if (has_pro_rata and n == 0):
# For first entry of monthly depr
if r == 0:
days_until_first_depr = date_diff(
monthly_schedule_date,
self.available_for_use_date)
per_day_amt = depreciation_amount / days
depreciation_amount_for_current_month = per_day_amt * days_until_first_depr
depreciation_amount -= depreciation_amount_for_current_month
date = monthly_schedule_date
amount = depreciation_amount_for_current_month
else:
date = add_months(monthly_schedule_date, r)
amount = depreciation_amount / (
month_range - 1)
elif (has_pro_rata and n
== cint(number_of_pending_depreciations) -
1) and r == cint(month_range) - 1:
# For last entry of monthly depr
date = last_schedule_date
amount = depreciation_amount / month_range
else:
date = add_months(monthly_schedule_date, r)
amount = depreciation_amount / month_range
self.append(
"schedules", {
"schedule_date": date,
"depreciation_amount": amount,
"depreciation_method":
d.depreciation_method,
"finance_book": d.finance_book,
"finance_book_id": d.idx
})
else:
self.append(
"schedules", {
"schedule_date": schedule_date,
"depreciation_amount": depreciation_amount,
"depreciation_method": d.depreciation_method,
"finance_book": d.finance_book,
"finance_book_id": d.idx
})
def get_gl_entries(self,
warehouse_account=None,
default_expense_account=None,
default_cost_center=None):
if not warehouse_account:
warehouse_account = get_warehouse_account_map(self.company)
sle_map = self.get_stock_ledger_details()
voucher_details = self.get_voucher_details(default_expense_account,
default_cost_center,
sle_map)
gl_list = []
warehouse_with_no_account = []
precision = frappe.get_precision("GL Entry",
"debit_in_account_currency")
for item_row in voucher_details:
sle_list = sle_map.get(item_row.name)
if sle_list:
for sle in sle_list:
if warehouse_account.get(sle.warehouse):
# from warehouse account/ target warehouse account
self.check_expense_account(item_row)
# If the item does not have the allow zero valuation rate flag set
# and ( valuation rate not mentioned in an incoming entry
# or incoming entry not found while delivering the item),
# try to pick valuation rate from previous sle or Item master and update in SLE
# Otherwise, throw an exception
if not sle.stock_value_difference and self.doctype != "Stock Reconciliation" \
and not item_row.get("allow_zero_valuation_rate"):
sle = self.update_stock_ledger_entries(sle)
gl_list.append(
self.get_gl_dict(
{
"account":
warehouse_account[
sle.warehouse]["account"],
"against":
item_row.expense_account,
"cost_center":
item_row.cost_center,
"project":
item_row.project or self.get('project'),
"remarks":
self.get("remarks")
or "Accounting Entry for Stock",
"debit":
flt(sle.stock_value_difference, precision),
"is_opening":
item_row.get("is_opening")
or self.get("is_opening") or "No",
},
warehouse_account[sle.warehouse]
["account_currency"],
item=item_row))
# expense account
gl_list.append(
self.get_gl_dict(
{
"account":
item_row.expense_account,
"against":
warehouse_account[
sle.warehouse]["account"],
"cost_center":
item_row.cost_center,
"project":
item_row.project or self.get('project'),
"remarks":
self.get("remarks")
or "Accounting Entry for Stock",
"credit":
flt(sle.stock_value_difference, precision),
"project":
item_row.get("project")
or self.get("project"),
"is_opening":
item_row.get("is_opening")
or self.get("is_opening") or "No"
},
item=item_row))
elif sle.warehouse not in warehouse_with_no_account:
warehouse_with_no_account.append(sle.warehouse)
if warehouse_with_no_account:
for wh in warehouse_with_no_account:
if frappe.db.get_value("Warehouse", wh, "company"):
frappe.throw(
_("Warehouse {0} is not linked to any account, please mention the account in the warehouse record or set default inventory account in company {1}."
).format(wh, self.company))
return process_gl_map(gl_list)
def _get_requested_qty():
return flt(frappe.db.get_value("Bin", {"item_code": "_Test Item Home Desktop 100",
"warehouse": "_Test Warehouse - _TC"}, "indented_qty"))
def get_value_after_depreciation(self, idx):
return flt(
self.get('finance_books')[cint(idx) - 1].value_after_depreciation)
def update_item(source_doc, target_doc, source_parent):
target_doc.base_amount = (flt(source_doc.qty) - flt(source_doc.delivered_qty)) * \
flt(source_doc.base_rate)
target_doc.amount = (flt(source_doc.qty) - flt(source_doc.delivered_qty)) * \
flt(source_doc.rate)
target_doc.qty = flt(source_doc.qty) - flt(source_doc.delivered_qty)
def set_depreciation_rate(self):
for d in self.get("finance_books"):
d.rate_of_depreciation = flt(
self.get_depreciation_rate(d, on_validate=True),
d.precision("rate_of_depreciation"))
def validate_pos(self):
if flt(self.paid_amount) + flt(self.write_off_amount) \
- flt(self.grand_total) > 1/(10**(self.precision("grand_total") + 1)) and self.is_return:
frappe.throw(
_("""Paid amount + Write Off Amount can not be greater than Grand Total"""
))
def _get_requested_qty(self, item_code, warehouse):
return flt(frappe.db.get_value("Bin", {"item_code": item_code, "warehouse": warehouse}, "indented_qty"))
def get_gl_entries(self, warehouse_account=None):
from erpnext.accounts.general_ledger import process_gl_map
stock_rbnb = self.get_company_default("stock_received_but_not_billed")
expenses_included_in_valuation = self.get_company_default("expenses_included_in_valuation")
gl_entries = []
warehouse_with_no_account = []
negative_expense_to_be_booked = 0.0
stock_items = self.get_stock_items()
for d in self.get("items"):
if d.item_code in stock_items and flt(d.valuation_rate) and flt(d.qty):
if warehouse_account.get(d.warehouse):
stock_value_diff = frappe.db.get_value("Stock Ledger Entry",
{"voucher_type": "Purchase Receipt", "voucher_no": self.name,
"voucher_detail_no": d.name, "warehouse": d.warehouse}, "stock_value_difference")
if not stock_value_diff:
continue
gl_entries.append(self.get_gl_dict({
"account": warehouse_account[d.warehouse]["account"],
"against": stock_rbnb,
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"debit": stock_value_diff
}, warehouse_account[d.warehouse]["account_currency"], item=d))
# stock received but not billed
stock_rbnb_currency = get_account_currency(stock_rbnb)
gl_entries.append(self.get_gl_dict({
"account": stock_rbnb,
"against": warehouse_account[d.warehouse]["account"],
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"credit": flt(d.base_net_amount, d.precision("base_net_amount")),
"credit_in_account_currency": flt(d.base_net_amount, d.precision("base_net_amount")) \
if stock_rbnb_currency==self.company_currency else flt(d.net_amount, d.precision("net_amount"))
}, stock_rbnb_currency, item=d))
negative_expense_to_be_booked += flt(d.item_tax_amount)
# Amount added through landed-cost-voucher
if flt(d.landed_cost_voucher_amount):
gl_entries.append(self.get_gl_dict({
"account": expenses_included_in_valuation,
"against": warehouse_account[d.warehouse]["account"],
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"credit": flt(d.landed_cost_voucher_amount),
"project": d.project
}, item=d))
# sub-contracting warehouse
if flt(d.rm_supp_cost) and warehouse_account.get(self.supplier_warehouse):
gl_entries.append(self.get_gl_dict({
"account": warehouse_account[self.supplier_warehouse]["account"],
"against": warehouse_account[d.warehouse]["account"],
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"credit": flt(d.rm_supp_cost)
}, warehouse_account[self.supplier_warehouse]["account_currency"], item=d))
# divisional loss adjustment
valuation_amount_as_per_doc = flt(d.base_net_amount, d.precision("base_net_amount")) + \
flt(d.landed_cost_voucher_amount) + flt(d.rm_supp_cost) + flt(d.item_tax_amount)
divisional_loss = flt(valuation_amount_as_per_doc - stock_value_diff,
d.precision("base_net_amount"))
if divisional_loss:
if self.is_return or flt(d.item_tax_amount):
loss_account = expenses_included_in_valuation
else:
loss_account = stock_rbnb
gl_entries.append(self.get_gl_dict({
"account": loss_account,
"against": warehouse_account[d.warehouse]["account"],
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"debit": divisional_loss,
"project": d.project
}, stock_rbnb_currency, item=d))
elif d.warehouse not in warehouse_with_no_account or \
d.rejected_warehouse not in warehouse_with_no_account:
warehouse_with_no_account.append(d.warehouse)
self.get_asset_gl_entry(gl_entries, expenses_included_in_valuation)
# Cost center-wise amount breakup for other charges included for valuation
valuation_tax = {}
for tax in self.get("taxes"):
if tax.category in ("Valuation", "Valuation and Total") and flt(tax.base_tax_amount_after_discount_amount):
if not tax.cost_center:
frappe.throw(_("Cost Center is required in row {0} in Taxes table for type {1}").format(tax.idx, _(tax.category)))
valuation_tax.setdefault(tax.cost_center, 0)
valuation_tax[tax.cost_center] += \
(tax.add_deduct_tax == "Add" and 1 or -1) * flt(tax.base_tax_amount_after_discount_amount)
if negative_expense_to_be_booked and valuation_tax:
# Backward compatibility:
# If expenses_included_in_valuation account has been credited in against PI
# and charges added via Landed Cost Voucher,
# post valuation related charges on "Stock Received But Not Billed"
negative_expense_booked_in_pi = frappe.db.sql("""select name from `tabPurchase Invoice Item` pi
where docstatus = 1 and purchase_receipt=%s
and exists(select name from `tabGL Entry` where voucher_type='Purchase Invoice'
and voucher_no=pi.parent and account=%s)""", (self.name, expenses_included_in_valuation))
if negative_expense_booked_in_pi:
expenses_included_in_valuation = stock_rbnb
against_account = ", ".join([d.account for d in gl_entries if flt(d.debit) > 0])
total_valuation_amount = sum(valuation_tax.values())
amount_including_divisional_loss = negative_expense_to_be_booked
i = 1
for cost_center, amount in iteritems(valuation_tax):
if i == len(valuation_tax):
applicable_amount = amount_including_divisional_loss
else:
applicable_amount = negative_expense_to_be_booked * (amount / total_valuation_amount)
amount_including_divisional_loss -= applicable_amount
gl_entries.append(
self.get_gl_dict({
"account": expenses_included_in_valuation,
"cost_center": cost_center,
"credit": applicable_amount,
"remarks": self.remarks or _("Accounting Entry for Stock"),
"against": against_account
})
)
i += 1
if warehouse_with_no_account:
frappe.msgprint(_("No accounting entries for the following warehouses") + ": \n" +
"\n".join(warehouse_with_no_account))
return process_gl_map(gl_entries)
def validate_account_for_change_amount(self):
if flt(self.change_amount) and not self.account_for_change_amount:
msgprint(_("Please enter Account for Change Amount"),
raise_exception=1)
def get_already_received_qty(self, po, po_detail):
qty = frappe.db.sql("""select sum(qty) from `tabPurchase Receipt Item`
where purchase_order_item = %s and docstatus = 1
and purchase_order=%s
and parent != %s""", (po_detail, po, self.name))
return qty and flt(qty[0][0]) or 0.0
def get_asset_gl_entry(self, gl_entries, expenses_included_in_valuation=None):
arbnb_account, cwip_account = None, None
cwip_disabled = is_cwip_accounting_disabled()
if not expenses_included_in_valuation:
expenses_included_in_valuation = self.get_company_default("expenses_included_in_valuation")
for d in self.get("items"):
if d.is_fixed_asset and not (arbnb_account and cwip_account):
arbnb_account = self.get_company_default("asset_received_but_not_billed")
# CWIP entry
cwip_account = get_asset_account("capital_work_in_progress_account", d.asset,
company = self.company)
if d.is_fixed_asset and not cwip_disabled:
asset_amount = flt(d.net_amount) + flt(d.item_tax_amount/self.conversion_rate)
base_asset_amount = flt(d.base_net_amount + d.item_tax_amount)
cwip_account_currency = get_account_currency(cwip_account)
gl_entries.append(self.get_gl_dict({
"account": cwip_account,
"against": arbnb_account,
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Asset"),
"debit": base_asset_amount,
"debit_in_account_currency": (base_asset_amount
if cwip_account_currency == self.company_currency else asset_amount)
}, item=d))
# Asset received but not billed
asset_rbnb_currency = get_account_currency(arbnb_account)
gl_entries.append(self.get_gl_dict({
"account": arbnb_account,
"against": cwip_account,
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Asset"),
"credit": base_asset_amount,
"credit_in_account_currency": (base_asset_amount
if asset_rbnb_currency == self.company_currency else asset_amount)
}, item=d))
if d.is_fixed_asset and flt(d.landed_cost_voucher_amount):
asset_account = (get_asset_category_account(d.asset, 'fixed_asset_account',
company = self.company) if cwip_disabled else cwip_account)
gl_entries.append(self.get_gl_dict({
"account": expenses_included_in_valuation,
"against": asset_account,
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"credit": flt(d.landed_cost_voucher_amount),
"project": d.project
}, item=d))
gl_entries.append(self.get_gl_dict({
"account": asset_account,
"against": expenses_included_in_valuation,
"cost_center": d.cost_center,
"remarks": self.get("remarks") or _("Accounting Entry for Stock"),
"debit": flt(d.landed_cost_voucher_amount),
"project": d.project
}, item=d))
if d.asset:
doc = frappe.get_doc("Asset", d.asset)
frappe.db.set_value("Asset", d.asset, "gross_purchase_amount",
doc.gross_purchase_amount + flt(d.landed_cost_voucher_amount))
frappe.db.set_value("Asset", d.asset, "purchase_receipt_amount",
doc.purchase_receipt_amount + flt(d.landed_cost_voucher_amount))
return gl_entries
def __validate_consumed_qty(self, row):
if self.backflush_based_on != 'BOM' and flt(row.consumed_qty) == 0.0:
msg = f'Row {row.idx}: the consumed qty cannot be zero for the item {frappe.bold(row.rm_item_code)}'
frappe.throw(_(msg),title=_('Consumed Items Qty Check'))
def get_current_stock(self):
for d in self.get('supplied_items'):
if self.supplier_warehouse:
bin = frappe.db.sql("select actual_qty from `tabBin` where item_code = %s and warehouse = %s", (d.rm_item_code, self.supplier_warehouse), as_dict = 1)
d.current_stock = bin and flt(bin[0]['actual_qty']) or 0
def validate(self, method):
# ---------------------------------------------------------------------------
# update item rate in employee service
# --------------------------------------------------------------------------- */
employee_list = frappe.get_all(
'Employee',
filters={"employee_price_list": cstr(self.price_list)},
fields=['name'])
for employee in employee_list:
employee_service_rate = frappe.db.get_value(
"Services", {
"parent": cstr(employee.name),
"service": cstr(self.item_code)
}, "billing_rate")
if self.price_list_rate != employee_service_rate:
frappe.db.set_value("Services", {
"parent": cstr(employee.name),
"service": cstr(self.item_code)
}, "billing_rate", self.price_list_rate)
frappe.db.commit()
# ---------------------------------------------------------------------------
# add/update item rate in all employee's price list having the same branch
# --------------------------------------------------------------------------- */
if self.is_from_object:
price_list = self.price_list
branch_by_price_list = frappe.db.get_value("Branch", str(price_list),
"name")
if branch_by_price_list:
emp_price_list = frappe.get_all(
"Employee",
filters=[["branch", "=", branch_by_price_list]],
fields=["name", "employee_price_list"])
for pl in emp_price_list:
if pl.employee_price_list:
price_list_record_name = frappe.db.get_value(
"Item Price", {
"price_list": str(pl.employee_price_list),
"item_code": str(self.item_code)
}, "name")
if price_list_record_name:
frappe.db.set_value(
"Item Price", {
"price_list": str(pl.employee_price_list),
"item_code": str(self.item_code)
}, "price_list_rate", self.price_list_rate)
frappe.db.commit()
else:
item_price = frappe.get_doc({
"doctype":
"Item Price",
"price_list":
pl.employee_price_list,
"selling":
1,
"currency":
frappe.db.get_value("Global Defaults", None,
"default_currency"),
"item_code":
str(self.item_code),
"price_list_rate":
flt(self.price_list_rate),
"item_name":
frappe.db.get_value("Item", str(self.item_code),
"item_name"),
"item_description":
frappe.db.get_value("Item", str(self.item_code),
"description"),
"is_from_object":
0
})
item_price.flags.ignore_permissions = True
item_price.insert()
def validate_data(self):
if not self.doc.reconciliation_json:
return
data = json.loads(self.doc.reconciliation_json)
# strip out extra columns (if any)
data = [row[:4] for row in data]
if self.head_row not in data:
msgprint(_("""Wrong Template: Unable to find head row."""),
raise_exception=1)
# remove the help part and save the json
head_row_no = 0
if data.index(self.head_row) != 0:
head_row_no = data.index(self.head_row)
data = data[head_row_no:]
self.doc.reconciliation_json = json.dumps(data)
def _get_msg(row_num, msg):
return _("Row # ") + ("%d: " % (row_num+head_row_no+2)) + _(msg)
self.validation_messages = []
item_warehouse_combinations = []
# validate no of rows
rows = data[1:]
if len(rows) > 100:
msgprint(_("""Sorry! We can only allow upto 100 rows for Stock Reconciliation."""),
raise_exception=True)
for row_num, row in enumerate(rows):
# find duplicates
if [row[0], row[1]] in item_warehouse_combinations:
self.validation_messages.append(_get_msg(row_num, "Duplicate entry"))
else:
item_warehouse_combinations.append([row[0], row[1]])
self.validate_item(row[0], row_num+head_row_no+2)
# note: warehouse will be validated through link validation
# if both not specified
if row[2] == "" and row[3] == "":
self.validation_messages.append(_get_msg(row_num,
"Please specify either Quantity or Valuation Rate or both"))
# do not allow negative quantity
if flt(row[2]) < 0:
self.validation_messages.append(_get_msg(row_num,
"Negative Quantity is not allowed"))
# do not allow negative valuation
if flt(row[3]) < 0:
self.validation_messages.append(_get_msg(row_num,
"Negative Valuation Rate is not allowed"))
# throw all validation messages
if self.validation_messages:
for msg in self.validation_messages:
msgprint(msg)
raise frappe.ValidationError
def calculate_write_off_amount(self):
if flt(self.doc.change_amount) > 0:
self.doc.write_off_amount = flt(self.doc.grand_total - self.doc.paid_amount + self.doc.change_amount,
self.doc.precision("write_off_amount"))
self.doc.base_write_off_amount = flt(self.doc.write_off_amount * self.doc.conversion_rate,
self.doc.precision("base_write_off_amount"))
def test_drop_shipping(self):
from erpnext.selling.doctype.sales_order.sales_order import make_purchase_order_for_drop_shipment
from erpnext.stock.doctype.item.test_item import make_item
from erpnext.buying.doctype.purchase_order.purchase_order import update_status
make_stock_entry(target="_Test Warehouse - _TC", qty=10, rate=100)
po_item = make_item(
"_Test Item for Drop Shipping", {
"is_stock_item": 1,
"delivered_by_supplier": 1,
'default_supplier': '_Test Supplier',
"expense_account": "_Test Account Cost for Goods Sold - _TC",
"cost_center": "_Test Cost Center - _TC"
})
dn_item = make_item(
"_Test Regular Item", {
"is_stock_item": 1,
"expense_account": "_Test Account Cost for Goods Sold - _TC",
"cost_center": "_Test Cost Center - _TC"
})
so_items = [{
"item_code": po_item.item_code,
"warehouse": "",
"qty": 2,
"rate": 400,
"delivered_by_supplier": 1,
"supplier": '_Test Supplier'
}, {
"item_code": dn_item.item_code,
"warehouse": "_Test Warehouse - _TC",
"qty": 2,
"rate": 300,
"conversion_factor": 1.0
}]
if frappe.db.get_value("Item", "_Test Regular Item",
"is_stock_item") == 1:
make_stock_entry(item="_Test Regular Item",
target="_Test Warehouse - _TC",
qty=10,
rate=100)
#setuo existing qty from bin
bin = frappe.get_all("Bin",
filters={
"item_code": po_item.item_code,
"warehouse": "_Test Warehouse - _TC"
},
fields=["ordered_qty", "reserved_qty"])
existing_ordered_qty = bin[0].ordered_qty if bin else 0.0
existing_reserved_qty = bin[0].reserved_qty if bin else 0.0
bin = frappe.get_all("Bin",
filters={
"item_code": dn_item.item_code,
"warehouse": "_Test Warehouse - _TC"
},
fields=["reserved_qty"])
existing_reserved_qty_for_dn_item = bin[0].reserved_qty if bin else 0.0
#create so, po and partial dn
so = make_sales_order(item_list=so_items, do_not_submit=True)
so.submit()
po = make_purchase_order_for_drop_shipment(so.name, '_Test Supplier')
po.submit()
dn = create_dn_against_so(so.name, delivered_qty=1)
self.assertEquals(so.customer, po.customer)
self.assertEquals(po.items[0].sales_order, so.name)
self.assertEquals(po.items[0].item_code, po_item.item_code)
self.assertEquals(dn.items[0].item_code, dn_item.item_code)
#test ordered_qty and reserved_qty
bin = frappe.get_all("Bin",
filters={
"item_code": po_item.item_code,
"warehouse": "_Test Warehouse - _TC"
},
fields=["ordered_qty", "reserved_qty"])
ordered_qty = bin[0].ordered_qty if bin else 0.0
reserved_qty = bin[0].reserved_qty if bin else 0.0
self.assertEquals(abs(flt(ordered_qty)), existing_ordered_qty)
self.assertEquals(abs(flt(reserved_qty)), existing_reserved_qty)
reserved_qty = frappe.db.get_value("Bin", {
"item_code": dn_item.item_code,
"warehouse": "_Test Warehouse - _TC"
}, "reserved_qty")
self.assertEquals(abs(flt(reserved_qty)),
existing_reserved_qty_for_dn_item + 1)
#test po_item length
self.assertEquals(len(po.items), 1)
#test per_delivered status
update_status("Delivered", po.name)
self.assertEquals(
flt(frappe.db.get_value("Sales Order", so.name, "per_delivered"),
2), 75.00)
#test reserved qty after complete delivery
dn = create_dn_against_so(so.name, delivered_qty=1)
reserved_qty = frappe.db.get_value("Bin", {
"item_code": dn_item.item_code,
"warehouse": "_Test Warehouse - _TC"
}, "reserved_qty")
self.assertEquals(abs(flt(reserved_qty)),
existing_reserved_qty_for_dn_item)
#test after closing so
so.db_set('status', "Closed")
so.update_reserved_qty()
bin = frappe.get_all("Bin",
filters={
"item_code": po_item.item_code,
"warehouse": "_Test Warehouse - _TC"
},
fields=["ordered_qty", "reserved_qty"])
ordered_qty = bin[0].ordered_qty if bin else 0.0
reserved_qty = bin[0].reserved_qty if bin else 0.0
self.assertEquals(abs(flt(ordered_qty)), existing_ordered_qty)
self.assertEquals(abs(flt(reserved_qty)), existing_reserved_qty)
reserved_qty = frappe.db.get_value("Bin", {
"item_code": dn_item.item_code,
"warehouse": "_Test Warehouse - _TC"
}, "reserved_qty")
self.assertEquals(abs(flt(reserved_qty)),
existing_reserved_qty_for_dn_item)
def round_off_totals(self, tax):
tax.total = flt(tax.total, tax.precision("total"))
tax.tax_amount = flt(tax.tax_amount, tax.precision("tax_amount"))
tax.tax_amount_after_discount_amount = flt(tax.tax_amount_after_discount_amount, tax.precision("tax_amount"))
self._set_in_company_currency(tax, ["total", "tax_amount", "tax_amount_after_discount_amount"])
def _set_in_company_currency(self, doc, fields):
"""set values in base currency"""
for f in fields:
val = flt(flt(doc.get(f), doc.precision(f)) * self.doc.conversion_rate, doc.precision("base_" + f))
doc.set("base_" + f, val)
def _get_tax_rate(self, tax, item_tax_map):
if item_tax_map.has_key(tax.account_head):
return flt(item_tax_map.get(tax.account_head), self.doc.precision("rate", tax))
else:
return tax.rate
def set_discount_amount(self):
if self.doc.additional_discount_percentage:
self.doc.discount_amount = flt(flt(self.doc.get(scrub(self.doc.apply_discount_on)))
* self.doc.additional_discount_percentage / 100, self.doc.precision("discount_amount"))