def sle(self): make_bin = [] qry = "select sum(actual_qty) from `tabStock Ledger Entry` where item_code='" + self.doc.item + "' and warehouse='Finished Goods - P'" #ebnotes.errprint(qry) w3 = webnotes.conn.sql(qry) qty_after_transaction = cstr(cint(w3[0][0]) + cint(self.doc.quantity)) actual_qty = self.doc.quantity import time tim = time.strftime("%X") from webnotes.model.doc import Document d = Document('Stock Ledger Entry') d.item_code = self.doc.item d.batch_no = self.doc.name d.stock_uom = 'Nos' d.warehouse = 'Finished Goods - P' d.actual_qty = actual_qty d.posting_date = today() d.posting_time = tim d.voucher_type = 'Purchase Receipt' d.serial_no = self.doc.serial_no.replace('\n', '') d.qty_after_transaction = qty_after_transaction d.is_cancelled = 'No' d.company = 'PowerCap' d.docstatus = 1 d.name = 'Batch wise item updation' d.owner = webnotes.session['user'] d.fields['__islocal'] = 1 d.save(new=1) make_bin.append({ "doctype": 'b', "sle_id": d.name, "item_code": self.doc.item, "warehouse": 'Finished Goods - P', "actual_qty": actual_qty })
def make_ledger(self): from webnotes.model.doc import Document for d in getlist(self.doclist, 'entries'): check = webnotes.conn.sql( "select status from `tabSerial No` where name='" + (d.serial_no).strip() + "'", as_list=1) if check[0][0] == 'Available': sle = Document('Stock Ledger Entry') sle.item_code = d.item_code sle.serial_no = d.serial_no sle.posting_date = nowdate() sle.posting_time = nowtime() sle.actual_qty = -d.qty qty_data = webnotes.conn.sql( "select b.actual_qty,foo.warehouse from(select item_code,warehouse from `tabSerial No` where name='" + (d.serial_no).strip() + "') as foo,tabBin as b where b.item_code=foo.item_code and b.warehouse=foo.warehouse", as_list=1, debug=1) after_transfer = cstr(flt(qty_data[0][0]) - flt(d.qty)) update_bin = webnotes.conn.sql( "update `tabBin` set actual_qty='" + after_transfer + "', projected_qty='" + after_transfer + "' where item_code='" + d.item_code + "' and warehouse='" + qty_data[0][1] + "'") sle.qty_after_transaction = after_transfer sle.warehouse = qty_data[0][1] sle.voucher_type = 'Delivery Note' sle.voucher_no = 'GRN000056' sle.stock_uom = webnotes.conn.get_value( "Item", d.item_code, "stock_uom") sle.company = 'PowerCap' sle.fiscal_year = webnotes.conn.get_value( "Global Defaults", None, "current_fiscal_year") update_serial = webnotes.conn.sql( "update `tabSerial No` set status='Delivered',warehouse=(select name from tabCustomer where 1=2) where name='" + (d.serial_no).strip() + "'") sle.docstatus = 1 sle.save()
def make_ledger(self): from webnotes.model.doc import Document for d in getlist(self.doclist, 'entries'): check=webnotes.conn.sql("select status from `tabSerial No` where name='"+(d.serial_no).strip()+"'",as_list=1) if check[0][0]=='Available': sle=Document('Stock Ledger Entry') sle.item_code=d.item_code sle.serial_no=d.serial_no sle.posting_date=nowdate() sle.posting_time=nowtime() sle.actual_qty=-d.qty qty_data=webnotes.conn.sql("select b.actual_qty,foo.warehouse from(select item_code,warehouse from `tabSerial No` where name='"+(d.serial_no).strip()+"') as foo,tabBin as b where b.item_code=foo.item_code and b.warehouse=foo.warehouse",as_list=1,debug=1) after_transfer=cstr(flt(qty_data[0][0])-flt(d.qty)) update_bin=webnotes.conn.sql("update `tabBin` set actual_qty='"+after_transfer+"', projected_qty='"+after_transfer+"' where item_code='"+d.item_code+"' and warehouse='"+qty_data[0][1]+"'") sle.qty_after_transaction=after_transfer sle.warehouse=qty_data[0][1] sle.voucher_type='Delivery Note' sle.voucher_no='GRN000056' sle.stock_uom=webnotes.conn.get_value("Item",d.item_code,"stock_uom") sle.company='PowerCap' sle.fiscal_year=webnotes.conn.get_value("Global Defaults", None, "current_fiscal_year") update_serial=webnotes.conn.sql("update `tabSerial No` set status='Delivered',warehouse=(select name from tabCustomer where 1=2) where name='"+(d.serial_no).strip()+"'") sle.docstatus=1 sle.save()
def sle(self): make_bin=[] qry="select sum(actual_qty) from `tabStock Ledger Entry` where item_code='"+self.doc.item+"' and warehouse='Finished Goods - P'" #ebnotes.errprint(qry) w3=webnotes.conn.sql(qry) qty_after_transaction=cstr(cint(w3[0][0])+cint(self.doc.quantity)) actual_qty=self.doc.quantity import time tim=time.strftime("%X") from webnotes.model.doc import Document d = Document('Stock Ledger Entry') d.item_code=self.doc.item d.batch_no=self.doc.name d.stock_uom='Nos' d.warehouse='Finished Goods - P' d.actual_qty=actual_qty d.posting_date=today() d.posting_time=tim d.voucher_type='Purchase Receipt' d.serial_no=self.doc.serial_no.replace('\n','') d.qty_after_transaction=qty_after_transaction d.is_cancelled = 'No' d.company='PowerCap' d.docstatus = 1; d.name = 'Batch wise item updation' d.owner = webnotes.session['user'] d.fields['__islocal'] = 1 d.save(new=1) make_bin.append({ "doctype": 'b', "sle_id":d.name, "item_code": self.doc.item, "warehouse": 'Finished Goods - P', "actual_qty": actual_qty })
def create_dn(self): ''' import urllib.request import json json_dict=[] ''' dn=Document('Delivery Note') dn.customer=self.doc.customer dn.customer_name=webnotes.conn.get_value("Customer",self.doc.customer,"customer_name") dn.company='InnoWorth' dn.conversion_rate=1.00 dn.posting_date=nowdate() dn.posting_time=nowtime() dn.customer_address=webnotes.conn.get_value("Address",{"customer":self.doc.customer},"name") dn.address_display=get_address_display(dn.customer_address) dn.price_list_currency='INR' dn.currency='INR' dn.docstatus=1 dn.status="Submitted" dn.selling_price_list='Standard Selling' dn.fiscal_year=webnotes.conn.get_value("Global Defaults", None, "current_fiscal_year") dn.save() j=0 html="" net_tot=0.00 for s in getlist(self.doclist,"purchase_receipt_details"): j=j+1 dni=Document("Delivery Note Item") dni.item_code=s.item_code dni.item_name=s.item_name dni.description=s.description dni.qty=s.qty dni.docstatus=1 dni.ref_rate=webnotes.conn.get_value("Item Price",{"item_code":dni.item_code,"price_list":"Standard Selling"},"ref_rate") dni.export_rate=webnotes.conn.get_value("Item Price",{"item_code":dni.item_code,"price_list":"Standard Selling"},"ref_rate") dni.export_amount=cstr(flt(s.qty)*flt(dni.ref_rate)) net_tot=cstr(flt(net_tot)+flt(dni.export_amount)) dni.warehouse=s.warehouse dni.stock_uom=s.uom dni.serial_no=s.serial_no dni.parent=dn.name dni.save() update_bin=("update tabBin set actual_qty=actual_qty-"+cstr(dni.qty)+" and projected_qty=projected_qty-"+cstr(dni.qty)+" where item_code='"+dni.item_code+"' and warehouse='"+dni.warehouse+"'") 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(dni.item_code)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(dni.description)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;text-align:right;'><div>"+cstr(dni.qty)+"</div></td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'>"+cstr(dni.stock_uom)+"</td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'><div style='text-align:right'>₹ "+cstr(dni.ref_rate)+"</div></td><td style='border:1px solid rgb(153, 153, 153);word-wrap: break-word;'><div style='text-align: right'>₹ "+cstr(dni.export_amount)+"</div></td></tr>") stl=Document("Stock Ledger Entry") stl.item_code=s.item_code stl.stock_uom=s.uom stl.serial_no=s.serial_no stl.warehouse=s.warehouse stl.posting_date=nowdate() stl.voucher_type="Delivery Note" stl.voucher_no=dn.name stl.voucher_detail_no=dni.name stl.is_cancelled="No" stl.fiscal_year=webnotes.conn.get_value("Global Defaults", None, "current_fiscal_year") stl.actual_qty=cstr(s.qty) qty=webnotes.conn.sql("select qty_after_transaction from `tabStock Ledger Entry` where item_code='"+s.item_code+"' and warehouse='"+s.warehouse+"' order by name desc limit 1",as_list=1) stl.qty_after_transaction=cstr(flt(qty[0][0])-flt(s.qty)) stl.save() if dni.serial_no: for se in dni.serial_no: update=webnotes.conn.sql("update `tabSerial No` set status='Delivered', warehouse=(select name from tabCustomer where 1=2) where name='"+se+"'") #json_dict.append({"serial_no":se,"supplier_name":self.doc.supplier_name,"item_code":s.item_code}) dn_=Document("Delivery Note",dn.name) dn_.net_total_export=cstr(net_tot) dn_.grand_total_export=cstr(net_tot) dn_.rounded_total_export=cstr(net_tot) a=html_data({"posting_date":datetime.datetime.strptime(nowdate(),'%Y-%m-%d').strftime('%d/%m/%Y'),"due_date":"","customer_name":dn.customer_name,"net_total":dn_.net_total_export,"grand_total":dn_.grand_total_export,"rounded_total":dn_.rounded_total_export,"table_data":html,"date_1":"Posting Date","date_2":"","doctype":"Delivery Note","doctype_no":dn.name,"company":dn.company,"addr_name":"Address","address":dn.address_display,"tax_detail":""}) attach_file(a,[dn.name,"Selling/Kirana","Delivery Note"]) '''
def on_submit(self): if cint(self.doc.update_stock) == 1: self.update_stock_ledger() else: # Check for Approving Authority if not self.doc.recurring_id: get_obj('Authorization Control').validate_approving_authority(self.doc.doctype, self.doc.company, self.doc.grand_total, self) self.check_prev_docstatus() self.update_status_updater_args() self.update_prevdoc_status() # this sequence because outstanding may get -ve self.make_gl_entries() self.check_credit_limit(self.doc.debit_to) if not cint(self.doc.is_pos) == 1: self.update_against_document_in_jv() self.update_c_form() self.update_time_log_batch(self.doc.name) self.convert_to_recurring() self.set_flag() sum_physician_amt=0 sum_patient_amt=0 referrer={} referrer1={} for s in getlist(self.doclist,'entries'): if s.item_code: bin_qty=webnotes.conn.sql("select actual_qty from tabBin where item_code='"+s.item_code+"' and warehouse='"+s.warehouse+"'",as_list=1) if bin_qty: bin_amt=flt(bin_qty[0][0])-flt(s.qty) else: bin_amt=flt(s.qty) a=webnotes.conn.sql("update tabBin set actual_qty="+cstr(bin_amt)+" where item_code='"+s.item_code+"' and warehouse='"+s.warehouse+"'") stl=Document('Stock Ledger Entry') stl.actual_qty=cstr(0-flt(s.qty)) stl.item_code=s.item_code stl.warehouse=s.warehouse stl.save() if s.referrer_name: check=webnotes.conn.sql("select salutation from tabLead where name='"+s.referrer_name+"'",as_list=1) if check[0][0]=='Dr.': sum_physician_amt = 0.0 if s.referrer_physician_credit_to: if not cstr(s.referrer_physician_credit_to) in referrer: referrer[cstr(s.referrer_physician_credit_to)] = 0.0 webnotes.errprint(referrer) update_flag=webnotes.conn.sql("update `tabEncounter` set is_invoiced='True' where name='%s'"%(s.encounter_id)) if not self.doc.lump_sum_payment: if s.referral_rule == "Percent": sum_physician_amt=flt(sum_physician_amt) + (flt(s.export_rate)*(flt(s.referral_fee)/100)) else: sum_physician_amt=flt(sum_physician_amt)+flt(s.referral_fee) if s.referrer_physician_credit_to and s.referral_fee: referrer[s.referrer_physician_credit_to] = flt(referrer[s.referrer_physician_credit_to])+sum_physician_amt if s.referrer_physician_debit_to: referrer1[s.referrer_physician_credit_to] = s.referrer_physician_debit_to if self.doc.lump_sum_payment == '1': sum_physician_amt = self.doc.amount amt=0 for e in getlist(self.doclist,'advance_adjustment_details'): if e.allocated_amount: amt=amt+flt(e.allocated_amount) outstanding_amount=flt(self.doc.patient_amount)-flt(self.doc.paid_amount_data)-flt(amt) w=webnotes.conn.sql("update `tabSales Invoice` set outstanding_amount='"+cstr(outstanding_amount)+"' where name='"+self.doc.name+"'") # webnotes.errprint(referrer) # webnotes.errprint(referrer1) for key in referrer: self.make_JV(referrer[key],key,referrer1[key],self.doc.company) self.make_JV1(self.doc.paid_amount_data,self.doc.debit_to,self.doc.patient_credit_to,self.doc.company)
def set_actual_qty(self): from webnotes.utils import get_first_day, get_last_day, add_to_date, nowdate, getdate today = nowdate() for p in getlist(self.doclist, 'item_details_table'): qr = webnotes.conn.sql( """ select is_asset_item from `tabItem` where item_code=%s""", p.item) if qr[0][0] == 'No': #webnotes.errprint("Is not asset item") qry = webnotes.conn.sql( """select sum(actual_qty) from `tabStock Ledger Entry` where item_code=%s and warehouse=%s""", (p.item, p.warehouse)) #webnotes.errprint(qry) qty_after_transaction = cstr( cint(qry[0][0]) - cint(p.quantity)) actual_qty = cstr(0 - cint(p.quantity)) tim = time.strftime("%X") #webnotes.errprint(qty_after_transaction) if cint(qty_after_transaction) < 0: webnotes.throw( "Not enough quantity available at specified Warehouse for item=" + cstr(p.item)) else: #webnotes.errprint("in stock ledger entry") d = Document('Stock Ledger Entry') d.item_code = p.item #d.batch_no=p.batch_no d.actual_qty = actual_qty #webnotes.errprint(actual_qty) d.qty_after_transaction = qty_after_transaction #webnotes.errprint(d.qty_after_transaction) #d.stock_uom=p.product_uom d.warehouse = p.warehouse d.voucher_type = 'Project Management' #webnotes.errprint(d.voucher_type) #d.valuation_rate=p.product_rate #d.stock_value=p.quantity d.posting_date = today #webnotes.errprint(d.posting_date) #webnotes.errprint(d.posting_date) d.posting_time = tim #d.save() d.docstatus = 1 #d.name = 'SLE/00000008' d.owner = webnotes.session['user'] d.fields['__islocal'] = 1 d.voucher_no = self.doc.name d.voucher_detail_no = p.name d.save() a = webnotes.conn.sql( "select actual_qty,projected_qty from`tabBin` where item_code='" + p.item + "' and warehouse='" + p.warehouse + "'", as_list=1, debug=1) #webnotes.errprint(a) #webnotes.errprint(a[0][0]) #webnotes.errprint(a[0][1]) if a: actual_qty = cstr(cint(a[0][0]) - cint(p.quantity)) projected_qty = cstr(cint(a[0][0]) - cint(p.quantity)) q = webnotes.conn.sql( "update `tabBin` set actual_qty=%s,Projected_qty=%s where item_code='" + p.item + "' and warehouse='" + p.warehouse + "'", (actual_qty, projected_qty), debug=1) else: pass #webnotes.errprint("Updated") #make_bin.append({ # "doctype": 'pm', # "sle_id":d.name, # "item_code": p.item, # "warehouse": p.warehouse, # "actual_qty": cstr(0-cint(p.quantity)) # #}) else: pass
def set_actual_qty(self): from webnotes.utils import get_first_day, get_last_day, add_to_date, nowdate, getdate today = nowdate() for p in getlist(self.doclist, 'item_details_table'): qr=webnotes.conn.sql(""" select is_asset_item from `tabItem` where item_code=%s""",p.item) if qr[0][0] =='No': #webnotes.errprint("Is not asset item") qry=webnotes.conn.sql("""select sum(actual_qty) from `tabStock Ledger Entry` where item_code=%s and warehouse=%s""", (p.item,p.warehouse)) #webnotes.errprint(qry) qty_after_transaction=cstr(cint(qry[0][0])-cint(p.quantity)) actual_qty=cstr(0-cint(p.quantity)) tim=time.strftime("%X") #webnotes.errprint(qty_after_transaction) if cint(qty_after_transaction) < 0: webnotes.throw("Not enough quantity available at specified Warehouse for item=" +cstr(p.item)) else: #webnotes.errprint("in stock ledger entry") d = Document('Stock Ledger Entry') d.item_code=p.item #d.batch_no=p.batch_no d.actual_qty=actual_qty #webnotes.errprint(actual_qty) d.qty_after_transaction=qty_after_transaction #webnotes.errprint(d.qty_after_transaction) #d.stock_uom=p.product_uom d.warehouse=p.warehouse d.voucher_type='Project Management' #webnotes.errprint(d.voucher_type) #d.valuation_rate=p.product_rate #d.stock_value=p.quantity d.posting_date=today #webnotes.errprint(d.posting_date) #webnotes.errprint(d.posting_date) d.posting_time=tim #d.save() d.docstatus = 1; #d.name = 'SLE/00000008' d.owner = webnotes.session['user'] d.fields['__islocal'] = 1 d.voucher_no = self.doc.name d.voucher_detail_no =p.name d.save() a=webnotes.conn.sql("select actual_qty,projected_qty from`tabBin` where item_code='"+p.item+"' and warehouse='"+p.warehouse+"'",as_list=1,debug=1) #webnotes.errprint(a) #webnotes.errprint(a[0][0]) #webnotes.errprint(a[0][1]) if a: actual_qty=cstr(cint(a[0][0])-cint(p.quantity)) projected_qty=cstr(cint(a[0][0])-cint(p.quantity)) q=webnotes.conn.sql("update `tabBin` set actual_qty=%s,Projected_qty=%s where item_code='"+p.item+"' and warehouse='"+p.warehouse+"'",(actual_qty,projected_qty),debug=1) else: pass #webnotes.errprint("Updated") #make_bin.append({ # "doctype": 'pm', # "sle_id":d.name, # "item_code": p.item, # "warehouse": p.warehouse, # "actual_qty": cstr(0-cint(p.quantity)) # #}) else: pass