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
