def check_value(self, ref_dt, ref_dn, ref_item_dn, val, item_code):
ref_val = webnotes.conn.get_value(ref_dt + " Item", ref_item_dn, "export_rate")
if flt(ref_val, 2) != flt(val, 2):
msgprint(
_("Rate is not matching with ") + ref_dt + ": " + ref_dn + _(" for item: ") + item_code,
raise_exception=True,
)
def get_tax_accounts(item_list, columns):
import json
item_tax = {}
tax_accounts = []
tax_details = webnotes.conn.sql("""select parent, account_head, item_wise_tax_detail
from `tabPurchase Taxes and Charges` where parenttype = 'Purchase Invoice'
and docstatus = 1 and ifnull(account_head, '') != '' and category in ('Total', 'Valuation and Total')
and parent in (%s)""" % ', '.join(['%s']*len(item_list)), tuple([item.parent for item in item_list]))
for parent, account_head, item_wise_tax_detail in tax_details:
if account_head not in tax_accounts:
tax_accounts.append(account_head)
if item_wise_tax_detail:
try:
item_wise_tax_detail = json.loads(item_wise_tax_detail)
for item, tax_amount in item_wise_tax_detail.items():
item_tax.setdefault(parent, {}).setdefault(item, {})[account_head] = \
flt(tax_amount[1]) if isinstance(tax_amount, list) else flt(tax_amount)
except ValueError:
continue
tax_accounts.sort()
columns += [account_head + ":Currency:80" for account_head in tax_accounts]
columns += ["Total Tax:Currency:80", "Total:Currency:80"]
return item_tax, tax_accounts
def create_remarks(self):
r = []
if self.doc.cheque_no :
if self.doc.cheque_date:
r.append('Via Reference #%s dated %s' %
(self.doc.cheque_no, formatdate(self.doc.cheque_date)))
else :
msgprint("Please enter Reference date", raise_exception=1)
for d in getlist(self.doclist, 'entries'):
if d.against_invoice and d.credit:
currency = webnotes.conn.get_value("Sales Invoice", d.against_invoice, "currency")
r.append('%s %s against Invoice: %s' %
(cstr(currency), fmt_money(flt(d.credit)), d.against_invoice))
if d.against_voucher and d.debit:
bill_no = webnotes.conn.sql("""select bill_no, bill_date, currency
from `tabPurchase Invoice` where name=%s""", d.against_voucher)
if bill_no and bill_no[0][0] and bill_no[0][0].lower().strip() \
not in ['na', 'not applicable', 'none']:
r.append('%s %s against Bill %s dated %s' %
(cstr(bill_no[0][2]), fmt_money(flt(d.debit)), bill_no[0][0],
bill_no[0][1] and formatdate(bill_no[0][1].strftime('%Y-%m-%d')) or ''))
if self.doc.user_remark:
r.append("User Remark : %s"%self.doc.user_remark)
if r:
self.doc.remark = ("\n").join(r)
else:
webnotes.msgprint("User Remarks is mandatory", raise_exception=1)
def get_requested_items(self):
item_projected_qty = self.get_projected_qty()
items_to_be_requested = webnotes._dict()
for item, so_item_qty in self.item_dict.items():
requested_qty = 0
total_qty = sum([flt(d[0]) for d in so_item_qty])
if total_qty > item_projected_qty.get(item, 0):
# shortage
requested_qty = total_qty - item_projected_qty.get(item, 0)
# consider minimum order qty
requested_qty = requested_qty > flt(so_item_qty[0][3]) and \
requested_qty or flt(so_item_qty[0][3])
# distribute requested qty SO wise
for item_details in so_item_qty:
if requested_qty:
sales_order = item_details[4] or "No Sales Order"
if requested_qty <= item_details[0]:
adjusted_qty = requested_qty
else:
adjusted_qty = item_details[0]
items_to_be_requested.setdefault(item, {}).setdefault(sales_order, 0)
items_to_be_requested[item][sales_order] += adjusted_qty
requested_qty -= adjusted_qty
else:
break
# requested qty >= total so qty, due to minimum order qty
if requested_qty:
items_to_be_requested.setdefault(item, {}).setdefault("No Sales Order", 0)
items_to_be_requested[item]["No Sales Order"] += requested_qty
return items_to_be_requested
def get_balance(self):
if not getlist(self.doclist,'entries'):
msgprint("Please enter atleast 1 entry in 'GL Entries' table")
else:
flag, self.doc.total_debit, self.doc.total_credit = 0, 0, 0
diff = flt(self.doc.difference, 2)
# If any row without amount, set the diff on that row
for d in getlist(self.doclist,'entries'):
if not d.credit and not d.debit and diff != 0:
if diff>0:
d.credit = diff
elif diff<0:
d.debit = diff
flag = 1
# Set the diff in a new row
if flag == 0 and diff != 0:
jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', self.doclist)
if diff>0:
jd.credit = abs(diff)
elif diff<0:
jd.debit = abs(diff)
# Set the total debit, total credit and difference
for d in getlist(self.doclist,'entries'):
self.doc.total_debit += flt(d.debit, 2)
self.doc.total_credit += flt(d.credit, 2)
self.doc.difference = flt(self.doc.total_debit, 2) - flt(self.doc.total_credit, 2)
def validate_pos(self):
if not self.doc.cash_bank_account and flt(self.doc.paid_amount):
msgprint("Cash/Bank Account is mandatory for POS, for making payment entry")
raise Exception
if (flt(self.doc.paid_amount) + flt(self.doc.write_off_amount) - round(flt(self.doc.grand_total), 2))>0.001:
msgprint("(Paid amount + Write Off Amount) can not be greater than Grand Total")
raise Exception
def get_leave_balance(self):
leave_all = sql("select total_leaves_allocated from `tabLeave Allocation` where employee = '%s' and leave_type = '%s' and fiscal_year = '%s' and docstatus = 1" % (self.doc.employee, self.doc.leave_type, self.doc.fiscal_year))
leave_all = leave_all and flt(leave_all[0][0]) or 0
leave_app = sql("select SUM(total_leave_days) from `tabLeave Application` where employee = '%s' and leave_type = '%s' and fiscal_year = '%s' and docstatus = 1" % (self.doc.employee, self.doc.leave_type, self.doc.fiscal_year))
leave_app = leave_app and flt(leave_app[0][0]) or 0
ret = {'leave_balance':leave_all - leave_app}
return ret
def validate_currency(args, item, meta=None):
from webnotes.model.meta import get_field_precision
if not meta:
meta = webnotes.get_doctype(args.doctype)
# validate conversion rate
if meta.get_field("currency"):
validate_conversion_rate(args.currency, args.conversion_rate,
meta.get_label("conversion_rate"), args.company)
# round it
args.conversion_rate = flt(args.conversion_rate,
get_field_precision(meta.get_field("conversion_rate"),
webnotes._dict({"fields": args})))
# validate price list conversion rate
if meta.get_field("price_list_currency") and (args.selling_price_list or args.buying_price_list) \
and args.price_list_currency:
validate_conversion_rate(args.price_list_currency, args.plc_conversion_rate,
meta.get_label("plc_conversion_rate"), args.company)
# round it
args.plc_conversion_rate = flt(args.plc_conversion_rate,
get_field_precision(meta.get_field("plc_conversion_rate"),
webnotes._dict({"fields": args})))
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 getlist(self.doclist, 'advance_adjustment_details'):
if flt(d.allocated_amount) > 0:
args = {
'voucher_no' : d.journal_voucher,
'voucher_detail_no' : d.jv_detail_no,
'against_voucher_type' : 'Sales Invoice',
'against_voucher' : self.doc.name,
'account' : self.doc.debit_to,
'is_advance' : 'Yes',
'dr_or_cr' : 'credit',
'unadjusted_amt' : flt(d.advance_amount),
'allocated_amt' : flt(d.allocated_amount)
}
lst.append(args)
if lst:
get_obj('GL Control').reconcile_against_document(lst)
def cal_charges_and_item_tax_amt(self):
""" Re-calculates other charges values and itemwise tax amount for getting valuation rate"""
import json
for pr in self.selected_pr:
obj = get_obj('Purchase Receipt', pr, with_children = 1)
total = 0
self.reset_other_charges(obj)
for prd in getlist(obj.doclist, 'purchase_receipt_details'):
prev_total, item_tax = flt(prd.amount), 0
total += flt(prd.qty) * flt(prd.purchase_rate)
try:
item_tax_rate = prd.item_tax_rate and json.loads(prd.item_tax_rate) or {}
except ValueError:
item_tax_rate = prd.item_tax_rate and eval(prd.item_tax_rate) or {}
ocd = getlist(obj.doclist, 'purchase_tax_details')
# calculate tax for other charges
for oc in range(len(ocd)):
# Get rate : consider if diff for this item
if item_tax_rate.get(ocd[oc].account_head) and ocd[oc].charge_type != 'Actual':
rate = item_tax_rate[ocd[oc].account_head]
else:
rate = flt(ocd[oc].rate)
tax_amount = self.cal_tax(ocd, prd, rate, obj.doc.net_total, oc)
total, prev_total, item_tax = self.add_deduct_taxes(ocd, oc, tax_amount, total, prev_total, item_tax)
prd.item_tax_amount = flt(item_tax)
prd.save()
obj.doc.save()
def add_charges_in_pr(self):
""" Add additional charges in selected pr proportionately"""
total_amt = self.get_total_amt()
for pr in self.selected_pr:
pr_obj = get_obj('Purchase Receipt', pr, with_children = 1)
cumulative_grand_total = flt(pr_obj.doc.grand_total)
for lc in getlist(self.doclist, 'landed_cost_details'):
amt = flt(lc.amount) * flt(pr_obj.doc.net_total)/ flt(total_amt)
self.prwise_cost[pr] = self.prwise_cost.get(pr, 0) + amt
cumulative_grand_total += amt
pr_oc_row = sql("select name from `tabPurchase Taxes and Charges` where parent = %s and category = 'Valuation' and add_deduct_tax = 'Add' and charge_type = 'Actual' and account_head = %s",(pr, lc.account_head))
if not pr_oc_row: # add if not exists
ch = addchild(pr_obj.doc, 'purchase_tax_details', 'Purchase Taxes and Charges', 1)
ch.category = 'Valuation'
ch.add_deduct_tax = 'Add'
ch.charge_type = 'Actual'
ch.description = lc.description
ch.account_head = lc.account_head
ch.rate = amt
ch.tax_amount = amt
ch.total = cumulative_grand_total
ch.docstatus = 1
ch.idx = 500 # add at the end
ch.save(1)
else: # overwrite if exists
sql("update `tabPurchase Taxes and Charges` set rate = %s, tax_amount = %s where name = %s and parent = %s ", (amt, amt, pr_oc_row[0][0], pr))
def determine_exclusive_rate(self):
if not any((cint(tax.included_in_print_rate) for tax in self.tax_doclist)):
# no inclusive tax
return
for item in self.item_doclist:
item_tax_map = self._load_item_tax_rate(item.item_tax_rate)
cumulated_tax_fraction = 0
for i, tax in enumerate(self.tax_doclist):
tax.tax_fraction_for_current_item = self.get_current_tax_fraction(tax, item_tax_map)
if i==0:
tax.grand_total_fraction_for_current_item = 1 + tax.tax_fraction_for_current_item
else:
tax.grand_total_fraction_for_current_item = \
self.tax_doclist[i-1].grand_total_fraction_for_current_item \
+ tax.tax_fraction_for_current_item
cumulated_tax_fraction += tax.tax_fraction_for_current_item
if cumulated_tax_fraction:
item.amount = flt((item.export_amount * self.doc.conversion_rate) /
(1 + cumulated_tax_fraction), self.precision("amount", item))
item.basic_rate = flt(item.amount / item.qty, self.precision("basic_rate", item))
if item.adj_rate == 100:
item.base_ref_rate = item.basic_rate
item.basic_rate = 0.0
else:
item.base_ref_rate = flt(item.basic_rate / (1 - (item.adj_rate / 100.0)),
self.precision("base_ref_rate", item))
def validate_max_discount(self):
for d in self.doclist.get({"parentfield": self.fname}):
discount = flt(webnotes.conn.get_value("Item", d.item_code, "max_discount"))
if discount and flt(d.adj_rate) > discount:
webnotes.throw(_("You cannot give more than ") + cstr(discount) + "% " +
_("discount on Item Code") + ": " + cstr(d.item_code))
def get_outstanding_amount(self, gle, report_date): payment_received = 0.0 for e in self.get_gl_entries_for(gle.account, gle.voucher_type, gle.voucher_no): if getdate(e.posting_date) <= report_date and e.name!=gle.name: payment_received += (flt(e.credit) - flt(e.debit)) return flt(gle.debit) - flt(gle.credit) - payment_received
def on_update(self):
if cint(self.doc.update_stock) == 1:
# Set default warehouse from pos setting
if cint(self.doc.is_pos) == 1:
w = self.get_warehouse()
if w:
for d in getlist(self.doclist, 'entries'):
if not d.warehouse:
d.warehouse = cstr(w)
self.make_packing_list()
else:
self.doclist = self.doc.clear_table(self.doclist, 'packing_details')
if cint(self.doc.is_pos) == 1:
if flt(self.doc.paid_amount) == 0:
if self.doc.cash_bank_account:
webnotes.conn.set(self.doc, 'paid_amount',
(flt(self.doc.grand_total) - flt(self.doc.write_off_amount)))
else:
# show message that the amount is not paid
webnotes.conn.set(self.doc,'paid_amount',0)
webnotes.msgprint("Note: Payment Entry will not be created since 'Cash/Bank Account' was not specified.")
else:
webnotes.conn.set(self.doc,'paid_amount',0)
def reconcile(self):
"""
Links booking and payment voucher
1. cancel payment voucher
2. split into multiple rows if partially adjusted, assign against voucher
3. submit payment voucher
"""
if not self.doc.voucher_no or not sql("select name from `tab%s` where name = %s" %(self.dt[self.doc.voucher_type], '%s'), self.doc.voucher_no):
msgprint("Please select valid Voucher No to proceed", raise_exception=1)
lst = []
for d in getlist(self.doclist, 'ir_payment_details'):
if flt(d.amt_to_be_reconciled) > 0:
args = {
'voucher_no' : d.voucher_no,
'voucher_detail_no' : d.voucher_detail_no,
'against_voucher_type' : self.dt[self.doc.voucher_type],
'against_voucher' : self.doc.voucher_no,
'account' : self.doc.account,
'is_advance' : 'No',
'dr_or_cr' : self.acc_type=='debit' and 'credit' or 'debit',
'unadjusted_amt' : flt(d.amt_due),
'allocated_amt' : flt(d.amt_to_be_reconciled)
}
lst.append(args)
if lst:
get_obj('GL Control').reconcile_against_document(lst)
msgprint("Successfully allocated.")
else:
msgprint("No amount allocated.", raise_exception=1)
def do_stock_reco(self, is_submit = 1):
"""
Make stock entry of qty diff, calculate incoming rate to maintain valuation rate.
If no qty diff, but diff in valuation rate, make (+1,-1) entry to update valuation
"""
for row in self.data:
# Get qty as per system
sys_stock = self.get_system_stock(row[0],row[1])
# Diff between file and system
qty_diff = row[2] != '~' and flt(row[2]) - flt(sys_stock['actual_qty']) or 0
rate_diff = row[3] != '~' and flt(row[3]) - flt(sys_stock['val_rate']) or 0
# Make sl entry
if qty_diff:
self.make_sl_entry(is_submit, row, qty_diff, sys_stock)
elif rate_diff:
self.make_sl_entry(is_submit, row, 1, sys_stock)
sys_stock['val_rate'] = row[3]
sys_stock['actual_qty'] += 1
self.make_sl_entry(is_submit, row, -1, sys_stock)
if is_submit == 1:
self.add_data_in_CSV(qty_diff, rate_diff)
msgprint("Stock Reconciliation Completed Successfully...")
def get_as_on_balance(self, account_name, fiscal_year, as_on, credit_or_debit, lft, rgt):
# initialization
det = webnotes.conn.sql("select start_date, opening from `tabAccount Balance` where period = %s and account = %s", (fiscal_year, account_name))
from_date, opening, debit_bal, credit_bal, closing_bal = det and det[0][0] or getdate(nowdate()), det and flt(det[0][1]) or 0, 0, 0, det and flt(det[0][1]) or 0
# prev month closing
prev_month_det = webnotes.conn.sql("select end_date, debit, credit, balance from `tabAccount Balance` where account = %s and end_date <= %s and fiscal_year = %s order by end_date desc limit 1", (account_name, as_on, fiscal_year))
if prev_month_det:
from_date = getdate(add_days(prev_month_det[0][0].strftime('%Y-%m-%d'), 1))
opening = 0
debit_bal = flt(prev_month_det[0][1])
credit_bal = flt(prev_month_det[0][2])
closing_bal = flt(prev_month_det[0][3])
# curr month transaction
if getdate(as_on) >= from_date:
curr_month_bal = webnotes.conn.sql("select SUM(t1.debit), SUM(t1.credit) from `tabGL Entry` t1, `tabAccount` t2 WHERE t1.posting_date >= %s AND t1.posting_date <= %s and ifnull(t1.is_opening, 'No') = 'No' AND t1.account = t2.name AND t2.lft >= %s AND t2.rgt <= %s and ifnull(t1.is_cancelled, 'No') = 'No'", (from_date, as_on, lft, rgt))
curr_debit_amt, curr_credit_amt = flt(curr_month_bal[0][0]), flt(curr_month_bal[0][1])
debit_bal = curr_month_bal and debit_bal + curr_debit_amt or debit_bal
credit_bal = curr_month_bal and credit_bal + curr_credit_amt or credit_bal
if credit_or_debit == 'Credit':
curr_debit_amt, curr_credit_amt = -1*flt(curr_month_bal[0][0]), -1*flt(curr_month_bal[0][1])
closing_bal = closing_bal + curr_debit_amt - curr_credit_amt
return flt(debit_bal), flt(credit_bal), flt(closing_bal)
def update_against_document_in_jv(self, obj, table_field_name, against_document_no, against_document_doctype, account_head, dr_or_cr,doctype):
for d in getlist(obj.doclist, table_field_name):
self.validate_jv_entry(d, account_head, dr_or_cr)
if flt(d.advance_amount) == flt(d.allocated_amount):
# cancel JV
jv_obj = get_obj('Journal Voucher', d.journal_voucher, with_children=1)
get_obj(dt='GL Control').make_gl_entries(jv_obj.doc, jv_obj.doclist, cancel =1, adv_adj =1)
# update ref in JV Detail
webnotes.conn.sql("update `tabJournal Voucher Detail` set %s = '%s' where name = '%s'" % (doctype=='Purchase Invoice' and 'against_voucher' or 'against_invoice', cstr(against_document_no), d.jv_detail_no))
# re-submit JV
jv_obj = get_obj('Journal Voucher', d.journal_voucher, with_children =1)
get_obj(dt='GL Control').make_gl_entries(jv_obj.doc, jv_obj.doclist, cancel = 0, adv_adj =1)
elif flt(d.advance_amount) > flt(d.allocated_amount):
# cancel JV
jv_obj = get_obj('Journal Voucher', d.journal_voucher, with_children=1)
get_obj(dt='GL Control').make_gl_entries(jv_obj.doc, jv_obj.doclist, cancel =1, adv_adj = 1)
# add extra entries
self.add_extra_entry(jv_obj, d.journal_voucher, d.jv_detail_no, flt(d.allocated_amount), account_head, doctype, dr_or_cr, against_document_no)
# re-submit JV
jv_obj = get_obj('Journal Voucher', d.journal_voucher, with_children =1)
get_obj(dt='GL Control').make_gl_entries(jv_obj.doc, jv_obj.doclist, cancel = 0, adv_adj = 1)
else:
msgprint("Allocation amount cannot be greater than advance amount")
raise Exception
def get_purchase_tax_details(self,obj, default = 0):
obj.doclist = self.doc.clear_table(obj.doclist,'purchase_tax_details')
if default: add_cond = " and ifnull(t2.is_default,0) = 1"
else: add_cond = " and t1.parent = '"+cstr(obj.doc.purchase_other_charges)+"'"
other_charge = sql("""
select t1.*
from `tabPurchase Taxes and Charges` t1, `tabPurchase Taxes and Charges Master` t2
where t1.parent = t2.name %s
order by t1.idx
"""% add_cond, as_dict = 1)
idx = 0
for other in other_charge:
d = addchild(obj.doc, 'purchase_tax_details', 'Purchase Taxes and Charges', 1, obj.doclist)
d.category = other['category']
d.add_deduct_tax = other['add_deduct_tax']
d.charge_type = other['charge_type']
d.row_id = other['row_id']
d.description = other['description']
d.account_head = other['account_head']
d.rate = flt(other['rate'])
d.tax_amount = flt(other['tax_amount'])
d.idx = idx
idx += 1
return obj.doclist
def create_auto_indent(self, i , doc_type, doc_name, cur_qty):
""" Create indent on reaching reorder level """
indent = Document('Purchase Request')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Purchase Request. It was raised because the (actual + ordered + indented - reserved) quantity reaches re-order level when %s %s was created"%(doc_type,doc_name)
indent.save(1)
indent_obj = get_obj('Purchase Request',indent.name,with_children=1)
indent_details_child = addchild(indent_obj.doc,'indent_details','Purchase Request Item',0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date= add_days(nowdate(),cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
indent_details_child.qty = i['re_order_qty'] or (flt(i['re_order_level']) - flt(cur_qty))
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Purchase Request',indent.name,with_children=1)
indent_obj.validate()
set(indent_obj.doc,'docstatus',1)
indent_obj.on_submit()
msgprint("Item: " + self.doc.item_code + " is to be re-ordered. Purchase Request %s raised. It was generated from %s %s"%(indent.name,doc_type, doc_name ))
if(i['email_notify']):
send_email_notification(doc_type,doc_name)
def check_budget(self,le_list,cancel):
# get value from record
acct, cost_center, debit, credit, post_dt, cfy, company = le_list
# get allocated budget
bgt = sql("select t1.budget_allocated, t1.actual, t2.distribution_id from `tabBudget Detail` t1, `tabCost Center` t2 where t1.account='%s' and t1.parent=t2.name and t2.name = '%s' and t1.fiscal_year='%s'" % (acct,cost_center,cfy), as_dict =1)
curr_amt = ((cancel and -1 or 1) * flt(debit)) + ((cancel and 1 or -1) * flt(credit))
if bgt and bgt[0]['budget_allocated']:
# check budget flag in Company
bgt_flag = sql("select yearly_bgt_flag, monthly_bgt_flag from `tabCompany` where name = '%s'" % company, as_dict =1)
if bgt_flag and bgt_flag[0]['monthly_bgt_flag'] in ['Stop', 'Warn']:
# get start date and last date
st_date = get_value('Fiscal Year', cfy, 'year_start_date').strftime('%Y-%m-%d')
lt_date = sql("select LAST_DAY('%s')" % post_dt)
# get Actual
actual = get_obj('GL Control').get_period_difference(acct + '~~~' + cstr(st_date) + '~~~' + cstr(lt_date[0][0]), cost_center)
# Get Monthly budget
budget = self.get_monthly_budget(bgt and bgt[0]['distribution_id'] or '' , cfy, st_date, post_dt, bgt[0]['budget_allocated'])
# validate monthly budget
self.validate_budget(acct, cost_center, flt(actual) + flt(curr_amt), budget, 'monthly_bgt_flag')
# update actual against budget allocated in cost center
sql("update `tabBudget Detail` set actual = ifnull(actual,0) + %s where account = '%s' and fiscal_year = '%s' and parent = '%s'" % (curr_amt,cstr(acct),cstr(cfy),cstr(cost_center)))
def get_other_charges(self,obj, default=0):
obj.doclist = obj.doc.clear_table(obj.doclist, 'other_charges')
if not getlist(obj.doclist, 'other_charges'):
if default: add_cond = 'ifnull(t2.is_default,0) = 1'
else: add_cond = 't1.parent = "'+cstr(obj.doc.charge)+'"'
idx = 0
other_charge = webnotes.conn.sql("""\
select t1.*
from
`tabSales Taxes and Charges` t1,
`tabSales Taxes and Charges Master` t2
where
t1.parent = t2.name and
t2.company = '%s' and
%s
order by t1.idx""" % (obj.doc.company, add_cond), as_dict=1)
from webnotes.model import default_fields
for other in other_charge:
# remove default fields like parent, parenttype etc.
# from query results
for field in default_fields:
if field in other: del other[field]
d = addchild(obj.doc, 'other_charges', 'Sales Taxes and Charges',
obj.doclist)
d.fields.update(other)
d.rate = flt(d.rate)
d.tax_amount = flt(d.tax_rate)
d.included_in_print_rate = cint(d.included_in_print_rate)
d.idx = idx
idx += 1
return obj.doclist
def validate_net_pay(self):
if flt(self.doc.net_pay) < 0:
msgprint("Net pay can not be negative")
raise Exception
elif flt(self.doc.net_pay) > flt(self.doc.ctc):
msgprint("Net pay can not be greater than CTC")
raise Exception
def get_available_qty(self,args):
tot_avail_qty = webnotes.conn.sql("select projected_qty, actual_qty from `tabBin` where item_code = '%s' and warehouse = '%s'" % (args['item_code'], args['warehouse']), as_dict=1)
ret = {
'projected_qty' : tot_avail_qty and flt(tot_avail_qty[0]['projected_qty']) or 0,
'actual_qty' : tot_avail_qty and flt(tot_avail_qty[0]['actual_qty']) or 0
}
return ret
def validate_po_pr(self, d):
# check po / pr for qty and rates and currency and conversion rate
# currency, import_rate must be equal to currency, import_rate of purchase order
if d.purchase_order and not d.purchase_order in self.po_list:
# currency
currency = cstr(sql("select currency from `tabPurchase Order` where name = '%s'" % d.purchase_order)[0][0])
if not cstr(currency) == cstr(self.doc.currency):
msgprint("Purchase Order: " + cstr(d.purchase_order) + " currency : " + cstr(currency) + " does not match with currency of current document.")
raise Exception
# import_rate
rate = flt(sql('select import_rate from `tabPurchase Order Item` where item_code=%s and parent=%s and name = %s', (d.item_code, d.purchase_order, d.po_detail))[0][0])
if abs(rate - flt(d.import_rate)) > 1 and cint(get_defaults('maintain_same_rate')):
msgprint("Import Rate for %s in the Purchase Order is %s. Rate must be same as Purchase Order Rate" % (d.item_code,rate))
raise Exception
if d.purchase_receipt and not d.purchase_receipt in self.pr_list:
# currency , conversion_rate
data = sql("select currency, conversion_rate from `tabPurchase Receipt` where name = '%s'" % d.purchase_receipt, as_dict = 1)
if not cstr(data[0]['currency']) == cstr(self.doc.currency):
msgprint("Purchase Receipt: " + cstr(d.purchase_receipt) + " currency : " + cstr(data[0]['currency']) + " does not match with currency of current document.")
raise Exception
if not flt(data[0]['conversion_rate']) == flt(self.doc.conversion_rate):
msgprint("Purchase Receipt: " + cstr(d.purchase_receipt) + " conversion_rate : " + cstr(data[0]['conversion_rate']) + " does not match with conversion_rate of current document.")
raise Exception
def get_workstation_details(self,workstation):
ws = sql("select hour_rate, capacity from `tabWorkstation` where name = %s",workstation , as_dict = 1)
ret = {
'hour_rate' : ws and flt(ws[0]['hour_rate']) or '',
'workstation_capacity' : ws and flt(ws[0]['capacity']) or ''
}
return ret
def update_packing_list_item(self,obj, packing_item_code, qty, warehouse, line): bin = self.get_bin_qty(packing_item_code, warehouse) item = self.get_packing_item_details(packing_item_code) # check if exists exists = 0 for d in getlist(obj.doclist, 'packing_details'): if d.parent_item == line.item_code and d.item_code == packing_item_code and d.parent_detail_docname == line.name: pi, exists = d, 1 break if not exists: pi = addchild(obj.doc, 'packing_details', 'Delivery Note Packing Item', obj.doclist) pi.parent_item = line.item_code pi.item_code = packing_item_code pi.item_name = item['item_name'] pi.parent_detail_docname = line.name pi.description = item['description'] pi.uom = item['stock_uom'] pi.qty = flt(qty) pi.actual_qty = bin and flt(bin['actual_qty']) or 0 pi.projected_qty = bin and flt(bin['projected_qty']) or 0 pi.prevdoc_doctype = line.prevdoc_doctype if not pi.warehouse: pi.warehouse = warehouse if not pi.batch_no: pi.batch_no = cstr(line.batch_no) pi.idx = self.packing_list_idx # saved, since this function is called on_update of delivery note pi.save() self.packing_list_idx += 1
def get_outstanding(self, args):
args = eval(args)
o_s = sql("select outstanding_amount from `tab%s` where name = '%s'" % (args['doctype'],args['docname']))
if args['doctype'] == 'Purchase Invoice':
return {'debit': o_s and flt(o_s[0][0]) or 0}
if args['doctype'] == 'Sales Invoice':
return {'credit': o_s and flt(o_s[0][0]) or 0}
def get_uom_details(self, arg = ''):
"""fetches details on change of UOM"""
import json
arg, ret = json.loads(arg), {}
uom = webnotes.conn.sql("""\
select conversion_factor
from `tabUOM Conversion Detail`
where parent = %s and uom = %s""", (arg['item_code'],arg['uom']), as_dict = 1)
if not uom: return ret
last_purchase_details, last_purchase_date = self.get_last_purchase_details(arg['item_code'], arg['doc_name'])
conversion_factor = flt(uom[0]['conversion_factor'])
conversion_rate = flt(arg['conversion_rate'])
purchase_ref_rate = last_purchase_details and \
(last_purchase_details['purchase_ref_rate'] * conversion_factor) or 0
purchase_rate = last_purchase_details and \
(last_purchase_details['purchase_rate'] * conversion_factor) or 0
ret = {
'conversion_factor': conversion_factor,
'qty': flt(arg['stock_qty']) / conversion_factor,
'purchase_ref_rate': purchase_ref_rate,
'purchase_rate': purchase_rate,
'import_ref_rate': purchase_ref_rate / conversion_rate,
'import_rate': purchase_rate / conversion_rate,
}
return ret
def update_stock(self, actual_qty=0, reserved_qty=0, ordered_qty=0, indented_qty=0, planned_qty=0, dt=None, sle_id='', posting_time='', serial_no = '', is_cancelled = 'No',doc_type='',doc_name='',is_amended='No'): if not dt: dt = nowdate() # update the stock values (for current quantities) self.doc.actual_qty = flt(self.doc.actual_qty) + flt(actual_qty) self.doc.ordered_qty = flt(self.doc.ordered_qty) + flt(ordered_qty) self.doc.reserved_qty = flt(self.doc.reserved_qty) + flt(reserved_qty) self.doc.indented_qty = flt(self.doc.indented_qty) + flt(indented_qty) self.doc.planned_qty = flt(self.doc.planned_qty) + flt(planned_qty) self.doc.projected_qty = flt(self.doc.actual_qty) + flt(self.doc.ordered_qty) + flt(self.doc.indented_qty) + flt(self.doc.planned_qty) - flt(self.doc.reserved_qty) self.doc.save() if(( flt(actual_qty)<0 or flt(reserved_qty)>0 ) and is_cancelled == 'No' and is_amended=='No'): self.reorder_item(doc_type,doc_name) if actual_qty: # check actual qty with total number of serial no if serial_no: self.check_qty_with_serial_no() # update valuation and qty after transaction for post dated entry self.update_entries_after(dt, posting_time)
def make_gl_entries(self):
auto_accounting_for_stock = \
cint(webnotes.defaults.get_global_default("auto_accounting_for_stock"))
gl_entries = []
# parent's gl entry
if self.doc.grand_total:
gl_entries.append(
self.get_gl_dict({
"account": self.doc.credit_to,
"against": self.doc.against_expense_account,
"credit": self.doc.total_amount_to_pay,
"remarks": self.doc.remarks,
"against_voucher": self.doc.name,
"against_voucher_type": self.doc.doctype,
}))
# tax table gl entries
valuation_tax = {}
for tax in self.doclist.get({"parentfield": "purchase_tax_details"}):
if tax.category in ("Total", "Valuation and Total") and flt(
tax.tax_amount):
gl_entries.append(
self.get_gl_dict({
"account":
tax.account_head,
"against":
self.doc.credit_to,
"debit":
tax.add_deduct_tax == "Add" and tax.tax_amount or 0,
"credit":
tax.add_deduct_tax == "Deduct" and tax.tax_amount or 0,
"remarks":
self.doc.remarks,
"cost_center":
tax.cost_center
}))
# accumulate valuation tax
if tax.category in ("Valuation", "Valuation and Total") and flt(
tax.tax_amount):
if auto_accounting_for_stock and not tax.cost_center:
webnotes.throw(
_("Row %(row)s: Cost Center is mandatory \
if tax/charges category is Valuation or Valuation and Total" %
{"row": tax.idx}))
valuation_tax.setdefault(tax.cost_center, 0)
valuation_tax[tax.cost_center] += \
(tax.add_deduct_tax == "Add" and 1 or -1) * flt(tax.tax_amount)
# item gl entries
stock_item_and_auto_accounting_for_stock = False
stock_items = self.get_stock_items()
for item in self.doclist.get({"parentfield": "entries"}):
if auto_accounting_for_stock and item.item_code in stock_items:
if flt(item.valuation_rate):
# if auto inventory accounting enabled and stock item,
# then do stock related gl entries
# expense will be booked in sales invoice
stock_item_and_auto_accounting_for_stock = True
valuation_amt = flt(item.amount + item.item_tax_amount,
self.precision("amount", item))
gl_entries.append(
self.get_gl_dict({
"account":
item.expense_head,
"against":
self.doc.credit_to,
"debit":
valuation_amt,
"remarks":
self.doc.remarks or "Accounting Entry for Stock"
}))
elif flt(item.amount):
# if not a stock item or auto inventory accounting disabled, book the expense
gl_entries.append(
self.get_gl_dict({
"account": item.expense_head,
"against": self.doc.credit_to,
"debit": item.amount,
"remarks": self.doc.remarks,
"cost_center": item.cost_center
}))
if stock_item_and_auto_accounting_for_stock and valuation_tax:
# credit valuation tax amount in "Expenses Included In Valuation"
# this will balance out valuation amount included in cost of goods sold
expenses_included_in_valuation = \
self.get_company_default("expenses_included_in_valuation")
for cost_center, amount in valuation_tax.items():
gl_entries.append(
self.get_gl_dict({
"account":
expenses_included_in_valuation,
"cost_center":
cost_center,
"against":
self.doc.credit_to,
"credit":
amount,
"remarks":
self.doc.remarks or "Accounting Entry for Stock"
}))
# writeoff account includes petty difference in the invoice amount
# and the amount that is paid
if self.doc.write_off_account and flt(self.doc.write_off_amount):
gl_entries.append(
self.get_gl_dict({
"account": self.doc.write_off_account,
"against": self.doc.credit_to,
"credit": flt(self.doc.write_off_amount),
"remarks": self.doc.remarks,
"cost_center": self.doc.write_off_cost_center
}))
if gl_entries:
from accounts.general_ledger import make_gl_entries
make_gl_entries(gl_entries, cancel=(self.doc.docstatus == 2))
def calculate_total_tds(self):
total = 0
for d in getlist(self.doclist, 'tds_payment_details'):
total = flt(total) + flt(d.total_tax_amount)
self.doc.total_tds = total
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
if data.index(self.head_row) != 0:
data = data[data.index(self.head_row):]
self.doc.reconciliation_json = json.dumps(data)
def _get_msg(row_num, msg):
return _("Row # ") + ("%d: " % (row_num + 2)) + _(msg)
self.validation_messages = []
item_warehouse_combinations = []
# validate no of rows
rows = data[data.index(self.head_row) + 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)
# 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 webnotes.ValidationError
def update_qty(self, args):
# update the stock values (for current quantities)
self.doc.actual_qty = flt(self.doc.actual_qty) + flt(
args.get("actual_qty", 0))
self.doc.ordered_qty = flt(self.doc.ordered_qty) + flt(
args.get("ordered_qty", 0))
self.doc.reserved_qty = flt(self.doc.reserved_qty) + flt(
args.get("reserved_qty"))
self.doc.indented_qty = flt(self.doc.indented_qty) + flt(
args.get("indented_qty"))
self.doc.planned_qty = flt(self.doc.planned_qty) + flt(
args.get("planned_qty"))
self.doc.projected_qty = flt(self.doc.actual_qty) + flt(self.doc.ordered_qty) + \
flt(self.doc.indented_qty) + flt(self.doc.planned_qty) - flt(self.doc.reserved_qty)
self.doc.save()
def update_entries_after(self, posting_date, posting_time, verbose=1):
"""
update valution rate and qty after transaction
from the current time-bucket onwards
"""
# Get prev sle
prev_sle = self.get_sle_prev_timebucket(posting_date, posting_time)
# if no prev sle, start from the first one (for repost)
if not prev_sle:
cqty, cval, val_rate, stock_val, self.fcfs_bal = 0, 0, 0, 0, []
# normal
else:
cqty = flt(prev_sle.get('bin_aqat', 0))
cval = flt(prev_sle.get('stock_value', 0))
val_rate = flt(prev_sle.get('valuation_rate', 0))
self.fcfs_bal = eval(prev_sle.get('fcfs_stack', '[]') or '[]')
# get valuation method
val_method = get_obj('Valuation Control').get_valuation_method(
self.doc.item_code)
# allow negative stock (only for moving average method)
from webnotes.utils import get_defaults
allow_negative_stock = get_defaults().get('allow_negative_stock', 0)
# recalculate the balances for all stock ledger entries
# after the prev sle
sll = sql("""
select *
from `tabStock Ledger Entry`
where item_code = %s
and warehouse = %s
and ifnull(is_cancelled, 'No') = 'No'
and timestamp(posting_date, posting_time) > timestamp(%s, %s)
order by timestamp(posting_date, posting_time) asc, name asc""", \
(self.doc.item_code, self.doc.warehouse, \
prev_sle.get('posting_date','1900-01-01'), \
prev_sle.get('posting_time', '12:00')), as_dict = 1)
self.exc_list = []
for sle in sll:
# block if stock level goes negative on any date
if (val_method != 'Moving Average') or (cint(allow_negative_stock)
== 0):
if self.validate_negative_stock(cqty, sle):
cqty += sle['actual_qty']
continue
stock_val, in_rate = 0, sle['incoming_rate'] # IN
serial_nos = sle["serial_no"] and ("'"+"', '".join(cstr(sle["serial_no"]).split('\n')) \
+ "'") or ''
# Get valuation rate
val_rate, in_rate = self.get_valuation_rate(val_method, serial_nos, \
val_rate, in_rate, stock_val, cqty, sle)
# Qty upto the sle
cqty += sle['actual_qty']
# Stock Value upto the sle
stock_val = self.get_stock_value(val_method, cqty, val_rate,
serial_nos)
# update current sle
sql("""update `tabStock Ledger Entry`
set bin_aqat=%s, valuation_rate=%s, fcfs_stack=%s, stock_value=%s,
incoming_rate = %s where name=%s""", \
(cqty, flt(val_rate), cstr(self.fcfs_bal), stock_val, in_rate, sle['name']))
if self.exc_list:
deficiency = min(e["diff"] for e in self.exc_list)
msg = """Negative stock error:
Cannot complete this transaction because stock will start
becoming negative (%s) for Item <b>%s</b> in Warehouse
<b>%s</b> on <b>%s %s</b> in Transaction %s %s.
Total Quantity Deficiency: <b>%s</b>""" % \
(self.exc_list[0]["diff"], self.doc.item_code, self.doc.warehouse,
self.exc_list[0]["posting_date"], self.exc_list[0]["posting_time"],
self.exc_list[0]["voucher_type"], self.exc_list[0]["voucher_no"],
abs(deficiency))
if verbose:
msgprint(msg, raise_exception=1)
else:
raise webnotes.ValidationError, msg
# update the bin
if sll or not prev_sle:
sql(
"""update `tabBin` set valuation_rate=%s, actual_qty=%s, stock_value = %s,
projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty -
reserved_qty) where name=%s
""", (flt(val_rate), cqty, flt(stock_val), self.doc.name))
def get_fifo_inventory_values(self, in_rate, actual_qty):
# add batch to fcfs balance
if actual_qty > 0:
self.fcfs_bal.append([flt(actual_qty), flt(in_rate)])
# remove from fcfs balance
else:
incoming_cost = 0
withdraw = flt(abs(actual_qty))
while withdraw:
if not self.fcfs_bal:
break # nothing in store
batch = self.fcfs_bal[0]
if batch[0] <= withdraw:
# not enough or exactly same qty in current batch, clear batch
incoming_cost += flt(batch[1]) * flt(batch[0])
withdraw -= batch[0]
self.fcfs_bal.pop(0)
else:
# all from current batch
incoming_cost += flt(batch[1]) * flt(withdraw)
batch[0] -= withdraw
withdraw = 0
in_rate = incoming_cost / flt(abs(actual_qty))
fcfs_val = sum([flt(d[0]) * flt(d[1]) for d in self.fcfs_bal])
fcfs_qty = sum([flt(d[0]) for d in self.fcfs_bal])
val_rate = fcfs_qty and fcfs_val / fcfs_qty or 0
return val_rate, in_rate
def validate_accepted_rejected_qty(self):
for d in getlist(self.doclist, "purchase_receipt_details"):
if not flt(d.received_qty) and flt(d.qty):
d.received_qty = flt(d.qty) - flt(d.rejected_qty)
elif not flt(d.qty) and flt(d.rejected_qty):
d.qty = flt(d.received_qty) - flt(d.rejected_qty)
elif not flt(d.rejected_qty):
d.rejected_qty = flt(d.received_qty) - flt(d.qty)
# Check Received Qty = Accepted Qty + Rejected Qty
if ((flt(d.qty) + flt(d.rejected_qty)) != flt(d.received_qty)):
msgprint(
"Sum of Accepted Qty and Rejected Qty must be equal to Received quantity. Error for Item: "
+ cstr(d.item_code))
raise Exception
def get_item_list(self, obj, is_stopped=0):
"""get item list"""
il = []
for d in getlist(obj.doclist, obj.fname):
reserved_wh, reserved_qty = '', 0 # used for delivery note
qty = flt(d.qty)
if is_stopped:
qty = flt(d.qty) > flt(d.delivered_qty) and flt(
flt(d.qty) - flt(d.delivered_qty)) or 0
if d.prevdoc_doctype == 'Sales Order':
# used in delivery note to reduce reserved_qty
# Eg.: if SO qty is 10 and there is tolerance of 20%, then it will allow DN of 12.
# But in this case reserved qty should only be reduced by 10 and not 12.
tot_qty, max_qty, tot_amt, max_amt, reserved_wh = self.get_curr_and_ref_doc_details(
d.doctype, 'prevdoc_detail_docname',
d.prevdoc_detail_docname, obj.doc.name, obj.doc.doctype)
if ((flt(tot_qty) + flt(qty) > flt(max_qty))):
reserved_qty = -(flt(max_qty) - flt(tot_qty))
else:
reserved_qty = -flt(qty)
if obj.doc.doctype == 'Sales Order':
reserved_wh = d.reserved_warehouse
if self.has_sales_bom(d.item_code):
for p in getlist(obj.doclist, 'packing_details'):
if p.parent_detail_docname == d.name and p.parent_item == d.item_code:
# the packing details table's qty is already multiplied with parent's qty
il.append({
'warehouse':
p.warehouse,
'reserved_warehouse':
reserved_wh,
'item_code':
p.item_code,
'qty':
flt(p.qty),
'reserved_qty':
(flt(p.qty) / qty) * (reserved_qty),
'uom':
p.uom,
'batch_no':
p.batch_no,
'serial_no':
p.serial_no,
'name':
d.name
})
else:
il.append({
'warehouse': d.warehouse,
'reserved_warehouse': reserved_wh,
'item_code': d.item_code,
'qty': qty,
'reserved_qty': reserved_qty,
'uom': d.stock_uom,
'batch_no': d.batch_no,
'serial_no': d.serial_no,
'name': d.name
})
return il
def update_entries_after(self, posting_date, posting_time):
"""
update valution rate and qty after transaction
from the current time-bucket onwards
"""
# Get prev sle
prev_sle = self.get_sle_prev_timebucket(posting_date, posting_time)
# if no prev sle, start from the first one (for repost)
if not prev_sle:
cqty, cval, val_rate, stock_val, self.fcfs_bal = 0, 0, 0, 0, []
# normal
else:
cqty = flt(prev_sle.get('bin_aqat', 0))
cval =flt(prev_sle.get('stock_value', 0))
val_rate = flt(prev_sle.get('valuation_rate', 0))
self.fcfs_bal = eval(prev_sle.get('fcfs_stack', '[]') or '[]')
# get valuation method
val_method = get_obj('Valuation Control').get_valuation_method(self.doc.item_code)
# allow negative stock (only for moving average method)
from webnotes.utils import get_defaults
allow_negative_stock = get_defaults().get('allow_negative_stock', 0)
# recalculate the balances for all stock ledger entries
# after the prev sle
sll = sql("""
select *
from `tabStock Ledger Entry`
where item_code = %s
and warehouse = %s
and ifnull(is_cancelled, 'No') = 'No'
and timestamp(posting_date, posting_time) > timestamp(%s, %s)
order by timestamp(posting_date, posting_time) asc, name asc""", \
(self.doc.item_code, self.doc.warehouse, \
prev_sle.get('posting_date','1900-01-01'), prev_sle.get('posting_time', '12:00')), as_dict = 1)
for sle in sll:
# block if stock level goes negative on any date
if val_method != 'Moving Average' or flt(allow_negative_stock) == 0:
self.validate_negative_stock(cqty, sle)
stock_val, in_rate = 0, sle['incoming_rate'] # IN
serial_nos = sle["serial_no"] and ("'"+"', '".join(cstr(sle["serial_no"]).split('\n')) \
+ "'") or ''
# Get valuation rate
val_rate, stock_val = self.get_valuation_rate(val_method, serial_nos, \
val_rate, in_rate, stock_val, cqty, sle)
# Qty upto the sle
cqty += sle['actual_qty']
# Stock Value upto the sle
stock_val = self.get_stock_value(val_method, cqty, stock_val, serial_nos)
# update current sle --> will it be good to update incoming rate in sle
# for outgoing stock entry?????
sql("""update `tabStock Ledger Entry`
set bin_aqat=%s, valuation_rate=%s, fcfs_stack=%s, stock_value=%s
where name=%s""", (cqty, flt(val_rate), cstr(self.fcfs_bal), stock_val, sle['name']))
# update the bin
if sll or not prev_sle:
sql("update `tabBin` set valuation_rate=%s, actual_qty=%s, stock_value = %s, projected_qty = (actual_qty + indented_qty + ordered_qty + planned_qty - reserved_qty) where name=%s", \
(flt(val_rate), cqty, flt(stock_val), self.doc.name))
def get_item_details(self, args, obj):
import json
if not obj.doc.price_list_name:
msgprint("Please Select Price List before selecting Items")
raise Exception
item = webnotes.conn.sql(
"select description, item_name, brand, item_group, stock_uom, default_warehouse, default_income_account, default_sales_cost_center, description_html, barcode from `tabItem` where name = '%s' and (ifnull(end_of_life,'')='' or end_of_life > now() or end_of_life = '0000-00-00') and (is_sales_item = 'Yes' or is_service_item = 'Yes')"
% (args['item_code']),
as_dict=1)
tax = webnotes.conn.sql(
"select tax_type, tax_rate from `tabItem Tax` where parent = %s",
args['item_code'])
t = {}
for x in tax:
t[x[0]] = flt(x[1])
ret = {
'description':
item and item[0]['description_html'] or item[0]['description'],
'barcode':
item and item[0]['barcode'] or '',
'item_group':
item and item[0]['item_group'] or '',
'item_name':
item and item[0]['item_name'] or '',
'brand':
item and item[0]['brand'] or '',
'stock_uom':
item and item[0]['stock_uom'] or '',
'reserved_warehouse':
item and item[0]['default_warehouse'] or '',
'warehouse':
item and item[0]['default_warehouse'] or args.get('warehouse'),
'income_account':
item and item[0]['default_income_account']
or args.get('income_account'),
'cost_center':
item and item[0]['default_sales_cost_center']
or args.get('cost_center'),
'qty':
1.00, # this is done coz if item once fetched is fetched again thn its qty shld be reset to 1
'adj_rate':
0,
'amount':
0,
'export_amount':
0,
'item_tax_rate':
json.dumps(t),
'batch_no':
''
}
if (
obj.doc.price_list_name and item
): #this is done to fetch the changed BASIC RATE and REF RATE based on PRICE LIST
base_ref_rate = self.get_ref_rate(args['item_code'],
obj.doc.price_list_name,
obj.doc.price_list_currency,
obj.doc.plc_conversion_rate)
ret['ref_rate'] = flt(base_ref_rate) / flt(obj.doc.conversion_rate)
ret['export_rate'] = flt(base_ref_rate) / flt(
obj.doc.conversion_rate)
ret['base_ref_rate'] = flt(base_ref_rate)
ret['basic_rate'] = flt(base_ref_rate)
if ret['warehouse'] or ret['reserved_warehouse']:
av_qty = self.get_available_qty({
'item_code':
args['item_code'],
'warehouse':
ret['warehouse'] or ret['reserved_warehouse']
})
ret.update(av_qty)
# get customer code for given item from Item Customer Detail
customer_item_code_row = webnotes.conn.sql(
"""\
select ref_code from `tabItem Customer Detail`
where parent = %s and customer_name = %s""",
(args['item_code'], obj.doc.customer))
if customer_item_code_row and customer_item_code_row[0][0]:
ret['customer_item_code'] = customer_item_code_row[0][0]
return ret
def _get_actual_qty(self):
return flt(webnotes.conn.get_value("Bin", {"item_code": "_Test Item",
"warehouse": "_Test Warehouse - _TC"}, "actual_qty"))
def update_bin(self, is_submit, is_stopped=0):
pc_obj = get_obj('Purchase Common')
for d in getlist(self.doclist, 'po_details'):
#1. Check if is_stock_item == 'Yes'
if sql("select is_stock_item from tabItem where name=%s",
d.item_code)[0][0] == 'Yes':
ind_qty, po_qty = 0, flt(d.qty) * flt(d.conversion_factor)
if is_stopped:
po_qty = flt(d.qty) > flt(d.received_qty) and flt(
flt(flt(d.qty) - flt(d.received_qty)) *
flt(d.conversion_factor)) or 0
# No updates in Indent on Stop / Unstop
if cstr(d.prevdoc_doctype) == 'Indent' and not is_stopped:
# get qty and pending_qty of prevdoc
curr_ref_qty = pc_obj.get_qty(d.doctype,
'prevdoc_detail_docname',
d.prevdoc_detail_docname,
'Indent Detail',
'Indent - Purchase Order',
self.doc.name)
max_qty, qty, curr_qty = flt(
curr_ref_qty.split('~~~')[1]), flt(
curr_ref_qty.split('~~~')[0]), 0
if flt(qty) + flt(po_qty) > flt(max_qty):
curr_qty = flt(max_qty) - flt(qty)
# special case as there is no restriction for Indent - Purchase Order
curr_qty = (curr_qty > 0) and curr_qty or 0
else:
curr_qty = flt(po_qty)
ind_qty = -flt(curr_qty)
#==> Update Bin's Indent Qty by +- ind_qty and Ordered Qty by +- qty
get_obj('Warehouse', d.warehouse).update_bin(
0, 0, (is_submit and 1 or -1) * flt(po_qty),
(is_submit and 1 or -1) * flt(ind_qty), 0, d.item_code,
self.doc.transaction_date)
def get_adj_percent(self, obj):
for d in getlist(obj.doclist, obj.fname):
base_ref_rate = self.get_ref_rate(d.item_code,
obj.doc.price_list_name,
obj.doc.price_list_currency,
obj.doc.plc_conversion_rate)
d.adj_rate = 0
d.ref_rate = flt(base_ref_rate) / flt(obj.doc.conversion_rate)
d.basic_rate = flt(base_ref_rate)
d.base_ref_rate = flt(base_ref_rate)
d.export_rate = flt(base_ref_rate) / flt(obj.doc.conversion_rate)
d.amount = flt(d.qty) * flt(base_ref_rate)
d.export_amount = flt(d.qty) * flt(base_ref_rate) / flt(
obj.doc.conversion_rate)
def validate(self):
for d in getlist(self.doclist, 'target_details'):
if not flt(d.target_qty) and not flt(d.target_amount):
webnotes.msgprint(
"Either target qty or target amount is mandatory.")
raise Exception
def add_bom(self, d):
#----- fetching default bom from Bill of Materials instead of Item Master --
bom_det = sql(
"select t1.item, t2.item_code, t2.qty_consumed_per_unit, t2.moving_avg_rate, t2.value_as_per_mar, t2.stock_uom, t2.name, t2.parent from `tabBill Of Materials` t1, `tabBOM Material` t2 where t2.parent = t1.name and t1.item = '%s' and ifnull(t1.is_default,0) = 1 and t1.docstatus = 1"
% d.item_code)
if not bom_det:
msgprint("No default BOM exists for item: %s" % d.item_code)
raise Exception
else:
#-------------- add child function--------------------
chgd_rqd_qty = []
for i in bom_det:
if i and not sql(
"select name from `tabPO Raw Material Detail` where reference_name = '%s' and bom_detail_no = '%s' and parent = '%s' "
% (d.name, i[6], self.doc.name)):
rm_child = addchild(self.doc, 'po_raw_material_details',
'PO Raw Material Detail', 1,
self.doclist)
rm_child.reference_name = d.name
rm_child.bom_detail_no = i and i[6] or ''
rm_child.main_item_code = i and i[0] or ''
rm_child.rm_item_code = i and i[1] or ''
rm_child.stock_uom = i and i[5] or ''
rm_child.rate = i and flt(i[3]) or flt(i[4])
rm_child.conversion_factor = d.conversion_factor
rm_child.required_qty = flt(i and flt(i[2]) or 0) * flt(
d.qty) * flt(d.conversion_factor)
rm_child.amount = flt(
flt(rm_child.consumed_qty) * flt(rm_child.rate))
rm_child.save()
chgd_rqd_qty.append(cstr(i[1]))
else:
act_qty = flt(i and flt(i[2]) or 0) * flt(d.qty) * flt(
d.conversion_factor)
for po_rmd in getlist(self.doclist,
'po_raw_material_details'):
if i and i[6] == po_rmd.bom_detail_no and (
flt(act_qty) != flt(po_rmd.required_qty)
or i[1] != po_rmd.rm_item_code):
chgd_rqd_qty.append(cstr(i[1]))
po_rmd.main_item_code = i[0]
po_rmd.rm_item_code = i[1]
po_rmd.stock_uom = i[5]
po_rmd.required_qty = flt(act_qty)
po_rmd.rate = i and flt(i[3]) or flt(i[4])
po_rmd.amount = flt(
flt(po_rmd.consumed_qty) * flt(po_rmd.rate))
def get_rates(self):
for d in getlist(self.doclist, "sales_bom_items"):
r = sql(
"select ref_rate from `tabRef Rate Detail` where price_list_name=%s and parent=%s and ref_currency = %s",
(self.doc.price_list, d.item_code, self.doc.currency))
d.rate = r and flt(r[0][0]) or 0.00
def get_tds_amount(self, obj):
# Validate if posting date b4 first tds entry for this category
self.validate_first_entry(obj)
# get current amount and supplier head
if obj.doc.doctype == 'Purchase Invoice':
supplier_account = obj.doc.credit_to
total_amount = flt(obj.doc.grand_total)
for d in getlist(obj.doclist, 'advance_allocation_details'):
if flt(d.tds_amount) != 0:
total_amount -= flt(d.allocated_amount)
elif obj.doc.doctype == 'Journal Voucher':
supplier_account = obj.doc.supplier_account
total_amount = obj.doc.total_debit
if obj.doc.tds_category:
# get total billed
total_billed = 0
pv = sql(
"select sum(ifnull(grand_total,0)), sum(ifnull(ded_amount,0)) from `tabPurchase Invoice` where tds_category = %s and credit_to = %s and fiscal_year = %s and docstatus = 1 and name != %s and is_opening != 'Yes'",
(obj.doc.tds_category, supplier_account, obj.doc.fiscal_year,
obj.doc.name))
jv = sql(
"select sum(ifnull(total_debit,0)), sum(ifnull(ded_amount,0)) from `tabJournal Voucher` where tds_category = %s and supplier_account = %s and fiscal_year = %s and docstatus = 1 and name != %s and is_opening != 'Yes'",
(obj.doc.tds_category, supplier_account, obj.doc.fiscal_year,
obj.doc.name))
tds_in_pv = pv and pv[0][1] or 0
tds_in_jv = jv and jv[0][1] or 0
total_billed += flt(pv and pv[0][0] or 0) + flt(
jv and jv[0][0] or 0) + flt(total_amount)
# get slab
slab = sql(
"SELECT * FROM `tabTDS Rate Detail` t1, `tabTDS Rate Chart` t2 WHERE t1.category = '%s' AND t1.parent=t2.name and t2.applicable_from <= '%s' ORDER BY t2.applicable_from DESC LIMIT 1"
% (obj.doc.tds_category, obj.doc.posting_date),
as_dict=1)
if slab and flt(slab[0]['slab_from']) <= total_billed:
if flt(tds_in_pv) <= 0 and flt(tds_in_jv) <= 0:
total_amount = total_billed
slab = slab[0]
# special tds rate
special_tds = sql(
"select special_tds_rate, special_tds_limit, special_tds_rate_applicable from `tabTDS Detail` where parent = '%s' and tds_category = '%s'"
% (supplier_account, obj.doc.tds_category))
# get_pan_number
pan_no = sql(
"select pan_number from `tabAccount` where name = '%s'" %
supplier_account)
pan_no = pan_no and cstr(pan_no[0][0]) or ''
if not pan_no and flt(slab.get('rate_without_pan')):
msgprint(
"As there is no PAN number mentioned in the account head: %s, TDS amount will be calculated at rate %s%%"
% (supplier_account, cstr(slab['rate_without_pan'])))
tds_rate = flt(slab.get('rate_without_pan'))
elif special_tds and special_tds[0][2] == 'Yes' and (
flt(special_tds[0][1]) == 0
or flt(special_tds[0][1]) >= flt(total_amount)):
tds_rate = flt(special_tds[0][0])
else:
tds_rate = flt(slab['rate'])
# calculate tds amount
if flt(slab['rate']):
ac = sql(
"SELECT account_head FROM `tabTDS Category Account` where parent=%s and company=%s",
(obj.doc.tds_category, obj.doc.company))
if ac:
obj.doc.tax_code = ac[0][0]
obj.doc.rate = tds_rate
obj.doc.ded_amount = round(
flt(tds_rate) * flt(total_amount) / 100)
else:
msgprint(
"TDS Account not selected in TDS Category %s" %
(obj.doc.tds_category))
raise Exception
def make_items_dict(self, item_list):
for i in item_list:
self.item_dict[i[0]] = [
(flt(self.item_dict.get(i[0], [0])[0]) + flt(i[1])), i[2],
i[3], i[4]
]
class DocType:
def __init__(self, doc, doclist=[]):
self.doc = doc
self.doclist = doclist
self.validated = 1
self.data = []
self.val_method = get_defaults()['valuation_method']
def get_template(self):
if self.val_method == 'Moving Average':
return [['Item Code', 'Warehouse', 'Quantity', 'Valuation Rate']]
else:
return [['Item Code', 'Warehouse', 'Quantity', 'Incoming Rate']]
def read_csv_content(self, submit = 1):
"""Get csv data"""
if submit:
from webnotes.utils.datautils import read_csv_content_from_attached_file
data = read_csv_content_from_attached_file(self.doc)
else:
from webnotes.utils.datautils import read_csv_content
data = read_csv_content(self.doc.diff_info)
return data
def convert_into_list(self, data, submit=1):
"""Convert csv data into list"""
count = 1
for s in data:
count += 1
if count == 2 and submit:
if cstr(s[0]).strip() != 'Item Code' or cstr(s[1]).strip() != 'Warehouse':
msgprint("First row of the attachment always should be same as \
template(Item Code, Warehouse, Quantity \
and Valuation Rate/Incoming Rate)", raise_exception=1)
else:
continue
# validate
if (submit and len(s) != 4) or (not submit and len(s) != 6):
msgprint("Data entered at Row No " + cstr(count) + " in Attachment File is not in correct format.", raise_exception=1)
self.validated = 0
self.validate_item(s[0], count)
self.validate_warehouse(s[1], count)
self.data.append(s)
if not self.validated:
raise Exception
def get_reconciliation_data(self, submit = 1):
"""Read and validate csv data"""
data = self.read_csv_content(submit)
self.convert_into_list(data, submit)
def validate_item(self, item, count):
""" Validate item exists and non-serialized"""
det = sql("select item_code, has_serial_no from `tabItem` where name = %s", cstr(item), as_dict = 1)
if not det:
msgprint("Item: " + cstr(item) + " mentioned at Row No. " + cstr(count) + "does not exist in the system")
self.validated = 0
elif det and det[0]['has_serial_no'] == 'Yes':
msgprint("""You cannot make Stock Reconciliation of items having serial no. \n
You can directly upload serial no to update their inventory. \n
Please remove Item Code : %s at Row No. %s""" %(cstr(item), cstr(count)))
self.validated = 0
def validate_warehouse(self, wh, count,):
"""Validate warehouse exists"""
if not sql("select name from `tabWarehouse` where name = %s", cstr(wh)):
msgprint("Warehouse: " + cstr(wh) + " mentioned at Row No. " + cstr(count) + " does not exist in the system")
self.validated = 0
def validate(self):
"""Validate attachment data"""
if self.doc.file_list:
self.get_reconciliation_data()
def get_system_stock(self, it, wh):
"""get actual qty on reconciliation date and time as per system"""
bin = sql("select name from tabBin where item_code=%s and warehouse=%s", (it, wh))
prev_sle = bin and get_obj('Bin', bin[0][0]).get_prev_sle(self.doc.reconciliation_date, self.doc.reconciliation_time) or {}
return {
'actual_qty': prev_sle.get('bin_aqat', 0),
'stock_uom' : sql("select stock_uom from tabItem where name = %s", it)[0][0],
'val_rate' : prev_sle.get('valuation_rate', 0)
}
def get_incoming_rate(self, row, qty_diff, sys_stock):
"""Calculate incoming rate to maintain valuation rate"""
if qty_diff:
if self.val_method == 'Moving Average':
in_rate = flt(row[3]) + (flt(sys_stock['actual_qty'])*(flt(row[3]) - flt(sys_stock['val_rate'])))/ flt(qty_diff)
elif not sys_stock and not row[3]:
msgprint("Incoming Rate is mandatory for item: %s and warehouse: %s" % (rpw[0], row[1]), raise_exception=1)
else:
in_rate = qty_diff > 0 and row[3] or 0
else:
in_rate = 0
return in_rate
def make_sl_entry(self, row, qty_diff, sys_stock):
"""Make stock ledger entry"""
in_rate = self.get_incoming_rate(row, qty_diff, sys_stock)
values = [{
'item_code' : row[0],
'warehouse' : row[1],
'transaction_date' : nowdate(),
'posting_date' : self.doc.reconciliation_date,
'posting_time' : self.doc.reconciliation_time,
'voucher_type' : self.doc.doctype,
'voucher_no' : self.doc.name,
'voucher_detail_no' : self.doc.name,
'actual_qty' : flt(qty_diff),
'stock_uom' : sys_stock['stock_uom'],
'incoming_rate' : in_rate,
'company' : get_defaults()['company'],
'fiscal_year' : get_defaults()['fiscal_year'],
'is_cancelled' : 'No',
'batch_no' : '',
'serial_no' : ''
}]
get_obj('Stock Ledger', 'Stock Ledger').update_stock(values)
def make_entry_for_valuation(self, row, sys_stock):
self.make_sl_entry(row, 1, sys_stock)
sys_stock['val_rate'] = row[3]
sys_stock['actual_qty'] += 1
self.make_sl_entry(row, -1, sys_stock)
def do_stock_reco(self):
"""
Make stock entry of qty diff, calculate incoming rate to maintain valuation rate.
If no qty diff, but diff in valuation rate, make (+1,-1) entry to update valuation
"""
self.diff_info = ''
for row in self.data:
# Get qty as per system
sys_stock = self.get_system_stock(row[0],row[1])
# Diff between file and system
qty_diff = row[2] != '~' and flt(row[2]) - flt(sys_stock['actual_qty']) or 0
rate_diff = row[3] != '~' and flt(row[3]) - flt(sys_stock['val_rate']) or 0
# Make sl entry
if qty_diff:
self.make_sl_entry(row, qty_diff, sys_stock)
sys_stock['actual_qty'] += qty_diff
if (not qty_diff and rate_diff) or qty_diff < 0 and self.val_method == 'Moving Average':
self.make_entry_for_valuation(row, sys_stock)
r = [cstr(i) for i in row] + [cstr(qty_diff), cstr(rate_diff)]
self.store_diff_info(r)
msgprint("Stock Reconciliation Completed Successfully...")
def store_diff_info(self, r):
"""Add diffs column in attached file"""
# add header
if not self.diff_info:
if self.val_method == 'Moving Average':
self.diff_info += "Item Code, Warehouse, Qty, Valuation Rate, Qty Diff, Rate Diff"
else:
self.diff_info += "Item Code, Warehouse, Qty, Incoming Rate, Qty Diff, Rate Diff"
# add data
self.diff_info += "\n" + ','.join(r)
webnotes.conn.set(self.doc, 'diff_info', self.diff_info)
def on_submit(self):
if not self.doc.file_list:
msgprint("Please attach file before submitting.", raise_exception=1)
else:
self.do_stock_reco()
def on_cancel(self):
self.cancel_stock_ledger_entries()
self.update_entries_after()
def cancel_stock_ledger_entries(self):
webnotes.conn.sql("""
update `tabStock Ledger Entry`
set is_cancelled = 'Yes'
where voucher_type = 'Stock Reconciliation' and voucher_no = %s
""", self.doc.name)
def update_entries_after(self):
# get distinct combination of item_code and warehouse to update bin
item_warehouse = webnotes.conn.sql("""select distinct item_code, warehouse
from `tabStock Ledger Entry` where voucher_no = %s and is_cancelled = 'Yes'
and voucher_type = 'Stock Reconciliation'""", self.doc.name)
from webnotes.model.code import get_obj
errors = []
for d in item_warehouse:
bin = webnotes.conn.sql("select name from `tabBin` where item_code = %s and \
warehouse = %s", (d[0], d[1]))
try:
get_obj('Bin',
bin[0][0]).update_entries_after(self.doc.reconciliation_date,
self.doc.reconciliation_time, verbose=0)
except webnotes.ValidationError, e:
errors.append([d[0], d[1], e])
if errors:
import re
error_msg = [["Item Code", "Warehouse", "Qty"]]
qty_regex = re.compile(": <b>(.*)</b>")
for e in errors:
qty = qty_regex.findall(unicode(e[2]))
qty = qty and abs(flt(qty[0])) or None
error_msg.append([e[0], e[1], flt(qty)])
webnotes.msgprint("""Your stock is going into negative value \
in a future transaction.
To cancel, you need to create a stock entry with the \
following values on %s %s""" % \
(formatdate(self.doc.reconciliation_date), self.doc.reconciliation_time))
webnotes.msgprint(error_msg, as_table=1, raise_exception=1)
def validate_write_off_account(self):
if flt(self.doc.write_off_amount) and not self.doc.write_off_account:
msgprint("Please enter Write Off Account", raise_exception=1)
def validate_approving_authority(self,
doctype_name,
company,
total,
doc_obj=''):
av_dis = 0
if doc_obj:
ref_rate, basic_rate = 0, 0
for d in getlist(doc_obj.doclist, doc_obj.fname):
if d.base_ref_rate and d.basic_rate:
ref_rate += flt(d.base_ref_rate)
basic_rate += flt(d.basic_rate)
if ref_rate: av_dis = 100 - flt(basic_rate * 100 / ref_rate)
final_based_on = [
'Grand Total', 'Average Discount', 'Customerwise Discount',
'Itemwise Discount'
]
# Individual User
# ================
# Check for authorization set for individual user
based_on = [
x[0] for x in webnotes.conn.sql(
"select distinct based_on from `tabAuthorization Rule` where transaction = %s and system_user = %s and (company = %s or ifnull(company,'')='') and docstatus != 2",
(doctype_name, session['user'], company))
]
for d in based_on:
self.bifurcate_based_on_type(doctype_name, total, av_dis, d,
doc_obj, 1, company)
# Remove user specific rules from global authorization rules
for r in based_on:
if r in final_based_on and r != 'Itemwise Discount':
final_based_on.remove(r)
# Specific Role
# ===============
# Check for authorization set on particular roles
based_on = [
x[0] for x in webnotes.conn.sql(
"""select based_on
from `tabAuthorization Rule`
where transaction = %s and system_role IN (%s) and based_on IN (%s)
and (company = %s or ifnull(company,'')='')
and docstatus != 2
""" % ('%s', "'" + "','".join(webnotes.user.get_roles()) + "'", "'" +
"','".join(final_based_on) + "'", '%s'), (doctype_name, company))
]
for d in based_on:
self.bifurcate_based_on_type(doctype_name, total, av_dis, d,
doc_obj, 2, company)
# Remove role specific rules from global authorization rules
for r in based_on:
if r in final_based_on and r != 'Itemwise Discount':
final_based_on.remove(r)
# Global Rule
# =============
# Check for global authorization
for g in final_based_on:
self.bifurcate_based_on_type(doctype_name, total, av_dis, g,
doc_obj, 0, company)
def calculate_total_invoiced_amount(self):
total = 0
for d in getlist(self.doclist, 'invoice_details'):
total += flt(d.grand_total)
webnotes.conn.set(self.doc, 'total_invoiced_amount', total)
def check_value(self, ref_dt, ref_dn, ref_item_dn, val, item_code):
ref_val = webnotes.conn.get_value(ref_dt + " Item", ref_item_dn, "export_rate")
if flt(ref_val, 2) != flt(val, 2):
msgprint(_("Rate is not matching with ") + ref_dt + ": " + ref_dn +
_(" for item: ") + item_code, raise_exception=True)
def execute(filters=None):
if not filters: filters = {}
invoice_list = get_invoices(filters)
columns, expense_accounts, tax_accounts = get_columns(invoice_list)
if not invoice_list:
msgprint(_("No record found"))
return columns, invoice_list
invoice_expense_map = get_invoice_expense_map(invoice_list)
invoice_expense_map, invoice_tax_map = get_invoice_tax_map(
invoice_list, invoice_expense_map, expense_accounts)
invoice_po_pr_map = get_invoice_po_pr_map(invoice_list)
account_map = get_account_details(invoice_list)
data = []
for inv in invoice_list:
# invoice details
purchase_order = list(
set(invoice_po_pr_map.get(inv.name, {}).get("purchase_order", [])))
purchase_receipt = list(
set(
invoice_po_pr_map.get(inv.name,
{}).get("purchase_receipt", [])))
project_name = list(
set(invoice_po_pr_map.get(inv.name, {}).get("project_name", [])))
row = [
inv.name, inv.posting_date, inv.supplier, inv.supplier_name,
inv.credit_to,
account_map.get(inv.credit_to), ", ".join(project_name),
inv.bill_no, inv.bill_date, inv.remarks, ", ".join(purchase_order),
", ".join(purchase_receipt)
]
# map expense values
net_total = 0
for expense_acc in expense_accounts:
expense_amount = flt(
invoice_expense_map.get(inv.name, {}).get(expense_acc))
net_total += expense_amount
row.append(expense_amount)
# net total
row.append(net_total or inv.net_total)
# tax account
total_tax = 0
for tax_acc in tax_accounts:
if tax_acc not in expense_accounts:
tax_amount = flt(
invoice_tax_map.get(inv.name, {}).get(tax_acc))
total_tax += tax_amount
row.append(tax_amount)
# total tax, grand total, outstanding amount & rounded total
row += [
total_tax, inv.grand_total,
flt(inv.grand_total, 2), inv.outstanding_amount
]
data.append(row)
# raise Exception
return columns, data
def get_total_allocated_leaves(self):
leave_det = self.get_carry_forwarded_leaves()
webnotes.conn.set(self.doc, 'carry_forwarded_leaves',
flt(leave_det['carry_forwarded_leaves']))
webnotes.conn.set(self.doc, 'total_leaves_allocated',
flt(leave_det['total_leaves_allocated']))
def send_mail_funct(self):
from webnotes.utils.email_lib import sendmail
emailid_ret = sql(
"select company_email from `tabEmployee` where name = '%s'" %
self.doc.employee)
if emailid_ret:
receiver = cstr(emailid_ret[0][0])
subj = 'Salary Slip - ' + cstr(self.doc.month) + '/' + cstr(
self.doc.fiscal_year)
earn_ret = sql(
"select e_type,e_modified_amount from `tabSalary Slip Earning` where parent = '%s'"
% self.doc.name)
ded_ret = sql(
"select d_type,d_modified_amount from `tabSalary Slip Deduction` where parent = '%s'"
% self.doc.name)
earn_table = ''
ded_table = ''
if earn_ret:
earn_table += "<table cellspacing=5px cellpadding=5px width='100%%'>"
for e in earn_ret:
if not e[1]:
earn_table += '<tr><td>%s</td><td align="right">0.00</td></tr>' % (
cstr(e[0]))
else:
earn_table += '<tr><td>%s</td><td align="right">%s</td></tr>' % (
cstr(e[0]), cstr(e[1]))
earn_table += '</table>'
if ded_ret:
ded_table += "<table cellspacing=5px cellpadding=5px width='100%%'>"
for d in ded_ret:
if not d[1]:
ded_table += '<tr><td">%s</td><td align="right">0.00</td></tr>' % (
cstr(d[0]))
else:
ded_table += '<tr><td>%s</td><td align="right">%s</td></tr>' % (
cstr(d[0]), cstr(d[1]))
ded_table += '</table>'
letter_head = sql(
"select value from `tabSingles` where field = 'letter_head' and doctype = 'Control Panel'"
)
if not letter_head:
letter_head = ''
msg = '''<div> %s <br>
<table cellspacing= "5" cellpadding="5" width = "100%%">
<tr>
<td width = "100%%" colspan = "2"><h4>Salary Slip</h4></td>
</tr>
<tr>
<td width = "50%%"><b>Employee Code : %s</b></td>
<td width = "50%%"><b>Employee Name : %s</b></td>
</tr>
<tr>
<td width = "50%%">Month : %s</td>
<td width = "50%%">Fiscal Year : %s</td>
</tr>
<tr>
<td width = "50%%">Department : %s</td>
<td width = "50%%">Branch : %s</td>
</tr>
<tr>
<td width = "50%%">Designation : %s</td>
<td width = "50%%">Grade : %s</td>
</tr>
<tr>
<td width = "50%%">Bank Account No. : %s</td>
<td width = "50%%">Bank Name : %s</td>
</tr>
<tr>
<td width = "50%%">Arrear Amount : <b>%s</b></td>
<td width = "50%%">Payment days : %s</td>
</tr>
</table>
<table border="1px solid #CCC" width="100%%" cellpadding="0px" cellspacing="0px">
<tr>
<td colspan = 2 width = "50%%" bgcolor="#CCC" align="center"><b>Earnings</b></td>
<td colspan = 2 width = "50%%" bgcolor="#CCC" align="center"><b>Deductions</b></td>
</tr>
<tr>
<td colspan = 2 width = "50%%" valign= "top">%s</td>
<td colspan = 2 width = "50%%" valign= "top">%s</td>
</tr>
</table>
<table cellspacing= "5" cellpadding="5" width = '100%%'>
<tr>
<td width = '25%%'><b>Gross Pay :</b> </td><td width = '25%%' align='right'>%s</td>
<td width = '25%%'><b>Total Deduction :</b></td><td width = '25%%' align='right'> %s</td>
</tr>
<tr>
<tdwidth='25%%'><b>Net Pay : </b></td><td width = '25%%' align='right'><b>%s</b></td>
<td colspan = '2' width = '50%%'></td>
</tr>
<tr>
<td width='25%%'><b>Net Pay(in words) : </td><td colspan = '3' width = '50%%'>%s</b></td>
</tr>
</table></div>''' % (cstr(letter_head[0][0]), cstr(self.doc.employee),
cstr(self.doc.employee_name), cstr(self.doc.month),
cstr(self.doc.fiscal_year), cstr(self.doc.department),
cstr(self.doc.branch), cstr(self.doc.designation),
cstr(self.doc.grade), cstr(self.doc.bank_account_no),
cstr(self.doc.bank_name), cstr(self.doc.arrear_amount),
cstr(self.doc.payment_days), earn_table, ded_table,
cstr(flt(self.doc.gross_pay)),
cstr(flt(self.doc.total_deduction)),
cstr(flt(
self.doc.net_pay)), cstr(self.doc.total_in_words))
sendmail([receiver], subject=subj, msg=msg)
else:
msgprint("Company Email ID not found, hence mail not sent")
def update_item(obj, target, source_parent):
target.qty = flt(obj.qty) - flt(obj.installed_qty)
target.serial_no = obj.serial_no
def set_total_invoiced_amount(self):
total = sum([
flt(d.grand_total)
for d in getlist(self.doclist, 'invoice_details')
])
webnotes.conn.set(self.doc, 'total_invoiced_amount', total)
def validate_for_items(self, obj):
check_list, chk_dupl_itm = [], []
for d in getlist(obj.doclist, obj.fname):
# validation for valid qty
if flt(d.qty) < 0 or (d.parenttype != 'Purchase Receipt'
and not flt(d.qty)):
msgprint("Please enter valid qty for item %s" %
cstr(d.item_code))
raise Exception
# udpate with latest quantities
bin = sql(
"select projected_qty from `tabBin` where item_code = %s and warehouse = %s",
(d.item_code, d.warehouse),
as_dict=1)
f_lst = {
'projected_qty': bin and flt(bin[0]['projected_qty']) or 0,
'ordered_qty': 0,
'received_qty': 0
}
if d.doctype == 'Purchase Receipt Item':
f_lst.pop('received_qty')
for x in f_lst:
if d.fields.has_key(x):
d.fields[x] = f_lst[x]
item = sql(
"select is_stock_item, is_purchase_item, is_sub_contracted_item, end_of_life from tabItem where name=%s",
d.item_code)
if not item:
msgprint("Item %s does not exist in Item Master." %
cstr(d.item_code),
raise_exception=True)
from stock.utils import validate_end_of_life
validate_end_of_life(d.item_code, item[0][3])
# validate stock item
if item[0][0] == 'Yes' and d.qty and not d.warehouse:
msgprint(
"Warehouse is mandatory for %s, since it is a stock item" %
d.item_code,
raise_exception=1)
# validate purchase item
if item[0][1] != 'Yes' and item[0][2] != 'Yes':
msgprint(
"Item %s is not a purchase item or sub-contracted item. Please check"
% (d.item_code),
raise_exception=True)
# list criteria that should not repeat if item is stock item
e = [
d.schedule_date, d.item_code, d.description, d.warehouse,
d.uom,
d.fields.has_key('prevdoc_docname') and d.prevdoc_docname
or '',
d.fields.has_key('prevdoc_detail_docname')
and d.prevdoc_detail_docname or '',
d.fields.has_key('batch_no') and d.batch_no or ''
]
# if is not stock item
f = [d.schedule_date, d.item_code, d.description]
ch = sql("select is_stock_item from `tabItem` where name = '%s'" %
d.item_code)
if ch and ch[0][0] == 'Yes':
# check for same items
if e in check_list:
msgprint(
"""Item %s has been entered more than once with same description, schedule date, warehouse and uom.\n
Please change any of the field value to enter the item twice""" %
d.item_code,
raise_exception=1)
else:
check_list.append(e)
elif ch and ch[0][0] == 'No':
# check for same items
if f in chk_dupl_itm:
msgprint(
"""Item %s has been entered more than once with same description, schedule date.\n
Please change any of the field value to enter the item twice.""" %
d.item_code,
raise_exception=1)
else:
chk_dupl_itm.append(f)
