def test_monthly_budget_crossed_for_mr(self): budget = make_budget(applicable_on_material_request=1, applicable_on_purchase_order=1, action_if_accumulated_monthly_budget_exceeded_on_mr="Stop", budget_against="Cost Center") fiscal_year = get_fiscal_year(nowdate())[0] frappe.db.set_value("Budget", budget.name, "action_if_accumulated_monthly_budget_exceeded", "Stop") frappe.db.set_value("Budget", budget.name, "fiscal_year", fiscal_year) mr = frappe.get_doc({ "doctype": "Material Request", "material_request_type": "Purchase", "transaction_date": nowdate(), "company": budget.company, "items": [{ 'item_code': '_Test Item', 'qty': 1, 'uom': "_Test UOM", 'warehouse': '_Test Warehouse - _TC', 'schedule_date': nowdate(), 'rate': 100000, 'expense_account': '_Test Account Cost for Goods Sold - _TC', 'cost_center': '_Test Cost Center - _TC' }] }) mr.set_missing_values() self.assertRaises(BudgetError, mr.submit) budget.load_from_db() budget.cancel()
def test_overdue(self): task = create_task("Testing Overdue", add_days(nowdate(), -10), add_days(nowdate(), -5)) from erpnext.projects.doctype.task.task import set_tasks_as_overdue set_tasks_as_overdue() self.assertEqual(frappe.db.get_value("Task", task.name, "status"), "Overdue")
def __init__(self, filters=None): self.filters = frappe._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_account_gl_entry_for_amount(doc,method): with_tax = frappe.new_doc('Accounts Receivables With Tax') with_tax.posting_date = nowdate() with_tax.transaction_date = nowdate() if doc.doctype == 'Sales Invoice': with_tax.account = doc.debit_to with_tax.party_type= 'Customer' with_tax.against_voucher_type= 'Sales Invoice' with_tax.voucher_type= 'Sales Invoice' with_tax.party=doc.customer with_tax.credit=0.0 with_tax.debit= doc.grand_total with_tax.against= doc.against_income_account elif doc.doctype == 'Purchase Invoice': with_tax.account = doc.credit_to with_tax.party_type= 'Supplier' with_tax.against_voucher_type= 'Purchase Invoice' with_tax.voucher_type= 'Purchase Invoice' with_tax.party=doc.supplier with_tax.debit=0.0 with_tax.credit= doc.grand_total with_tax.against= doc.credit_to with_tax.against_voucher= doc.name with_tax.voucher_no = doc.name with_tax.paid_amount = doc.grand_total-doc.outstanding_amount with_tax.outstanding_amount= doc.outstanding_amount with_tax.is_opening=doc.is_opening with_tax.fiscal_year=doc.fiscal_year with_tax.company= doc.company with_tax.save(ignore_permissions=True)
def create_quotation(self): quotation = frappe.new_doc("Quotation") values = { "doctype": "Quotation", "quotation_to": "Customer", "order_type": "Shopping Cart", "party_name": get_party(frappe.session.user).name, "docstatus": 0, "contact_email": frappe.session.user, "selling_price_list": "_Test Price List Rest of the World", "currency": "USD", "taxes_and_charges" : "_Test Tax 1 - _TC", "conversion_rate":1, "transaction_date" : nowdate(), "valid_till" : add_months(nowdate(), 1), "items": [{ "item_code": "_Test Item", "qty": 1 }], "taxes": frappe.get_doc("Sales Taxes and Charges Template", "_Test Tax 1 - _TC").taxes, "company": "_Test Company" } quotation.update(values) quotation.insert(ignore_permissions=True) return quotation
def test_expense_claim_status(self): payable_account = get_payable_account("Wind Power LLC") expense_claim = frappe.get_doc({ "doctype": "Expense Claim", "employee": "_T-Employee-0001", "payable_account": payable_account, "approval_status": "Approved", "expenses": [{ "expense_type": "Travel", "default_account": "Travel Expenses - WP", "claim_amount": 300, "sanctioned_amount": 200 }] }) expense_claim.submit() je_dict = make_bank_entry(expense_claim.name) je = frappe.get_doc(je_dict) je.posting_date = nowdate() je.cheque_no = random_string(5) je.cheque_date = nowdate() je.submit() expense_claim = frappe.get_doc("Expense Claim", expense_claim.name) self.assertEqual(expense_claim.status, "Paid") je.cancel() expense_claim = frappe.get_doc("Expense Claim", expense_claim.name) self.assertEqual(expense_claim.status, "Unpaid")
def create_purchase_order(**args): po = frappe.new_doc("Purchase Order") args = frappe._dict(args) if args.transaction_date: po.transaction_date = args.transaction_date po.schedule_date = add_days(nowdate(), 1) po.company = args.company or "_Test Company" po.supplier = args.customer or "_Test Supplier" po.is_subcontracted = args.is_subcontracted or "No" po.currency = args.currency or frappe.get_cached_value('Company', po.company, "default_currency") po.conversion_factor = args.conversion_factor or 1 po.supplier_warehouse = args.supplier_warehouse or None po.append("items", { "item_code": args.item or args.item_code or "_Test Item", "warehouse": args.warehouse or "_Test Warehouse - _TC", "qty": args.qty or 10, "rate": args.rate or 500, "schedule_date": add_days(nowdate(), 1), "include_exploded_items": args.get('include_exploded_items', 1) }) if not args.do_not_save: po.insert() if not args.do_not_submit: if po.is_subcontracted == "Yes": supp_items = po.get("supplied_items") for d in supp_items: d.reserve_warehouse = args.warehouse or "_Test Warehouse - _TC" po.submit() return po
def make_request_for_quotation(supplier_data=None): """ :param supplier_data: List containing supplier data """ supplier_data = supplier_data if supplier_data else get_supplier_data() rfq = frappe.new_doc('Request for Quotation') rfq.transaction_date = nowdate() rfq.status = 'Draft' rfq.company = '_Test Company' rfq.message_for_supplier = 'Please supply the specified items at the best possible rates.' for data in supplier_data: rfq.append('suppliers', data) rfq.append("items", { "item_code": "_Test Item", "description": "_Test Item", "uom": "_Test UOM", "qty": 5, "warehouse": "_Test Warehouse - _TC", "schedule_date": nowdate() }) rfq.submit() return rfq
def get_no_of_days(self): no_of_days_in_month = calendar.monthrange(getdate(nowdate()).year, getdate(nowdate()).month) no_of_holidays_in_month = len([1 for i in calendar.monthcalendar(getdate(nowdate()).year, getdate(nowdate()).month) if i[6] != 0]) return [no_of_days_in_month[1], no_of_holidays_in_month]
def create_purchase_order(**args): po = frappe.new_doc("Purchase Order") args = frappe._dict(args) if args.transaction_date: po.transaction_date = args.transaction_date po.schedule_date = add_days(nowdate(), 1) po.company = args.company or "_Test Company" po.supplier = args.customer or "_Test Supplier" po.is_subcontracted = args.is_subcontracted or "No" po.currency = args.currency or frappe.db.get_value("Company", po.company, "default_currency") po.conversion_factor = args.conversion_factor or 1 po.append("items", { "item_code": args.item or args.item_code or "_Test Item", "warehouse": args.warehouse or "_Test Warehouse - _TC", "qty": args.qty or 10, "rate": args.rate or 500, "schedule_date": add_days(nowdate(), 1) }) if not args.do_not_save: po.insert() if not args.do_not_submit: po.submit() return po
def test_create_asset_maintenance(self): pr = make_purchase_receipt(item_code="Photocopier", qty=1, rate=100000.0, location="Test Location") asset_name = frappe.db.get_value("Asset", {"purchase_receipt": pr.name}, 'name') asset_doc = frappe.get_doc('Asset', asset_name) month_end_date = get_last_day(nowdate()) purchase_date = nowdate() if nowdate() != month_end_date else add_days(nowdate(), -15) asset_doc.available_for_use_date = purchase_date asset_doc.purchase_date = purchase_date asset_doc.calculate_depreciation = 1 asset_doc.append("finance_books", { "expected_value_after_useful_life": 200, "depreciation_method": "Straight Line", "total_number_of_depreciations": 3, "frequency_of_depreciation": 10, "depreciation_start_date": month_end_date }) asset_doc.save() if not frappe.db.exists("Asset Maintenance", "Photocopier"): asset_maintenance = frappe.get_doc({ "doctype": "Asset Maintenance", "asset_name": "Photocopier", "maintenance_team": "Team Awesome", "company": "_Test Company", "asset_maintenance_tasks": get_maintenance_tasks() }).insert() next_due_date = calculate_next_due_date(nowdate(), "Monthly") self.assertEqual(asset_maintenance.asset_maintenance_tasks[0].next_due_date, next_due_date)
def assignmember(memberid,ftv): frappe.db.sql("""update `tabFirst Timer` set ftv_owner='%s' where name='%s' """ % (memberid,ftv)) # recipients='*****@*****.**' member=frappe.db.sql("select member_name,email_id,phone_1 from `tabMember` where name='%s'"%(memberid)) member_ph = frappe.db.sql("select phone_1 from `tabMember` where name='%s'"%(memberid)) ftvdetails=frappe.db.sql("select ftv_name,email_id,task_description,due_date,phone_1 from `tabFirst Timer` where name='%s'"%(ftv)) ftv_ph = frappe.db.sql("select phone_1 from `tabMember` where name='%s'"%(ftv)) msg_member="""Hello %s,\n The First Timer '%s' name: '%s' Email ID: '%s' is assigned to you for follow up.\n Regards,\n Verve """%(member[0][0],ftv,ftvdetails[0][0],ftvdetails[0][1]) msg_ftv="""Hello %s,\n The Member '%s' name: '%s' Email ID: '%s' is assigned to you for follow up.\n Regards, \n Verve """%(ftvdetails[0][0],memberid,member[0][0],member[0][1]) desc="""Member '%s' is assigned to First Timer '%s' for followup."""%(memberid,ftv) task=frappe.get_doc({ "doctype": "Task", "subject": "Assign For followup", "expected_start_date":nowdate(), "expected_start_date":add_days(nowdate(),2), "status": "Open", "project": "", "description":desc }).insert(ignore_permissions=True) if frappe.db.exists("User", ftvdetails[0][1]): frappe.share.add("Task", task.name, ftvdetails[0][1], write=0) if frappe.db.exists("User", member[0][1]): frappe.share.add("Task", task.name, member[0][1], write=1) notify = frappe.db.sql("""select value from `tabSingles` where doctype='Notification Settings' and field='assign_for_followup'""",as_list=1) if "Email" in notify[0][0]: if member: frappe.sendmail(recipients=member[0][1], content=msg_member, subject='Assign For FollowUp Notification') if ftvdetails: frappe.sendmail(recipients=ftvdetails[0][1], content=msg_ftv, subject='Assign For FollowUp Notification') if "SMS" in notify[0][0]: if member_ph: send_sms(member_ph[0], msg_member) if ftv_ph: send_sms(ftv_ph[0], msg_ftv) if "Push Notification" in notify[0][0]: data={} data['Message']=msg_member gcm = GCM('AIzaSyBIc4LYCnUU9wFV_pBoFHHzLoGm_xHl-5k') res=frappe.db.sql("select device_id from tabUser where name ='%s'" %(member[0][1]),as_list=1) frappe.errprint(res) if res: res = gcm.json_request(registration_ids=res, data=data,collapse_key='uptoyou', delay_while_idle=True, time_to_live=3600) # receiver_list=[] # receiver_list.append(member[0][2]) # frappe.errprint(['rev[0]',receiver_list[0]]) # if receiver_list[0] : # frappe.errprint(receiver_list[0]) # send_sms(receiver_list, cstr(msg_member)) # frappe.sendmail(recipients=member[0][1], sender='*****@*****.**', content=msg_member, subject='Assign for follow up') # frappe.sendmail(recipients=ftvdetails[0][1], sender='*****@*****.**', content=msg_ftv, subject='Assign for follow up') return "Done"
def make_rfq(item, supplier, contact): # make rfq rfq = frappe.get_doc({ 'doctype': 'Request for Quotation', 'transaction_date': nowdate(), 'status': 'Draft', 'company': frappe.db.get_single_value('Marketplace Settings', 'company'), 'message_for_supplier': 'Please supply the specified items at the best possible rates', 'suppliers': [ { 'supplier': supplier.name, 'contact': contact.name } ], 'items': [ { 'item_code': item.item_code, 'qty': 1, 'schedule_date': nowdate(), 'warehouse': item.default_warehouse or get_root_of("Warehouse"), 'description': item.description, 'uom': item.stock_uom } ] }).insert() rfq.save() rfq.submit() return rfq
def test_future_inactive_signed_contract_status(self): self.contract_doc.is_signed = True self.contract_doc.start_date = add_days(nowdate(), 1) self.contract_doc.end_date = add_days(nowdate(), 2) self.contract_doc.insert() self.assertEqual(self.contract_doc.status, "Inactive")
def get_leave_details(self, joining_date=None, relieving_date=None, lwp=None): if not self.fiscal_year: # if default fiscal year is not set, get from nowdate self.fiscal_year = get_fiscal_year(nowdate())[0] if not self.month: self.month = "%02d" % getdate(nowdate()).month self.set_month_dates() if not joining_date: joining_date, relieving_date = frappe.db.get_value("Employee", self.employee, ["date_of_joining", "relieving_date"]) holidays = self.get_holidays_for_employee(self.start_date, self.end_date) working_days = date_diff(self.end_date, self.start_date) + 1 if not cint(frappe.db.get_value("HR Settings", None, "include_holidays_in_total_working_days")): working_days -= len(holidays) if working_days < 0: frappe.throw(_("There are more holidays than working days this month.")) if not lwp: lwp = self.calculate_lwp(holidays, working_days) self.total_days_in_month = working_days self.leave_without_pay = lwp payment_days = flt(self.get_payment_days(joining_date, relieving_date)) - flt(lwp) self.payment_days = payment_days > 0 and payment_days or 0
def test_fee_validity(self): patient = get_random("Patient") physician = get_random("Physician") if not patient: patient = frappe.new_doc("Patient") patient.patient_name = "Test Patient" patient.sex = "Male" patient.save(ignore_permissions=True) patient = patient.name if not physician: physician = frappe.new_doc("Physician") physician.first_name = "Amit Jain" physician.save(ignore_permissions=True) physician = physician.name frappe.db.set_value("Healthcare Settings", None, "max_visit", 2) frappe.db.set_value("Healthcare Settings", None, "valid_days", 7) appointment = create_appointment(patient, physician, nowdate()) invoice = frappe.db.get_value("Patient Appointment", appointment.name, "sales_invoice") self.assertEqual(invoice, None) create_invoice(frappe.defaults.get_global_default("company"), physician, patient, appointment.name, appointment.appointment_date) appointment = create_appointment(patient, physician, add_days(nowdate(), 4)) invoice = frappe.db.get_value("Patient Appointment", appointment.name, "sales_invoice") self.assertTrue(invoice) appointment = create_appointment(patient, physician, add_days(nowdate(), 5)) invoice = frappe.db.get_value("Patient Appointment", appointment.name, "sales_invoice") self.assertEqual(invoice, None) appointment = create_appointment(patient, physician, add_days(nowdate(), 10)) invoice = frappe.db.get_value("Patient Appointment", appointment.name, "sales_invoice") self.assertEqual(invoice, None)
def validate(self): member_name = frappe.get_value('Member', dict(email=frappe.session.user)) if not member_name: user = frappe.get_doc('User', frappe.session.user) member = frappe.get_doc(dict( doctype='Member', email=frappe.session.user, member_name=user.get_fullname() )).insert(ignore_permissions=True) member_name = member.name if self.get("__islocal"): self.member = member_name # get last membership (if active) last_membership = foundation.get_last_membership() # if person applied for offline membership if last_membership and not frappe.session.user == "Administrator": # if last membership does not expire in 30 days, then do not allow to renew if getdate(add_days(last_membership.to_date, -30)) > getdate(nowdate()) : frappe.throw(_('You can only renew if your membership expires within 30 days')) self.from_date = add_days(last_membership.to_date, 1) elif frappe.session.user == "Administrator": self.from_date = self.from_date else: self.from_date = nowdate() self.to_date = add_years(self.from_date, 1)
def __init__(self, filters=None): self.filters = frappe._dict(filters or {}) self.filters.from_date = getdate(self.filters.from_date or nowdate()) self.filters.to_date = getdate(self.filters.to_date or nowdate()) if not self.filters.get("company"): self.filters["company"] = frappe.db.get_single_value('Global Defaults', 'default_company')
def daily(): users = frappe.db.sql("""select email, name from tabUser where name in (select parent from tabUserRole where role='Accounts Manager')""", as_dict=1) today = nowdate() end = add_days(today, 3) sales_invoices = frappe.db.sql("""select name from `tabSales Invoice` where (date(due_date) between date(%(start)s) and date(%(end)s)) and outstanding_amount > 0 and docstatus=1""", { "start": today, "end": end, }, as_dict=1) if sales_invoices: subject = "Outstanding sales invoices due %s" % today message = "<p>Please review and follow up with the customers on the outstanding sales invoices below:</p><ul>" from frappe.utils import get_link_to_form for si in sales_invoices: message += "<li>" + get_link_to_form("Sales Invoice", si.name, si.name) + "</li>" message += "</ul><p><b>Note:</b> The list above contains the invoices that are either overdue or have its due date within the next 3 business days</p>" frappe.sendmail(recipients=[u.email for u in users], subject=subject, message=message, reply_to=EMAIL_SENDER, bulk=True) formatted_si = ", ".join(si.name for si in sales_invoices) for u in [u.name for u in users]: todo_doc = frappe.get_doc({ "doctype": "ToDo", "owner": u, "description": subject + ": " + formatted_si, "priority": "Medium", "status": "Open", "role": "Accounts Manager", "date": nowdate(), "assigned_by": frappe.session.user, }) todo_doc.insert(ignore_permissions=True)
def get_balance_on(account=None, date=None, party_type=None, party=None): if not account and frappe.form_dict.get("account"): account = frappe.form_dict.get("account") if not date and frappe.form_dict.get("date"): date = frappe.form_dict.get("date") if not party_type and frappe.form_dict.get("party_type"): party_type = frappe.form_dict.get("party_type") if not party and frappe.form_dict.get("party"): party = frappe.form_dict.get("party") 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: 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 if account: acc = frappe.get_doc("Account", account) acc.check_permission("read") # for pl accounts, get balance within a fiscal year if acc.report_type == 'Profit and Loss': cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" \ % year_start_date) # different filter for group and ledger - improved performance if acc.is_group: cond.append("""exists ( select name from `tabAccount` ac where ac.name = gle.account and ac.lft >= %s and ac.rgt <= %s )""" % (acc.lft, acc.rgt)) else: cond.append("""gle.account = "%s" """ % (account.replace('"', '\\"'), )) if party_type and party: cond.append("""gle.party_type = "%s" and gle.party = "%s" """ % (party_type.replace('"', '\\"'), party.replace('"', '\\"'))) if account or (party_type and party): bal = frappe.db.sql(""" SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) FROM `tabGL Entry` gle WHERE %s""" % " and ".join(cond))[0][0] # if bal is None, return 0 return flt(bal)
def set_stock_balance_as_per_serial_no(item_code=None, posting_date=None, posting_time=None, fiscal_year=None): if not posting_date: posting_date = nowdate() if not posting_time: posting_time = nowtime() condition = " and item.name='%s'" % item_code.replace("'", "\'") if item_code else "" bin = frappe.db.sql("""select bin.item_code, bin.warehouse, bin.actual_qty, item.stock_uom from `tabBin` bin, tabItem item where bin.item_code = item.name and item.has_serial_no = 1 %s""" % condition) for d in bin: serial_nos = frappe.db.sql("""select count(name) from `tabSerial No` where item_code=%s and warehouse=%s and docstatus < 2""", (d[0], d[1])) if serial_nos and flt(serial_nos[0][0]) != flt(d[2]): print d[0], d[1], d[2], serial_nos[0][0] sle = frappe.db.sql("""select valuation_rate, company from `tabStock Ledger Entry` where item_code = %s and warehouse = %s and ifnull(is_cancelled, 'No') = 'No' order by posting_date desc limit 1""", (d[0], d[1])) sle_dict = { 'doctype' : 'Stock Ledger Entry', 'item_code' : d[0], 'warehouse' : d[1], 'transaction_date' : nowdate(), 'posting_date' : posting_date, 'posting_time' : posting_time, 'voucher_type' : 'Stock Reconciliation (Manual)', 'voucher_no' : '', 'voucher_detail_no' : '', 'actual_qty' : flt(serial_nos[0][0]) - flt(d[2]), 'stock_uom' : d[3], 'incoming_rate' : sle and flt(serial_nos[0][0]) > flt(d[2]) and flt(sle[0][0]) or 0, 'company' : sle and cstr(sle[0][1]) or 0, 'is_cancelled' : 'No', 'batch_no' : '', 'serial_no' : '' } sle_doc = frappe.get_doc(sle_dict) sle_doc.flags.ignore_validate = True sle_doc.flags.ignore_links = True sle_doc.insert() args = sle_dict.copy() args.update({ "sle_id": sle_doc.name, "is_amended": 'No' }) update_bin(args) update_entries_after({ "item_code": d[0], "warehouse": d[1], "posting_date": posting_date, "posting_time": posting_time })
def update_maintenance_status(): serial_nos = frappe.db.sql('''select name from `tabSerial No` where (amc_expiry_date<%s or warranty_expiry_date<%s) and maintenance_status not in ('Out of Warranty', 'Out of AMC')''', (nowdate(), nowdate())) for serial_no in serial_nos: doc = frappe.get_doc("Serial No", serial_no[0]) doc.set_maintenance_status() frappe.db.set_value('Serial No', doc.name, 'maintenance_status', doc.maintenance_status)
def validate_date(self): print nowdate() print self.transaction_date if getdate(self.transaction_date) > getdate(nowdate()): frappe.throw("Date cannot be greater than today's date") if getdate(self.transaction_date) < getdate(nowdate()): frappe.throw("Date cannot be less than today's date")
def execute(filters=None): if not filters: filters = {} supplier_naming_by = frappe.db.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 frappe.db.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 \ or (gle.against_voucher_type == "Purchase Order"): 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 create_material_request(material_requests): """ Create indent on reaching reorder level """ mr_list = [] defaults = frappe.defaults.get_defaults() exceptions_list = [] from erpnext.accounts.utils import get_fiscal_year current_fiscal_year = get_fiscal_year(nowdate())[0] or defaults.fiscal_year 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 = frappe.new_doc("Material Request") mr.update({ "company": company, "fiscal_year": current_fiscal_year, "transaction_date": nowdate(), "material_request_type": request_type }) for d in items: item = frappe.get_doc("Item", d.item_code) mr.append("indent_details", { "doctype": "Material Request Item", "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.insert() mr.submit() mr_list.append(mr) except: if frappe.local.message_log: exceptions_list.append([] + frappe.local.message_log) frappe.local.message_log = [] else: exceptions_list.append(frappe.get_traceback()) if mr_list: if getattr(frappe.local, "reorder_email_notify", None) is None: frappe.local.reorder_email_notify = cint(frappe.db.get_value('Stock Settings', None, 'reorder_email_notify')) if(frappe.local.reorder_email_notify): send_email_notification(mr_list) if exceptions_list: notify_errors(exceptions_list)
def test_lapsed_contract_status(self): self.contract_doc.contract_term = "_Test Customer Contract with Requirements" self.contract_doc.start_date = add_days(nowdate(), -2) self.contract_doc.end_date = add_days(nowdate(), 1) self.contract_doc.requires_fulfilment = 1 self.contract_doc.fulfilment_deadline = add_days(nowdate(), -1) self.contract_doc.save() self.assertEqual(self.contract_doc.fulfilment_status, "Lapsed")
def update_fields(doc,method): if doc.is_subcontracting == 1: doc.transaction_date = add_days(nowdate(), -1) else: doc.transaction_date = nowdate () letter_head_tax = frappe.db.get_value("Purchase Taxes and Charges Template", \ doc.taxes_and_charges, "letter_head") doc.letter_head = letter_head_tax
def make_salary_slip_from_salary_structure(employee): sal_struct = make_salary_structure('Salary Structure Sample') sal_slip = make_salary_slip(sal_struct, employee = employee) sal_slip.employee_name = frappe.get_value("Employee", {"name":employee}, "employee_name") sal_slip.start_date = nowdate() sal_slip.posting_date = nowdate() sal_slip.payroll_frequency = "Monthly" sal_slip.insert() sal_slip.submit() return sal_slip
def get_balance_on(account=None, date=None): if not account and frappe.form_dict.get("account"): account = frappe.form_dict.get("account") date = frappe.form_dict.get("date") acc = frappe.db.get_value("Account", account, ["lft", "rgt", "report_type", "group_or_ledger"], as_dict=1) if not acc: frappe.throw(_("Account {0} does not exist").format(account), frappe.DoesNotExistError) 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: 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 # for pl accounts, get balance within a fiscal year if acc.report_type == "Profit and Loss": cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" % year_start_date) # different filter for group and ledger - improved performance if acc.group_or_ledger == "Group": cond.append( """exists ( select * from `tabAccount` ac where ac.name = gle.account and ac.lft >= %s and ac.rgt <= %s )""" % (acc.lft, acc.rgt) ) else: cond.append("""gle.account = "%s" """ % (account.replace('"', '"'),)) bal = frappe.db.sql( """ SELECT sum(ifnull(debit, 0)) - sum(ifnull(credit, 0)) FROM `tabGL Entry` gle WHERE %s""" % " and ".join(cond) )[0][0] # if bal is None, return 0 return flt(bal)
def validate_dates(self): cur_year = getdate(nowdate()).year cur_month= getdate(nowdate()).month if int(self.fiscal_year) > int(cur_year): frappe.throw(_("Salary Increment Cannot be run for future years.")) if int(self.fiscal_year) < int(cur_year): frappe.throw(_("Salary Increment Cannot be run for past years.")) '''
def make_sales_order(source_name, target_doc=None): quotation = frappe.db.get_value("Quotation", source_name, ["transaction_date", "valid_till"], as_dict = 1) if quotation.valid_till and (quotation.valid_till < quotation.transaction_date or quotation.valid_till < getdate(nowdate())): frappe.throw(_("Validity period of this quotation has ended.")) return _make_sales_order(source_name, target_doc)
def get_outstanding_reference_documents(args): if isinstance(args, string_types): args = json.loads(args) # confirm that Supplier is not blocked if args.get('party_type') == 'Supplier': supplier_status = get_supplier_block_status(args['party']) if supplier_status['on_hold']: if supplier_status['hold_type'] == 'All': return [] elif supplier_status['hold_type'] == 'Payments': if not supplier_status['release_date'] or getdate( nowdate()) <= supplier_status['release_date']: return [] party_account_currency = get_account_currency(args.get("party_account")) company_currency = frappe.get_cached_value('Company', args.get("company"), "default_currency") # Get negative outstanding sales /purchase invoices negative_outstanding_invoices = [] if args.get("party_type") not in ["Student", "Employee" ] and not args.get("voucher_no"): negative_outstanding_invoices = get_negative_outstanding_invoices( args.get("party_type"), args.get("party"), args.get("party_account"), party_account_currency, company_currency) # Get positive outstanding sales /purchase invoices/ Fees condition = "" if args.get("voucher_type") and args.get("voucher_no"): condition = " and voucher_type='{0}' and voucher_no='{1}'"\ .format(frappe.db.escape(args["voucher_type"]), frappe.db.escape(args["voucher_no"])) # Add cost center condition if args.get("cost_center"): condition += " and cost_center='%s'" % args.get("cost_center") outstanding_invoices = get_outstanding_invoices(args.get("party_type"), args.get("party"), args.get("party_account"), condition=condition) for d in outstanding_invoices: d["exchange_rate"] = 1 if party_account_currency != company_currency: if d.voucher_type in ("Sales Invoice", "Purchase Invoice", "Expense Claim"): d["exchange_rate"] = frappe.db.get_value( d.voucher_type, d.voucher_no, "conversion_rate") elif d.voucher_type == "Journal Entry": d["exchange_rate"] = get_exchange_rate(party_account_currency, company_currency, d.posting_date) if d.voucher_type in ("Purchase Invoice"): d["bill_no"] = frappe.db.get_value(d.voucher_type, d.voucher_no, "bill_no") # Get all SO / PO which are not fully billed or aginst which full advance not paid orders_to_be_billed = [] if (args.get("party_type") != "Student"): orders_to_be_billed = get_orders_to_be_billed(args.get("posting_date"), args.get("party_type"), args.get("party"), party_account_currency, company_currency) return negative_outstanding_invoices + outstanding_invoices + orders_to_be_billed
def test_reschedule_dependent_task(self): project = frappe.get_value("Project", {"project_name": "_Test Project"}) task1 = create_task("_Test Task 1", nowdate(), add_days(nowdate(), 10)) task2 = create_task("_Test Task 2", add_days(nowdate(), 11), add_days(nowdate(), 15), task1.name) task2.get("depends_on")[0].project = project task2.save() task3 = create_task("_Test Task 3", add_days(nowdate(), 11), add_days(nowdate(), 15), task2.name) task3.get("depends_on")[0].project = project task3.save() task1.update({"exp_end_date": add_days(nowdate(), 20)}) task1.save() self.assertEqual( frappe.db.get_value("Task", task2.name, "exp_start_date"), getdate(add_days(nowdate(), 21))) self.assertEqual( frappe.db.get_value("Task", task2.name, "exp_end_date"), getdate(add_days(nowdate(), 25))) self.assertEqual( frappe.db.get_value("Task", task3.name, "exp_start_date"), getdate(add_days(nowdate(), 26))) self.assertEqual( frappe.db.get_value("Task", task3.name, "exp_end_date"), getdate(add_days(nowdate(), 30)))
def get_payment_entry(dt, dn, party_amount=None, bank_account=None, bank_amount=None): doc = frappe.get_doc(dt, dn) if dt in ("Sales Order", "Purchase Order") and flt(doc.per_billed, 2) > 0: frappe.throw( _("Can only make payment against unbilled {0}").format(dt)) if dt in ("Sales Invoice", "Sales Order"): party_type = "Customer" elif dt in ("Purchase Invoice", "Purchase Order"): party_type = "Supplier" elif dt in ("Expense Claim"): party_type = "Employee" elif dt in ("Fees"): party_type = "Student" # party account if dt == "Sales Invoice": party_account = doc.debit_to elif dt == "Purchase Invoice": party_account = doc.credit_to elif dt == "Fees": party_account = doc.receivable_account else: party_account = get_party_account(party_type, doc.get(party_type.lower()), doc.company) party_account_currency = doc.get( "party_account_currency") or get_account_currency(party_account) # payment type if (dt == "Sales Order" or (dt in ("Sales Invoice", "Fees") and doc.outstanding_amount > 0)) \ or (dt=="Purchase Invoice" and doc.outstanding_amount < 0): payment_type = "Receive" else: payment_type = "Pay" # amounts grand_total = outstanding_amount = 0 if party_amount: grand_total = outstanding_amount = party_amount elif dt in ("Sales Invoice", "Purchase Invoice"): grand_total = doc.base_grand_total if party_account_currency == doc.company_currency else doc.grand_total outstanding_amount = doc.outstanding_amount elif dt in ("Expense Claim"): grand_total = doc.total_sanctioned_amount outstanding_amount = doc.total_sanctioned_amount - doc.total_amount_reimbursed elif dt == "Fees": grand_total = doc.grand_total outstanding_amount = doc.outstanding_amount else: total_field = "base_grand_total" if party_account_currency == doc.company_currency else "grand_total" grand_total = flt(doc.get(total_field)) outstanding_amount = grand_total - flt(doc.advance_paid) # bank or cash bank = get_default_bank_cash_account( doc.company, "Bank", mode_of_payment=doc.get("mode_of_payment"), account=bank_account) paid_amount = received_amount = 0 if party_account_currency == bank.account_currency: paid_amount = received_amount = abs(outstanding_amount) elif payment_type == "Receive": paid_amount = abs(outstanding_amount) if bank_amount: received_amount = bank_amount else: received_amount = abs(outstanding_amount) if bank_amount: paid_amount = bank_amount pe = frappe.new_doc("Entry Of Payment") pe.payment_type = payment_type pe.company = doc.company pe.posting_date = nowdate() pe.mode_of_payment = doc.get("mode_of_payment") pe.party_type = party_type pe.party = doc.get(scrub(party_type)) pe.paid_from = party_account if payment_type == "Receive" else bank.account pe.paid_to = party_account if payment_type == "Pay" else bank.account pe.paid_from_account_currency = party_account_currency \ if payment_type=="Receive" else bank.account_currency pe.paid_to_account_currency = party_account_currency if payment_type == "Pay" else bank.account_currency pe.paid_amount = paid_amount pe.received_amount = received_amount pe.allocate_payment_amount = 1 pe.letter_head = doc.get("letter_head") pe.append( "references", { "reference_doctype": dt, "reference_name": dn, "bill_no": doc.get("bill_no"), "due_date": doc.get("due_date"), "total_amount": grand_total, "outstanding_amount": outstanding_amount, "allocated_amount": outstanding_amount }) pe.setup_party_account_field() pe.set_missing_values() if party_account and bank: pe.set_exchange_rate() pe.set_amounts() return pe
def get_insight_engine_dashboards(start_date=None, end_date=None): today = getdate(nowdate()) yesterday = add_days(today, -1) last_week = add_days(today, -7) last_month = add_days(today, -30) # Default the dates to a week if not end_date: end_date = today if not start_date: start_date = last_week date_range = (getdate(start_date), getdate(end_date)) new_customer_details = get_new_customer_details() yesterdays_revenue = get_revenue_by_date(yesterday) weekly_revenue = get_revenue_by_date_range(last_week, today) monthly_revenue = get_revenue_by_date_range(last_month, today) top_products = get_top_products(*date_range) top_customers = get_top_customers(*date_range) top_customer_groups = get_top_customer_groups(*date_range) top_territories = get_top_territories(*date_range) top_sales_partners = get_top_sales_partners(*date_range) total_sales = get_sales_by_date_range(*date_range) pending_invoices = get_pending_invoices(*date_range) cash_on_hand = get_cash_on_hand() order_conversion_rate = get_order_conversion_rate() total_invoices = frappe.db.count("Sales Invoice", filters={"docstatus": 1}) total_skus = frappe.db.count("Item", filters={ "is_sales_item": 1, "disabled": 0 }) total_items_sold = frappe.db.get_value("Sales Invoice Item", filters={"docstatus": 1}, fieldname="SUM(qty)") # get upsell sales reporting from other apps total_upsell_sales = {} insight_engine_hooks = frappe.get_hooks('insight_engine') if insight_engine_hooks and isinstance(insight_engine_hooks, dict): upsell_hooks = insight_engine_hooks.get("total_upsell_sales") for fn in upsell_hooks: total_upsell_sales = frappe.get_attr(fn)(*date_range) return { "new_customers_by_month": new_customer_details.get("new_count_by_month"), "total_customers_by_month": new_customer_details.get("total_count_by_month"), "new_customer_sales_by_month": new_customer_details.get("new_sales_by_month"), "total_customer_sales_by_month": new_customer_details.get("total_sales_by_month"), "yesterday_revenue": yesterdays_revenue, "total_weekly_revenue": weekly_revenue.get("total", 0), "average_weekly_revenue": weekly_revenue.get("average", 0), "total_monthly_revenue": monthly_revenue.get("total", 0), "average_monthly_revenue": monthly_revenue.get("average", 0), "top_customers_by_revenue": top_customers.get("revenue"), "top_customer_groups_by_revenue": top_customer_groups.get("revenue"), "top_territories_by_revenue": top_territories.get("revenue"), "top_sales_partners_by_revenue": top_sales_partners.get("revenue"), "top_products_by_revenue": top_products.get("revenue"), "top_products_by_volume": top_products.get("by_volume"), "top_products_by_time": top_products.get("by_time"), "total_invoices": total_invoices, "paid_invoices": pending_invoices.get("paid_invoices"), "unpaid_invoices": pending_invoices.get("unpaid_invoices"), "overdue_invoices": pending_invoices.get("overdue_invoices"), "returned_invoices": pending_invoices.get("returned_invoices"), "credit_invoices": pending_invoices.get("credit_invoices"), "cash_on_hand": cash_on_hand, "order_conversion_rate": order_conversion_rate, "total_skus": total_skus, "total_items_sold": total_items_sold, # split total sales by different time periods "total_sales_by_day": total_sales.get("daily"), "total_sales_by_week": total_sales.get("weekly"), "total_sales_by_month": total_sales.get("monthly"), "total_sales_by_year": total_sales.get("yearly"), # split upsold sales by different time periods "total_upsell_sales_by_day": total_upsell_sales.get("daily"), "total_upsell_sales_by_week": total_upsell_sales.get("weekly"), "total_upsell_sales_by_month": total_upsell_sales.get("monthly"), "total_upsell_sales_by_year": total_upsell_sales.get("yearly") }
def test_payment_days(self): no_of_days = self.get_no_of_days() # Holidays not included in working days frappe.db.set_value("Payroll Settings", None, "include_holidays_in_total_working_days", 1) # set joinng date in the same month make_employee("*****@*****.**") if getdate(nowdate()).day >= 15: relieving_date = getdate(add_days(nowdate(), -10)) date_of_joining = getdate(add_days(nowdate(), -10)) elif getdate(nowdate()).day < 15 and getdate(nowdate()).day >= 5: date_of_joining = getdate(add_days(nowdate(), -3)) relieving_date = getdate(add_days(nowdate(), -3)) elif getdate(nowdate()).day < 5 and not getdate(nowdate()).day == 1: date_of_joining = getdate(add_days(nowdate(), -1)) relieving_date = getdate(add_days(nowdate(), -1)) elif getdate(nowdate()).day == 1: date_of_joining = getdate(nowdate()) relieving_date = getdate(nowdate()) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "date_of_joining", date_of_joining) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "relieving_date", None) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "status", "Active") ss = make_employee_salary_slip("*****@*****.**", "Monthly", "Test Payment Days") self.assertEqual(ss.total_working_days, no_of_days[0]) self.assertEqual(ss.payment_days, (no_of_days[0] - getdate(date_of_joining).day + 1)) # set relieving date in the same month frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "date_of_joining", (add_days(nowdate(), -60))) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "relieving_date", relieving_date) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "status", "Left") ss.save() self.assertEqual(ss.total_working_days, no_of_days[0]) self.assertEqual(ss.payment_days, getdate(relieving_date).day) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "relieving_date", None) frappe.db.set_value( "Employee", frappe.get_value("Employee", {"employee_name": "*****@*****.**"}, "name"), "status", "Active")
def get_outstanding_invoices(party_type, party, account, condition=None, filters=None): outstanding_invoices = [] precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2 if account: root_type = frappe.get_cached_value("Account", account, "root_type") party_account_type = "Receivable" if root_type == "Asset" else "Payable" else: party_account_type = erpnext.get_party_account_type(party_type) if party_account_type == 'Receivable': dr_or_cr = "debit_in_account_currency - credit_in_account_currency" payment_dr_or_cr = "credit_in_account_currency - debit_in_account_currency" else: dr_or_cr = "credit_in_account_currency - debit_in_account_currency" payment_dr_or_cr = "debit_in_account_currency - credit_in_account_currency" held_invoices = get_held_invoices(party_type, party) invoice_list = frappe.db.sql(""" select voucher_no, voucher_type, posting_date, due_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {dr_or_cr} > 0 {condition} and ((voucher_type = 'Journal Entry' and (against_voucher = '' or against_voucher is null)) or (voucher_type not in ('Journal Entry', 'Payment Entry'))) group by voucher_type, voucher_no order by posting_date, name""".format(dr_or_cr=dr_or_cr, condition=condition or ""), { "party_type": party_type, "party": party, "account": account, }, as_dict=True) payment_entries = frappe.db.sql(""" select against_voucher_type, against_voucher, ifnull(sum({payment_dr_or_cr}), 0) as payment_amount from `tabGL Entry` where party_type = %(party_type)s and party = %(party)s and account = %(account)s and {payment_dr_or_cr} > 0 and against_voucher is not null and against_voucher != '' group by against_voucher_type, against_voucher """.format(payment_dr_or_cr=payment_dr_or_cr), { "party_type": party_type, "party": party, "account": account }, as_dict=True) pe_map = frappe._dict() for d in payment_entries: pe_map.setdefault((d.against_voucher_type, d.against_voucher), d.payment_amount) for d in invoice_list: payment_amount = pe_map.get((d.voucher_type, d.voucher_no), 0) outstanding_amount = flt(d.invoice_amount - payment_amount, precision) if outstanding_amount > 0.5 / (10**precision): if (filters and filters.get("outstanding_amt_greater_than") and not (outstanding_amount >= filters.get("outstanding_amt_greater_than") and outstanding_amount <= filters.get("outstanding_amt_less_than"))): continue if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices: outstanding_invoices.append( frappe._dict({ 'voucher_no': d.voucher_no, 'voucher_type': d.voucher_type, 'posting_date': d.posting_date, 'invoice_amount': flt(d.invoice_amount), 'payment_amount': payment_amount, 'outstanding_amount': outstanding_amount, 'due_date': d.due_date })) outstanding_invoices = sorted( outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate())) return outstanding_invoices
def get_count_on(account, fieldname, date): cond = [] if date: cond.append("posting_date <= %s" % frappe.db.escape(cstr(date))) else: # get balance of all entries that exist date = nowdate() try: year_start_date = get_fiscal_year(date, verbose=0)[1] except FiscalYearError: 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 if account: acc = frappe.get_doc("Account", account) if not frappe.flags.ignore_account_permission: acc.check_permission("read") # for pl accounts, get balance within a fiscal year if acc.report_type == 'Profit and Loss': cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" \ % year_start_date) # different filter for group and ledger - improved performance if acc.is_group: cond.append("""exists ( select name from `tabAccount` ac where ac.name = gle.account and ac.lft >= %s and ac.rgt <= %s )""" % (acc.lft, acc.rgt)) else: cond.append("""gle.account = %s """ % (frappe.db.escape(account, percent=False), )) entries = frappe.db.sql(""" SELECT name, posting_date, account, party_type, party,debit,credit, voucher_type, voucher_no, against_voucher_type, against_voucher FROM `tabGL Entry` gle WHERE {0}""".format(" and ".join(cond)), as_dict=True) count = 0 for gle in entries: if fieldname not in ('invoiced_amount', 'payables'): count += 1 else: dr_or_cr = "debit" if fieldname == "invoiced_amount" else "credit" cr_or_dr = "credit" if fieldname == "invoiced_amount" else "debit" select_fields = "ifnull(sum(credit-debit),0)" \ if fieldname == "invoiced_amount" else "ifnull(sum(debit-credit),0)" if ((not gle.against_voucher) or (gle.against_voucher_type in ["Sales Order", "Purchase Order"]) or (gle.against_voucher == gle.voucher_no and gle.get(dr_or_cr) > 0)): payment_amount = frappe.db.sql( """ SELECT {0} FROM `tabGL Entry` gle WHERE docstatus < 2 and posting_date <= %(date)s and against_voucher = %(voucher_no)s and party = %(party)s and name != %(name)s""".format(select_fields), { "date": date, "voucher_no": gle.voucher_no, "party": gle.party, "name": gle.name })[0][0] outstanding_amount = flt(gle.get(dr_or_cr)) - flt( gle.get(cr_or_dr)) - payment_amount currency_precision = get_currency_precision() or 2 if abs(flt(outstanding_amount) ) > 0.1 / 10**currency_precision: count += 1 return count
def get_balance_on(account=None, date=None, party_type=None, party=None, company=None, in_account_currency=True, cost_center=None, ignore_account_permission=False): if not account and frappe.form_dict.get("account"): account = frappe.form_dict.get("account") if not date and frappe.form_dict.get("date"): date = frappe.form_dict.get("date") if not party_type and frappe.form_dict.get("party_type"): party_type = frappe.form_dict.get("party_type") if not party and frappe.form_dict.get("party"): party = frappe.form_dict.get("party") if not cost_center and frappe.form_dict.get("cost_center"): cost_center = frappe.form_dict.get("cost_center") cond = [] if date: cond.append("posting_date <= %s" % frappe.db.escape(cstr(date))) else: # get balance of all entries that exist date = nowdate() if account: acc = frappe.get_doc("Account", account) try: year_start_date = get_fiscal_year(date, verbose=0)[1] except FiscalYearError: 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 allow_cost_center_in_entry_of_bs_account = get_allow_cost_center_in_entry_of_bs_account( ) if account: report_type = acc.report_type else: report_type = "" if cost_center and (allow_cost_center_in_entry_of_bs_account or report_type == 'Profit and Loss'): cc = frappe.get_doc("Cost Center", cost_center) if cc.is_group: cond.append(""" exists ( select 1 from `tabCost Center` cc where cc.name = gle.cost_center and cc.lft >= %s and cc.rgt <= %s )""" % (cc.lft, cc.rgt)) else: cond.append("""gle.cost_center = %s """ % (frappe.db.escape(cost_center, percent=False), )) if account: if not (frappe.flags.ignore_account_permission or ignore_account_permission): acc.check_permission("read") if report_type == 'Profit and Loss': # for pl accounts, get balance within a fiscal year cond.append("posting_date >= '%s' and voucher_type != 'Period Closing Voucher'" \ % year_start_date) # different filter for group and ledger - improved performance if acc.is_group: cond.append("""exists ( select name from `tabAccount` ac where ac.name = gle.account and ac.lft >= %s and ac.rgt <= %s )""" % (acc.lft, acc.rgt)) # If group and currency same as company, # always return balance based on debit and credit in company currency if acc.account_currency == frappe.get_cached_value( 'Company', acc.company, "default_currency"): in_account_currency = False else: cond.append("""gle.account = %s """ % (frappe.db.escape(account, percent=False), )) if party_type and party: cond.append("""gle.party_type = %s and gle.party = %s """ % (frappe.db.escape(party_type), frappe.db.escape(party, percent=False))) if company: cond.append("""gle.company = %s """ % (frappe.db.escape(company, percent=False))) if account or (party_type and party): if in_account_currency: select_field = "sum(debit_in_account_currency) - sum(credit_in_account_currency)" else: select_field = "sum(debit) - sum(credit)" bal = frappe.db.sql(""" SELECT {0} FROM `tabGL Entry` gle WHERE {1}""".format(select_field, " and ".join(cond)))[0][0] # if bal is None, return 0 return flt(bal)
def get_dashboard_info(party_type, party, loyalty_program=None): current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True) doctype = "Sales Invoice" if party_type == "Customer" else "Purchase Invoice" companies = frappe.get_all(doctype, filters={ 'docstatus': 1, party_type.lower(): party }, distinct=1, fields=['company']) company_wise_info = [] company_wise_grand_total = frappe.get_all( doctype, filters={ 'docstatus': 1, party_type.lower(): party, 'posting_date': ('between', [ current_fiscal_year.year_start_date, current_fiscal_year.year_end_date ]) }, group_by="company", fields=[ "company", "sum(grand_total) as grand_total", "sum(base_grand_total) as base_grand_total" ]) loyalty_point_details = [] if party_type == "Customer": loyalty_point_details = frappe._dict( frappe.get_all( "Loyalty Point Entry", filters={ 'customer': party, 'expiry_date': ('>=', getdate()), }, group_by="company", fields=["company", "sum(loyalty_points) as loyalty_points"], as_list=1)) company_wise_billing_this_year = frappe._dict() for d in company_wise_grand_total: company_wise_billing_this_year.setdefault( d.company, { "grand_total": d.grand_total, "base_grand_total": d.base_grand_total }) company_wise_total_unpaid = frappe._dict( frappe.db.sql( """ select company, sum(debit_in_account_currency) - sum(credit_in_account_currency) from `tabGL Entry` where party_type = %s and party=%s group by company""", (party_type, party))) for d in companies: company_default_currency = frappe.db.get_value("Company", d.company, 'default_currency') party_account_currency = get_party_account_currency( party_type, party, d.company) if party_account_currency == company_default_currency: billing_this_year = flt( company_wise_billing_this_year.get(d.company, {}).get("base_grand_total")) else: billing_this_year = flt( company_wise_billing_this_year.get(d.company, {}).get("grand_total")) total_unpaid = flt(company_wise_total_unpaid.get(d.company)) if loyalty_point_details: loyalty_points = loyalty_point_details.get(d.company) info = {} info["billing_this_year"] = flt( billing_this_year) if billing_this_year else 0 info["currency"] = party_account_currency info["total_unpaid"] = flt(total_unpaid) if total_unpaid else 0 info["company"] = d.company if party_type == "Customer" and loyalty_point_details: info["loyalty_points"] = loyalty_points if party_type == "Supplier": info["total_unpaid"] = -1 * info["total_unpaid"] company_wise_info.append(info) return company_wise_info
def setUp(self): create_loan_accounts() create_loan_type("Personal Loan", 500000, 8.4, is_term_loan=1, mode_of_payment='Cash', payment_account='Payment Account - _TC', loan_account='Loan Account - _TC', interest_income_account='Interest Income Account - _TC', penalty_income_account='Penalty Income Account - _TC') create_loan_type("Stock Loan", 2000000, 13.5, 25, 1, 5, 'Cash', 'Payment Account - _TC', 'Loan Account - _TC', 'Interest Income Account - _TC', 'Penalty Income Account - _TC') create_loan_type("Demand Loan", 2000000, 13.5, 25, 0, 5, 'Cash', 'Payment Account - _TC', 'Loan Account - _TC', 'Interest Income Account - _TC', 'Penalty Income Account - _TC') create_loan_security_type() create_loan_security() create_loan_security_price("Test Security 1", 500, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) create_loan_security_price("Test Security 2", 250, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) self.applicant1 = make_employee("*****@*****.**") if not frappe.db.exists("Customer", "_Test Loan Customer"): frappe.get_doc(get_customer_dict('_Test Loan Customer')).insert(ignore_permissions=True) self.applicant2 = frappe.db.get_value("Customer", {'name': '_Test Loan Customer'}, 'name') create_loan(self.applicant1, "Personal Loan", 280000, "Repay Over Number of Periods", 20)
def execute(filters=None): if not filters: filters = {} conditions = get_conditions(filters) columns = get_column(filters, conditions) data = [] master = get_master(conditions, filters) # details = get_details(conditions,filters) combo_dict = {} total = 0 for i in master: row = {} row["ifw_retailskusuffix"] = i.get("ifw_retailskusuffix") row["item_name"] = i.get("item_name") row["item_code"] = i.get("item_code") row["ifw_duty_rate"] = i.get("ifw_duty_rate") row["ifw_discontinued"] = i.get("ifw_discontinued") row["ifw_product_name_ci"] = i.get("ifw_product_name_ci") row["ifw_item_notes"] = i.get("ifw_item_notes") row["ifw_item_notes2"] = i.get("ifw_item_notes2") row["ifw_po_notes"] = i.get("ifw_po_notes") row["ais_poreorderqty"] = i.get("ais_poreorderqty") row["ais_poreorderlevel"] = i.get("ais_poreorderlevel") row["country_of_origin"] = i.get("country_of_origin") row["customs_tariff_number"] = i.get("customs_tariff_number") row["supplier_sku"] = i.get("supplier_part_no") row["supplier_name"] = i.get("supplier") row["barcode"] = frappe.db.get_value("Item Barcode", {"parent": i.get("item_code")}, "barcode") row["asi_item_class"] = i.get("asi_item_class") row["item_image"] = "<a target=" + str("_blank") + " href = " + str( i.get("image")) + "> " + str(i.get("image")) + " </a>" row["rate"] = get_item_details(i.get("item_code"), "Selling") # row["item_discontinued"] = i.get("disabled") row["date_last_received"] = get_date_last_received( i.get("item_code"), i.get("supplier")) row["item_cost"] = get_item_details(i.get("item_code"), "Buying", i.get("supplier")) row["wh_whs"] = get_qty(i.get("item_code"), "W01-WHS-Active Stock - ICL") or 0 row["wh_dtn"] = get_qty(i.get("item_code"), "R05-DTN-Active Stock - ICL") or 0 row["wh_queen"] = get_qty(i.get("item_code"), "R07-Queen-Active Stock - ICL") or 0 row["wh_amb"] = get_qty(i.get("item_code"), "R06-AMB-Active Stock - ICL") or 0 row["wh_mon"] = get_qty(i.get("item_code"), "R04-Mon-Active Stock - ICL") or 0 row["wh_vic"] = get_qty(i.get("item_code"), "R03-Vic-Active Stock - ICL") or 0 row["wh_edm"] = get_qty(i.get("item_code"), "R02-Edm-Active Stock - ICL") or 0 row["wh_gor"] = get_qty(i.get("item_code"), "R01-Gor-Active Stock - ICL") or 0 row["total_actual_qty"] = 0 if row.get("wh_whs") > 0: row["total_actual_qty"] += row.get("wh_whs") if row.get("wh_dtn") > 0: row["total_actual_qty"] += row.get("wh_dtn") if row.get("wh_queen") > 0: row["total_actual_qty"] += row.get("wh_queen") if row.get("wh_amb") > 0: row["total_actual_qty"] += row.get("wh_amb") if row.get("wh_mon") > 0: row["total_actual_qty"] += row.get("wh_mon") if row.get("wh_vic") > 0: row["total_actual_qty"] += row.get("wh_vic") if row.get("wh_edm") > 0: row["total_actual_qty"] += row.get("wh_edm") if row.get("wh_gor") > 0: row["total_actual_qty"] += row.get("wh_gor") row["material_request"], row['mr_status'] = get_open_material_request( i.get("item_code")) row["tag"] = get_tags(i.get("item_code")) expected_pos = get_purchase_orders(i.get("item_code"), i.get("supplier")) row["expected_pos"] = expected_pos row["po_eta"] = get_last_purchase_orders(i.get("item_code"), i.get("supplier")) ordered_qty = get_open_po_qty(i.get("item_code"), i.get("supplier")) row["ordered_qty"] = ordered_qty or 0.0 row["last_sold_date"], row['olast_sold_date'] = get_date_last_sold( i.get("item_code")) sales_data = get_total_sold(i.get("item_code")) row["previous_year_sale"] = 0 row["total"] = 0 row["last_twelve_months"] = 0 today = getdate(nowdate()) last_year = today.year - 1 current_year = today.year last_month = getdate(str(datetime(today.year - 1, 1, 1))) while last_month <= today: month = last_month.strftime("%B") row[frappe.scrub("sold" + month + str(last_month.year))] = 0 last_month = last_month + relativedelta(months=1) row["sold_last_ten_days"] = 0 row["sold_last_thirty_days"] = 0 row["sold_last_sixty_days"] = 0 for d in sales_data: posting_date = getdate(d.get("posting_date")) qty = d.get("qty") month = posting_date.strftime("%B") if posting_date.year == last_year: row["previous_year_sale"] += qty row[frappe.scrub("sold" + month + str(posting_date.year))] += qty elif posting_date.year == current_year: row["total"] += qty row[frappe.scrub("sold" + month + str(posting_date.year))] += qty # if row.get(frappe.scrub("sold"+month+str(posting_date.year))): # row[frappe.scrub("sold"+month+str(posting_date.year))] += qty # row[frappe.scrub("soldjanuary2021")] += qty last12_month_date = today - relativedelta(years=1) if posting_date >= last12_month_date: row["last_twelve_months"] += qty sold_last_ten_days = today - timedelta(days=10) sold_last_thirty_days = today - timedelta(days=30) sold_last_sixty_days = today - timedelta(days=60) if posting_date >= sold_last_sixty_days: row["sold_last_sixty_days"] += qty if posting_date >= sold_last_thirty_days: row["sold_last_thirty_days"] += qty if posting_date >= sold_last_ten_days: row["sold_last_ten_days"] += qty data.append(row) data = sorted(data, key=itemgetter("olast_sold_date"), reverse=True) return columns, data
def get_column(filters, conditions): columns = [ { "label": _("RetailSkuSuffix"), "fieldname": "ifw_retailskusuffix", "fieldtype": "Data", "width": 150, }, { "label": "ERPNextItemCode", "options": "Item", "fieldname": "item_code", "fieldtype": "Link", "width": 150, "align": "left", }, { "label": _("Barcode"), "fieldname": "barcode", "fieldtype": "Data", "width": 150, "align": "left", }, { "label": _("ItemClass"), "fieldname": "asi_item_class", "fieldtype": "Data", "width": 150, "align": "left", }, { "label": _("ItemName"), "fieldname": "item_name", "fieldtype": "Data", "width": 300, }, { "label": _("ItemImage"), "fieldname": "item_image", "fieldtype": "Data", "width": 200, }, { "label": _("Duty rate"), "fieldname": "ifw_duty_rate", "fieldtype": "Int", "width": 100, }, { "label": _("Discontinued"), "fieldname": "ifw_discontinued", "fieldtype": "Check", "width": 100, }, { "label": _("ProductNameCI"), "fieldname": "ifw_product_name_ci", "fieldtype": "Data", "width": 100, }, { "label": _("Item Notes"), "fieldname": "ifw_item_notes", "fieldtype": "Data", "width": 100, }, { "label": _("Item Notes2"), "fieldname": "ifw_item_notes2", "fieldtype": "Data", "width": 100, }, { "label": _("PONotes"), "fieldname": "ifw_po_notes", "fieldtype": "Data", "width": 100, }, { "label": _("POReorderLevel"), "fieldname": "ais_poreorderlevel", "fieldtype": "Int", "width": 100, }, { "label": _("POReorderQty"), "fieldname": "ais_poreorderqty", "fieldtype": "Int", "width": 100, }, { "label": _("Country of Origin"), "fieldname": "country_of_origin", "fieldtype": "Link", "options": "Country", "width": 100, }, { "label": _("HS Code"), "fieldname": "customs_tariff_number", "fieldtype": "Link", "options": "Customs Tariff Number", "width": 100, }, { "label": _("Tags"), "fieldname": "tag", "fieldtype": "Data", "width": 100, }, { "label": _("Rate"), "fieldname": "rate", "fieldtype": "Currency", "width": 100, }, # { # "label": _("Discointinued"), # "fieldname": "item_discontinued", # "fieldtype": "Boolean", # "width": 100, # "default": False, # }, # { # "label": _("ETA"), # "fieldname": "eta", # "fieldtype": "Date", # "width": 100, # }, { "label": _("DateLastReceived"), "fieldname": "date_last_received", "fieldtype": "DateTime", "width": 200, }, { "label": _("Cost"), "fieldname": "item_cost", "fieldtype": "Currency", "width": 100, }, { "label": _("Suplier SKU"), "fieldname": "supplier_sku", "fieldtype": "Data", "width": 100, }, { "label": _("Supplier Name"), "fieldname": "supplier_name", "fieldtype": "Link", "options": "Supplier", "width": 200, }, { "label": _("W01-WHS-Active Stock - ICL"), "fieldname": "wh_whs", "fieldtype": "Int", "width": 200, }, { "label": _("R05-DTN-Active Stock - ICL"), "fieldname": "wh_dtn", "fieldtype": "Int", "width": 200, }, { "label": _("R07-Queen-Active Stock - ICL"), "fieldname": "wh_queen", "fieldtype": "Int", "width": 200, }, { "label": _("R06-AMB-Active Stock - ICL"), "fieldname": "wh_amb", "fieldtype": "Int", "width": 200, }, { "label": _("R04-Mon-Active Stock - ICL"), "fieldname": "wh_mon", "fieldtype": "Int", "width": 200, }, { "label": _("R03-Vic-Active Stock - ICL"), "fieldname": "wh_vic", "fieldtype": "Int", "width": 200, }, { "label": _("R02-Edm-Active Stock - ICL"), "fieldname": "wh_edm", "fieldtype": "Int", "width": 200, }, { "label": _("R01-Gor-Active Stock - ICL"), "fieldname": "wh_gor", "fieldtype": "Int", "width": 200, }, { "label": _("TotalQOH"), "fieldname": "total_actual_qty", "fieldtype": "Int", "width": 140, }, { "label": _("Material Request"), "fieldname": "material_request", "fieldtype": "Data", "width": 200, }, { "label": _("Material Request Status"), "fieldname": "mr_status", "fieldtype": "Data", "width": 200, }, { "label": _("Expected PO Nos"), "fieldname": "expected_pos", "fieldtype": "Data", "width": 240, }, { "label": _("ETA date PO"), "fieldname": "po_eta", "fieldtype": "Data", "width": 200, }, { "label": _("OrderedQty"), "fieldname": "ordered_qty", "fieldtype": "Int", "width": 120, }, { "label": _("PreviousYSale"), "fieldname": "previous_year_sale", "fieldtype": "Int", "width": 140, }, { "label": _("CurrentYearSales"), "fieldname": "total", "fieldtype": "Int", "width": 140, }, { "label": _("TotalSold12Months"), "fieldname": "last_twelve_months", "fieldtype": "Int", "width": 140, } ] today = getdate(nowdate()) last_month = getdate(str(datetime(today.year - 1, today.month, 1))) while last_month <= today: month = last_month.strftime("%B") columns.append({ "label": _(str(last_month.year) + "_Sold" + month), "fieldname": frappe.scrub("sold" + month + str(last_month.year)), "fieldtype": "Int", "width": 140, }) last_month = last_month + relativedelta(months=1) columns.extend([{ "label": _("SoldLast10Days"), "fieldname": "sold_last_ten_days", "fieldtype": "Int", "width": 140, }, { "label": _("SoldLast30Days"), "fieldname": "sold_last_thirty_days", "fieldtype": "Int", "width": 140, }, { "label": _("SoldLast60Days"), "fieldname": "sold_last_sixty_days", "fieldtype": "Int", "width": 140, "default": False, }, { "label": _("DateLastSold"), "fieldname": "last_sold_date", "fieldtype": "Data", "width": 100, }]) return columns
def create_material_request(material_requests): """ Create indent on reaching reorder level """ mr_list = [] defaults = frappe.defaults.get_defaults() exceptions_list = [] def _log_exception(): if frappe.local.message_log: exceptions_list.extend(frappe.local.message_log) frappe.local.message_log = [] else: exceptions_list.append(frappe.get_traceback()) try: current_fiscal_year = get_fiscal_year( nowdate())[0] or defaults.fiscal_year except FiscalYearError: _log_exception() notify_errors(exceptions_list) return 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 = frappe.new_doc("Material Request") mr.update({ "company": company, "fiscal_year": current_fiscal_year, "transaction_date": nowdate(), "material_request_type": request_type }) for d in items: d = frappe._dict(d) item = frappe.get_doc("Item", d.item_code) mr.append( "indent_details", { "doctype": "Material Request Item", "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.insert() mr.submit() mr_list.append(mr) except: _log_exception() if mr_list: if getattr(frappe.local, "reorder_email_notify", None) is None: frappe.local.reorder_email_notify = cint( frappe.db.get_value('Stock Settings', None, 'reorder_email_notify')) if (frappe.local.reorder_email_notify): send_email_notification(mr_list) if exceptions_list: notify_errors(exceptions_list)
def add(args=None): """add in someone's to do list args = { "assign_to": , "doctype": , "name": , "description": } """ if not args: args = frappe.local.form_dict if frappe.db.sql( """select owner from `tabToDo` where reference_type=%(doctype)s and reference_name=%(name)s and status="Open" and owner=%(assign_to)s""", args): frappe.throw(_("Already in user's To Do list"), DuplicateToDoError) else: from frappe.utils import nowdate # if args.get("re_assign"): # remove_from_todo_if_already_assigned(args['doctype'], args['name']) if not args.get('description'): args['description'] = _('Assignment') d = frappe.get_doc({ "doctype": "ToDo", "owner": args['assign_to'], "reference_type": args['doctype'], "reference_name": args['name'], "description": args.get('description'), "priority": args.get("priority", "Medium"), "status": "Open", "date": args.get('date', nowdate()), "assigned_by": args.get('assigned_by', frappe.session.user), }).insert(ignore_permissions=True) # set assigned_to if field exists if frappe.get_meta(args['doctype']).get_field("assigned_to"): frappe.db.set_value(args['doctype'], args['name'], "assigned_to", args['assign_to']) doc = frappe.get_doc(args['doctype'], args['name']) # if assignee does not have permissions, share if not frappe.has_permission(doc=doc, user=args['assign_to']): frappe.share.add(doc.doctype, doc.name, args['assign_to']) frappe.msgprint(_('Shared with user {0} with read access').format( args['assign_to']), alert=True) # notify notify_assignment(d.assigned_by, d.owner, d.reference_type, d.reference_name, action='ASSIGN',\ description=args.get("description"), notify=args.get('notify')) return get(args)
def get_todays_events(): """Returns a count of todays events in calendar""" from frappe.desk.doctype.event.event import get_events from frappe.utils import nowdate today = nowdate() return len(get_events(today, today))
def test_payment_days_based_on_attendance(self): from erpnext.hr.doctype.attendance.attendance import mark_attendance no_of_days = self.get_no_of_days() # Payroll based on attendance frappe.db.set_value("Payroll Settings", None, "payroll_based_on", "Attendance") frappe.db.set_value("Payroll Settings", None, "daily_wages_fraction_for_half_day", 0.75) emp_id = make_employee( "*****@*****.**") frappe.db.set_value("Employee", emp_id, { "relieving_date": None, "status": "Active" }) frappe.db.set_value("Leave Type", "Leave Without Pay", "include_holiday", 0) month_start_date = get_first_day(nowdate()) month_end_date = get_last_day(nowdate()) first_sunday = frappe.db.sql( """ select holiday_date from `tabHoliday` where parent = 'Salary Slip Test Holiday List' and holiday_date between %s and %s order by holiday_date """, (month_start_date, month_end_date))[0][0] mark_attendance(emp_id, first_sunday, 'Absent', ignore_validate=True) # invalid lwp mark_attendance(emp_id, add_days(first_sunday, 1), 'Absent', ignore_validate=True) # counted as absent mark_attendance(emp_id, add_days(first_sunday, 2), 'Half Day', leave_type='Leave Without Pay', ignore_validate=True) # valid 0.75 lwp mark_attendance(emp_id, add_days(first_sunday, 3), 'On Leave', leave_type='Leave Without Pay', ignore_validate=True) # valid lwp mark_attendance(emp_id, add_days(first_sunday, 4), 'On Leave', leave_type='Casual Leave', ignore_validate=True) # invalid lwp mark_attendance(emp_id, add_days(first_sunday, 7), 'On Leave', leave_type='Leave Without Pay', ignore_validate=True) # invalid lwp ss = make_employee_salary_slip( "*****@*****.**", "Monthly", "Test Payment Based On Attendence") self.assertEqual(ss.leave_without_pay, 1.25) self.assertEqual(ss.absent_days, 1) days_in_month = no_of_days[0] no_of_holidays = no_of_days[1] self.assertEqual(ss.payment_days, days_in_month - no_of_holidays - 2.25) #Gross pay calculation based on attendances gross_pay = 78000 - ((78000 / (days_in_month - no_of_holidays)) * flt(ss.leave_without_pay + ss.absent_days)) self.assertEqual(ss.gross_pay, gross_pay) frappe.db.set_value("Payroll Settings", None, "payroll_based_on", "Leave")
def setUp(self): create_loan_accounts() create_loan_type("Demand Loan", 2000000, 13.5, 25, 0, 5, 'Cash', 'Payment Account - _TC', 'Loan Account - _TC', 'Interest Income Account - _TC', 'Penalty Income Account - _TC') create_loan_security_type() create_loan_security() create_loan_security_price("Test Security 1", 500, "Nos", get_datetime() , get_datetime(add_to_date(nowdate(), hours=24))) if not frappe.db.exists("Customer", "_Test Loan Customer"): frappe.get_doc(get_customer_dict('_Test Loan Customer')).insert(ignore_permissions=True) self.applicant = frappe.db.get_value("Customer", {'name': '_Test Loan Customer'}, 'name')
def test_payment_days_based_on_leave_application(self): no_of_days = self.get_no_of_days() # Payroll based on attendance frappe.db.set_value("Payroll Settings", None, "payroll_based_on", "Leave") emp_id = make_employee( "*****@*****.**") frappe.db.set_value("Employee", emp_id, { "relieving_date": None, "status": "Active" }) frappe.db.set_value("Leave Type", "Leave Without Pay", "include_holiday", 0) month_start_date = get_first_day(nowdate()) month_end_date = get_last_day(nowdate()) first_sunday = frappe.db.sql( """ select holiday_date from `tabHoliday` where parent = 'Salary Slip Test Holiday List' and holiday_date between %s and %s order by holiday_date """, (month_start_date, month_end_date))[0][0] make_leave_application(emp_id, first_sunday, add_days(first_sunday, 3), "Leave Without Pay") leave_type_ppl = create_leave_type( leave_type_name="Test Partially Paid Leave", is_ppl=1) leave_type_ppl.save() alloc = create_leave_allocation(employee=emp_id, from_date=add_days(first_sunday, 4), to_date=add_days(first_sunday, 10), new_leaves_allocated=3, leave_type="Test Partially Paid Leave") alloc.save() alloc.submit() #two day leave ppl with fraction_of_daily_salary_per_leave = 0.5 equivalent to single day lwp make_leave_application(emp_id, add_days(first_sunday, 4), add_days(first_sunday, 5), "Test Partially Paid Leave") ss = make_employee_salary_slip( "*****@*****.**", "Monthly", "Test Payment Based On Leave Application") self.assertEqual(ss.leave_without_pay, 4) days_in_month = no_of_days[0] no_of_holidays = no_of_days[1] self.assertEqual(ss.payment_days, days_in_month - no_of_holidays - 4) frappe.db.set_value("Payroll Settings", None, "payroll_based_on", "Leave")
def return_drug_item(self): dn_names = get_unique_delivery_notes(self) if not dn_names: for item in self.drug_items: if item.child_name: update_drug_prescription(item, item.child_name) for dn in dn_names: source_doc = frappe.get_doc("Delivery Note", dn.delivery_note_no) target_doc = frappe.new_doc("Delivery Note") target_doc.customer = source_doc.customer if source_doc.medical_department: target_doc.medical_department = source_doc.medical_department target_doc.healthcare_service_unit = source_doc.healthcare_service_unit target_doc.patient = source_doc.patient target_doc.patient_name = source_doc.patient_name if source_doc.coverage_plan_name: target_doc.coverage_plan_name = source_doc.coverage_plan_name target_doc.company = source_doc.company target_doc.posting_date = nowdate() target_doc.posting_time = nowtime() if source_doc.form_sales_invoice: target_doc.form_sales_invoice = source_doc.form_sales_invoice target_doc.is_return = 1 target_doc.return_against = source_doc.name target_doc.reference_doctype = "LRPMT Returns" #source_doc.reference_doctype target_doc.reference_name = self.name #source_doc.reference_name target_doc.currency = source_doc.currency target_doc.conversion_rate = source_doc.conversion_rate target_doc.selling_price_list = source_doc.selling_price_list target_doc.price_list_currency = source_doc.price_list_currency target_doc.plc_conversion_rate = source_doc.plc_conversion_rate target_doc.ignore_pricing_rule = 1 if source_doc.healthcare_practitioner: target_doc.healthcare_practitioner = source_doc.healthcare_practitioner for item in self.drug_items: if item.child_name: update_drug_prescription(item, item.child_name) if ( not item.dn_detail or not item.delivery_note_no or not item.status or item.status == "Draft" ): continue if dn.delivery_note_no == item.delivery_note_no: for dni in source_doc.items: if ((item.dn_detail == dni.name) and (item.drug_name == dni.item_code)): target_doc.append("items", { "item_code": item.drug_name, "item_name": item.drug_name, "description": dni.description, "qty": -1 * flt(item.quantity_to_return or 0), "stock_uom": dni.stock_uom, "uom": dni.uom, "rate": dni.rate, "conversion_factor": dni.conversion_factor, "warehouse": dni.warehouse, "target_warehouse": dni.target_warehouse or "", "dn_detail": dni.name, "healthcare_service_unit": dni.healthcare_service_unit or "", "healthcare_practitioner": dni.healthcare_practitioner or "", "department": dni.department, "cost_center": dni.cost_center, "reference_doctype": dni.reference_doctype, "reference_name": dni.reference_name }) target_doc.save(ignore_permissions=True) target_doc.submit() return self.name
def test_repack_with_additional_costs(self): company = frappe.db.get_value('Warehouse', 'Stores - TCP1', 'company') make_stock_entry(item_code="_Test Item", target="Stores - TCP1", company=company, qty=50, basic_rate=100, expense_account="Stock Adjustment - TCP1") repack = make_stock_entry(company=company, purpose="Repack", do_not_save=True) repack.posting_date = nowdate() repack.posting_time = nowtime() expenses_included_in_valuation = frappe.get_value( "Company", company, "expenses_included_in_valuation") items = get_multiple_items() repack.items = [] for item in items: repack.append("items", item) repack.set("additional_costs", [ { "expense_account": expenses_included_in_valuation, "description": "Actual Operating Cost", "amount": 1000 }, { "expense_account": expenses_included_in_valuation, "description": "Additional Operating Cost", "amount": 200 }, ]) repack.set_stock_entry_type() repack.insert() repack.submit() stock_in_hand_account = get_inventory_account( repack.company, repack.get("items")[1].t_warehouse) rm_stock_value_diff = abs( frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": "Stock Entry", "voucher_no": repack.name, "item_code": "_Test Item" }, "stock_value_difference")) fg_stock_value_diff = abs( frappe.db.get_value( "Stock Ledger Entry", { "voucher_type": "Stock Entry", "voucher_no": repack.name, "item_code": "_Test Item Home Desktop 100" }, "stock_value_difference")) stock_value_diff = flt(fg_stock_value_diff - rm_stock_value_diff, 2) self.assertEqual(stock_value_diff, 1200) self.check_gl_entries( "Stock Entry", repack.name, sorted([[stock_in_hand_account, 1200, 0.0], ["Expenses Included In Valuation - TCP1", 0.0, 1200.0]]))
def __init__(self, filters=None): self.filters = frappe._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 on_recurring(self, reference_doc, subscription_doc): self.reference_no = reference_doc.name self.reference_date = nowdate()
def validate_release_date(self): if self.release_date and getdate(nowdate()) >= getdate(self.release_date): frappe.msgprint('Release date must be in the future', raise_exception=True)
def calculate_depreciation_date(): return get_last_day(nowdate())
def on_recurring(self, reference_doc, auto_repeat_doc): self.reference_no = reference_doc.name self.reference_date = nowdate()
def invoice_is_blocked(self): return self.on_hold and (not self.release_date or self.release_date > getdate(nowdate()))
def test_gle_with_cwip_toggling(self): # TEST: purchase an asset with cwip enabled and then disable cwip and try submitting the asset frappe.db.set_value("Asset Category", "Computers", "enable_cwip_accounting", 1) pr = make_purchase_receipt(item_code="Macbook Pro", qty=1, rate=5000, do_not_submit=True, location="Test Location") pr.set('taxes', [{ 'category': 'Total', 'add_deduct_tax': 'Add', 'charge_type': 'On Net Total', 'account_head': '_Test Account Service Tax - _TC', 'description': '_Test Account Service Tax', 'cost_center': 'Main - _TC', 'rate': 5.0 }, { 'category': 'Valuation and Total', 'add_deduct_tax': 'Add', 'charge_type': 'On Net Total', 'account_head': '_Test Account Shipping Charges - _TC', 'description': '_Test Account Shipping Charges', 'cost_center': 'Main - _TC', 'rate': 5.0 }]) pr.submit() expected_gle = (("Asset Received But Not Billed - _TC", 0.0, 5250.0), ("CWIP Account - _TC", 5250.0, 0.0)) pr_gle = frappe.db.sql( """select account, debit, credit from `tabGL Entry` where voucher_type='Purchase Receipt' and voucher_no = %s order by account""", pr.name) self.assertEqual(pr_gle, expected_gle) pi = make_invoice(pr.name) pi.submit() expected_gle = ( ("_Test Account Service Tax - _TC", 250.0, 0.0), ("_Test Account Shipping Charges - _TC", 250.0, 0.0), ("Asset Received But Not Billed - _TC", 5250.0, 0.0), ("Creditors - _TC", 0.0, 5500.0), ("Expenses Included In Asset Valuation - _TC", 0.0, 250.0), ) pi_gle = frappe.db.sql( """select account, debit, credit from `tabGL Entry` where voucher_type='Purchase Invoice' and voucher_no = %s order by account""", pi.name) self.assertEqual(pi_gle, expected_gle) asset = frappe.db.get_value('Asset', { 'purchase_receipt': pr.name, 'docstatus': 0 }, 'name') asset_doc = frappe.get_doc('Asset', asset) month_end_date = get_last_day(nowdate()) asset_doc.available_for_use_date = nowdate( ) if nowdate() != month_end_date else add_days(nowdate(), -15) self.assertEqual(asset_doc.gross_purchase_amount, 5250.0) asset_doc.append( "finance_books", { "expected_value_after_useful_life": 200, "depreciation_method": "Straight Line", "total_number_of_depreciations": 3, "frequency_of_depreciation": 10, "depreciation_start_date": month_end_date }) # disable cwip and try submitting frappe.db.set_value("Asset Category", "Computers", "enable_cwip_accounting", 0) asset_doc.submit() # asset should have gl entries even if cwip is disabled expected_gle = (("_Test Fixed Asset - _TC", 5250.0, 0.0), ("CWIP Account - _TC", 0.0, 5250.0)) gle = frappe.db.sql( """select account, debit, credit from `tabGL Entry` where voucher_type='Asset' and voucher_no = %s order by account""", asset_doc.name) self.assertEqual(gle, expected_gle) frappe.db.set_value("Asset Category", "Computers", "enable_cwip_accounting", 1)
def get_payment_entry(dt, dn, party_amount=None, bank_account=None, bank_amount=None): doc = frappe.get_doc(dt, dn) if dt in ("Sales Order", "Purchase Order") and flt(doc.per_billed, 2) > 0: frappe.throw( _("Can only make payment against unbilled {0}").format(dt)) if dt in ("Sales Invoice", "Sales Order"): party_type = "Customer" elif dt in ("Purchase Invoice", "Purchase Order"): party_type = "Supplier" elif dt in ("Expense Claim", "Employee Advance"): party_type = "Employee" elif dt in ("Fees"): party_type = "Student" # party account if dt == "Sales Invoice": party_account = doc.debit_to elif dt == "Purchase Invoice": party_account = doc.credit_to elif dt == "Fees": party_account = doc.receivable_account elif dt == "Employee Advance": party_account = doc.advance_account elif dt == "Expense Claim": party_account = doc.payable_account else: party_account = get_party_account(party_type, doc.get(party_type.lower()), doc.company) party_account_currency = doc.get( "party_account_currency") or get_account_currency(party_account) # payment type if (dt == "Sales Order" or (dt in ("Sales Invoice", "Fees") and doc.outstanding_amount > 0)) \ or (dt=="Purchase Invoice" and doc.outstanding_amount < 0): payment_type = "Receive" else: payment_type = "Pay" # amounts grand_total = outstanding_amount = 0 if party_amount: grand_total = outstanding_amount = party_amount elif dt in ("Sales Invoice", "Purchase Invoice"): if party_account_currency == doc.company_currency: grand_total = doc.base_rounded_total or doc.base_grand_total else: grand_total = doc.rounded_total or doc.grand_total outstanding_amount = doc.outstanding_amount elif dt in ("Expense Claim"): grand_total = doc.total_sanctioned_amount outstanding_amount = doc.total_sanctioned_amount \ - doc.total_amount_reimbursed - flt(doc.total_advance_amount) elif dt == "Employee Advance": grand_total = doc.advance_amount outstanding_amount = flt(doc.advance_amount) - flt(doc.paid_amount) elif dt == "Fees": grand_total = doc.grand_total outstanding_amount = doc.outstanding_amount else: if party_account_currency == doc.company_currency: grand_total = flt( doc.get("base_rounded_total") or doc.base_grand_total) else: grand_total = flt(doc.get("rounded_total") or doc.grand_total) outstanding_amount = grand_total - flt(doc.advance_paid) # bank or cash bank = get_default_bank_cash_account( doc.company, "Bank", mode_of_payment=doc.get("mode_of_payment"), account=bank_account) paid_amount = received_amount = 0 if party_account_currency == bank.account_currency: paid_amount = received_amount = abs(outstanding_amount) elif payment_type == "Receive": paid_amount = abs(outstanding_amount) if bank_amount: received_amount = bank_amount else: received_amount = abs(outstanding_amount) if bank_amount: paid_amount = bank_amount pe = frappe.new_doc("Payment Entry") pe.payment_type = payment_type pe.company = doc.company pe.cost_center = doc.get("cost_center") pe.posting_date = nowdate() pe.mode_of_payment = doc.get("mode_of_payment") pe.party_type = party_type pe.party = doc.get(scrub(party_type)) pe.contact_person = doc.get("contact_person") pe.contact_email = doc.get("contact_email") pe.ensure_supplier_is_not_blocked() pe.paid_from = party_account if payment_type == "Receive" else bank.account pe.paid_to = party_account if payment_type == "Pay" else bank.account pe.paid_from_account_currency = party_account_currency \ if payment_type=="Receive" else bank.account_currency pe.paid_to_account_currency = party_account_currency if payment_type == "Pay" else bank.account_currency pe.paid_amount = paid_amount pe.received_amount = received_amount pe.allocate_payment_amount = 1 pe.letter_head = doc.get("letter_head") if pe.party_type in ["Customer", "Supplier"]: bank_account = get_party_bank_account(pe.party_type, pe.party) pe.set("bank_account", bank_account) pe.set_bank_account_data() # only Purchase Invoice can be blocked individually if doc.doctype == "Purchase Invoice" and doc.invoice_is_blocked(): frappe.msgprint( _('{0} is on hold till {1}'.format(doc.name, doc.release_date))) else: pe.append( "references", { 'reference_doctype': dt, 'reference_name': dn, "bill_no": doc.get("bill_no"), "due_date": doc.get("due_date"), 'total_amount': grand_total, 'outstanding_amount': outstanding_amount, 'allocated_amount': outstanding_amount }) pe.setup_party_account_field() pe.set_missing_values() if party_account and bank: pe.set_exchange_rate() pe.set_amounts() return pe
def make_history_of_assignment_item(sal, date, doc_type, pr_name, qty): sal_child = sal.append('document_stock_assignment', {}) sal_child.created_date = nowdate() sal_child.document_type = doc_type sal_child.document_no = pr_name sal_child.qty = qty