def __init__(self, filters=None): self.filters = webnotes._dict(filters or {}) self.filters.report_date = getdate(self.filters.report_date or nowdate()) self.age_as_on = getdate(nowdate()) \ if self.filters.report_date > getdate(nowdate()) \ else self.filters.report_date
def create_feed_and_todo(self): """update activty feed and create todo for creation of item, customer, vendor""" import home home.make_feed( 'Comment', 'ToDo', '', webnotes.session['user'], '<i>"' + 'Setup Complete. Please check your <a href="#!todo">\ To Do List</a>' + '"</i>', '#6B24B3') d = Document('ToDo') d.description = 'Create your first Customer' d.priority = 'High' d.date = nowdate() d.reference_type = 'Customer' d.save(1) d = Document('ToDo') d.description = 'Create your first Item' d.priority = 'High' d.date = nowdate() d.reference_type = 'Item' d.save(1) d = Document('ToDo') d.description = 'Create your first Supplier' d.priority = 'High' d.date = nowdate() d.reference_type = 'Supplier' d.save(1)
def test_save_journal_voucher(self): expense_ac_balance = get_balance_on(get_name("Test Expense"), nowdate()) supplier_ac_balance = get_balance_on(get_name("Test Supplier"), nowdate()) dl = webnotes.model.insert(data["journal_voucher"]) dl.submit() dl.load_from_db() # test submitted jv self.assertTrue(webnotes.conn.exists("Journal Voucher", dl.doclist[0].name)) for d in dl.doclist[1:]: self.assertEquals(webnotes.conn.get_value("Journal Voucher Detail", d.name, "parent"), dl.doclist[0].name) # test gl entry gle = webnotes.conn.sql("""select account, debit, credit from `tabGL Entry` where voucher_no = %s order by account""", dl.doclist[0].name) self.assertEquals((gle[0][0], flt(gle[0][1]), flt(gle[0][2])), ('Test Expense - %s' % abbr, 5000.0, 0.0)) self.assertEquals((gle[1][0], flt(gle[1][1]), flt(gle[1][2])), ('Test Supplier - %s' % abbr, 0.0, 5000.0)) # check balance as on today self.assertEqual(get_balance_on(get_name("Test Expense"), nowdate()), expense_ac_balance + 5000) self.assertEqual(get_balance_on(get_name("Test Supplier"), nowdate()), supplier_ac_balance + 5000) # check previous balance self.assertEqual(get_balance_on(get_name("Test Expense"), add_days(nowdate(), -1)), 0)
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) account_supplier = dict( webnotes.conn.sql("""select account.name, supplier.supplier_name from `tabAccount` account, `tabSupplier` supplier where account.master_type="Supplier" and supplier.name=account.master_name""" )) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount( gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [ gle.posting_date, gle.account, account_supplier.get(gle.account, ""), gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) data.append(row) return columns, data
def manage_recurring_invoices(): """ Create recurring invoices on specific date by copying the original one and notify the concerned people """ rv = webnotes.conn.sql("""select name, recurring_id from `tabSales Invoice` \ where ifnull(convert_into_recurring_invoice, 0) = 1 and next_date = %s \ and next_date <= ifnull(end_date, '2199-12-31') and docstatus=1""", nowdate()) exception_list = [] for d in rv: if not webnotes.conn.sql("""select name from `tabSales Invoice` \ where posting_date = %s and recurring_id = %s and docstatus=1""", (nowdate(), d[1])): try: prev_rv = get_obj('Sales Invoice', d[0], with_children=1) new_rv = create_new_invoice(prev_rv) send_notification(new_rv) webnotes.conn.commit() except Exception, e: webnotes.conn.rollback() webnotes.conn.begin() webnotes.conn.sql("update `tabSales Invoice` set \ convert_into_recurring_invoice = 0 where name = %s", d[0]) notify_errors(d[0], prev_rv.doc.owner) webnotes.conn.commit() exception_list.append(e) finally:
def manage_recurring_invoices(): """ Create recurring invoices on specific date by copying the original one and notify the concerned people """ rv = webnotes.conn.sql( """select name, recurring_id from `tabSales Invoice` \ where ifnull(convert_into_recurring_invoice, 0) = 1 and next_date = %s \ and next_date <= ifnull(end_date, '2199-12-31') and docstatus=1""", nowdate()) exception_list = [] for d in rv: if not webnotes.conn.sql( """select name from `tabSales Invoice` \ where posting_date = %s and recurring_id = %s and docstatus=1""", (nowdate(), d[1])): try: prev_rv = get_obj('Sales Invoice', d[0], with_children=1) new_rv = create_new_invoice(prev_rv) send_notification(new_rv) webnotes.conn.commit() except Exception, e: webnotes.conn.rollback() webnotes.conn.begin() webnotes.conn.sql( "update `tabSales Invoice` set \ convert_into_recurring_invoice = 0 where name = %s", d[0]) notify_errors(d[0], prev_rv.doc.owner) webnotes.conn.commit() exception_list.append(e) finally:
def make_stock_ledger_entry(self, qty): from webnotes.model.code import get_obj values = [ { "item_code": self.doc.item_code, "warehouse": self.doc.warehouse, "transaction_date": nowdate(), "posting_date": self.doc.purchase_date or (self.doc.creation and self.doc.creation.split(" ")[0]) or nowdate(), "posting_time": self.doc.purchase_time or "00:00", "voucher_type": "Serial No", "voucher_no": self.doc.name, "voucher_detail_no": "", "actual_qty": qty, "stock_uom": webnotes.conn.get_value("Item", self.doc.item_code, "stock_uom"), "incoming_rate": self.doc.purchase_rate, "company": self.doc.company, "fiscal_year": self.doc.fiscal_year, "is_cancelled": "No", # is_cancelled is always 'No' because while deleted it can not find creation entry if it not created directly, voucher no != serial no "batch_no": "", "serial_no": self.doc.name, } ] get_obj("Stock Ledger", "Stock Ledger").update_stock(values)
def get_valuation_rate(self, args): """ Get average valuation rate of relevant warehouses as per valuation method (MAR/FIFO) as on costing date """ from stock.utils import get_incoming_rate dt = self.doc.costing_date or nowdate() time = self.doc.costing_date == nowdate() and now().split( )[1] or '23:59' warehouse = webnotes.conn.sql( "select warehouse from `tabBin` where item_code = %s", args['item_code']) rate = [] for wh in warehouse: r = get_incoming_rate({ "item_code": args.get("item_code"), "warehouse": wh[0], "posting_date": dt, "posting_time": time, "qty": args.get("qty") or 0 }) if r: rate.append(r) return rate and flt(sum(rate)) / len(rate) or 0
def get_balance_on(account=None, date=None): if not account and webnotes.form_dict.get("account"): account = webnotes.form_dict.get("account") date = webnotes.form_dict.get("date") cond = [] if date: cond.append("posting_date <= '%s'" % date) else: # get balance of all entries that exist date = nowdate() try: year_start_date = get_fiscal_year(date, verbose=0)[1] except FiscalYearError, e: from webnotes.utils import getdate if getdate(date) > getdate(nowdate()): # if fiscal year not found and the date is greater than today # get fiscal year for today's date and its corresponding year start date year_start_date = get_fiscal_year(nowdate(), verbose=1)[1] else: # this indicates that it is a date older than any existing fiscal year. # hence, assuming balance as 0.0 return 0.0
def make_stock_ledger_entry(self, update_stock): from webnotes.model.code import get_obj values = [ { "item_code": self.doc.item_code, "warehouse": self.doc.warehouse, "transaction_date": nowdate(), "posting_date": self.doc.purchase_date or (self.doc.creation and self.doc.creation.split(" ")[0]) or nowdate(), "posting_time": self.doc.purchase_time or "00:00", "voucher_type": "Serial No", "voucher_no": self.doc.name, "voucher_detail_no": "", "actual_qty": 1, "stock_uom": webnotes.conn.get_value("Item", self.doc.item_code, "stock_uom"), "incoming_rate": self.doc.purchase_rate, "company": self.doc.company, "fiscal_year": self.doc.fiscal_year, "is_cancelled": update_stock and "No" or "Yes", "batch_no": "", "serial_no": self.doc.name, } ] get_obj("Stock Ledger", "Stock Ledger").update_stock(values)
def create_feed_and_todo(self): """update activty feed and create todo for creation of item, customer, vendor""" import home home.make_feed('Comment', 'ToDo', '', webnotes.session['user'], '<i>"' + 'Setup Complete. Please check your <a href="#!todo">\ To Do List</a>' + '"</i>', '#6B24B3') d = Document('ToDo') d.description = 'Create your first Customer' d.priority = 'High' d.date = nowdate() d.reference_type = 'Customer' d.save(1) d = Document('ToDo') d.description = 'Create your first Item' d.priority = 'High' d.date = nowdate() d.reference_type = 'Item' d.save(1) d = Document('ToDo') d.description = 'Create your first Supplier' d.priority = 'High' d.date = nowdate() d.reference_type = 'Supplier' d.save(1)
def test_get_value(self): webnotes.conn.sql( """delete from `tabProfile` where name not in ('Administrator', 'Guest')""" ) make_test_records("Profile") self.assertEquals( webnotes.conn.get_value("Profile", {"name": ["=", "Administrator"]}), "Administrator") self.assertEquals( webnotes.conn.get_value("Profile", {"name": ["like", "Admin%"]}), "Administrator") self.assertEquals( webnotes.conn.get_value("Profile", {"name": ["!=", "Guest"]}), "Administrator") from webnotes.utils import nowdate self.assertEquals( webnotes.conn.get_value("Profile", {"modified": ["<", nowdate()]}), "Administrator") self.assertEquals( webnotes.conn.get_value("Profile", {"modified": ["<=", nowdate()]}), "Administrator") self.assertEquals( webnotes.conn.get_value("Profile", {"modified": [">", nowdate()]}), "*****@*****.**") self.assertEquals( webnotes.conn.get_value("Profile", {"modified": [">=", nowdate()]}), "*****@*****.**")
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 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 manage_recurring_invoices(): """ Create recurring invoices on specific date by copying the original one and notify the concerned people """ recurring_invoices = webnotes.conn.sql("""select name, recurring_id from `tabSales Invoice` where ifnull(convert_into_recurring_invoice, 0)=1 and docstatus=1 and next_date=%s and next_date <= ifnull(end_date, '2199-12-31')""", nowdate()) exception_list = [] for ref_invoice, recurring_id in recurring_invoices: if not webnotes.conn.sql("""select name from `tabSales Invoice` where posting_date=%s and recurring_id=%s and docstatus=1""", (nowdate(), recurring_id)): try: ref_wrapper = webnotes.model_wrapper('Sales Invoice', ref_invoice) new_invoice_wrapper = make_new_invoice(ref_wrapper) send_notification(new_invoice_wrapper) webnotes.conn.commit() except Exception, e: webnotes.conn.rollback() webnotes.conn.begin() webnotes.conn.sql("update `tabSales Invoice` set \ convert_into_recurring_invoice = 0 where name = %s", ref_invoice) notify_errors(ref_invoice, ref_wrapper.doc.owner) webnotes.conn.commit() exception_list.append(webnotes.getTraceback()) finally:
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 create_material_request(material_requests): """ Create indent on reaching reorder level """ mr_list = [] defaults = webnotes.defaults.get_defaults() exceptions_list = [] for request_type in material_requests: for company in material_requests[request_type]: try: items = material_requests[request_type][company] if not items: continue mr = [{ "doctype": "Material Request", "company": company, "fiscal_year": defaults.fiscal_year, "transaction_date": nowdate(), "material_request_type": request_type }] for d in items: item = webnotes.doc("Item", d.item_code) mr.append({ "doctype": "Material Request Item", "parenttype": "Material Request", "parentfield": "indent_details", "item_code": d.item_code, "schedule_date": add_days(nowdate(),cint(item.lead_time_days)), "uom": item.stock_uom, "warehouse": d.warehouse, "item_name": item.item_name, "description": item.description, "item_group": item.item_group, "qty": d.reorder_qty, "brand": item.brand, }) mr_bean = webnotes.bean(mr) mr_bean.insert() mr_bean.submit() mr_list.append(mr_bean) except: if webnotes.local.message_log: exceptions_list.append([] + webnotes.local.message_log) webnotes.local.message_log = [] else: exceptions_list.append(webnotes.getTraceback()) if mr_list: if getattr(webnotes.local, "reorder_email_notify", None) is None: webnotes.local.reorder_email_notify = cint(webnotes.conn.get_value('Stock Settings', None, 'reorder_email_notify')) if(webnotes.local.reorder_email_notify): send_email_notification(mr_list) if exceptions_list: notify_errors(exceptions_list)
def execute(): item_map = {} for item in webnotes.conn.sql("""select * from tabItem""", as_dict=1): item_map.setdefault(item.name, item) warehouse_map = get_warehosue_map() naming_series = "STE/13/" for company in webnotes.conn.sql("select name from tabCompany"): stock_entry = [{ "doctype": "Stock Entry", "naming_series": naming_series, "posting_date": nowdate(), "posting_time": nowtime(), "purpose": "Material Transfer", "company": company[0], "remarks": "Material Transfer to activate perpetual inventory", "fiscal_year": get_fiscal_year(nowdate())[0] }] expense_account = "Cost of Goods Sold - NISL" cost_center = "Default CC Ledger - NISL" for bin in webnotes.conn.sql("""select * from tabBin bin where ifnull(item_code, '')!='' and ifnull(warehouse, '') in (%s) and ifnull(actual_qty, 0) != 0 and (select company from tabWarehouse where name=bin.warehouse)=%s""" % (', '.join(['%s']*len(warehouse_map)), '%s'), (warehouse_map.keys() + [company[0]]), as_dict=1): item_details = item_map[bin.item_code] new_warehouse = warehouse_map[bin.warehouse].get("fixed_asset_warehouse") \ if cstr(item_details.is_asset_item) == "Yes" \ else warehouse_map[bin.warehouse].get("current_asset_warehouse") if item_details.has_serial_no == "Yes": serial_no = "\n".join([d[0] for d in webnotes.conn.sql("""select name from `tabSerial No` where item_code = %s and warehouse = %s and status in ('Available', 'Sales Returned')""", (bin.item_code, bin.warehouse))]) else: serial_no = None stock_entry.append({ "doctype": "Stock Entry Detail", "parentfield": "mtn_details", "s_warehouse": bin.warehouse, "t_warehouse": new_warehouse, "item_code": bin.item_code, "description": item_details.description, "qty": bin.actual_qty, "transfer_qty": bin.actual_qty, "uom": item_details.stock_uom, "stock_uom": item_details.stock_uom, "conversion_factor": 1, "expense_account": expense_account, "cost_center": cost_center, "serial_no": serial_no }) webnotes.bean(stock_entry).insert()
def execute(filters=None): if not filters: filters = {} supplier_naming_by = webnotes.conn.get_value("Buying Settings", None, "supp_master_name") columns = get_columns(supplier_naming_by) entries = get_gl_entries(filters) account_map = dict(((r.name, r) for r in webnotes.conn.sql("""select acc.name, supp.supplier_name, supp.name as supplier from `tabAccount` acc, `tabSupplier` supp where acc.master_type="Supplier" and supp.name=acc.master_name""", as_dict=1))) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() voucher_detail_map = get_voucher_details() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: voucher_details = voucher_detail_map.get(gle.voucher_type, {}).get(gle.voucher_no, {}) invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount(gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, voucher_details.get("due_date", ""), voucher_details.get("bill_no", ""), voucher_details.get("bill_date", ""), invoiced_amount, paid_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = voucher_details.get("due_date", "") else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) + \ [account_map.get(gle.account).get("supplier") or ""] if supplier_naming_by == "Naming Series": row += [account_map.get(gle.account).get("supplier_name") or ""] row += [account_supplier_type_map.get(gle.account), gle.remarks] data.append(row) for i in range(0, len(data)): data[i].insert(4, """<a href="%s"><i class="icon icon-share" style="cursor: pointer;"></i></a>""" \ % ("/".join(["#Form", data[i][2], data[i][3]]),)) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] total_invoiced_amount = total_paid = total_outstanding = 0 for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 paid_amount = get_paid_amount(gle, filters.get("report_date") or nowdate(), entries_after_report_date) outstanding_amount = invoiced_amount - paid_amount if abs(flt(outstanding_amount)) > 0.01: row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) # Add to total total_invoiced_amount += flt(invoiced_amount) total_paid += flt(paid_amount) total_outstanding += flt(outstanding_amount) data.append(row) if data: data.append(["", "", "", "", "", "", "", "Total", "", total_invoiced_amount, total_paid, total_outstanding, "", "", "", ""]) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 paid_amount = get_paid_amount( gle, filters.get("report_date") or nowdate(), entries_after_report_date) outstanding_amount = invoiced_amount - paid_amount if abs(flt(outstanding_amount)) > 0.01: row = [ gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) data.append(row) return columns, data
def insert_purchase_request(self, items_to_be_requested, fiscal_year): purchase_request_list = [] if items_to_be_requested: for item in items_to_be_requested: item_wrapper = webnotes.bean("Item", item) pr_doclist = [{ "doctype": "Material Request", "__islocal": 1, "naming_series": "IDT", "transaction_date": nowdate(), "status": "Draft", "company": self.doc.company, "fiscal_year": fiscal_year, "requested_by": webnotes.session.user, "remark": "Automatically raised from Production Planning Tool", "material_request_type": "Purchase" }, { "doctype": "Material Request Item", "__islocal": 1, "parentfield": "indent_details", "item_code": item, "item_name": item_wrapper.doc.item_name, "description": item_wrapper.doc.description, "uom": item_wrapper.doc.stock_uom, "item_group": item_wrapper.doc.item_group, "brand": item_wrapper.doc.brand, "qty": items_to_be_requested[item], "schedule_date": add_days(nowdate(), cint(item_wrapper.doc.lead_time_days)), "warehouse": self.doc.purchase_request_for_warehouse }] pr_wrapper = webnotes.bean(pr_doclist) pr_wrapper.ignore_permissions = 1 pr_wrapper.submit() purchase_request_list.append(pr_wrapper.doc.name) if purchase_request_list: pur_req = ["""<a href="#Form/Material Request/%s" target="_blank">%s</a>""" % \ (p, p) for p in purchase_request_list] webnotes.msgprint( "Following Material Request created successfully: \n%s" % "\n".join(pur_req)) else: webnotes.msgprint("Nothing to request")
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) account_customer = dict( webnotes.conn.sql("""select account.name, customer.customer_name from `tabAccount` account, `tabCustomer` customer where account.master_type="Customer" and customer.name=account.master_name""" )) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, upto_report_date=False)] account_territory_map = get_account_territory_map() si_due_date_map = get_si_due_date_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: due_date = (gle.voucher_type == "Sales Invoice") \ and si_due_date_map.get(gle.voucher_no) or "" invoiced_amount = gle.debit > 0 and gle.debit or 0 outstanding_amount = get_outstanding_amount( gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: payment_amount = invoiced_amount - outstanding_amount row = [ gle.posting_date, gle.account, account_customer.get(gle.account, ""), gle.voucher_type, gle.voucher_no, gle.remarks, due_date, account_territory_map.get(gle.account), invoiced_amount, payment_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) data.append(row) return columns, data
def update_acc_bal(self,args): args = eval(args) self.doc.credit_balance = cint(self.doc.credit_balance) + cint(args.get('net_cr')) self.doc.total_users = cint(self.doc.total_users) + cint(args.get('total_users')) if cint(self.doc.is_trial_account) == 1: if not self.doc.account_start_date: self.doc.account_start_date = nowdate() self.doc.is_trial_account = 0 self.doc.billing_cycle_date = nowdate() self.doc.last_deduction_date = nowdate() self.doc.save()
def declare_completed(self): if self.doc.status == 'Open': self.validate_for_pending_review() self.doc.review_date = nowdate() else: self.validate_with_timesheet_dates() self.validate_for_closed() self.doc.closing_date = nowdate() set(self.doc, 'status', 'Closed') set(self.doc, 'docstatus', 1) self.doc.save() return cstr('true')
def get_fy_details(self, fy_start): st = {'1st Jan':'01-01','1st Apr':'04-01','1st Jul':'07-01', '1st Oct': '10-01'} curr_year = getdate(nowdate()).year if cint(getdate(nowdate()).month) < cint((st[fy_start].split('-'))[0]): curr_year = getdate(nowdate()).year - 1 stdt = cstr(curr_year)+'-'+cstr(st[fy_start]) #eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0])) if(fy_start == '1st Jan'): fy = cstr(getdate(nowdate()).year) else: fy = cstr(curr_year) + '-' + cstr(curr_year+1) return fy,stdt
def get_fy_details(self, fy_start): st = {"1st Jan": "01-01", "1st Apr": "04-01", "1st Jul": "07-01", "1st Oct": "10-01"} curr_year = getdate(nowdate()).year if cint(getdate(nowdate()).month) < cint((st[fy_start].split("-"))[0]): curr_year = getdate(nowdate()).year - 1 stdt = cstr(curr_year) + "-" + cstr(st[fy_start]) # eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0])) if fy_start == "1st Jan": fy = cstr(getdate(nowdate()).year) else: fy = cstr(curr_year) + "-" + cstr(curr_year + 1) return fy, stdt
def check_late_submission(self): current_month = nowdate().split('-')[1] current_day = nowdate().split('-')[2] to_date_month = self.doc.end_date.split('-')[1] if flt(current_day) > 5 and flt(current_month) != flt(to_date_month): self.doc.status = 'Late' msgprint("Late Submission") if not self.doc.reason_for_late_submission: msgprint("Please Enter the reason for LATE Submission") raise Exception else: self.doc.status = 'On Time'
def calc_days(self): if self.doc.billing_cycle_date: next_bill_month = cint(nowdate().split('-')[1]) if cint(nowdate().split('-')[2]) > cint(self.doc.billing_cycle_date.split('-')[2]): next_bill_month = cint(nowdate().split('-')[1]) + 1 next_bill_year = nowdate().split('-')[0] if next_bill_month > 12: next_bill_month = next_bill_month % 12 next_bill_year += 1 self.next_bill_sdate = cstr(next_bill_year)+'-'+cstr(next_bill_month)+'-'+(self.calc_next_day(next_bill_year,next_bill_month)) #msgprint("next_bill_month :::" + self.next_bill_sdate) return date_diff(self.next_bill_sdate, nowdate())
def update_serial_no_warranty_amc_status(self, serial_no_list = []): if serial_no_list: sr_list = serial_no_list else: sr_list = [s[0] for s in sql("select name from `tabSerial No` where docstatus = 1")] for s in sr_list: sr = Document('Serial No', s) if sr.amc_expiry_date: sr.warranty_amc_status = (sr.amc_expiry_date < nowdate()) and 'Out of AMC' or 'Under AMC' elif sr.warranty_expiry_date: sr.warranty_amc_status = (sr.warranty_expiry_date < nowdate()) and 'Out of Warranty' or 'Under Warranty' sr.save()
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, upto_report_date=False)] account_territory_map = get_account_territory_map() si_due_date_map = get_si_due_date_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] total_invoiced_amount = total_payment = total_outstanding = 0 for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: due_date = (gle.voucher_type == "Sales Invoice") \ and si_due_date_map.get(gle.voucher_no) or "" invoiced_amount = gle.debit > 0 and gle.debit or 0 payment_amount = get_payment_amount(gle, filters.get("report_date") or nowdate(), entries_after_report_date) outstanding_amount = invoiced_amount - payment_amount if abs(flt(outstanding_amount)) > 0.01: row = [gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, gle.remarks, due_date, account_territory_map.get(gle.account), invoiced_amount, payment_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) # Add to total total_invoiced_amount += flt(invoiced_amount) total_payment += flt(payment_amount) total_outstanding += flt(outstanding_amount) data.append(row) if data: data.append(["", "", "", "", "", "", "Total", total_invoiced_amount, total_payment, total_outstanding, "", "", "", ""]) return columns, data
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) account_supplier = dict(webnotes.conn.sql("""select account.name, supplier.supplier_name from `tabAccount` account, `tabSupplier` supplier where account.master_type="Supplier" and supplier.name=account.master_name""")) entries_after_report_date = [[gle.voucher_type, gle.voucher_no] for gle in get_gl_entries(filters, before_report_date=False)] account_supplier_type_map = get_account_supplier_type_map() pi_map = get_pi_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: if gle.voucher_type == "Purchase Invoice": pi_info = pi_map.get(gle.voucher_no) due_date = pi_info.get("due_date") bill_no = pi_info.get("bill_no") bill_date = pi_info.get("bill_date") else: due_date = bill_no = bill_date = "" invoiced_amount = gle.credit > 0 and gle.credit or 0 outstanding_amount = get_outstanding_amount(gle, filters.get("report_date") or nowdate()) if abs(flt(outstanding_amount)) > 0.01: paid_amount = invoiced_amount - outstanding_amount row = [gle.posting_date, gle.account, account_supplier.get(gle.account, ""), gle.voucher_type, gle.voucher_no, gle.remarks, account_supplier_type_map.get(gle.account), due_date, bill_no, bill_date, invoiced_amount, paid_amount, outstanding_amount] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(age_on, ageing_based_on_date, outstanding_amount) data.append(row) return columns, data
def get_fy_details(self, fy_start): st = {'1st Jan':'01-01','1st Apr':'04-01','1st Jul':'07-01', '1st Oct': '10-01'} curr_year = getdate(nowdate()).year if cint(getdate(nowdate()).month) < cint((st[fy_start].split('-'))[0]): curr_year = getdate(nowdate()).year - 1 stdt = cstr(curr_year)+'-'+cstr(st[fy_start]) #eddt = sql("select DATE_FORMAT(DATE_SUB(DATE_ADD('%s', INTERVAL 1 YEAR), INTERVAL 1 DAY),'%%d-%%m-%%Y')" % (stdt.split('-')[2]+ '-' + stdt.split('-')[1] + '-' + stdt.split('-')[0])) if(fy_start == '1st Jan'): fy = cstr(getdate(nowdate()).year) abbr = cstr(fy)[-2:] else: fy = cstr(curr_year) + '-' + cstr(curr_year+1) abbr = cstr(curr_year)[-2:] + '-' + cstr(curr_year+1)[-2:] return fy, stdt, abbr
def insert_purchase_request(self): items_to_be_requested = self.get_requested_items() from accounts.utils import get_fiscal_year fiscal_year = get_fiscal_year(nowdate())[0] purchase_request_list = [] if items_to_be_requested: for item in items_to_be_requested: item_wrapper = webnotes.bean("Item", item) pr_doclist = [{ "doctype": "Material Request", "__islocal": 1, "naming_series": "IDT", "transaction_date": nowdate(), "status": "Draft", "company": self.doc.company, "fiscal_year": fiscal_year, "requested_by": webnotes.session.user, "material_request_type": "Purchase" }] for sales_order, requested_qty in items_to_be_requested[item].items(): pr_doclist.append({ "doctype": "Material Request Item", "__islocal": 1, "parentfield": "indent_details", "item_code": item, "item_name": item_wrapper.doc.item_name, "description": item_wrapper.doc.description, "uom": item_wrapper.doc.stock_uom, "item_group": item_wrapper.doc.item_group, "brand": item_wrapper.doc.brand, "qty": requested_qty, "schedule_date": add_days(nowdate(), cint(item_wrapper.doc.lead_time_days)), "warehouse": self.doc.purchase_request_for_warehouse, "sales_order_no": sales_order if sales_order!="No Sales Order" else None }) pr_wrapper = webnotes.bean(pr_doclist) pr_wrapper.ignore_permissions = 1 pr_wrapper.submit() purchase_request_list.append(pr_wrapper.doc.name) if purchase_request_list: pur_req = ["""<a href="#Form/Material Request/%s" target="_blank">%s</a>""" % \ (p, p) for p in purchase_request_list] msgprint("Material Request(s) created: \n%s" % "\n".join(pur_req)) else: msgprint(_("Nothing to request"))
def execute(filters=None): if not filters: filters = {} columns = get_columns() entries = get_gl_entries(filters) entries_after_report_date = [[ gle.voucher_type, gle.voucher_no ] for gle in get_gl_entries(filters, upto_report_date=False)] account_territory_map = get_account_territory_map() si_due_date_map = get_si_due_date_map() # Age of the invoice on this date age_on = getdate(filters.get("report_date")) > getdate(nowdate()) \ and nowdate() or filters.get("report_date") data = [] for gle in entries: if cstr(gle.against_voucher) == gle.voucher_no or not gle.against_voucher \ or [gle.against_voucher_type, gle.against_voucher] in entries_after_report_date: due_date = (gle.voucher_type == "Sales Invoice") \ and si_due_date_map.get(gle.voucher_no) or "" invoiced_amount = gle.debit > 0 and gle.debit or 0 payment_amount = get_payment_amount( gle, filters.get("report_date") or nowdate(), entries_after_report_date) outstanding_amount = invoiced_amount - payment_amount if abs(flt(outstanding_amount)) > 0.01: row = [ gle.posting_date, gle.account, gle.voucher_type, gle.voucher_no, gle.remarks, due_date, account_territory_map.get(gle.account), invoiced_amount, payment_amount, outstanding_amount ] # Ageing if filters.get("ageing_based_on") == "Due Date": ageing_based_on_date = due_date else: ageing_based_on_date = gle.posting_date row += get_ageing_data(ageing_based_on_date, age_on, outstanding_amount) data.append(row) return columns, data
def declare_completed(self): if self.doc.status == 'Open': self.validate_for_pending_review() self.doc.review_date = nowdate() else: self.validate_with_timesheet_dates() self.validate_for_closed() self.doc.closing_date = nowdate() self.doc.status = 'Closed' self.doc.docstatus = 1 self.doc.save() self.remove_event_from_calender() return cstr('true')
def set_maintenance_status(self): if not self.doc.warranty_expiry_date and not self.doc.amc_expiry_date: self.doc.maintenance_status = None if self.doc.warranty_expiry_date and self.doc.warranty_expiry_date < nowdate(): self.doc.maintenance_status = "Out of Warranty" if self.doc.amc_expiry_date and self.doc.amc_expiry_date < nowdate(): self.doc.maintenance_status = "Out of AMC" if self.doc.amc_expiry_date and self.doc.amc_expiry_date >= nowdate(): self.doc.maintenance_status = "Under AMC" if self.doc.warranty_expiry_date and self.doc.warranty_expiry_date >= nowdate(): self.doc.maintenance_status = "Under Warranty"
def create_material_request(material_requests): """ Create indent on reaching reorder level """ mr_list = [] defaults = webnotes.defaults.get_defaults() for request_type in material_requests: for company in material_requests[request_type]: items = material_requests[request_type][company] if items: mr = [{ "doctype": "Material Request", "company": company, "fiscal_year": defaults.fiscal_year, "transaction_date": nowdate(), "material_request_type": request_type, "remark": _("This is an auto generated Material Request.") + \ _("""It was raised because the (actual + ordered + indented - reserved) quantity reaches re-order level when the following record was created""") }] for d in items: item = webnotes.doc("Item", d.item_code) mr.append({ "doctype": "Material Request Item", "parenttype": "Material Request", "parentfield": "indent_details", "item_code": d.item_code, "schedule_date": add_days(nowdate(),cint(item.lead_time_days)), "uom": item.stock_uom, "warehouse": d.warehouse, "item_name": item.item_name, "description": item.description, "item_group": item.item_group, "qty": d.reorder_qty, "brand": item.brand, }) mr_bean = webnotes.bean(mr) mr_bean.insert() mr_bean.submit() mr_list.append(mr_bean) if mr_list: if not hasattr(webnotes, "reorder_email_notify"): webnotes.reorder_email_notify = webnotes.conn.get_value('Global Defaults', None, 'reorder_email_notify') if(webnotes.reorder_email_notify): send_email_notification(mr_list)
def send_event_digest(): today = nowdate() for user in webnotes.conn.sql("""select name, email, language from tabProfile where ifnull(enabled,0)=1 and user_type='System User' and name not in ('Guest', 'Administrator')""", as_dict=1): events = get_events(today, today, user.name, for_reminder=True) if events: text = "" webnotes.set_user_lang(user.name, user.language) webnotes.load_translations("core", "doctype", "event") text = "<h3>" + webnotes._("Events In Today's Calendar") + "</h3>" for e in events: if e.all_day: e.starts_on = "All Day" text += "<h4>%(starts_on)s: %(subject)s</h4><p>%(description)s</p>" % e text += '<p style="color: #888; font-size: 80%; margin-top: 20px; padding-top: 10px; border-top: 1px solid #eee;">'\ + webnotes._("Daily Event Digest is sent for Calendar Events where reminders are set.")+'</p>' from webnotes.utils.email_lib import sendmail sendmail(recipients=user.email, subject=webnotes._("Upcoming Events for Today"), msg=text)
def get_activity_list(self): out = {} import webnotes rt = webnotes.user.can_read dt_list = [ d[0] for d in sql("select distinct t2.name from tabDocField t1, tabDocType t2 where t1.fieldname='status' and t1.docstatus=0 and (t2.istable is null or t2.istable = 0) and t1.parent = t2.name" ) ] if not dt_list: return out # get list of activity dt for dt in dt_list: if dt in rt: out[dt] = {} # get status list sl = sql("select distinct status from `tab%s`" % dt) for s in sl: if s[0]: # get count cnt = sql( "select count(*) from `tab%s` where status = '%s' and modified > '%s'" % (dt, s[0], add_days(nowdate(), -7)))[0][0] out[dt][s[0]] = cint(cnt) return out
def get_valuation_rate(self, arg): """ Get average valuation rate of relevant warehouses as per valuation method (MAR/FIFO) as on costing date """ dt = self.doc.costing_date or nowdate() time = self.doc.costing_date == nowdate() and now().split()[1] or '23:59' warehouse = sql("select warehouse from `tabBin` where item_code = %s", arg['item_code']) rate = [] for wh in warehouse: r = get_obj('Valuation Control').get_incoming_rate(dt, time, arg['item_code'], wh[0], qty = arg.get('qty', 0)) if r: rate.append(r) return rate and flt(sum(rate))/len(rate) or 0
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_valuation_rate(self, arg): """ Get average valuation rate of relevant warehouses as per valuation method (MAR/FIFO) as on costing date """ dt = self.doc.costing_date or nowdate() time = self.doc.costing_date == nowdate() and now().split()[1] or "23:59" warehouse = sql("select warehouse from `tabBin` where item_code = %s", arg["item_code"]) rate = [] for wh in warehouse: r = get_obj("Valuation Control").get_incoming_rate(dt, time, arg["item_code"], wh[0], qty=arg.get("qty", 0)) if r: rate.append(r) return rate and flt(sum(rate)) / len(rate) or 0
def sent_reminder_task(): task_list = sql(""" select subject, allocated_to, project, exp_start_date, exp_end_date, priority, status, name, senders_name, opening_date, review_date, description from tabTask where task_email_notify=1 and sent_reminder=0 and status='Open' and exp_start_date is not null""", as_dict=1) for i in task_list: if date_diff(i['exp_start_date'], nowdate()) == 2: msg2 = """<h2>Two days to complete: %(name)s</h2> <p>This is a reminder for the task %(name)s has been assigned to you by %(senders_name)s on %(opening_date)s</p> <p><b>Subject:</b> %(subject)s </p> <p><b>Project:</b> %(project)s</p> <p><b>Expected Start Date:</b> %(exp_start_date)s</p> <p><b>Expected End Date:</b> %(exp_end_date)s</p> <p><b>Review Date:</b> %(review_date)s</p> <p><b>Details:</b> %(description)s</p> <p>If you have already completed this task, please update the system</p> <p>Good Luck!</p> <p>(This notification is autogenerated)</p>""" % i sendmail(i['allocated_to'], sender='*****@*****.**', msg=msg2,send_now=1, \ subject='A task has been assigned') sql("update `tabTask` set sent_reminder='1' where name='%(name)s' and allocated_to= '%(allocated_to)s'" % i)
def make_new_invoice(ref_wrapper): from webnotes.model.wrapper import clone new_invoice = clone(ref_wrapper) mcount = month_map[ref_wrapper.doc.recurring_type] today = nowdate() new_invoice.doc.fields.update({ "posting_date": today, "aging_date": today, "due_date": add_days(today, cint(date_diff(ref_wrapper.doc.due_date, ref_wrapper.doc.posting_date))), "invoice_period_from_date": \ get_next_date(ref_wrapper.doc.invoice_period_from_date, mcount), "invoice_period_to_date": \ get_next_date(ref_wrapper.doc.invoice_period_to_date, mcount), "owner": ref_wrapper.doc.owner, }) new_invoice.submit() return new_invoice
def get_leave_details(self, lwp=None): if not self.doc.fiscal_year: self.doc.fiscal_year = webnotes.get_default("fiscal_year") if not self.doc.month: self.doc.month = "%02d" % getdate(nowdate()).month m = get_obj('Salary Manager').get_month_details(self.doc.fiscal_year, self.doc.month) holidays = self.get_holidays_for_employee(m) if not cint(webnotes.conn.get_value("HR Settings", "HR Settings", "include_holidays_in_total_working_days")): m["month_days"] -= len(holidays) if m["month_days"] < 0: msgprint(_("Bummer! There are more holidays than working days this month."), raise_exception=True) if not lwp: lwp = self.calculate_lwp(holidays, m) self.doc.total_days_in_month = m['month_days'] self.doc.leave_without_pay = lwp payment_days = flt(self.get_payment_days(m)) - flt(lwp) self.doc.payment_days = payment_days > 0 and payment_days or 0 ss="select DATEDIFF(NOW(),(select date_of_joining from tabEmployee where name='"+self.doc.employee+"'))/365 from tabEmployee where name='"+self.doc.employee+"' and status='Left' and DATEDIFF(CURDATE(), (select relieving_date from tabEmployee where name='"+self.doc.employee+"')) <=31 and DATEDIFF(NOW(),(select date_of_joining from tabEmployee where name='"+self.doc.employee+"'))/365 >5" # webnotes.errprint(ss) res=webnotes.conn.sql(ss) # webnotes.errprint(res) if res: lst_sal_qry="select net_pay from `tabSalary Slip` where employee='"+self.doc.employee+"' order by creation desc limit 1" # webnotes.errprint(lst_sal_qry) lst_sal=webnotes.conn.sql(lst_sal_qry) grp_amt=(cint(lst_sal[0][0])* cint(res[0][0]))/27 # webnotes.errprint(grp_amt) self.doc.grauity_amount=grp_amt
def has_pwd_expired(self): if session['user'] != 'Administrator' and session['user'].lower() != 'demo': last_pwd_date = None try: last_pwd_date = sql("select password_last_updated from tabProfile where name=%s",session['user'])[0][0] or '' except: return 'No' if cstr(last_pwd_date) == '': sql("update tabProfile set password_last_updated = '%s' where name='%s'"% (nowdate(),session['user'])) return 'No' else: date_diff = (getdate(nowdate()) - last_pwd_date).days expiry_period = sql("select value from tabSingles where doctype='Control Panel' and field='password_expiry_days'") if expiry_period and cint(expiry_period[0][0]) and cint(expiry_period[0][0]) < date_diff: return 'Yes' return 'No'
def validate(self): if getdate(self.doc.timesheet_date) > getdate(nowdate()): msgprint("You can not prepare timesheet for future date") raise Exception chk = sql( "select name from `tabTimesheet` where timesheet_date=%s and owner=%s and status!='Cancelled' and name!=%s", (self.doc.timesheet_date, self.doc.owner, self.doc.name)) if chk: msgprint("You have already created timesheet " + cstr(chk and chk[0][0] or '') + " for this date.") raise Exception import time for d in getlist(self.doclist, 'timesheet_details'): if d.act_start_time and d.act_end_time: d1 = time.strptime(d.act_start_time, "%H:%M") d2 = time.strptime(d.act_end_time, "%H:%M") if d1 > d2: msgprint( "Start time can not be greater than end time. Check for Task Id : " + cstr(d.task_id)) raise Exception elif d1 == d2: msgprint( "Start time and end time can not be same. Check for Task Id : " + cstr(d.task_id)) raise Exception
def raise_purchase_request(self): """ Raise Material Request if projected qty is less than qty required Requested qty should be shortage qty considering minimum order qty """ if not self.doc.purchase_request_for_warehouse: webnotes.msgprint( "Please enter Warehouse for which Material Request will be raised", raise_exception=1) bom_dict = self.get_distinct_items_and_boms()[0] self.get_raw_materials(bom_dict) item_projected_qty = self.get_projected_qty() from accounts.utils import get_fiscal_year fiscal_year = get_fiscal_year(nowdate())[0] items_to_be_requested = webnotes._dict() for item in self.item_dict: if flt(self.item_dict[item][0]) > item_projected_qty[item]: # shortage requested_qty = flt( self.item_dict[item][0]) - item_projected_qty[item] # comsider minimum order qty requested_qty = requested_qty > flt(self.item_dict[item][3]) and \ requested_qty or flt(self.item_dict[item][3]) items_to_be_requested[item] = requested_qty self.insert_purchase_request(items_to_be_requested, fiscal_year)
def set_pur_serial_no_values(self, obj, serial_no, d, s, new_rec): item_details = sql("select item_group, warranty_period from `tabItem` where name = '%s' and \ (ifnull(end_of_life,'')='' or end_of_life = '0000-00-00' or end_of_life > now()) " %(d.item_code), as_dict=1) s.purchase_document_type = obj.doc.doctype s.purchase_document_no = obj.doc.name s.purchase_date = obj.doc.posting_date s.purchase_time = obj.doc.posting_time s.purchase_rate = d.valuation_rate or d.incoming_rate s.item_code = d.item_code s.item_name = d.item_name s.brand = d.brand s.description = d.description s.item_group = item_details and item_details[0]['item_group'] or '' s.warranty_period = item_details and item_details[0]['warranty_period'] or 0 s.supplier = obj.doc.supplier s.supplier_name = obj.doc.supplier_name s.address_display = obj.doc.address_display or obj.doc.supplier_address s.warehouse = d.warehouse or d.t_warehouse s.docstatus = 0 s.status = 'In Store' s.modified = nowdate() s.modified_by = session['user'] s.serial_no = serial_no s.sle_exists = 1 s.fiscal_year = obj.doc.fiscal_year s.company = obj.doc.company s.save(new_rec)
def item_query(doctype, txt, searchfield, start, page_len, filters): from webnotes.utils import nowdate conditions = [] return webnotes.conn.sql( """select tabItem.name, if(length(tabItem.item_name) > 40, concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name, if(length(tabItem.description) > 40, \ concat(substr(tabItem.description, 1, 40), "..."), description) as decription from tabItem where tabItem.docstatus < 2 and (ifnull(tabItem.end_of_life, '') = '' or tabItem.end_of_life > %(today)s) and (tabItem.`{key}` LIKE %(txt)s or tabItem.item_name LIKE %(txt)s) {fcond} {mcond} order by if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999), if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999), name, item_name limit %(start)s, %(page_len)s """.format( key=searchfield, fcond=get_filters_cond(doctype, filters, conditions), mcond=get_match_cond(doctype, searchfield)), { "today": nowdate(), "txt": "%%%s%%" % txt, "_txt": txt.replace("%", ""), "start": start, "page_len": page_len })
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 make_bank_voucher(self): self.set_flag() """ get default bank account,default salary acount from company """ #amt = self.get_total_salary() com = webnotes.conn.sql("""select default_bank_account, default_expense_account from `tabCompany` where name = '%s'""" % self.doc.company,as_list=1) if not com[0][0] or not com[0][1]: msgprint("You can set Default Bank Account in Company master.") if not self.doc.jv: jv = Document('Journal Voucher') jv.voucher_type = 'Bank Voucher' jv.user_remark = 'Referrals Payment' jv.fiscal_year = '2013-14' jv.total_credit = jv.total_debit = self.doc.total_amount jv.company = self.doc.company jv.posting_date = nowdate() jv.save() jvd = Document('Journal Voucher Detail') jvd.account = com and com[0][0] or '' jvd.credit = self.doc.total_amount jvd.parent = jv.name jvd.save() jvd1 = Document('Journal Voucher Detail') jvd1.account = com and com[0][1] or '' jvd1.debit = self.doc.total_amount jvd1.parent = jv.name jvd1.save() self.doc.jv = jv.name self.doc.save()
def get_barcode_details(self, barcode): item = webnotes.conn.sql( "select name, end_of_life, is_sales_item, is_service_item \ from `tabItem` where barcode = %s", barcode, as_dict=1) ret = {} if not item: msgprint("""No item found for this barcode: %s. May be barcode not updated in item master. Please check""" % barcode) elif item[0]['end_of_life'] and getdate(cstr( item[0]['end_of_life'])) < nowdate(): msgprint( "Item: %s has been expired. Please check 'End of Life' field in item master" % item[0]['name']) elif item[0]['is_sales_item'] == 'No' and item[0][ 'is_service_item'] == 'No': msgprint("Item: %s is not a sales or service item" % item[0]['name']) elif len(item) > 1: msgprint( "There are multiple item for this barcode. \nPlease select item code manually" ) else: ret = {'item_code': item and item[0]['name'] or ''} return ret
def get_leave_details(self, lwp=None): if not self.doc.fiscal_year: self.doc.fiscal_year = webnotes.get_default("fiscal_year") if not self.doc.month: self.doc.month = "%02d" % getdate(nowdate()).month m = get_obj('Salary Manager').get_month_details( self.doc.fiscal_year, self.doc.month) holidays = self.get_holidays_for_employee(m) if not cint( webnotes.conn.get_value( "HR Settings", "HR Settings", "include_holidays_in_total_working_days")): m["month_days"] -= len(holidays) if m["month_days"] < 0: msgprint(_( "Bummer! There are more holidays than working days this month." ), raise_exception=True) if not lwp: lwp = self.calculate_lwp(holidays, m) self.doc.total_days_in_month = m['month_days'] self.doc.leave_without_pay = lwp payment_days = flt(self.get_payment_days(m)) - flt(lwp) self.doc.payment_days = payment_days > 0 and payment_days or 0