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 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_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_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 __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))