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)
