def quote_table(self): if getlist(self.doclist, "enq_details"): header_lbl = ["Item Code", "Item Name", "Description", "Reqd Qty", "UOM"] item_tbl = """<table style="width:90%%; border:1px solid #AAA; border-collapse:collapse"><tr>""" for i in header_lbl: item_header = ( """<td style="width=20%%; border:1px solid #AAA; border-collapse:collapse;"><b>%s</b></td>""" % i ) item_tbl += item_header item_tbl += """</tr>""" for d in getlist(self.doclist, "enq_details"): item_det = """ <tr><td style="width:20%%; border:1px solid #AAA; border-collpase:collapse">%s</td> <td style="width:20%%; border:1px solid #AAA; border-collapse:collpase">%s</td> <td style="width:20%%; border:1px solid #AAA; border-collapse:collpase">%s</td> <td style="width:20%%; border:1px solid #AAA; border-collapse:collpase">%s</td> <td style="width:20%%; border:1px solid #AAA; border-collapse:collpase">%s</td></tr> """ % ( d.item_code, d.item_name, d.description, d.reqd_qty, d.uom, ) item_tbl += item_det item_tbl += """</table>""" return item_tbl
def get_against_account(self): # Debit = Credit debit, credit = 0.0, 0.0 debit_list, credit_list = [], [] for d in getlist(self.doclist, "entries"): debit += flt(d.debit) credit += flt(d.credit) if flt(d.debit) > 0 and (d.account not in debit_list): debit_list.append(d.account) if flt(d.credit) > 0 and (d.account not in credit_list): credit_list.append(d.account) self.doc.total_debit = debit if self.doc.tds_applicable == "Yes": self.doc.total_credit = credit + flt(self.doc.ded_amount) else: self.doc.total_credit = credit if abs(self.doc.total_debit - self.doc.total_credit) > 0.001: msgprint( "Debit must be equal to Credit. The difference is %s" % (self.doc.total_debit - self.doc.total_credit) ) raise Exception # update against account for d in getlist(self.doclist, "entries"): if flt(d.debit) > 0: d.against_account = ", ".join(credit_list) if flt(d.credit) > 0: d.against_account = ", ".join(debit_list)
def update_warranty_amc_history(self, submit = 1): if self.doc.order_type in ['AMC', 'OTS (One Time Service)']: sr_list = [] if submit: for d in getlist(self.doclist, 'service_order_details'): sr = Document('Serial No', d.serial_no) child = addchild(sr, 'warranty_amc_history', 'Warranty AMC History', 0) child.from_date = d.start_date child.to_date = d.end_date child.status = (self.doc.order_type == 'AMC') and 'Under AMC' or 'OTS (One Time Service)' child.against_doctype = self.doc.doctype child.against_docname = self.doc.name child.customer = self.doc.customer child.territory = self.doc.territory child.save() sr.warranty_amc_status = (self.doc.order_type == 'AMC') and 'Under AMC' or 'OTS (One Time Service)' sr.amc_expiry_date = d.end_date sr.save() sr_list.append(d.serial_no) else: sql("delete from `tabWarranty AMC History` where against_doctype = %s and against_docname = %s", (self.doc.doctype, self.doc.name)) for d in getlist(self.doclist, 'service_order_details'): sql("update `tabSerial No` set amc_expiry_date = '' where name = '%s'" % (d.serial_no)) sr_list.append(d.serial_no) self.update_serial_no_warranty_amc_status(serial_no_list = sr_list)
def get_item_list(self, obj, is_stopped): il = [] for d in getlist(obj.doclist,obj.fname): 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 = self.get_curr_and_ref_doc_details(d.doctype, 'prevdoc_detail_docname', d.prevdoc_detail_docname, 'Sales Order Item', 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) warehouse = (obj.fname == "sales_order_details") and d.reserved_warehouse or d.warehouse if self.has_sales_bom(d.item_code): for p in getlist(obj.doclist, 'packing_details'): #if p.parent_item == d.item_code: -- this fails when item with same name appears more than once in delivery note item table if p.parent_detail_docname == d.name: # the packing details table's qty is already multiplied with parent's qty il.append([warehouse, p.item_code, flt(p.qty), (flt(p.qty)/qty)*(reserved_qty), p.uom, p.batch_no, p.serial_no]) else: il.append([warehouse, d.item_code, qty, reserved_qty, d.stock_uom, d.batch_no, d.serial_no]) return il
def calc_pr_other_charges(self, name): # re-calculate other charges obj = get_obj('Purchase Receipt', name, with_children = 1) total = 0 net_total = obj.doc.net_total for prd in getlist(obj.doclist, 'purchase_receipt_details'): prev_total, item_tax = flt(prd.amount), 0 total += flt(flt(prd.qty) * flt(prd.purchase_rate)) check_tax = 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)): if check_tax.get(ocd[oc].account_head) and ocd[oc].charge_type != 'Actual': rate = check_tax[ocd[oc].account_head] else: rate = flt(ocd[oc].rate) tax_amount = self.cal_tax(ocd, prd, rate, net_total, oc) if ocd[oc].add_deduct_tax == 'Add': add_deduct_taxes(self, ocd, oc, tax_amount, total_amount, total, prev_total, f=1) elif ocd[oc].add_deduct_tax == 'Deduct': add_deduct_taxes(self, ocd, oc, tax_amount, total_amount, total, prev_total, f=-1) prd.item_tax_amount = flt(item_tax) prd.save() obj.doc.save()
def create_sal_slip(self): """ Creates salary slip for selected employees if already not created """ emp_list = self.get_emp_list() ss_list = [] for emp in emp_list: if not sql("""select name from `tabSalary Slip` where docstatus!= 2 and employee = %s and month = %s and fiscal_year = %s and company = %s """, (emp[0], self.doc.month, self.doc.fiscal_year, self.doc.company)): ss = Document('Salary Slip') ss.fiscal_year = self.doc.fiscal_year ss.employee = emp[0] ss.month = self.doc.month ss.email_check = self.doc.send_email ss.company = self.doc.company ss.save(1) ss_obj = get_obj('Salary Slip', ss.name, with_children=1) ss_obj.get_emp_and_leave_details() ss_obj.calculate_net_pay() ss_obj.validate() ss_obj.doc.save() for d in getlist(ss_obj.doclist, 'earning_details'): d.save() for d in getlist(ss_obj.doclist, 'deduction_details'): d.save() ss_list.append(ss.name) return self.create_log(ss_list)
def get_allocated_sum(self,obj): sum = 0 for d in getlist(obj.doclist,'sales_team'): sum += flt(d.allocated_percentage) if (flt(sum) != 100) and getlist(obj.doclist,'sales_team'): msgprint("Total Allocated % of Sales Persons should be 100%") raise Exception
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 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) # If any row without amount, set the diff on that row for d in getlist(self.doclist,'entries'): if (d.credit==0 or d.credit is None) and (d.debit==0 or d.debit is None) and (flt(diff) != 0): if diff>0: d.credit = flt(diff) elif diff<0: d.debit = flt(diff) flag = 1 # Set the diff in a new row if flag == 0 and (flt(diff) != 0): jd = addchild(self.doc, 'entries', 'Journal Voucher Detail', 1, self.doclist) if diff>0: jd.credit = flt(diff) elif diff<0: jd.debit = flt(diff) # Set the total debit, total credit and difference for d in getlist(self.doclist,'entries'): self.doc.total_debit += flt(d.debit) self.doc.total_credit += flt(d.credit) if self.doc.tds_applicable == 'Yes': self.doc.total_credit = flt(self.doc.total_credit) + flt(self.doc.ded_amount) self.doc.difference = flt(self.doc.total_debit) - flt(self.doc.total_credit)
def update_warranty_amc_history(self, submit = 1): if submit: for d in getlist(self.doclist, 'delivery_note_details'): if d.serial_no: serial_nos = self.get_sr_no_list(d.serial_no) for s in serial_nos: sr = Document('Serial No', s) child = addchild(sr, 'warranty_amc_history', 'Warranty AMC History', 0) child.from_date = self.doc.transaction_date child.to_date = d.warranty_expiry_date child.status = 'Under Warranty' child.against_doctype = self.doc.doctype child.against_docname = self.doc.name child.customer = self.doc.customer child.territory = self.doc.territory child.save() sr.warranty_amc_status = 'Under Warranty' sr.warranty_expiry_date = d.warranty_expiry_date sr.save() else: sql("delete from `tabWarranty AMC History` where against_doctype = %s and against_docname = %s", (self.doc.doctype, self.doc.name)) sr_list = [] for d in getlist(self.doclist, 'service_order_details'): if d.serial_no: serial_nos = self.get_sr_no_list(d.serial_no) for s in serial_nos: sql("update `tabSerial No` set warranty_expiry_date = '' where name = '%s'" % (s)) sr_list.append(s) self.update_serial_no_warranty_amc_status(serial_no_list = sr_list)
def update_warranty_amc_history(self, submit=1): if self.doc.order_type in ["AMC", "OTS (One Time Service)"]: sr_list = [] if submit: for d in getlist(self.doclist, "service_order_details"): sr = Document("Serial No", d.serial_no) child = addchild(sr, "warranty_amc_history", "Warranty AMC History", 0) child.from_date = d.start_date child.to_date = d.end_date child.status = (self.doc.order_type == "AMC") and "Under AMC" or "OTS (One Time Service)" child.against_doctype = self.doc.doctype child.against_docname = self.doc.name child.customer = self.doc.customer child.territory = self.doc.territory child.save() sr.warranty_amc_status = (self.doc.order_type == "AMC") and "Under AMC" or "OTS (One Time Service)" sr.amc_expiry_date = d.end_date sr.save() sr_list.append(d.serial_no) else: sql( "delete from `tabWarranty AMC History` where against_doctype = %s and against_docname = %s", (self.doc.doctype, self.doc.name), ) for d in getlist(self.doclist, "service_order_details"): sql("update `tabSerial No` set amc_expiry_date = '' where name = '%s'" % (d.serial_no)) sr_list.append(d.serial_no) self.update_serial_no_warranty_amc_status(serial_no_list=sr_list)
def on_submit(self): if not getlist(self.doclist, 'maintenance_schedule_detail'): msgprint("Please click on 'Generate Schedule' to get schedule") raise Exception self.check_serial_no_added() self.validate_serial_no_warranty() self.validate_schedule() email_map ={} for d in getlist(self.doclist, 'item_maintenance_detail'): if d.serial_no: self.update_amc_date(d.serial_no, d.end_date) if d.incharge_name not in email_map: e = sql("select email_id, name from `tabSales Person` where name='%s' " %(d.incharge_name),as_dict=1)[0] email_map[d.incharge_name] = (e['email_id']) scheduled_date =sql("select scheduled_date from `tabMaintenance Schedule Detail` \ where incharge_name='%s' and item_code='%s' and parent='%s' " %(d.incharge_name, \ d.item_code, self.doc.name), as_dict=1) for key in scheduled_date: if email_map[d.incharge_name]: self.add_calender_event(key["scheduled_date"],email_map[d.incharge_name],d.item_code) set(self.doc, 'status', 'Submitted')
def sum_mod_val(self): count = count1 = count2 = 0.0 for e in getlist(self.doclist,'exe_declaration_details'): count += flt(e.modified_amount1) count = round(flt(self.doc.gross_income)) - count for oi in getlist(self.doclist,'oth_inc_decl_details'): count += flt(oi.modified_amount2) for vi in getlist(self.doclist,'chap_via_decl_details'): count2 += flt(vi.modified_amount3) count = count - count2 for inv in getlist(self.doclist,'invest_80_decl_details'): count1 += flt(inv.modified_amount4) if(count1 >= 100000): break if(count1>100000): count1=100000 count_lst = [count,count1] return count_lst
def validate_reference_value(self, obj, to_docname): for t in getlist(self.doclist, 'table_mapper_details'): # Reference key is the fieldname which will relate to the from_table if t.reference_doctype_key: for d in getlist(obj.doclist, t.to_field): if d.fields[t.reference_doctype_key] == self.doc.from_doctype: self.check_consistency(obj.doc, d, to_docname) self.check_ref_docstatus()
def add_to_calendar(self): send = sql("select email_id from `tabSales Person` where name = %s", self.doc.prepared_by) for d in getlist(self.doclist, "scheduled_visit_details"): message = """Purpose : %s for Customer : %s""" % (d.purpose, d.customer_name) self.calendar_entry(send, message, d.scheduled_date, self.doc.doctype, self.doc.name) for d in getlist(self.doclist, "unscheduled_visit_details"): message = """Purpose : %s for Customer : %s""" % (d.purpose, d.customer_name) self.calendar_entry(send, message, d.scheduled_date, self.doc.doctype, self.doc.name)
def update_current_stock(self): for d in getlist(self.doclist, 'delivery_note_details'): bin = sql("select actual_qty from `tabBin` where item_code = %s and warehouse = %s", (d.item_code, d.warehouse), as_dict = 1) d.actual_qty = bin and flt(bin[0]['actual_qty']) or 0 for d in getlist(self.doclist, 'packing_details'): bin = sql("select actual_qty, projected_qty from `tabBin` where item_code = %s and warehouse = %s", (d.item_code, d.warehouse), as_dict = 1) d.actual_qty = bin and flt(bin[0]['actual_qty']) or 0 d.projected_qty = bin and flt(bin[0]['projected_qty']) or 0
def ovr_mapper(self, doclist, ovr, ignore, onupdate): doclist = [Document(fielddata = d) for d in doclist] doc = doclist[0] orig_modified = doc.modified cur_doc = Document('DocType Mapper',doc.name) added = 0 fld_lst = '' # Field Mapper Details fields # ------ for d in getlist(doclist, 'field_mapper_details'): fld = '' # if exists if d.from_field and d.to_field: fld = sql("select name from `tabField Mapper Detail` where from_field=%s and to_field=%s and parent=%s", (d.from_field, d.to_field, d.parent)) if (not fld) and d.from_field and d.to_field: # must have label # add field nd = Document(fielddata = d.fields) nd.oldfieldname, nd.oldfieldtype = '', '' nd.save(new = 1, ignore_fields = ignore, check_links = 0) fld_lst += "\n"+'From Field : '+cstr(d.from_field)+' --- To Field : '+cstr(d.to_field) added += 1 # Table Mapper Details fields # ------ for d in getlist(doclist, 'table_mapper_details'): fld = '' # if exists if d.from_table and d.to_table: fld = sql("select name from `tabTable Mapper Detail` where from_table=%s and to_table = %s and parent=%s", (d.from_table, d.to_table, d.parent)) if (not fld) and d.from_table and d.to_table: # must have label # add field nd = Document(fielddata = d.fields) nd.oldfieldname, nd.oldfieldtype = '', '' nd.save(new = 1, ignore_fields = ignore, check_links = 0) fld_lst += "\n"+'From Table : '+cstr(d.from_table)+' --- To Table : '+cstr(d.to_table) added += 1 cur_doc.save(ignore_fields = ignore, check_links = 0) if onupdate: so = get_obj('DocType Mapper', doc.name, with_children = 1) if hasattr(so, 'on_update'): so.on_update() set(doc,'modified',orig_modified) if in_transaction: sql("COMMIT") if added == 0: added_fields = '' else: added_fields = ' <div style="color : RED">Added Fields :- '+ cstr(fld_lst)+ '</div>' return doc.name + (' Upgraded: %s fields added' % added)+added_fields
def check_serial_no_added(self): serial_present =[] for d in getlist(self.doclist, 'item_maintenance_detail'): if d.serial_no: serial_present.append(d.item_code) for m in getlist(self.doclist, 'maintenance_schedule_detail'): if serial_present: if m.item_code in serial_present and not m.serial_no: msgprint("Please click on 'Generate Schedule' to fetch serial no added for item "+m.item_code) raise Exception
def on_update(self): """ Update C-Form No on invoices""" if len(getlist(self.doclist, 'invoice_details')): inv = "'" + "', '".join([d.invoice_no for d in getlist(self.doclist, 'invoice_details')]) + "'" sql("""update `tabReceivable Voucher` set c_form_no = '%s', modified ='%s' where name in (%s)"""%(self.doc.name, self.doc.modified, inv)) sql("""update `tabReceivable Voucher` set c_form_no = '', modified = %s where name not in (%s) and ifnull(c_form_no, '') = %s""", (self.doc.modified, self.doc.name, inv)) else: msgprint("Please enter atleast 1 invoice in the table below", raise_exception=1)
def on_submit(self): if not getlist(self.doclist, 'maintenance_schedule_detail'): msgprint("Please click on 'Generate Schedule' to get schedule") raise Exception self.check_serial_no_added() self.validate_serial_no_warranty() self.validate_schedule() for d in getlist(self.doclist, 'item_maintenance_detail'): if d.serial_no: self.update_amc_date(d.serial_no, d.end_date) set(self.doc, 'status', 'Submitted')
def send_sms(self): if not self.doc.message: msgprint("Please type the message before sending") elif not getlist(self.doclist, 'receiver_details'): msgprint("Receiver Table is blank.") else: receiver_list = [] for d in getlist(self.doclist, 'receiver_details'): if d.mobile_no: receiver_list.append(d.mobile_no) if receiver_list: msgprint(get_obj('SMS Control', 'SMS Control').send_sms(receiver_list, self.doc.message))
def remove_series(self): if not getlist(self.doclist, 'series_details'): msgprint("Please pull already existed series for the selected doctype and check the series that you want to remove") else: sr= sql("Select options from `tabDocField` where fieldname='voucher_series' and parent='%s'"%(self.doc.select_doctype)) if sr: sr_list=sr[0][0].split("\n") for d in getlist(self.doclist, 'series_details'): if d.remove == 1: sr_list.remove(d.series) sql("update `tabDocField` set options='%s' where fieldname='voucher_series' and parent='%s'"%("\n".join(sr_list),self.doc.select_doctype)) self.get_series()
def get_item_list(self, obj, is_stopped): """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: # the packing details table's qty is already multiplied with parent's qty il.append({ 'warehouse': d.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 validate(self): for d in getlist(self.doclist, 'field_mapper_details'): # Automatically assigns default value if not entered if not d.match_id: d.match_id = 0 if not d.map: d.map = 'Yes' for d in getlist(self.doclist, 'table_mapper_details'): if not d.reference_doctype_key: d.reference_doctype_key = '' if not d.reference_key: d.reference_key = '' # Check wrong field name self.check_fields_in_dt()
def send_sms(self): if not self.doc.sms_message: msgprint("Please enter message in SMS Section ") raise Exception elif not getlist(self.doclist, 'enquiry_sms_detail'): msgprint("Please mention mobile no. to which sms needs to be sent") raise Exception else: receiver_list = [] for d in getlist(self.doclist,'enquiry_sms_detail'): if d.other_mobile_no: receiver_list.append(d.other_mobile_no) if receiver_list: msgprint(get_obj('SMS Control', 'SMS Control').send_sms(receiver_list, self.doc.sms_message)) self.add_in_follow_up(self.doc.sms_message,'SMS')
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 """ lst = [] for d in getlist(self.doclist, 'ir_payment_details'): if d.selected and 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 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) if lst: get_obj('GL Control').reconcile_against_document(lst) msgprint("Successfully reconciled.") else: msgprint("No payment entries selected.", raise_exception=1)
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 make_gl_entries(self, doc, doclist, cancel=0, adv_adj = 0, use_mapper='', merge_entries = 1, update_outstanding='Yes'): self.entries = [] # get entries le_map_list = webnotes.conn.sql("select * from `tabGL Mapper Detail` where parent = %s", use_mapper or doc.doctype, as_dict=1) self.td, self.tc = 0.0, 0.0 for le_map in le_map_list: if le_map['table_field']: for d in getlist(doclist,le_map['table_field']): # purchase_tax_details is the table of other charges in purchase cycle if le_map['table_field'] != 'purchase_tax_details' or (le_map['table_field'] == 'purchase_tax_details' and d.fields.get('category') != 'For Valuation'): self.make_single_entry(doc,d,le_map,cancel, merge_entries) else: self.make_single_entry(None,doc,le_map,cancel, merge_entries) # save entries self.save_entries(cancel, adv_adj, update_outstanding) # check total debit / credit # Due to old wrong entries (total debit != total credit) some voucher could be cancelled if abs(self.td - self.tc) > 0.001 and not cancel: msgprint("Debit and Credit not equal for this voucher: Diff (Debit) is %s" % (self.td-self.tc)) raise Exception # set as cancelled if cancel: vt, vn = self.get_val(le_map['voucher_type'], doc, doc), self.get_val(le_map['voucher_no'], doc, doc) webnotes.conn.sql("update `tabGL Entry` set is_cancelled='Yes' where voucher_type=%s and voucher_no=%s", (vt, vn))
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 update_customer_issue(self, flag): for d in getlist(self.doclist, 'maintenance_visit_details'): if d.prevdoc_docname and d.prevdoc_doctype == 'Customer Issue' : if flag==1: mntc_date = self.doc.mntc_date service_person = d.service_person work_done = d.work_done if self.doc.completion_status == 'Fully Completed': status = 'Closed' elif self.doc.completion_status == 'Partially Completed': status = 'Work In Progress' else: nm = sql("select t1.name, t1.mntc_date, t2.service_person, t2.work_done from `tabMaintenance Visit` t1, `tabMaintenance Visit Detail` t2 where t2.parent = t1.name and t1.completion_status = 'Partially Completed' and t2.prevdoc_docname = %s and t1.name!=%s and t1.docstatus = 1 order by t1.name desc limit 1", (d.prevdoc_docname, self.doc.name)) if nm: status = 'Work In Progress' mntc_date = nm and nm[0][1] or '' service_person = nm and nm[0][2] or '' work_done = nm and nm[0][3] or '' else: status = 'Open' mntc_date = '' service_person = '' work_done = '' sql("update `tabCustomer Issue` set resolution_date=%s, resolved_by=%s, resolution_details=%s, status=%s where name =%s",(mntc_date,service_person,work_done,status,d.prevdoc_docname))
def pr_required(self): res = sql( "select value from `tabSingles` where doctype = 'Global Defaults' and field = 'pr_required'" ) if res and res[0][0] == 'Yes': for d in getlist(self.doclist, 'entries'): if not d.purchase_receipt: msgprint("Purchase Receipt No. required against item %s" % d.item_code) raise Exception
def validate_fixed_asset_account(self): for d in getlist(self.doclist,'entries'): item = webnotes.conn.sql("select name,is_asset_item,is_sales_item from `tabItem` where name = '%s' and (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now())"% d.item_code) acc = webnotes.conn.sql("select account_type from `tabAccount` where name = '%s' and docstatus != 2" % d.income_account) if not acc: msgprint("Account: "+d.income_account+" does not exist in the system") raise Exception elif item and item[0][1] == 'Yes' and not acc[0][0] == 'Fixed Asset Account': msgprint("Please select income head with account type 'Fixed Asset Account' as Item %s is an asset item" % d.item_code) raise Exception
def po_required(self): res = sql( "select value from `tabSingles` where doctype = 'Global Defaults' and field = 'po_required'" ) if res and res[0][0] == 'Yes': for d in getlist(self.doclist, 'purchase_receipt_details'): if not d.prevdoc_docname: msgprint("Purchse Order No. required against item %s" % d.item_code) raise Exception
def validate_entries_for_advance(self): for d in getlist(self.doclist, 'entries'): if not d.is_advance and not d.against_voucher and not d.against_invoice and d.against_jv: master_type = self.get_master_type(d.account) if (master_type == 'Customer' and flt(d.credit) > 0) or (master_type == 'Supplier' and flt(d.debit) > 0): msgprint( "Message: Please check Is Advance as 'Yes' against Account %s if this is an advance entry." % d.account)
def validate_schedule(self): item_lst1 =[] item_lst2 =[] for d in getlist(self.doclist, 'item_maintenance_detail'): if d.item_code not in item_lst1: item_lst1.append(d.item_code) for m in getlist(self.doclist, 'maintenance_schedule_detail'): if m.item_code not in item_lst2: item_lst2.append(m.item_code) if len(item_lst1) != len(item_lst2): msgprint("Maintenance Schedule is not generated for all the items. Please click on 'Generate Schedule'") raise Exception else: for x in item_lst1: if x not in item_lst2: msgprint("Maintenance Schedule is not generated for item "+x+". Please click on 'Generate Schedule'") raise Exception
def validate_sales_order(self): for d in getlist(self.doclist, 'item_maintenance_detail'): if d.prevdoc_docname: chk = sql( "select t1.name from `tabMaintenance Schedule` t1, `tabMaintenance Schedule Item` t2 where t2.parent=t1.name and t2.prevdoc_docname=%s and t1.docstatus=1", d.prevdoc_docname) if chk: msgprint("Maintenance Schedule against " + d.prevdoc_docname + " already exist") raise Exception
def validate_bom_belongs_to_item(self): for d in getlist(self.doclist, 'mtn_details'): if d.bom_no and not webnotes.conn.sql( """\ SELECT name FROM `tabBill Of Materials` WHERE item = %s and name = %s """, (d.item_code, d.bom_no)): msgprint("BOM %s does not belong to Item: %s at row %s" % (d.bom_no, d.item_code, d.idx), raise_exception=1)
def get_checklist(self): checklist = [] for f in getlist(self.doclist, 'field_mapper_details'): # Check which field's value will be compared if f.checking_operator: checklist.append([ f.from_field, f.to_field, f.checking_operator, f.match_id ]) return checklist
def send_sms(self): if not self.doc.sms_message: msgprint("Please enter message in SMS Section ") raise Exception elif not getlist(self.doclist, 'enquiry_sms_detail'): msgprint("Please mention mobile no. to which sms needs to be sent") raise Exception else: receiver_list = [] for d in getlist(self.doclist, 'enquiry_sms_detail'): if d.other_mobile_no: receiver_list.append(d.other_mobile_no) if receiver_list: msgprint( get_obj('SMS Control', 'SMS Control').send_sms(receiver_list, self.doc.sms_message)) self.add_in_follow_up(self.doc.sms_message, 'SMS')
def validate_for_items(self): chk_dupl_itm = [] for d in getlist(self.doclist, 'quotation_details'): if [cstr(d.item_code), cstr(d.description)] in chk_dupl_itm: msgprint( "Item %s has been entered twice. Please change description atleast to continue" % d.item_code) raise Exception else: chk_dupl_itm.append([cstr(d.item_code), cstr(d.description)])
def validate_appraisal_detail(self): if not self.doc.kra_approver: msgprint("Please mention the name of Approver") raise Exception if not getlist(self.doclist, 'appraisal_details'): msgprint("Please add KRA Details") raise Exception self.validate_total_weightage()
def validate_sales_mntc_quotation(self): for d in getlist(self.doclist, 'sales_order_details'): if d.prevdoc_docname: res = sql( "select name from `tabQuotation` where name=%s and order_type = %s", (d.prevdoc_docname, self.doc.order_type)) if not res: msgprint( """Order Type (%s) should be same in Quotation: %s \ and current Sales Order""" % (self.doc.order_type, d.prevdoc_docname))
def po_required(self): res = sql( "select value from `tabSingles` where doctype = 'Manage Account' and field = 'po_required'" ) if res and res[0][0] == 'Yes': for d in getlist(self.doclist, 'entries'): if not d.purchase_order: msgprint("Purchse Order No. required against item %s" % d.item_code) raise Exception
def apply_action(self, rule_no, form_obj): rule_obj = get_obj('Workflow Rule', rule_no, with_children=1) #msgprint("action") for d in getlist(rule_obj.doclist, 'workflow_action_details'): field_name = sql( "select fieldname from tabDocField where parent='%s' and label='%s'" % (form_obj.doc.doctype, d.action_field))[0][0] if field_name: #msgprint(field_name) form_obj.doc.fields[field_name] = d.action_value return
def validate_operations(self): """ Check duplicate operation no""" self.op = [] for d in getlist(self.doclist, 'bom_operations'): if cstr(d.operation_no) in self.op: msgprint("Operation no: %s is repeated in Operations Table" % d.operation_no, raise_exception=1) else: # add operation in op list self.op.append(cstr(d.operation_no))
def update_stock_ledger(self, is_cancelled=0): self.values = [] for d in getlist(self.doclist, 'mtn_details'): if cstr(d.s_warehouse): self.add_to_values(d, cstr(d.s_warehouse), -flt(d.transfer_qty), is_cancelled) if cstr(d.t_warehouse): self.add_to_values(d, cstr(d.t_warehouse), flt(d.transfer_qty), is_cancelled) get_obj('Stock Ledger', 'Stock Ledger').update_stock( self.values, self.doc.amended_from and 'Yes' or 'No')
def validate(self): if not getlist(self.doclist, 'maintenance_visit_details'): msgprint("Please enter maintenance details") raise Exception for d in getlist(self.doclist, 'maintenance_visit_details'): if d.prevdoc_doctype == 'Sales Order': check_for = 'Sales Order' elif d.prevdoc_doctype == 'Customer Issue': check_for = 'Customer Issue' else: msgprint( "Maintenance Visit must be created either against Sales Order or against Customer Issue" ) raise Exception if check_for: self.validate_reference_value(check_for) self.validate_serial_no()
def validate_data(self): for d in getlist(self.doclist, 'pp_details'): if not d.pro_created: self.validate_bom_no(d) if not flt(d.planned_qty): msgprint( "Please Enter Planned Qty for item: %s at row no: %s" % (d.item_code, d.idx), raise_exception=1) return 'validated'
def check_qty_in_stock(self): for d in getlist(self.doclist, 'entries'): is_stock_item = webnotes.conn.sql("select is_stock_item from `tabItem` where name = '%s'" % d.item_code)[0][0] actual_qty = 0 if d.item_code and d.warehouse: actual_qty = webnotes.conn.sql("select actual_qty from `tabBin` where item_code = '%s' and warehouse = '%s'" % (d.item_code, d.warehouse)) actual_qty = actual_qty and flt(actual_qty[0][0]) or 0 if is_stock_item == 'Yes' and flt(d.qty) > flt(actual_qty): msgprint("For Item: " + cstr(d.item_code) + " at Warehouse: " + cstr(d.warehouse) + " Quantity: " + cstr(d.qty) +" is not Available. (Must be less than or equal to " + cstr(actual_qty) + " )") raise Exception, "Validation Error"
def set_pos_item_values(self): if cint(self.doc.is_pos) ==1: dtl = webnotes.conn.sql("select income_account, warehouse, cost_center from `tabPOS Setting` where ifnull(user,'') = '%s' and company = '%s'" % (session['user'], self.doc.company), as_dict=1) if not dtl: dtl = webnotes.conn.sql("select income_account, warehouse, cost_center from `tabPOS Setting` where ifnull(user,'') = '' and company = '%s'" % (self.doc.company), as_dict=1) for d in getlist(self.doclist,'entries'): # overwrite if mentioned in item item = webnotes.conn.sql("select default_income_account, default_sales_cost_center, default_warehouse from tabItem where name = '%s'" %(d.item_code), as_dict=1) d.income_account = item and item[0]['default_income_account'] or dtl and dtl[0]['income_account'] or d.income_account d.cost_center = item and item[0]['default_sales_cost_center'] or dtl and dtl[0]['cost_center'] or d.cost_center d.warehouse = item and item[0]['default_warehouse'] or dtl and dtl[0]['warehouse'] or d.warehouse
def validate_customer(self): """ Validate customer name with SO and DN """ for d in getlist(self.doclist,'entries'): dt = d.delivery_note and 'Delivery Note' or d.sales_order and 'Sales Order' or '' if dt: dt_no = d.delivery_note or d.sales_order cust = webnotes.conn.sql("select customer from `tab%s` where name = %s" % (dt, '%s'), dt_no) if cust and cstr(cust[0][0]) != cstr(self.doc.customer): msgprint("Customer %s does not match with customer of %s: %s." %(self.doc.customer, dt, dt_no), raise_exception=1)
def calculate_op_cost(self): """Update workstation rate and calculates totals""" total_op_cost = 0 for d in getlist(self.doclist, 'bom_operations'): hour_rate = sql( "select hour_rate from `tabWorkstation` where name = %s", d.workstation) d.hour_rate = hour_rate and flt(hour_rate[0][0]) or 0 d.operating_cost = flt(d.hour_rate) * flt(d.time_in_mins) / 60 total_op_cost += d.operating_cost self.doc.operating_cost = total_op_cost
def update_voucher(self): sql("delete from `tabExpense Claim Detail` where parent = '%s'"%self.doc.name) for d in getlist(self.doclist, 'expense_voucher_details'): if not d.expense_type or not d.claim_amount: msgprint("Please remove the extra blank row added") raise Exception d.save(1) if self.doc.total_sanctioned_amount: set(self.doc,'total_sanctioned_amount',self.doc.total_sanctioned_amount) if self.doc.remark: set(self.doc, 'remark', self.doc.remark)
def get_income_account(self, doctype): for d in getlist(self.doclist, doctype): if d.item_code: item = webnotes.conn.sql( "select default_income_account, default_sales_cost_center from tabItem where name = '%s'" % (d.item_code), as_dict=1) d.income_account = item and item[0][ 'default_income_account'] or '' d.cost_center = item and item[0][ 'default_sales_cost_center'] or ''
def get_item_list(self, obj, is_stopped): il = [] for d in getlist(obj.doclist, obj.fname): 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 = self.get_curr_and_ref_doc_details( d.doctype, 'prevdoc_detail_docname', d.prevdoc_detail_docname, 'Sales Order Item', 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) warehouse = (obj.fname == "sales_order_details" ) and d.reserved_warehouse or d.warehouse if self.has_sales_bom(d.item_code): for p in getlist(obj.doclist, 'packing_details'): #if p.parent_item == d.item_code: -- this fails when item with same name appears more than once in delivery note item table if p.parent_detail_docname == d.name: # the packing details table's qty is already multiplied with parent's qty il.append([ warehouse, p.item_code, flt(p.qty), (flt(p.qty) / qty) * (reserved_qty), p.uom, p.batch_no, p.serial_no ]) else: il.append([ warehouse, d.item_code, qty, reserved_qty, d.stock_uom, d.batch_no, d.serial_no ]) return il
def calculate_amount(self, d): amt = 0 for i in getlist(self.doclist, 'pr_raw_material_details'): if (i.reference_name == d.name): #if i.consumed_qty == 0: # msgprint("consumed qty cannot be 0. Please Enter consumed qty ") #raise Exception i.amount = flt(i.consumed_qty) * flt(i.rate) amt += i.amount d.rm_supp_cost = amt d.save()
def validate_max_discount(self, obj, detail_table): for d in getlist(obj.doclist, detail_table): discount = webnotes.conn.sql( "select max_discount from tabItem where name = '%s'" % (d.item_code), as_dict=1) if discount and discount[0]['max_discount'] and (flt( d.adj_rate) > flt(discount[0]['max_discount'])): msgprint("You cannot give more than " + cstr(discount[0]['max_discount']) + " % discount on Item Code : " + cstr(d.item_code)) raise Exception
def validate_exp_details(self): if not getlist(self.doclist, 'expense_voucher_details'): msgprint("Please add expense voucher details") raise Exception if not self.doc.total_claimed_amount: msgprint("Please calculate Total Claimed Amount") raise Exception if not self.doc.exp_approver: msgprint("Please select Expense Claim approver") raise Exception
def send_via_personalized_gateway(self, arg): ss = get_obj('SMS Settings', 'SMS Settings', with_children=1) args = {ss.doc.message_parameter: arg.get('message')} for d in getlist(ss.doclist, 'static_parameter_details'): args[d.parameter] = d.value resp = [] for d in arg.get('receiver_list'): args[ss.doc.receiver_parameter] = d resp.append(self.send_request(ss.doc.sms_gateway_url, args)) return resp
def update_valuation_rate(self): total_b_cost = flt(self.doc.buying_cost_transport) + flt( self.doc.buying_cost_taxes) + flt(self.doc.buying_cost_other) for d in getlist(self.doclist, 'purchase_receipt_details'): if flt(self.doc.net_total) and flt(d.qty): #d.valuation_rate = (flt(d.purchase_rate) + ((flt(d.amount) * (total_b_cost)) / (self.doc.net_total * flt(d.qty))) + (flt(d.rm_supp_cost) / flt(d.qty))) / flt(d.conversion_factor) d.valuation_rate = (flt(d.purchase_rate) + ((flt(d.amount) * (total_b_cost)) / (self.doc.net_total * flt(d.qty))) + (flt(d.rm_supp_cost) / flt(d.qty)) + (flt(d.item_tax_amount) / flt(d.qty))) / flt(d.conversion_factor)