def create_advance_entry(advance_amount, customer_name, debit_to, company): jv = Document('Journal Voucher') jv.voucher_type = 'Cash Voucher' jv.user_remark = 'Advance Payment' jv.fiscal_year = webnotes.conn.get_value('Global Defaults',None,'current_fiscal_year') jv.user_remark = "Advance from patient %s"%customer_name jv.remark = "User Remark : Advance from patient %s"%customer_name jv.company = company jv.posting_date = nowdate() jv.docstatus=1 jv.save() chld1 = Document('Journal Voucher Detail') chld1.parent = jv.name chld1.account = debit_to chld1.cost_center = webnotes.conn.get_value('Company',company,'cost_center') chld1.credit = advance_amount chld1.is_advance = 'Yes' chld1.save() chld2 = Document('Journal Voucher Detail') chld2.parent = jv.name chld2.account = webnotes.conn.get_value('Company',company,'default_cash_account') chld2.cost_center = webnotes.conn.get_value('Company',company,'cost_center') chld2.debit = advance_amount chld2.save() create_gl_entry(jv.name, jv.user_remark, company)
def make_bank_voucher(self): self.set_flag() """ get default bank account,default salary acount from company """ #amt = self.get_total_salary() com = webnotes.conn.sql("""select default_bank_account, default_expense_account from `tabCompany` where name = '%s'""" % self.doc.company,as_list=1) if not com[0][0] or not com[0][1]: msgprint("You can set Default Bank Account in Company master.") if not self.doc.jv: jv = Document('Journal Voucher') jv.voucher_type = 'Bank Voucher' jv.user_remark = 'Referrals Payment' jv.fiscal_year = '2013-14' jv.total_credit = jv.total_debit = self.doc.total_amount jv.company = self.doc.company jv.posting_date = nowdate() jv.save() jvd = Document('Journal Voucher Detail') jvd.account = com and com[0][0] or '' jvd.credit = self.doc.total_amount jvd.parent = jv.name jvd.save() jvd1 = Document('Journal Voucher Detail') jvd1.account = com and com[0][1] or '' jvd1.debit = self.doc.total_amount jvd1.parent = jv.name jvd1.save() self.doc.jv = jv.name self.doc.save()
def create_advance_entry(advance_amount, customer_name, debit_to, company): jv = Document('Journal Voucher') jv.voucher_type = 'Cash Voucher' jv.user_remark = 'Advance Payment' jv.fiscal_year = webnotes.conn.get_value('Global Defaults', None, 'current_fiscal_year') jv.user_remark = "Advance from patient %s" % customer_name jv.remark = "User Remark : Advance from patient %s" % customer_name jv.company = company jv.posting_date = nowdate() jv.docstatus = 1 jv.save() chld1 = Document('Journal Voucher Detail') chld1.parent = jv.name chld1.account = debit_to chld1.cost_center = webnotes.conn.get_value('Company', company, 'cost_center') chld1.credit = advance_amount chld1.is_advance = 'Yes' chld1.save() chld2 = Document('Journal Voucher Detail') chld2.parent = jv.name chld2.account = webnotes.conn.get_value('Company', company, 'default_cash_account') chld2.cost_center = webnotes.conn.get_value('Company', company, 'cost_center') chld2.debit = advance_amount chld2.save() create_gl_entry(jv.name, jv.user_remark, company)
def create_new_balances(self, det): # check if sql( "select count(t1.name) from `tabAccount Balance` t1, tabAccount t2 where t1.fiscal_year=%s and t2.lft <= %s and t2.rgt >= %s and t2.name = t1.account", (self.doc.fiscal_year, det[0][0], det[0][1] ))[0][0] < 13 * (cint(det[0][1]) - cint(det[0][0]) + 1) / 2: period_list = self.get_period_list() accounts = sql( "select name from tabAccount where lft <= %s and rgt >= %s" % (det[0][0], det[0][1])) for p in period_list: for a in accounts: # check if missing if not sql( "select name from `tabAccount Balance` where period=%s and account=%s and fiscal_year=%s", (p[0], a[0], self.doc.fiscal_year)): d = Document('Account Balance') d.account = a[0] d.period = p[0] d.start_date = p[1].strftime('%Y-%m-%d') d.end_date = p[2].strftime('%Y-%m-%d') d.fiscal_year = self.doc.fiscal_year d.debit = 0 d.credit = 0 d.opening = 0 d.balance = 0 d.save(1)
def create_account_balances(self): # get periods period_list = self.get_period_list() cnt = 0 # get accounts al = sql("select name from tabAccount") for a in al: # check if sql( "select count(*) from `tabAccount Balance` where fiscal_year=%s and account=%s", (self.doc.name, a[0]))[0][0] < 13: for p in period_list: # check if missing if not sql( "select name from `tabAccount Balance` where period=%s and account=%s and fiscal_year=%s", (p[0], a[0], self.doc.name)): d = Document('Account Balance') d.account = a[0] d.period = p[0] d.start_date = p[1].strftime('%Y-%m-%d') d.end_date = p[2].strftime('%Y-%m-%d') d.fiscal_year = p[3] d.debit = 0 d.credit = 0 d.opening = 0 d.balance = 0 d.save(1) cnt += 1 if cnt % 100 == 0: sql("commit") sql("start transaction") return cnt
def create_account_balances(self): # get periods period_list = self.get_period_list() cnt = 0 # get accounts al = sql("select name from tabAccount") for a in al: # check if sql("select count(*) from `tabAccount Balance` where fiscal_year=%s and account=%s", (self.doc.name, a[0]))[0][0] < 13: for p in period_list: # check if missing if not sql("select name from `tabAccount Balance` where period=%s and account=%s and fiscal_year=%s", (p[0], a[0], self.doc.name)): d = Document('Account Balance') d.account = a[0] d.period = p[0] d.start_date = p[1].strftime('%Y-%m-%d') d.end_date = p[2].strftime('%Y-%m-%d') d.fiscal_year = p[3] d.debit = 0 d.credit = 0 d.opening = 0 d.balance = 0 d.save(1) cnt += 1 if cnt % 100 == 0: sql("commit") sql("start transaction") return cnt
def make_JV1(self,sum_patient_amt,debit_to,patient_credit_to,company): jv=Document('Journal Voucher') jv.voucher_type='Bank Voucher' jv.posting_date=nowdate() jv.user_remark ='Payment Entry against '+self.doc.name jv.fiscal_year = '2013-14' jv.total_credit = jv.total_debit = sum_patient_amt jv.company = company jv.save() jvd = Document('Journal Voucher Detail') jvd.account = debit_to jvd.credit = sum_patient_amt jvd.against_invoice=self.doc.name jvd.parent = jv.name jvd.save() jvd1 = Document('Journal Voucher Detail') jvd1.account = patient_credit_to jvd1.debit = sum_patient_amt jvd1.parent = jv.name jvd1.save()
def make_JV(self,amount,referrer_physician_credit_to,referrer_physician_debit_to,company): jv=Document('Journal Voucher') jv.voucher_type='Bank Voucher' jv.posting_date=nowdate() jv.user_remark = 'Referrals Payment against bill '+ self.doc.name jv.fiscal_year = '2013-14' jv.total_credit = jv.total_debit = amount jv.company = company jv.against_bill = self.doc.name jv.save() jvd = Document('Journal Voucher Detail') jvd.account = referrer_physician_credit_to jvd.debit = amount jvd.parent = jv.name jvd.save() jvd1 = Document('Journal Voucher Detail') jvd1.account = referrer_physician_debit_to jvd1.credit = amount jvd1.parent = jv.name jvd1.save()
def set_year_balance(self): p = sql("select name, start_date, end_date, fiscal_year from `tabPeriod` where docstatus != 2 and period_type in ('Month', 'Year')") for d in p: if not sql("select name from `tabAccount Balance` where account=%s and period=%s", (self.doc.name, d[0])): ac = Document('Account Balance') ac.account = self.doc.name ac.period = d[0] ac.start_date = d[1].strftime('%Y-%m-%d') ac.end_date = d[2].strftime('%Y-%m-%d') ac.fiscal_year = d[3] ac.opening = 0 ac.debit = 0 ac.credit = 0 ac.balance = 0 ac.save(1)
def create_gl(self,data): for r in data: gl=Document("GL Entry") gl.account=r['account'] gl.debit=r['debit'] gl.credit=r['credit'] gl.against=r['against'] gl.against_voucher_type=r['against_voucher_type'] gl.voucher_type=r['voucher_type'] gl.against_voucher=r['against_voucher'] gl.voucher_no=r['voucher_no'] gl.cost_center=r['cost_center'] gl.posting_date=nowdate() gl.aging_date=nowdate() gl.fiscal_year=webnotes.conn.get_value("Global Defaults",None,"current_fiscal_year") gl.company='InnoWorth' gl.is_opening='No' gl.save()
def create_new_balances(self, det): # check if sql("select count(t1.name) from `tabAccount Balance` t1, tabAccount t2 where t1.fiscal_year=%s and t2.lft <= %s and t2.rgt >= %s and t2.name = t1.account", (self.doc.fiscal_year, det[0][0], det[0][1]))[0][0] < 13*(cint(det[0][1]) - cint(det[0][0]) +1)/2: period_list = self.get_period_list() accounts = sql("select name from tabAccount where lft <= %s and rgt >= %s" % (det[0][0], det[0][1])) for p in period_list: for a in accounts: # check if missing if not sql("select name from `tabAccount Balance` where period=%s and account=%s and fiscal_year=%s", (p[0], a[0], self.doc.fiscal_year)): d = Document('Account Balance') d.account = a[0] d.period = p[0] d.start_date = p[1].strftime('%Y-%m-%d') d.end_date = p[2].strftime('%Y-%m-%d') d.fiscal_year = self.doc.fiscal_year d.debit = 0 d.credit = 0 d.opening = 0 d.balance = 0 d.save(1)
def import_vouchers(common_values, data, start_idx, import_type): from webnotes.model.doc import Document from webnotes.model.bean import Bean from accounts.utils import get_fiscal_year from webnotes.utils.dateutils import parse_date messages = [] def get_account_details(account): acc_details = webnotes.conn.sql("""select is_pl_account, master_name from tabAccount where name=%s""", account, as_dict=1) if not acc_details: webnotes.msgprint("%s is not an Account" % account, raise_exception=1) return acc_details[0] def apply_cost_center_and_against_invoice(detail, d): account = get_account_details(detail.account) if account.is_pl_account == "Yes": detail.cost_center = d.cost_center if account.master_name: map_fields([ "against_sales_invoice:against_invoice", "against_purhase_invoice:against_voucher", "against_journal_voucher:against_jv" ], d, detail.fields) webnotes.conn.commit() try: jv = Document("Journal Voucher") webnotes.conn.begin() for i in xrange(len(data)): jv = Document("Journal Voucher") d = data[i][0] if import_type == "Voucher Import: Two Accounts" and flt( d.get("amount")) == 0: webnotes.message_log = ["Amount not specified"] raise Exception elif import_type == "Voucher Import: Multiple Accounts" and \ (flt(d.get("total_debit")) == 0 or flt(d.get("total_credit")) == 0): webnotes.message_log = [ "Total Debit and Total Credit amount can not be zero" ] raise Exception else: d.posting_date = parse_date(d.posting_date) d.due_date = d.due_date and parse_date(d.due_date) or None if d.ref_number: if not d.ref_date: raise webnotes.ValidationError, \ """Ref Date is Mandatory if Ref Number is specified""" d.ref_date = parse_date(d.ref_date) d.company = common_values.company map_fields([ "voucher_type", "posting_date", "naming_series", "remarks:user_remark", "ref_number:cheque_no", "ref_date:cheque_date", "is_opening", "due_date", "company" ], d, jv.fields) jv.fiscal_year = get_fiscal_year(jv.posting_date)[0] details = [] if import_type == "Voucher Import: Two Accounts": map_fields(["amount:total_debit", "amount:total_credit"], d, jv.fields) detail1 = Document("Journal Voucher Detail") detail1.parent = True detail1.parentfield = "entries" map_fields(["debit_account:account", "amount:debit"], d, detail1.fields) apply_cost_center_and_against_invoice(detail1, d) detail2 = Document("Journal Voucher Detail") detail2.parent = True detail2.parentfield = "entries" map_fields(["credit_account:account", "amount:credit"], d, detail2.fields) apply_cost_center_and_against_invoice(detail2, d) details = [detail1, detail2] elif import_type == "Voucher Import: Multiple Accounts": map_fields(["total_debit", "total_credit"], d, jv.fields) accounts = data[i][1] for acc in accounts: detail = Document("Journal Voucher Detail") detail.parent = True detail.parentfield = "entries" detail.account = acc detail.debit = flt(accounts[acc]) > 0 and flt( accounts[acc]) or 0 detail.credit = flt( accounts[acc]) < 0 and -1 * flt(accounts[acc]) or 0 apply_cost_center_and_against_invoice(detail, d) details.append(detail) if not details: webnotes.message_log = [ """No accounts found. If you entered accounts correctly, please check template once""" ] raise Exception doclist = Bean([jv] + details) # validate datatype from core.page.data_import_tool.data_import_tool import check_record for d in doclist: check_record(d.fields, d.parenttype) doclist.submit() messages.append("""<p style='color: green'>[row #%s] <a href=\"#Form/Journal Voucher/%s\">%s</a> imported</p>""" \ % ((start_idx + 1) + i, jv.name, jv.name)) webnotes.conn.commit() except Exception, e: webnotes.conn.rollback() err_msg = webnotes.message_log and "<br>".join( webnotes.message_log) or unicode(e) messages.append( """<p style='color: red'>[row #%s] %s failed: %s</p>""" % ((start_idx + 1) + i, jv.name or "", err_msg or "No message")) messages.append( "<p style='color: red'>All transactions rolled back</p>") webnotes.errprint(webnotes.getTraceback()) webnotes.message_log = []
def import_vouchers(common_values, data, start_idx, import_type): from webnotes.model.doc import Document from webnotes.model.bean import Bean from accounts.utils import get_fiscal_year from webnotes.utils.dateutils import parse_date messages = [] def get_account_details(account): acc_details = webnotes.conn.sql("""select is_pl_account, master_name from tabAccount where name=%s""", account, as_dict=1) if not acc_details: webnotes.msgprint("%s is not an Account" % account, raise_exception=1) return acc_details[0] def apply_cost_center_and_against_invoice(detail, d): account = get_account_details(detail.account) if account.is_pl_account=="Yes": detail.cost_center = d.cost_center if account.master_name: map_fields(["against_sales_invoice:against_invoice", "against_purhase_invoice:against_voucher", "against_journal_voucher:against_jv"], d, detail.fields) webnotes.conn.commit() try: jv = Document("Journal Voucher") webnotes.conn.begin() for i in xrange(len(data)): jv = Document("Journal Voucher") d = data[i][0] if import_type == "Voucher Import: Two Accounts" and flt(d.get("amount")) == 0: webnotes.message_log = ["Amount not specified"] raise Exception elif import_type == "Voucher Import: Multiple Accounts" and \ (flt(d.get("total_debit")) == 0 or flt(d.get("total_credit")) == 0): webnotes.message_log = ["Total Debit and Total Credit amount can not be zero"] raise Exception else: d.posting_date = parse_date(d.posting_date) d.due_date = d.due_date and parse_date(d.due_date) or None if d.ref_number: if not d.ref_date: webnotes.msgprint(_("Ref Date is Mandatory if Ref Number is specified"), raise_exception=1) d.ref_date = parse_date(d.ref_date) d.company = common_values.company map_fields(["voucher_type", "posting_date", "naming_series", "remarks:user_remark", "ref_number:cheque_no", "ref_date:cheque_date", "is_opening", "due_date", "company"], d, jv.fields) jv.fiscal_year = get_fiscal_year(jv.posting_date)[0] details = [] if import_type == "Voucher Import: Two Accounts": map_fields(["amount:total_debit", "amount:total_credit"], d, jv.fields) detail1 = Document("Journal Voucher Detail") detail1.parent = True detail1.parentfield = "entries" map_fields(["debit_account:account","amount:debit"], d, detail1.fields) apply_cost_center_and_against_invoice(detail1, d) detail2 = Document("Journal Voucher Detail") detail2.parent = True detail2.parentfield = "entries" map_fields(["credit_account:account","amount:credit"], d, detail2.fields) apply_cost_center_and_against_invoice(detail2, d) details = [detail1, detail2] elif import_type == "Voucher Import: Multiple Accounts": map_fields(["total_debit", "total_credit"], d, jv.fields) accounts = data[i][1] for acc in accounts: detail = Document("Journal Voucher Detail") detail.parent = True detail.parentfield = "entries" detail.account = acc detail.debit = flt(accounts[acc]) > 0 and flt(accounts[acc]) or 0 detail.credit = flt(accounts[acc]) < 0 and -1*flt(accounts[acc]) or 0 apply_cost_center_and_against_invoice(detail, d) details.append(detail) if not details: webnotes.message_log = ["""No accounts found. If you entered accounts correctly, please check template once"""] raise Exception doclist = Bean([jv]+details) # validate datatype from core.page.data_import_tool.data_import_tool import check_record for d in doclist: check_record(d.fields, d.parenttype) doclist.submit() messages.append("""<p style='color: green'>[row #%s] <a href=\"#Form/Journal Voucher/%s\">%s</a> imported</p>""" \ % ((start_idx + 1) + i, jv.name, jv.name)) webnotes.conn.commit() except Exception, e: webnotes.conn.rollback() err_msg = webnotes.message_log and "<br>".join(webnotes.message_log) or cstr(e) messages.append("""<p style='color: red'>[row #%s] %s failed: %s</p>""" % ((start_idx + 1) + i, jv.name or "", err_msg or "No message")) messages.append("<p style='color: red'>All transactions rolled back</p>") webnotes.errprint(webnotes.getTraceback()) webnotes.message_log = []
def make_si(self,args): parent=sql("select * from `tabSales Order` where name='"+(args['Sales Order No']).strip()+"'",as_dict=1) if parent: for r in parent: si=Document("Sales Invoice") si.customer=r['customer'] si.customer_name=r['customer_name'] si.posting_date=nowdate() si.due_date=nowdate() si.charge=r['charge'] si.company='InnoWorth' si.conversion_rate=1.00 si.customer_group='Individual' si.territory=r['territory'] si.debit_to=webnotes.conn.get_value('Account',{"master_name":r['customer']},'name') si.price_list_currency='INR' si.currency='INR' si.selling_price_list='Standard Selling' si.fiscal_year=webnotes.conn.get_value("Global Defaults", None, "current_fiscal_year") si.net_total_export=cstr(r['net_total_export']) si.grand_total_export=cstr(r['grand_total_export']) si.other_charges_total_export=cstr(r['other_charges_total_export']) si.grand_total=cstr(r['grand_total_export']) si.rounded_total_export=cstr(r['rounded_total_export']) si.save() si=Document("Sales Invoice",si.name) adv=c_amt=0.00 flag=False check=0 total=si.grand_total_export parent_jv=[] advance_payment=sql("select credit,parent,name,against_account from `tabJournal Voucher Detail` where account='"+si.debit_to+"' and is_advance='Yes' and credit<>0 and ifnull(against_invoice,'')='' and docstatus=1 order by name asc",as_list=1) if advance_payment: for s in advance_payment: if s[1] not in parent_jv: parent_jv.append(s[1]) if flt(total) < flt(s[0]) and flag==False: adv=cstr(si.grand_total_export) update_jv=sql("update `tabJournal Voucher Detail` set against_invoice='"+si.name+"', credit='"+cstr(total)+"' where name='"+s[2]+"'") jv = Document("Journal Voucher Detail") jv.account=si.debit_to jv.cost_center= "Main - Frsh" jv.credit= cstr(flt(s[0])-flt(total)) jv.is_advance= "Yes" jv.parent=s[1] jv.against_account=s[3] jv.docstatus=1 jv.save() flag=True elif flag==False: adv=cstr(flt(adv)+flt(s[0])) total=cstr(flt(total)-flt(s[0])) update_jv=sql("update `tabJournal Voucher Detail` set against_invoice='"+si.name+"' where name='"+s[2]+"'") if flt(total)==0: flag=True else: flag=False si.total_advance=cstr(adv) si.outstanding_amount=cstr(flt(r['grand_total_export'])-flt(adv)) si.docstatus=1 si.save() update=sql("update `tabSales Order` set per_billed='100' where name='"+cstr(r['name'])+"'") child=sql("select * from `tabSales Order Item` where parent='"+(args['Sales Order No']).strip()+"'",as_dict=1) html="" j=0 credit_amt=0.00 for s in child: j=j+1 sic=Document("Sales Invoice Item") sic.parent=si.name sic.item_code=s['item_code'] sic.item_name=s['item_name'] sic.item_group=s['item_group'] sic.description=s['description'] sic.qty=s['qty'] sic.stock_uom=s['stock_uom'] sic.ref_rate=s['ref_rate'] sic.amount=s['export_amount'] c_amt=cstr(flt(c_amt)+flt(sic.amount)) sic.export_rate=s['export_rate'] sic.export_amount=s['export_amount'] sic.income_account='Sales - innow' sic.cost_center='Main - innow' sic.warehouse=s['reserved_warehouse'] sic.sales_order=r['name'] sic.so_detail=s['name'] sic.docstatus=1 html+=("<tr><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(j)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(sic.item_code)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(sic.description)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;text-align:right;'><div>"+cstr(sic.qty)+"</div></td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(sic.stock_uom)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'><div style='text-align:right'>₹ "+cstr(sic.ref_rate)+"</div></td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'><div style='text-align: right'>₹ "+cstr(sic.export_amount)+"</div></td></tr>") update=sql("update `tabSales Order Item` set billed_amt='"+cstr(s['export_amount'])+"' where name='"+cstr(s['name'])+"'") sic.save() tax_html=self.sales_tax_html((args['Sales Order No']).strip(),si.name,1) if parent_jv: self.make_adv_payment_gl(parent_jv) data=[{"against_voucher":si.name,"account":si.debit_to,"debit":cstr(si.grand_total_export),"credit":"0","against":"Sales - innow","against_voucher_type":"Sales Invoice","voucher_type":"Sales Invoice","voucher_no":si.name,"cost_center":""},{"account":'Sales - innow',"debit":"0","credit":cstr(c_amt),"against":si.debit_to,"against_voucher_type":"","voucher_type":"Sales Invoice","voucher_no":si.name,"cost_center":"Main - innow","against_voucher":""}] self.create_gl(data) a=html_data({"posting_date":datetime.datetime.strptime(nowdate(),'%Y-%m-%d').strftime('%d/%m/%Y'),"due_date":datetime.datetime.strptime(nowdate(),'%Y-%m-%d').strftime('%d/%m/%Y'),"customer_name":si.customer_name,"net_total":cstr(si.net_total_export),"grand_total":cstr(si.grand_total_export),"rounded_total":cstr(si.rounded_total_export),"table_data":html,"date_1":"Posting Date","date_2":"Due Date","doctype":"Sales Invoice","doctype_no":si.name,"company":si.company,"addr_name":"","address":"","tax_detail":tax_html}) file_path_=attach_file(a,[si.name,"Account/Kirana","Sales Invoice"]) return {"Sales Invoice":si.namek,"File Copy":file_path_}
def import_vouchers(common_values, data, start_idx, import_type): from webnotes.model.doc import Document from webnotes.model.doclist import DocList from webnotes.model.code import get_obj from accounts.utils import get_fiscal_year from webnotes.utils.dateutils import parse_date messages = [] def get_account_details(account): acc_details = webnotes.conn.sql("""select is_pl_account, master_name from tabAccount where name=%s""", account, as_dict=1) if not acc_details: webnotes.msgprint("%s is not an Account" % account, raise_exception=1) return acc_details[0] def apply_cost_center_and_against_invoice(detail, d): account = get_account_details(detail.account) if account.is_pl_account=="Yes": detail.cost_center = d.cost_center if account.master_name: map_fields(["against_sales_invoice:against_invoice", "against_purhase_invoice:against_voucher", "against_journal_voucher:against_jv"], d, detail.fields) webnotes.conn.commit() for i in xrange(len(data)): d = data[i][0] jv = webnotes.DictObj() try: d.posting_date = parse_date(d.posting_date) d.due_date = d.due_date and parse_date(d.due_date) or None if d.ref_number: if not d.ref_date: raise webnotes.ValidationError, \ """Ref Date is Mandatory if Ref Number is specified""" d.ref_date = parse_date(d.ref_date) d.company = common_values.company jv = Document("Journal Voucher") map_fields(["voucher_type", "posting_date", "naming_series", "remarks:user_remark", "ref_number:cheque_no", "ref_date:cheque_date", "is_opening", "amount:total_debit", "amount:total_credit", "due_date", "company"], d, jv.fields) jv.fiscal_year = get_fiscal_year(jv.posting_date)[0] details = [] if import_type == "Voucher Import: Two Accounts": detail1 = Document("Journal Voucher Detail") detail1.parent = True detail1.parentfield = "entries" map_fields(["debit_account:account","amount:debit"], d, detail1.fields) apply_cost_center_and_against_invoice(detail1, d) detail2 = Document("Journal Voucher Detail") detail2.parent = True detail2.parentfield = "entries" map_fields(["credit_account:account","amount:credit"], d, detail2.fields) apply_cost_center_and_against_invoice(detail2, d) details = [detail1, detail2] elif import_type == "Voucher Import: Multiple Accounts": accounts = data[i][1] for acc in accounts: detail = Document("Journal Voucher Detail") detail.parent = True detail.parentfield = "entries" detail.account = acc detail.debit = flt(accounts[acc]) > 0 and flt(accounts[acc]) or 0 detail.credit = flt(accounts[acc]) < 0 and -1*flt(accounts[acc]) or 0 apply_cost_center_and_against_invoice(detail, d) details.append(detail) if not details: messages.append("""<p style='color: red'>No accounts found. If you entered accounts correctly, please check template once</p>""") return webnotes.conn.begin() doclist = DocList([jv]+details) doclist.submit() webnotes.conn.commit() messages.append("""<p style='color: green'>[row #%s] <a href=\"#Form/Journal Voucher/%s\">%s</a> imported</p>""" \ % ((start_idx + 1) + i, jv.name, jv.name)) except Exception, e: webnotes.conn.rollback() err_msg = webnotes.message_log and webnotes.message_log[0] or unicode(e) messages.append("<p style='color: red'>[row #%s] %s failed: %s</p>" \ % ((start_idx + 1) + i, jv.name or "", err_msg or "No message")) webnotes.errprint(webnotes.getTraceback()) webnotes.message_log = []
def service_si(self,s): total_amt=0.00 si=Document("Sales Invoice") si.customer=webnotes.conn.get_value("Customer",{"innoworth_id":(s['Customer Id']).strip()},"name") si.customer_name=webnotes.conn.get_value("Customer",{"innoworth_id":(s['Customer Id']).strip()},"name") si.posting_date=nowdate() si.due_date=nowdate() si.company='InnoWorth' si.conversion_rate=1.00 si.customer_group='Individual' si.territory=s['Territory'] si.charge=webnotes.conn.get_value("Applicable Territory",{"territory":s['Territory']},"parent") si.debit_to=webnotes.conn.get_value('Account',{"master_name":si.customer},'name') si.price_list_currency='INR' si.currency='INR' si.selling_price_list='Standard Selling' si.fiscal_year=webnotes.conn.get_value("Global Defaults", None, "current_fiscal_year") si.docstatus=1 si.save() html="" j=0 item_list=[] for r in s['Child']: j=j+1 child_data=sql("select name,item_name,item_group,description,stock_uom from `tabItem` where name='"+(r['Item Name']).strip()+"'",as_list=1) sic=Document("Sales Invoice Item") sic.parent=si.name sic.item_code=child_data[0][0] sic.item_name=child_data[0][1] sic.item_group=child_data[0][2] sic.description=child_data[0][3] sic.qty=(r['Qty']).strip() sic.stock_uom=child_data[0][4] sic.ref_rate=webnotes.conn.get_value("Item Price",{"item_code":(r['Item Name']).strip(),"price_list":"Standard Selling"},"ref_rate") sic.export_rate=webnotes.conn.get_value("Item Price",{"item_code":(r['Item Name']).strip(),"price_list":"Standard Selling"},"ref_rate") if child_data[0][1]: sic.export_amount=cstr(flt(sic.ref_rate)*flt((r['Qty']).strip())) else: sic.export_amount=cstr((r['Qty']).strip()) total_amt=cstr(flt(total_amt)+flt(sic.export_amount)) sic.income_account='Sales - innow' sic.cost_center='Main - innow' sic.docstatus=1 item_list.append({"item_code":sic.item_code,"export_amt":sic.export_amount}) sic.save() html+=("<tr><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(j)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(sic.item_code)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(sic.description)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;text-align:right;'><div>"+cstr(sic.qty)+"</div></td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(sic.stock_uom)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'><div style='text-align:right'>₹ "+cstr(sic.ref_rate)+"</div></td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'><div style='text-align: right'>₹ "+cstr(sic.export_amount)+"</div></td></tr>") tax_total=0.00 tax_html="" if si.charge: tax_total=self.create_tax(si.charge,si.name,item_list,'Sales Invoice','Sales Taxes and Charges',total_amt) tax_html=self.sales_tax_html(si.name,None,0) si_=Document('Sales Invoice',si.name) si_.net_total_export=cstr(total_amt) si_.other_charges_total_export=cstr(tax_total) si_.grand_total_export=cstr(flt(si_.net_total_export)+flt(si_.other_charges_total_export)) si_.grand_total=cstr(si_.grand_total_export) si_.rounded_total_export=cstr(round(flt(si_.grand_total_export))) adv=c_amt=0.00 flag=False check=0 total=si_.grand_total_export parent_jv=[] advance_payment=sql("select credit,parent,name,against_account from `tabJournal Voucher Detail` where account='"+si.debit_to+"' and is_advance='Yes' and credit<>0 and ifnull(against_invoice,'')='' and docstatus=1 order by name asc",as_list=1) if advance_payment: for s in advance_payment: if s[1] not in parent_jv: parent_jv.append(s[1]) if flt(total) < flt(s[0]) and flag==False: adv=cstr(si_.grand_total_export) update_jv=sql("update `tabJournal Voucher Detail` set against_invoice='"+si.name+"', credit='"+cstr(total)+"' where name='"+s[2]+"'") jv = Document("Journal Voucher Detail") jv.account=si.debit_to jv.cost_center= "Main - Frsh" jv.credit= cstr(flt(s[0])-flt(total)) jv.is_advance= "Yes" jv.parent=s[1] jv.against_account=s[3] jv.docstatus=1 jv.save() flag=True elif flag==False: adv=cstr(flt(adv)+flt(s[0])) total=cstr(flt(total)-flt(s[0])) update_jv=sql("update `tabJournal Voucher Detail` set against_invoice='"+si.name+"' where name='"+s[2]+"'") if flt(total)==0: flag=True else: flag=False si_.total_advance=cstr(adv) si_.outstanding_amount=cstr(flt(si_.grand_total_export)-flt(adv)) si_.docstatus=1 si_.save() if parent_jv: self.make_adv_payment_gl(parent_jv) data=[{"against_voucher":si.name,"account":si.debit_to,"debit":cstr(si_.grand_total_export),"credit":"0","against":"Sales - innow","against_voucher_type":"Sales Invoice","voucher_type":"Sales Invoice","voucher_no":si.name,"cost_center":""},{"against_voucher":"","account":'Sales - innow',"debit":"0","credit":cstr(total_amt),"against":si.debit_to,"against_voucher_type":"","voucher_type":"Sales Invoice","voucher_no":si.name,"cost_center":"Main - innow"}] self.create_gl(data) a=html_data({"posting_date":datetime.datetime.strptime(nowdate(),'%Y-%m-%d').strftime('%d/%m/%Y'),"due_date":datetime.datetime.strptime(nowdate(),'%Y-%m-%d').strftime('%d/%m/%Y'),"customer_name":si.customer_name,"net_total":si_.net_total_export,"grand_total":si_.grand_total_export,"rounded_total":si_.rounded_total_export,"table_data":html,"date_1":"Posting Date","date_2":"Due Date","doctype":"Sales Invoice","doctype_no":si.name,"company":si.company,"addr_name":"","address":"","tax_detail":tax_html}) file_path_=attach_file(a,[si.name,"Account/Kirana","Sales Invoice"]) return {"Sales Invoice":si.name,"File Copy":file_path_}