def create_auto_indent(self, i, doc_type, doc_name):
""" Create indent on reaching reorder level """
indent = Document('Indent')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Indent. It was raised because the (actual + ordered + indented - reserved) quantity reaches re-order level when %s %s was created" % (
doc_type, doc_name)
indent.save(1)
indent_obj = get_obj('Indent', indent.name, with_children=1)
indent_details_child = addchild(indent_obj.doc, 'indent_details',
'Indent Detail', 0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date = add_days(
nowdate(), cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
indent_details_child.qty = i['re_order_qty']
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Indent', indent.name, with_children=1)
indent_obj.validate()
set(indent_obj.doc, 'docstatus', 1)
indent_obj.on_submit()
msgprint(
"Item: " + self.doc.item_code +
" is to be re-ordered. Indent %s raised. It was generated from %s %s"
% (indent.name, doc_type, doc_name))
if (i['email_notify']):
send_email_notification(doc_type, doc_name)
def validate(self):
self.validate_fiscal_year()
# Step 1:=> set status as "Draft"
set(self.doc, 'status', 'Draft')
# Step 2:=> get Purchase Common Obj
pc_obj = get_obj(dt='Purchase Common')
# Step 3:=> validate mandatory
pc_obj.validate_mandatory(self)
# Step 4:=> validate for items
pc_obj.validate_for_items(self)
# Step 5:=> validate conversion rate
pc_obj.validate_conversion_rate(self)
# Get po date
pc_obj.get_prevdoc_date(self)
# validate_doc
self.validate_doc(pc_obj)
# Check for stopped status
self.check_for_stopped_status(pc_obj)
# get total in words
self.doc.in_words = pc_obj.get_total_in_words(get_defaults().get('currency') and get_defaults()['currency'] or 'INR', self.doc.grand_total)
self.doc.in_words_import = pc_obj.get_total_in_words(self.doc.currency, self.doc.grand_total_import)
def create_auto_indent(self, i , doc_type, doc_name, cur_qty):
""" Create indent on reaching reorder level """
indent = Document('Purchase Request')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Purchase Request. It was raised because the (actual + ordered + indented - reserved) quantity reaches re-order level when %s %s was created"%(doc_type,doc_name)
indent.save(1)
indent_obj = get_obj('Purchase Request',indent.name,with_children=1)
indent_details_child = addchild(indent_obj.doc,'indent_details','Purchase Request Item',0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date= add_days(nowdate(),cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
indent_details_child.qty = i['re_order_qty'] or (flt(i['re_order_level']) - flt(cur_qty))
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Purchase Request',indent.name,with_children=1)
indent_obj.validate()
set(indent_obj.doc,'docstatus',1)
indent_obj.on_submit()
msgprint("Item: " + self.doc.item_code + " is to be re-ordered. Purchase Request %s raised. It was generated from %s %s"%(indent.name,doc_type, doc_name ))
if(i['email_notify']):
send_email_notification(doc_type,doc_name)
def reorder_indent(self,i,item_reorder_level,doc_type,doc_name,email_notify=1):
indent = Document('Indent')
indent.transaction_date = nowdate()
indent.naming_series = 'IDT'
indent.company = get_defaults()['company']
indent.fiscal_year = get_defaults()['fiscal_year']
indent.remark = "This is an auto generated Indent. It was raised because the projected quantity has fallen below the minimum re-order level when %s %s was created"%(doc_type,doc_name)
indent.save(1)
indent_obj = get_obj('Indent',indent.name,with_children=1)
indent_details_child = addchild(indent_obj.doc,'indent_details','Indent Detail',0)
indent_details_child.item_code = self.doc.item_code
indent_details_child.uom = self.doc.stock_uom
indent_details_child.warehouse = self.doc.warehouse
indent_details_child.schedule_date= add_days(nowdate(),cint(i['lead_time_days']))
indent_details_child.item_name = i['item_name']
indent_details_child.description = i['description']
indent_details_child.item_group = i['item_group']
if (i['min_order_qty'] < ( flt(item_reorder_level)-flt(self.doc.projected_qty) )):
indent_details_child.qty =flt(flt(item_reorder_level)-flt(self.doc.projected_qty))
else:
indent_details_child.qty = i['min_order_qty']
indent_details_child.brand = i['brand']
indent_details_child.save()
indent_obj = get_obj('Indent',indent.name,with_children=1)
indent_obj.validate()
set(indent_obj.doc,'docstatus',1)
indent_obj.on_submit()
msgprint("Item: " + self.doc.item_code + " is to be re-ordered. Indent %s raised.Was generated from %s %s"%(indent.name,doc_type, doc_name ))
if(email_notify):
send_email_notification(doc_type,doc_name)
def lease_installment_post(self, args):
"""
Posts the Installment receipt into Journal Voucher
"""
next_inst = sql(
"select amount,name from `tabLease Installment` where parent=%s and ifnull(cheque_number,'')='' order by due_date limit 1",
self.doc.name,
)
data = json.loads(args)
data["voucher_type"] = "Lease Receipt"
data["naming_series"] = "JV"
data["amount"] = next_inst[0][0]
data["debit_account"] = data.get("bank_account")
data["credit_account"] = self.doc.account
data["fiscal_year"] = get_defaults()["fiscal_year"]
data["company"] = get_defaults()["company"]
jv_name = post_jv(data)
sql(
"update `tabLease Installment` set cheque_number=%s, cheque_date=%s, jv_number=%s where name=%s",
(data.get("cheque_number"), data.get("cheque_date"), jv_name, next_inst[0][1]),
)
self.doclist = [Document(d.doctype, d.name) for d in self.doclist]
def loan_post(self): data['voucher_type']='Loan Issue' data['naming_series']='JV' data['fiscal_year'] = get_defaults()['fiscal_year'] # To be modified to take care data['company'] = get_defaults()['company'] data['debit_account'] = self.doc['receivable_account'] data['credit_account'] = self.doc['account'] data['amount'] = self.doc.loan_amount jv_name=post_jv(data)
def set_print_format_fields(self):
for d in getlist(self.doclist, 'entries'):
chk_type = sql("select master_type, account_type from `tabAccount` where name='%s'" % d.account)
master_type, acc_type = chk_type and cstr(chk_type[0][0]) or '', chk_type and cstr(chk_type[0][1]) or ''
if master_type in ['Supplier', 'Customer']:
if not self.doc.pay_to_recd_from:
self.doc.pay_to_recd_from = get_value(master_type, ' - '.join(d.account.split(' - ')[:-1]), master_type == 'Customer' and 'customer_name' or 'supplier_name')
if acc_type == 'Bank or Cash':
amt = cint(d.debit) and d.debit or d.credit
self.doc.total_amount = get_defaults()['currency']+'. '+ cstr(amt)
self.doc.total_amount_in_words = get_obj('Sales Common').get_total_in_words(get_defaults()['currency'], cstr(amt))
def yr_wk_dates(self,fy):
from datetime import date
yr_st = get_defaults()['year_start_date']
yr_en = get_defaults()['year_end_date']
fy = fy.split('-')
y1 = yr_st.split('-')
date1 = date(cint(fy[0]),cint(y1[1]),cint(y1[2]))
y2 = yr_en.split('-')
date2 = date(cint(fy[1]),cint(y2[1]),cint(y2[2]))
date_lst = [[1,self.get_months(cint(y1[1]))]]
m1=cint(y1[1])+1
x_axis_lst = [[1,'Week1',cint(y1[1])]]
from datetime import date, timedelta
d =dt= date1
week=k=1
for i in range(0,53):
if dt <= date2:
if(d.weekday()>3):
d = d+timedelta(7-d.weekday())
else:
d = d - timedelta(d.weekday())
dlt = timedelta(days = (week-1)*7)
dt = d + dlt + timedelta(days=6)
m2 = cint(sql("Select month('%s')"%dt)[0][0])
if(m1 == m2):
date_lst.append([i+2,self.get_months(m2)])
x_axis_lst.append([i+2,'Week1',m2])
k=1
m1 += 1
if(m1==13): m1 =1
else:
date_lst.append([i+2,' '])
x_axis_lst.append([i+2,'Week%d'%k,m2])
week += 1
k +=1
return [date_lst,x_axis_lst]
def yr_wk_dates(self,fy):
from datetime import date
yr_st = get_defaults()['year_start_date']
yr_en = get_defaults()['year_end_date']
fy = fy.split('-')
y1 = yr_st.split('-')
date1 = date(cint(fy[0]),cint(y1[1]),cint(y1[2]))
y2 = yr_en.split('-')
date2 = date(cint(fy[1]),cint(y2[1]),cint(y2[2]))
date_lst = [[1,self.get_months(cint(y1[1]))]]
m1=cint(y1[1])+1
x_axis_lst = [[1,'Week1',cint(y1[1])]]
from datetime import date, timedelta
d =dt= date1
week=k=1
for i in range(0,53):
if dt <= date2:
if(d.weekday()>3):
d = d+timedelta(7-d.weekday())
else:
d = d - timedelta(d.weekday())
dlt = timedelta(days = (week-1)*7)
dt = d + dlt + timedelta(days=6)
m2 = cint(sql("Select month('%s')"%dt)[0][0])
if(m1 == m2):
date_lst.append([i+2,self.get_months(m2)])
x_axis_lst.append([i+2,'Week1',m2])
k=1
m1 += 1
if(m1==13): m1 =1
else:
date_lst.append([i+2,' '])
x_axis_lst.append([i+2,'Week%d'%k,m2])
week += 1
k +=1
return [date_lst,x_axis_lst]
def get_att_data(self):
fy = get_defaults()['fiscal_year'] #get default fiscal year
comp = get_defaults()['company'] #get default company
#get naming series of attendance
import webnotes.model.doctype
docfield = webnotes.model.doctype.get('Attendance')
series = [d.options for d in docfield if d.doctype == 'DocField' and d.fieldname == 'naming_series']
if not series:
msgprint("Please create naming series for Attendance.\nGo to Setup--> Numbering Series.")
raise Exception
else:
sr = series[0] or ''
return {'fy':fy,'comp':comp,'sr':sr}
def create_production_order(self, items):
"""Create production order. Called from Production Planning Tool"""
default_values = {
'posting_date': nowdate(),
'origin': 'MRP',
'wip_warehouse': '',
'fg_warehouse': '',
'status': 'Draft',
'fiscal_year': get_defaults()['fiscal_year']
}
pro_list = []
for item_so in items:
if item_so[1]:
self.validate_production_order_against_so(
item_so[0], item_so[1], items[item_so].get("qty"))
pro_doc = Document('Production Order')
pro_doc.production_item = item_so[0]
pro_doc.sales_order = item_so[1]
for key in items[item_so]:
pro_doc.fields[key] = items[item_so][key]
for key in default_values:
pro_doc.fields[key] = default_values[key]
pro_doc.save(new=1)
pro_list.append(pro_doc.name)
return pro_list
def get_period_balance(self, arg):
acc, f, t = arg.split('~~~')
c, fy = '', get_defaults()['fiscal_year']
det = webnotes.conn.sql(
"select debit_or_credit, lft, rgt, is_pl_account from tabAccount where name=%s",
acc)
if f: c += (' and t1.posting_date >= "%s"' % f)
if t: c += (' and t1.posting_date <= "%s"' % t)
bal = webnotes.conn.sql(
"select sum(ifnull(t1.debit,0))-sum(ifnull(t1.credit,0)) from `tabGL Entry` t1 where t1.account='%s' and ifnull(is_opening, 'No') = 'No' %s"
% (acc, c))
bal = bal and flt(bal[0][0]) or 0
if det[0][0] != 'Debit':
bal = (-1) * bal
# add opening for balance sheet accounts
if det[0][3] == 'No':
opening = flt(
webnotes.conn.sql(
"select opening from `tabAccount Balance` where account=%s and period=%s",
(acc, fy))[0][0])
bal = bal + opening
return flt(bal)
def setup_account(self, args):
import webnotes, json
if isinstance(args, basestring):
args = json.loads(args)
webnotes.conn.begin()
self.update_profile_name(args)
add_all_roles_to(webnotes.session.user)
self.create_fiscal_year_and_company(args)
self.set_defaults(args)
create_territories()
self.create_price_lists(args)
self.create_feed_and_todo()
self.create_email_digest()
webnotes.clear_cache()
msgprint(
"Company setup is complete. This page will be refreshed in a moment."
)
webnotes.conn.commit()
return {
'sys_defaults':
get_defaults(),
'user_fullname': (args.get('first_name') or '') +
(args.get('last_name') and (" " + args.get('last_name')) or '')
}
def validate(self):
self.so_required()
self.validate_fiscal_year()
self.validate_proj_cust()
sales_com_obj = get_obj(dt="Sales Common")
sales_com_obj.check_stop_sales_order(self)
sales_com_obj.check_active_sales_items(self)
sales_com_obj.get_prevdoc_date(self)
self.validate_mandatory()
# self.validate_prevdoc_details()
self.validate_reference_value()
self.validate_for_items()
sales_com_obj.make_packing_list(self, "delivery_note_details")
get_obj("Stock Ledger").validate_serial_no(self, "packing_details")
sales_com_obj.validate_max_discount(
self, "delivery_note_details"
) # verify whether rate is not greater than max discount
sales_com_obj.get_allocated_sum(self) # this is to verify that the allocated % of sales persons is 100%
sales_com_obj.check_conversion_rate(self)
# ::::::: Get total in Words ::::::::
self.doc.in_words = sales_com_obj.get_total_in_words(get_defaults()["currency"], self.doc.rounded_total)
self.doc.in_words_export = sales_com_obj.get_total_in_words(self.doc.currency, self.doc.rounded_total_export)
# ::::::: Set actual qty for each item in selected warehouse :::::::
self.update_current_stock()
# :::::: set DN status :::::::
self.doc.status = "Draft"
if not self.doc.billing_status:
self.doc.billing_status = "Not Billed"
if not self.doc.installation_status:
self.doc.installation_status = "Not Installed"
def get_att_data(self):
fy = get_defaults()["fiscal_year"] # get default fiscal year
comp = get_defaults()["company"] # get default company
# get naming series of attendance
import webnotes.model.doctype
docfield = webnotes.model.doctype.get("Attendance")
series = [d.options for d in docfield if d.doctype == "DocField" and d.fieldname == "naming_series"]
if not series:
msgprint("Please create naming series for Attendance.\nGo to Setup--> Numbering Series.")
raise Exception
else:
sr = series[0] or ""
return {"fy": fy, "comp": comp, "sr": sr}
def create_production_order(self, items):
"""Create production order. Called from Production Planning Tool"""
default_values = {
'posting_date' : nowdate(),
'origin' : 'MRP',
'wip_warehouse' : '',
'fg_warehouse' : '',
'status' : 'Draft',
'fiscal_year' : get_defaults()['fiscal_year']
}
pro_list = []
for item_so in items:
if item_so[1]:
self.validate_production_order_against_so(
item_so[0], item_so[1], items[item_so].get("qty"))
pro_doc = Document('Production Order')
pro_doc.production_item = item_so[0]
pro_doc.sales_order = item_so[1]
for key in items[item_so]:
pro_doc.fields[key] = items[item_so][key]
for key in default_values:
pro_doc.fields[key] = default_values[key]
pro_doc.save(new = 1)
pro_list.append(pro_doc.name)
return pro_list
def on_rename(self, newdn, olddn):
# update customer_name if not naming series
if get_defaults().get("cust_master_name") == "Customer Name":
update_fields = [
("Customer", "name"),
("Address", "customer"),
("Contact", "customer"),
("Customer Issue", "customer"),
("Delivery Note", "customer"),
("Opportunity", "customer"),
("Installation Note", "customer"),
("Maintenance Schedule", "customer"),
("Maintenance Visit", "customer"),
("Project", "customer"),
("Quotation", "customer"),
("Sales Invoice", "customer"),
("Sales Order", "customer"),
("Serial No", "customer"),
("Shipping Address", "customer"),
("Stock Entry", "customer"),
("Support Ticket", "customer"),
("Task", "customer"),
]
for rec in update_fields:
sql("update `tab%s` set customer_name = '%s' where %s = '%s'" % (rec[0], newdn, rec[1], olddn))
# update master_name in doctype account
sql(
"update `tabAccount` set master_name = '%s', master_type = 'Customer' where master_name = '%s'"
% (newdn, olddn)
)
def validate(self):
self.validate_fiscal_year()
self.validate_order_type()
self.validate_mandatory()
self.validate_proj_cust()
self.validate_po_date()
#self.validate_reference_value()
self.validate_for_items()
sales_com_obj = get_obj(dt = 'Sales Common')
sales_com_obj.check_active_sales_items(self)
sales_com_obj.check_conversion_rate(self)
# verify whether rate is not greater than max_discount
sales_com_obj.validate_max_discount(self,'sales_order_details')
# this is to verify that the allocated % of sales persons is 100%
sales_com_obj.get_allocated_sum(self)
sales_com_obj.make_packing_list(self,'sales_order_details')
# get total in words
self.doc.in_words = sales_com_obj.get_total_in_words(get_defaults()['currency'], self.doc.rounded_total)
self.doc.in_words_export = sales_com_obj.get_total_in_words(self.doc.currency, self.doc.rounded_total_export)
# set SO status
self.doc.status='Draft'
if not self.doc.billing_status: self.doc.billing_status = 'Not Billed'
if not self.doc.delivery_status: self.doc.delivery_status = 'Not Delivered'
def on_rename(self, new, old):
#update customer_name if not naming series
if get_defaults().get('cust_master_name') == 'Customer Name':
update_fields = [
('Customer', 'name'),
('Address', 'customer'),
('Contact', 'customer'),
('Customer Issue', 'customer'),
('Delivery Note', 'customer'),
('Opportunity', 'customer'),
('Installation Note', 'customer'),
('Maintenance Schedule', 'customer'),
('Maintenance Visit', 'customer'),
('Project', 'customer'),
('Quotation', 'customer'),
('Sales Invoice', 'customer'),
('Sales Order', 'customer'),
('Serial No', 'customer'),
('Shipping Address', 'customer'),
('Stock Entry', 'customer'),
('Support Ticket', 'customer'),
('Task', 'customer')]
for rec in update_fields:
sql("""update `tab%s` set customer_name = %s
where `%s` = %s""" % (rec[0], "%s" ,rec[1], "%s"), (new, old))
for account in webnotes.conn.sql("""select name, account_name from
tabAccount where master_name=%s and master_type='Customer'""", old, as_dict=1):
if account.account_name != new:
webnotes.rename_doc("Account", account.name, new)
#update master_name in doctype account
webnotes.conn.sql("""update `tabAccount` set master_name = %s,
master_type = 'Customer' where master_name = %s""", (new,old))
def get_att_data(self):
fy = get_defaults()['fiscal_year'] #get default fiscal year
comp = get_defaults()['company'] #get default company
#get naming series of attendance
#sr = sql("select series_options from `tabNaming Series Options` where doc_type='Attendance'")
sr = sql("select options from `tabDocField` where parent = 'Attendance' and fieldname = 'naming_series'")
if not sr:
msgprint("Please create naming series for Attendance.\nGo to Setup--> Manage Series.")
raise Exception
else:
sr = sr and sr[0][0]
return {'fy':fy,'comp':comp,'sr':sr}
def get_year_weekwise_amount(self,lst):
lst = lst.split(',')
yr_st = get_defaults()['year_start_date']
fy = lst[0]
m1 = cint(yr_st.split('-')[1])
cases = ' '
for i in range(1,13):
cases += self.get_week_cases(m1,fy)
m1 +=1
if(m1 == 13): m1 = 1
if not lst[1]:
query = "SELECT SUM(grand_total) AMOUNT,CASE WEEK(due_date)"+cases+"END Weekly, month(due_date) month FROM `tabSales Invoice` WHERE docstatus = 1 AND fiscal_year = '%s' GROUP BY `month`,weekly ORDER BY `month`,weekly"
ret = convert_to_lists(sql(query%lst[0]))
else:
query = "SELECT SUM(t2.amount) AMOUNT,CASE WEEK(t1.due_date)" + cases + "END Weekly, month(due_date) month FROM `tabSales Invoice` t1, `tabSales Invoice Item` t2 WHERE t1.docstatus = 1 AND t1.fiscal_year = '%s' AND t1.name = t2.parent AND t2.item_group ='%s' GROUP BY Weekly ORDER BY Weekly"
ret = convert_to_lists(sql(query%(lst[0],lst[1])))
return ret and ret or ''
def get_status_details(arg=None):
"""get toolbar items"""
from webnotes.utils import cint, date_diff, nowdate, get_defaults
online = get_online_users()
# system messages
ret = {
'user_count':
len(online) or 0,
#'unread_messages': get_unread_messages(),
#'open_support_tickets': get_open_support_tickets(),
'online_users':
online or [],
'setup_status':
get_setup_status(),
'registration_complete':
cint(get_defaults('registration_complete')) and 'Yes' or 'No',
'profile':
webnotes.conn.sql("""\
SELECT first_name, last_name FROM `tabProfile`
WHERE name=%s AND docstatus<2""",
webnotes.user.name,
as_dict=1)
}
return ret
def get_valuation_method(item_code):
"""get valuation method from item or default"""
val_method = webnotes.conn.get_value('Item', item_code, 'valuation_method')
if not val_method:
from webnotes.utils import get_defaults
val_method = get_defaults().get('valuation_method', 'FIFO')
return val_method
def get_appr_user_role(self, det, doctype_name, total, based_on, condition, item, company):
amt_list, appr_users, appr_roles = [], [], []
users, roles = '',''
if det:
for x in det:
amt_list.append(flt(x[0]))
max_amount = max(amt_list)
app_dtl = sql("select approving_user, approving_role from `tabAuthorization Rule` where transaction = '%s' and (value = '%s' or value > '%s') and docstatus != 2 and based_on = '%s' and company = '%s' %s" % (doctype_name, flt(max_amount), total, based_on, company, condition))
if not app_dtl:
app_dtl = sql("select approving_user, approving_role from `tabAuthorization Rule` where transaction = '%s' and (value = '%s' or value > '%s') and docstatus != 2 and based_on = '%s' and ifnull(company,'') = '' %s" % (doctype_name, flt(max_amount), total, based_on, condition))
for d in app_dtl:
if(d[0]): appr_users.append(d[0])
if(d[1]): appr_roles.append(d[1])
if not has_common(appr_roles, webnotes.user.get_roles()) and not has_common(appr_users, session['user']):
msg, add_msg = '',''
if max_amount:
if based_on == 'Grand Total': msg = "since Grand Total exceeds %s. %s" % (get_defaults()['currency'], flt(max_amount))
elif based_on == 'Itemwise Discount': msg = "since Discount exceeds %s for Item Code : %s" % (cstr(max_amount)+'%', item)
elif based_on == 'Average Discount' or based_on == 'Customerwise Discount': msg = "since Discount exceeds %s" % (cstr(max_amount)+'%')
if appr_users: add_msg = "Users : "+cstr(appr_users)
if appr_roles: add_msg = "Roles : "+cstr(appr_roles)
if appr_users and appr_roles: add_msg = "Users : "+cstr(appr_users)+" or "+"Roles : "+cstr(appr_roles)
msgprint("You do not have an authority to submit this %s %s. Please send for approval to %s" % (doctype_name, msg, add_msg))
raise Exception
def get_acct_dtl(self):
res = sql(
"select default_bank_account,default_salary_acount from `tabCompany` where name = '%s'"
% get_defaults()["company"],
as_dict=1,
)
return res[0]
def get_att_data(self):
fy = get_defaults()['fiscal_year'] #get default fiscal year
comp = get_defaults()['company'] #get default company
#get naming series of attendance
#sr = sql("select series_options from `tabNaming Series Options` where doc_type='Attendance'")
sr = sql("select options from `tabDocField` where parent = 'Attendance' and fieldname = 'naming_series'")
if not sr:
msgprint("Please create naming series for Attendance.\nGo to Setup--> Manage Series.")
raise Exception
else:
sr = sr and sr[0][0]
return {'fy':fy,'comp':comp,'sr':sr}
def get_dashboard_values(self, arg=''):
d = get_defaults()
self.fiscal_year = d['fiscal_year']
if arg:
company = arg
else:
company = d['company']
r = {}
r['Income'] = self.bl('Income', company)
r['Expenses'] = self.bl('Expenses', company)
r['Profit'] = []
for i in range(3):
r['Profit'].append(r['Income'][i] - r['Expenses'][i])
r['Current Assets'] = self.bl_bs('Current Assets', company,
getdate(d['year_start_date']))
r['Current Liabilities'] = self.bl_bs('Current Liabilities', company,
getdate(d['year_start_date']))
r['Working Capital'] = []
for i in range(3):
r['Working Capital'].append(r['Current Assets'][i] -
r['Current Liabilities'][i])
r['Bank Accounts'] = self.bl_bs('Bank Accounts', company,
getdate(d['year_start_date']))
r['Top Customers'] = convert_to_lists(self.get_top_5_cust(company))
r['Top Expenses'] = convert_to_lists(self.get_top_5_exp(company))
return r
def get_valuation_method(self, item_code):
"""get valuation method from item or default"""
val_method = webnotes.conn.get_value('Item', item_code, 'valuation_method')
if not val_method:
from webnotes.utils import get_defaults
val_method = get_defaults().get('valuation_method', 'FIFO')
return val_method
def validate_values(self):
# Master name by naming series -> Series field mandatory
if get_defaults().get(
'cust_master_name'
) == 'Naming Series' and not self.doc.naming_series:
msgprint("Series is Mandatory.")
raise Exception
def create_production_order(self,company, pp_items):
"""Create production order. Called from Production Planning Tool"""
default_values = {
'posting_date' : nowdate(),
'origin' : 'MRP',
'wip_warehouse' : '',
'fg_warehouse' : '',
'status' : 'Draft',
'company' : company,
'fiscal_year' : get_defaults()['fiscal_year']
}
pro_list = []
for d in pp_items:
pro_doc = Document('Production Order')
for key in d.keys():
pro_doc.fields[key] = d[key]
for key in default_values:
pro_doc.fields[key] = default_values[key]
pro_doc.save(new = 1)
pro_list.append(pro_doc.name)
return pro_list
def get_dashboard_values(self, arg=''):
d = get_defaults()
self.fiscal_year = d['fiscal_year']
if arg:
company = arg
else:
company = d['company']
r = {}
r['Income'] = self.bl('Income', company)
r['Expenses'] = self.bl('Expenses', company)
r['Profit'] = []
for i in range(3):
r['Profit'].append(r['Income'][i] - r['Expenses'][i])
r['Current Assets'] = self.bl_bs('Current Assets', company, getdate(d['year_start_date']))
r['Current Liabilities'] = self.bl_bs('Current Liabilities', company, getdate(d['year_start_date']))
r['Working Capital'] = []
for i in range(3):
r['Working Capital'].append(r['Current Assets'][i] - r['Current Liabilities'][i])
r['Bank Accounts'] = self.bl_bs('Bank Accounts', company, getdate(d['year_start_date']))
r['Top Customers'] = convert_to_lists(self.get_top_5_cust(company))
r['Top Expenses'] = convert_to_lists(self.get_top_5_exp(company))
return r
def get_month_diff(self):
#end_month = get_defaults()['end_month']
month_dict = {"January" :'01', "February" :'02',"March" :'03',"April":'04',"May":'05',"June":'06',"July":'07',"August":'08',"September":'09',"October":'10',"November":'11',"December":'12'}
import datetime
start_month = getdate(get_defaults()['year_start_date']).month
end_month = cint(start_month) - 1
if end_month <= 0:
end_month = 12
str_end_month = cstr(end_month)
if len(str_end_month)==1:
str_end_month = '0'+str_end_month
to_month = datetime.date.today().strftime("%B")
to_year = datetime.date.today().strftime("%Y")
fiscal_year = self.doc.fiscal_year
str_fy =fiscal_year.split("-")
endym=int(str_fy[1]+str_end_month)
startym= int(to_year+month_dict[to_month])
month_diff =sql("SELECT PERIOD_DIFF(%d,%d);" %(endym,startym))[0][0]+1
return month_diff
def create_production_order(self, company, pp_items):
"""Create production order. Called from Production Planning Tool"""
default_values = {
'posting_date': nowdate(),
'origin': 'MRP',
'wip_warehouse': '',
'fg_warehouse': '',
'status': 'Draft',
'company': company,
'fiscal_year': get_defaults()['fiscal_year']
}
pro_list = []
for d in pp_items:
pro_doc = Document('Production Order')
for key in d.keys():
pro_doc.fields[key] = d[key]
for key in default_values:
pro_doc.fields[key] = default_values[key]
pro_doc.save(new=1)
pro_list.append(pro_doc.name)
return pro_list
def update_cp(self):
def_list = [['fiscal_year',self.doc.current_fiscal_year],
['company',self.doc.default_company],
['currency',self.doc.default_currency],
['price_list_name',self.doc.default_price_list or ''],
['price_list_currency', self.doc.default_price_list_currency or ''],
['item_group',self.doc.default_item_group or ''],
['customer_group',self.doc.default_customer_group or ''],
['cust_master_name',self.doc.cust_master_name or ''],
['supplier_type',self.doc.default_supplier_type or ''],
['supp_master_name',self.doc.supp_master_name],
['territory',self.doc.default_territory or ''],
['stock_uom',self.doc.default_stock_uom or ''],
['fraction_currency',self.doc.default_currency_fraction or ''],
['valuation_method',self.doc.default_valuation_method]]
for d in def_list:
self.set_system_default(d[0],d[1])
# Update Currency Format
sql("update `tabSingles` set value = '%s' where field = 'currency_format' and doctype = 'Control Panel'" % self.doc.default_currency_format)
sql("update `tabSingles` set value = '%s' where field = 'date_format' and doctype = 'Control Panel'" %self.doc.date_format)
return get_defaults()
def __init__(self, doc, doclist=[]): self.doc = doc self.doclist = doclist self.defaults = get_defaults() self.tname = 'Purchase Receipt Detail' self.fname = 'purchase_receipt_details' self.count = 0
def on_rename(self, newdn, olddn):
#update customer_name if not naming series
if get_defaults().get('cust_master_name') == 'Customer Name':
update_fields = [('Customer', 'name'), ('Address', 'customer'),
('Contact', 'customer'),
('Customer Issue', 'customer'),
('Delivery Note', 'customer'),
('Opportunity', 'customer'),
('Installation Note', 'customer'),
('Maintenance Schedule', 'customer'),
('Maintenance Visit', 'customer'),
('Project', 'customer'),
('Quotation', 'customer'),
('Sales Invoice', 'customer'),
('Sales Order', 'customer'),
('Serial No', 'customer'),
('Shipping Address', 'customer'),
('Stock Entry', 'customer'),
('Support Ticket', 'customer'),
('Task', 'customer')]
for rec in update_fields:
sql("update `tab%s` set customer_name = '%s' where %s = '%s'" %
(rec[0], newdn, rec[1], olddn))
#update master_name in doctype account
sql("update `tabAccount` set master_name = '%s', master_type = 'Customer' where master_name = '%s'"
% (newdn, olddn))
def get_monthwise_amount(self, lst):
lst = lst.split(',')
if not lst[1]:
ret = convert_to_lists(
sql("SELECT SUM(grand_total) AMOUNT,CASE MONTH(due_date) WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEB' WHEN 3 THEN 'MAR' WHEN 4 THEN 'APR' WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN' WHEN 7 THEN 'JUL' WHEN 8 THEN 'AUG' WHEN 9 THEN 'SEP' WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DEC' END MONTHNAME FROM `tabReceivable Voucher` WHERE docstatus = 1 AND fiscal_year = '%s' GROUP BY MONTH(due_date) ORDER BY MONTH(due_date)"
% lst[0]))
else:
ret = convert_to_lists(
sql("select sum(t2.amount) AMOUNT ,CASE MONTH(t1.due_date) WHEN 1 THEN 'JAN' WHEN 2 THEN 'FEB' WHEN 3 THEN 'MAR' WHEN 4 THEN 'APR' WHEN 5 THEN 'MAY' WHEN 6 THEN 'JUN' WHEN 7 THEN 'JUL' WHEN 8 THEN 'AUG' WHEN 9 THEN 'SEP' WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DEC' END MONTHNAME from `tabReceivable Voucher` t1,`tabRV Detail` t2 WHERE t1.name = t2.parent and t1.docstatus = 1 and t2.item_group = '%s' AND t1.fiscal_year = '%s' GROUP BY MONTH(t1.due_date) ORDER BY MONTH(t1.due_date)"
% (lst[1], lst[0])))
m = cint(
sql("select month('%s')" %
(get_defaults()['year_start_date']))[0][0])
lst1 = [[1, 'JAN'], [2, 'FEB'], [3, 'MAR'], [4, 'APR'], [5, 'MAY'],
[6, 'JUN'], [7, 'JUL'], [8, 'AUG'], [9, 'SEP'], [10, 'OCT'],
[11, 'NOV'], [12, 'DEC']]
lst2 = []
k = 1
for i in range(1, 13):
for j in lst1:
if j[0] == m:
lst2.append([k, j[1]])
m += 1
if m == 13: m = 1
k += 1
return {'msg_data': ret, 'x_axis': lst2}
def on_rename(self,newdn,olddn):
#update customer_name if not naming series
if get_defaults().get('cust_master_name') == 'Customer Name':
update_fields = [
('Customer', 'name'),
('Address', 'customer'),
('Contact', 'customer'),
('Customer Issue', 'customer'),
('Delivery Note', 'customer'),
('Enquiry', 'customer'),
('Installation Note', 'customer'),
('Maintenance Schedule', 'customer'),
('Maintenance Visit', 'customer'),
('Project', 'customer'),
('Quotation', 'customer'),
('Receivable Voucher', 'customer'),
('Sales Order', 'customer'),
('Serial No', 'customer'),
('Shipping Address', 'customer'),
('Stock Entry', 'customer'),
('Support Ticket', 'customer'),
('Ticket', 'customer')]
for rec in update_fields:
sql("update `tab%s` set customer_name = '%s' where %s = '%s'" %(rec[0],newdn,rec[1],olddn))
#update master_name in doctype account
sql("update `tabAccount` set master_name = '%s', master_type = 'Customer' where master_name = '%s'" %(newdn,olddn))
def validate_po_pr(self, d):
# check po / pr for qty and rates and currency and conversion rate
# currency, import_rate must be equal to currency, import_rate of purchase order
if d.purchase_order and not d.purchase_order in self.po_list:
# currency
currency = cstr(sql("select currency from `tabPurchase Order` where name = '%s'" % d.purchase_order)[0][0])
if not cstr(currency) == cstr(self.doc.currency):
msgprint("Purchase Order: " + cstr(d.purchase_order) + " currency : " + cstr(currency) + " does not match with currency of current document.")
raise Exception
# import_rate
rate = flt(sql('select import_rate from `tabPurchase Order Item` where item_code=%s and parent=%s and name = %s', (d.item_code, d.purchase_order, d.po_detail))[0][0])
if abs(rate - flt(d.import_rate)) > 1 and cint(get_defaults('maintain_same_rate')):
msgprint("Import Rate for %s in the Purchase Order is %s. Rate must be same as Purchase Order Rate" % (d.item_code,rate))
raise Exception
if d.purchase_receipt and not d.purchase_receipt in self.pr_list:
# currency , conversion_rate
data = sql("select currency, conversion_rate from `tabPurchase Receipt` where name = '%s'" % d.purchase_receipt, as_dict = 1)
if not cstr(data[0]['currency']) == cstr(self.doc.currency):
msgprint("Purchase Receipt: " + cstr(d.purchase_receipt) + " currency : " + cstr(data[0]['currency']) + " does not match with currency of current document.")
raise Exception
if not flt(data[0]['conversion_rate']) == flt(self.doc.conversion_rate):
msgprint("Purchase Receipt: " + cstr(d.purchase_receipt) + " conversion_rate : " + cstr(data[0]['conversion_rate']) + " does not match with conversion_rate of current document.")
raise Exception
def get_children():
args = webnotes.form_dict
ctype, company = args['ctype'], args['comp']
company_field = ctype=='Account' and 'company' or 'company_name'
# root
if args['parent'] == company:
acc = webnotes.conn.sql(""" select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = ''
and %s = %s and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), company_field, '%s'),
args['parent'], as_dict=1)
else:
# other
acc = webnotes.conn.sql("""select
name as value, if(group_or_ledger='Group', 1, 0) as expandable
from `tab%s`
where ifnull(parent_%s,'') = %s
and docstatus<2
order by name""" % (ctype, ctype.lower().replace(' ','_'), '%s'),
args['parent'], as_dict=1)
if ctype == 'Account':
currency = webnotes.conn.sql("select default_currency from `tabCompany` where name = %s", company)[0][0]
for each in acc:
bal = webnotes.conn.sql("select balance from `tabAccount Balance` \
where account = %s and period = %s", (each.get('value'), get_defaults('fiscal_year')))[0][0]
each['balance'] = currency + ' ' + cstr(bal)
return acc
def validate_po_pr(self, d):
# check po / pr for qty and rates and currency and conversion rate
# currency, import_rate must be equal to currency, import_rate of purchase order
if d.purchase_order and not d.purchase_order in self.po_list:
# currency
currency = cstr(sql("select currency from `tabPurchase Order` where name = '%s'" % d.purchase_order)[0][0])
if not cstr(currency) == cstr(self.doc.currency):
msgprint("Purchase Order: " + cstr(d.purchase_order) + " currency : " + cstr(currency) + " does not match with currency of current document.")
raise Exception
# import_rate
rate = flt(sql('select import_rate from `tabPurchase Order Item` where item_code=%s and parent=%s and name = %s', (d.item_code, d.purchase_order, d.po_detail))[0][0])
if abs(rate - flt(d.import_rate)) > 1 and cint(get_defaults('maintain_same_rate')):
msgprint("Import Rate for %s in the Purchase Order is %s. Rate must be same as Purchase Order Rate" % (d.item_code,rate))
raise Exception
if d.purchase_receipt and not d.purchase_receipt in self.pr_list:
# currency , conversion_rate
data = sql("select currency, conversion_rate from `tabPurchase Receipt` where name = '%s'" % d.purchase_receipt, as_dict = 1)
if not cstr(data[0]['currency']) == cstr(self.doc.currency):
msgprint("Purchase Receipt: " + cstr(d.purchase_receipt) + " currency : " + cstr(data[0]['currency']) + " does not match with currency of current document.")
raise Exception
if not flt(data[0]['conversion_rate']) == flt(self.doc.conversion_rate):
msgprint("Purchase Receipt: " + cstr(d.purchase_receipt) + " conversion_rate : " + cstr(data[0]['conversion_rate']) + " does not match with conversion_rate of current document.")
raise Exception
def get_year_weekwise_amount(self,lst):
lst = lst.split(',')
yr_st = get_defaults()['year_start_date']
fy = lst[0]
m1 = cint(yr_st.split('-')[1])
cases = ' '
for i in range(1,13):
cases += self.get_week_cases(m1,fy)
m1 +=1
if(m1 == 13): m1 = 1
if not lst[1]:
query = "SELECT SUM(grand_total) AMOUNT,CASE WEEK(due_date)"+cases+"END Weekly, month(due_date) month FROM `tabSales Invoice` WHERE docstatus = 1 AND fiscal_year = '%s' GROUP BY `month`,weekly ORDER BY `month`,weekly"
ret = convert_to_lists(sql(query%lst[0]))
else:
query = "SELECT SUM(t2.amount) AMOUNT,CASE WEEK(t1.due_date)" + cases + "END Weekly, month(due_date) month FROM `tabSales Invoice` t1, `tabSales Invoice Item` t2 WHERE t1.docstatus = 1 AND t1.fiscal_year = '%s' AND t1.name = t2.parent AND t2.item_group ='%s' GROUP BY Weekly ORDER BY Weekly"
ret = convert_to_lists(sql(query%(lst[0],lst[1])))
return ret and ret or ''
def __init__(self, doc, doclist=[]):
self.doc = doc
self.doclist = doclist
self.defaults = get_defaults()
self.tname = 'Purchase Receipt Item'
self.fname = 'purchase_receipt_details'
self.count = 0
def on_rename(self, newdn, olddn):
sql("update `tabCompany` set company_name = '%s' where name = '%s'" % (newdn, olddn))
sql(
"update `tabSingles` set value = %s where doctype='Global Defaults' and field = 'default_company' and value = %s",
(newdn, olddn),
)
if get_defaults("company") == olddn:
set_default("company", newdn)
def on_rename(self, newdn, olddn):
sql("update `tabCompany` set company_name = '%s' where name = '%s'" %
(newdn, olddn))
sql(
"update `tabSingles` set value = %s where doctype='Global Defaults' and field = 'default_company' and value = %s",
(newdn, olddn))
if get_defaults('company') == olddn:
set_default('company', newdn)
def autoname(self):
cust_master_name = get_defaults().get('cust_master_name')
if cust_master_name == 'Customer Name':
if webnotes.conn.exists("Supplier", self.doc.customer_name):
msgprint(_("A Supplier exists with same name"), raise_exception=1)
self.doc.name = self.doc.customer_name
else:
self.doc.name = make_autoname(self.doc.naming_series+'.#####')
def autoname(self):
supp_master_name = get_defaults()['supp_master_name']
if supp_master_name == 'Supplier Name':
if webnotes.conn.exists("Customer", self.doc.supplier_name):
webnotes.msgprint(_("A Customer exists with same name"), raise_exception=1)
self.doc.name = self.doc.supplier_name
else:
self.doc.name = make_autoname(self.doc.naming_series + '.#####')
def make_sl_entry(self, is_submit, row, qty_diff, sys_stock):
"""Make stock ledger entry"""
in_rate = self.get_incoming_rate(row, qty_diff, sys_stock)
values = [{
'item_code' : row[0],
'warehouse' : row[1],
'transaction_date' : nowdate(),
'posting_date' : self.doc.reconciliation_date,
'posting_time' : self.doc.reconciliation_time,
'voucher_type' : self.doc.doctype,
'voucher_no' : self.doc.name,
'voucher_detail_no' : self.doc.name,
'actual_qty' : flt(is_submit) * flt(qty_diff),
'stock_uom' : sys_stock['stock_uom'],
'incoming_rate' : in_rate,
'company' : get_defaults()['company'],
'fiscal_year' : get_defaults()['fiscal_year'],
'is_cancelled' : (is_submit==1) and 'No' or 'Yes',
'batch_no' : '',
'serial_no' : ''
}]
get_obj('Stock Ledger', 'Stock Ledger').update_stock(values)
def lease_installment_post(self, args):
"""
Posts the Installment receipt into Journal Voucher
"""
next_inst = sql(
"select amount,name from `tabLease Installment` where parent=%s and ifnull(cheque_number,'')='' order by due_date limit 1",
self.doc.name)
data = json.loads(args)
data['voucher_type'] = 'Lease Receipt'
data['naming_series'] = 'JV'
data['amount'] = next_inst[0][0]
data['debit_account'] = data.get('bank_account')
data['credit_account'] = self.doc.account
data['fiscal_year'] = get_defaults()['fiscal_year']
data['company'] = get_defaults()['company']
jv_name = post_jv(data)
sql(
"update `tabLease Installment` set cheque_number=%s, cheque_date=%s, jv_number=%s where name=%s",
(data.get('cheque_number'), data.get('cheque_date'), jv_name,
next_inst[0][1]))
self.doclist = [Document(d.doctype, d.name) for d in self.doclist]
def on_rename(self, newdn, olddn):
#update supplier_name if not naming series
if get_defaults().get('supp_master_name') == 'Supplier Name':
update_fields = [('Supplier', 'name'), ('Address', 'supplier'),
('Contact', 'supplier'),
('Purchase Invoice', 'supplier'),
('Purchase Order', 'supplier'),
('Purchase Receipt', 'supplier'),
('Serial No', 'supplier')]
for rec in update_fields:
sql("update `tab%s` set supplier_name = '%s' where %s = '%s'" %
(rec[0], newdn, rec[1], olddn))
#update master_name in doctype account
sql("update `tabAccount` set master_name = '%s', master_type = 'Supplier' where master_name = '%s'"
% (newdn, olddn))
def get_period_difference(self,arg, cost_center =''):
# used in General Ledger Page Report
# used for Budget where cost center passed as extra argument
acc, f, t = arg.split('~~~')
c, fy = '', get_defaults()['fiscal_year']
det = webnotes.conn.sql("select debit_or_credit, lft, rgt, is_pl_account from tabAccount where name=%s", acc)
if f: c += (' and t1.posting_date >= "%s"' % f)
if t: c += (' and t1.posting_date <= "%s"' % t)
if cost_center: c += (' and t1.cost_center = "%s"' % cost_center)
bal = webnotes.conn.sql("select sum(ifnull(t1.debit,0))-sum(ifnull(t1.credit,0)) from `tabGL Entry` t1 where t1.account='%s' %s" % (acc, c))
bal = bal and flt(bal[0][0]) or 0
if det[0][0] != 'Debit':
bal = (-1) * bal
return flt(bal)
def autoname(self):
cust_master_name = get_defaults().get('cust_master_name')
if cust_master_name == 'Customer Name':
# filter out bad characters in name
#cust = self.doc.customer_name.replace('&','and').replace('.','').replace("'",'').replace('"','').replace(',','').replace('`','')
cust = self.doc.customer_name
supp = sql("select name from `tabSupplier` where name = %s",
(cust))
supp = supp and supp[0][0] or ''
if supp:
msgprint("You already have a Supplier with same name")
raise Exception("You already have a Supplier with same name")
else:
self.doc.name = cust
else:
self.doc.name = make_autoname(self.doc.naming_series + '.#####')
def get_comp(self):
ret = {}
type = []
comp = []
# ------ get period -----------
ret['period'] = ['Annual', 'Half Yearly', 'Quarterly', 'Monthly']
# ---- get companies ---------
res = sql("select name from `tabCompany`")
for r in res:
comp.append(r[0])
#comp.append(r[0] for r in res)
ret['company'] = comp
#--- to get fiscal year and start_date of that fiscal year -----
res = sql("select name, year_start_date from `tabFiscal Year`")
ret['fiscal_year'] = [r[0] for r in res]
ret['start_dates'] = {}
for r in res:
ret['start_dates'][r[0]] = str(r[1])
#--- from month and to month (for MIS - Comparison Report) -------
month_list = [
'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep',
'Oct', 'Nov', 'Dec'
]
fiscal_start_month = sql(
"select MONTH(year_start_date) from `tabFiscal Year` where name = %s",
(get_defaults()['fiscal_year']))
fiscal_start_month = fiscal_start_month and fiscal_start_month[0][
0] or 1
mon = ['']
for i in range(fiscal_start_month, 13):
mon.append(month_list[i - 1])
for i in range(0, fiscal_start_month - 1):
mon.append(month_list[i])
ret['month'] = mon
# ------------------------ get MIS Type on basis of roles of session user ------------------------------------------
if has_common(self.roles, ['Sales Manager']):
type.append('Sales')
if has_common(self.roles, ['Purchase Manager']):
type.append('Purchase')
ret['type'] = type
return ret
def get_month_diff(self):
#end_month = get_defaults()['end_month']
month_dict = {
"January": '01',
"February": '02',
"March": '03',
"April": '04',
"May": '05',
"June": '06',
"July": '07',
"August": '08',
"September": '09',
"October": '10',
"November": '11',
"December": '12'
}
import datetime
start_month = getdate(get_defaults()['year_start_date']).month
end_month = cint(start_month) - 1
if end_month <= 0:
end_month = 12
str_end_month = cstr(end_month)
if len(str_end_month) == 1:
str_end_month = '0' + str_end_month
to_month = datetime.date.today().strftime("%B")
to_year = datetime.date.today().strftime("%Y")
fiscal_year = self.doc.fiscal_year
str_fy = fiscal_year.split("-")
endym = int(str_fy[1] + str_end_month)
startym = int(to_year + month_dict[to_month])
month_diff = sql("SELECT PERIOD_DIFF(%d,%d);" %
(endym, startym))[0][0] + 1
return month_diff
def autoname(self):
#get default naming conventional from control panel
supp_master_name = get_defaults()['supp_master_name']
if supp_master_name == 'Supplier Name':
# filter out bad characters in name
#supp = self.doc.supplier_name.replace('&','and').replace('.','').replace("'",'').replace('"','').replace(',','').replace('`','')
supp = self.doc.supplier_name
cust = sql("select name from `tabCustomer` where name = '%s'" %
(supp))
cust = cust and cust[0][0] or ''
if cust:
msgprint("You already have a Customer with same name")
raise Exception
self.doc.name = supp
else:
self.doc.name = make_autoname(self.doc.naming_series + '.#####')
def get_cl(self, arg):
fy = get_defaults()['fiscal_year']
parent, parent_acc_name, company, type = arg.split(',')
# get children account details
if type == 'Account':
if parent == 'Root Node':
cl = webnotes.conn.sql(
"select t1.name, t1.group_or_ledger, t1.debit_or_credit, t2.balance, t1.account_name from tabAccount t1, `tabAccount Balance` t2 where ifnull(t1.parent_account, '') = '' and t1.docstatus != 2 and t1.company=%s and t1.name = t2.account and t2.period = %s order by t1.name asc",
(company, fy),
as_dict=1)
else:
cl = webnotes.conn.sql(
"select t1.name, t1.group_or_ledger, t1.debit_or_credit, t2.balance, t1.account_name from tabAccount t1, `tabAccount Balance` t2 where ifnull(t1.parent_account, '')=%s and t1.docstatus != 2 and t1.company=%s and t1.name = t2.account and t2.period = %s order by t1.name asc",
(parent, company, fy),
as_dict=1)
# remove Decimals
for c in cl:
c['balance'] = flt(c['balance'])
# get children cost center details
elif type == 'Cost Center':
if parent == 'Root Node':
cl = webnotes.conn.sql(
"select name,group_or_ledger, cost_center_name from `tabCost Center` where ifnull(parent_cost_center, '')='' and docstatus != 2 and company_name=%s order by name asc",
(company),
as_dict=1)
else:
cl = webnotes.conn.sql(
"select name,group_or_ledger,cost_center_name from `tabCost Center` where ifnull(parent_cost_center, '')=%s and docstatus != 2 and company_name=%s order by name asc",
(parent, company),
as_dict=1)
return {'parent': parent, 'parent_acc_name': parent_acc_name, 'cl': cl}
def on_rename(self, new, old):
#update supplier_name if not naming series
if get_defaults().get('supp_master_name') == 'Supplier Name':
update_fields = [('Supplier', 'name'), ('Address', 'supplier'),
('Contact', 'supplier'),
('Purchase Invoice', 'supplier'),
('Purchase Order', 'supplier'),
('Purchase Receipt', 'supplier'),
('Serial No', 'supplier')]
for rec in update_fields:
sql("update `tab%s` set supplier_name = %s where `%s` = %s" % \
(rec[0], '%s', rec[1], '%s'), (new, old))
for account in webnotes.conn.sql("""select name, account_name from
tabAccount where master_name=%s and master_type='Supplier'""",
old,
as_dict=1):
if account.account_name != new:
webnotes.rename_doc("Account", account.name, new)
#update master_name in doctype account
webnotes.conn.sql(
"""update `tabAccount` set master_name = %s,
master_type = 'Supplier' where master_name = %s""", (new, old))
def on_rename(self, new, old):
#update customer_name if not naming series
if get_defaults().get('cust_master_name') == 'Customer Name':
update_fields = [('Customer', 'name'), ('Address', 'customer'),
('Contact', 'customer'),
('Customer Issue', 'customer'),
('Delivery Note', 'customer'),
('Opportunity', 'customer'),
('Installation Note', 'customer'),
('Maintenance Schedule', 'customer'),
('Maintenance Visit', 'customer'),
('Project', 'customer'),
('Quotation', 'customer'),
('Sales Invoice', 'customer'),
('Sales Order', 'customer'),
('Serial No', 'customer'),
('Shipping Address', 'customer'),
('Stock Entry', 'customer'),
('Support Ticket', 'customer'),
('Task', 'customer')]
for rec in update_fields:
sql(
"""update `tab%s` set customer_name = %s
where `%s` = %s""" % (rec[0], "%s", rec[1], "%s"), (new, old))
for account in webnotes.conn.sql("""select name, account_name from
tabAccount where master_name=%s and master_type='Customer'""",
old,
as_dict=1):
if account.account_name != new:
webnotes.rename_doc("Account", account.name, new)
#update master_name in doctype account
webnotes.conn.sql(
"""update `tabAccount` set master_name = %s,
master_type = 'Customer' where master_name = %s""", (new, old))
