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)