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
