def validate_filters(filters):
''' Validate if dates are properly set and lie in the same fiscal year'''
if filters.from_date > filters.to_date:
frappe.throw(_("From Date must be before To Date"))
from_year = get_fiscal_year(filters.from_date)[0]
to_year = get_fiscal_year(filters.to_date)[0]
if from_year != to_year:
frappe.throw(_("From Date and To Date lie in different Fiscal Year"))
filters["fiscal_year"] = from_year
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_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 make_new_invoice(ref_wrapper, posting_date):
from erpnext.accounts.utils import get_fiscal_year
new_invoice = frappe.copy_doc(ref_wrapper)
mcount = month_map[ref_wrapper.recurring_type]
invoice_period_from_date = get_next_date(ref_wrapper.invoice_period_from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(ref_wrapper.invoice_period_from_date)) == \
cstr(ref_wrapper.invoice_period_from_date)) and \
(cstr(get_last_day(ref_wrapper.invoice_period_to_date)) == \
cstr(ref_wrapper.invoice_period_to_date)):
invoice_period_to_date = get_last_day(get_next_date(ref_wrapper.invoice_period_to_date,
mcount))
else:
invoice_period_to_date = get_next_date(ref_wrapper.invoice_period_to_date, mcount)
new_invoice.update({
"posting_date": posting_date,
"aging_date": posting_date,
"due_date": add_days(posting_date, cint(date_diff(ref_wrapper.due_date,
ref_wrapper.posting_date))),
"invoice_period_from_date": invoice_period_from_date,
"invoice_period_to_date": invoice_period_to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.owner,
})
new_invoice.submit()
return new_invoice
def get_period_date_ranges(self):
from dateutil.relativedelta import relativedelta, MO
from_date, to_date = getdate(self.filters.from_date), getdate(self.filters.to_date)
increment = {
"Monthly": 1,
"Quarterly": 3,
"Half-Yearly": 6,
"Yearly": 12
}.get(self.filters.range, 1)
if self.filters.range in ['Monthly', 'Quarterly']:
from_date = from_date.replace(day = 1)
elif self.filters.range == "Yearly":
from_date = get_fiscal_year(from_date)[1]
else:
from_date = from_date + relativedelta(from_date, weekday=MO(-1))
self.periodic_daterange = []
for dummy in range(1, 53):
if self.filters.range == "Weekly":
period_end_date = add_days(from_date, 6)
else:
period_end_date = add_to_date(from_date, months=increment, days=-1)
if period_end_date > to_date:
period_end_date = to_date
self.periodic_daterange.append(period_end_date)
from_date = add_days(period_end_date, 1)
if period_end_date == to_date:
break
def validate_and_set_fiscal_year(self):
if not self.fiscal_year:
self.fiscal_year = get_fiscal_year(self.posting_date, company=self.company)[0]
else:
from erpnext.accounts.utils import validate_fiscal_year
validate_fiscal_year(self.posting_date, self.fiscal_year, self.company,
self.meta.get_label("posting_date"), self)
def set_tax_withholding_category(company):
accounts = []
abbr = frappe.get_value("Company", company, "abbr")
tds_account = frappe.get_value("Account", 'TDS Payable - {0}'.format(abbr), 'name')
if company and tds_account:
accounts = [dict(company=company, account=tds_account)]
fiscal_year = get_fiscal_year(today(), company=company)[0]
docs = get_tds_details(accounts, fiscal_year)
for d in docs:
try:
doc = frappe.get_doc(d)
doc.flags.ignore_permissions = True
doc.flags.ignore_mandatory = True
doc.insert()
except frappe.DuplicateEntryError:
doc = frappe.get_doc("Tax Withholding Category", d.get("name"))
doc.append("accounts", accounts[0])
# if fiscal year don't match with any of the already entered data, append rate row
fy_exist = [k for k in doc.get('rates') if k.get('fiscal_year')==fiscal_year]
if not fy_exist:
doc.append("rates", d.get('rates')[0])
doc.save()
def get_achieved_details(filters, territory, item_groups):
start_date, end_date = get_fiscal_year(fiscal_year = filters["fiscal_year"])[1:]
lft, rgt = frappe.db.get_value("Territory", territory, ["lft", "rgt"])
item_details = frappe.db.sql("""
select
soi.item_code, sum(soi.qty) as qty, sum(soi.base_net_amount) as amount,
MONTHNAME(so.transaction_date) as month_name
from
`tabSales Order Item` soi, `tabSales Order` so
where
soi.parent=so.name and so.docstatus=1
and so.transaction_date>=%s and so.transaction_date<=%s
and exists(select name from `tabTerritory` where lft >=%s and rgt <= %s and name=so.territory)
group by
month_name, item_code
""", (start_date, end_date, lft, rgt), as_dict=1)
item_actual_details = {}
for d in item_details:
item_group = item_groups[d.item_code]
item_actual_details.setdefault(item_group, frappe._dict())\
.setdefault(d.month_name, frappe._dict({
"quantity": 0,
"amount": 0
}))
value_dict = item_actual_details[item_group][d.month_name]
value_dict.quantity += flt(d.qty)
value_dict.amount += flt(d.amount)
return item_actual_details
def make_holiday(holiday_list_name):
if not frappe.db.exists('Holiday List', holiday_list_name):
current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True)
dt = getdate(nowdate())
new_year = dt + relativedelta(month=1, day=1, year=dt.year)
republic_day = dt + relativedelta(month=1, day=26, year=dt.year)
test_holiday = dt + relativedelta(month=2, day=2, year=dt.year)
frappe.get_doc({
'doctype': 'Holiday List',
'from_date': current_fiscal_year.year_start_date,
'to_date': current_fiscal_year.year_end_date,
'holiday_list_name': holiday_list_name,
'holidays': [{
'holiday_date': new_year,
'description': 'New Year'
}, {
'holiday_date': republic_day,
'description': 'Republic Day'
}, {
'holiday_date': test_holiday,
'description': 'Test Holiday'
}]
}).insert()
return holiday_list_name
def get_gl_dict(self, args, account_currency=None):
"""this method populates the common properties of a gl entry record"""
gl_dict = frappe._dict({
'company': self.company,
'posting_date': self.posting_date,
'fiscal_year': get_fiscal_year(self.posting_date, company=self.company)[0],
'voucher_type': self.doctype,
'voucher_no': self.name,
'remarks': self.get("remarks"),
'debit': 0,
'credit': 0,
'debit_in_account_currency': 0,
'credit_in_account_currency': 0,
'is_opening': self.get("is_opening") or "No",
'party_type': None,
'party': None
})
gl_dict.update(args)
if not account_currency:
account_currency = get_account_currency(gl_dict.account)
if self.doctype not in ["Journal Entry", "Period Closing Voucher"]:
self.validate_account_currency(gl_dict.account, account_currency)
set_balance_in_account_currency(gl_dict, account_currency, self.get("conversion_rate"), self.company_currency)
return gl_dict
def get_achieved_details(filters, sales_person, all_sales_persons, target_item_group, item_groups):
start_date, end_date = get_fiscal_year(fiscal_year = filters["fiscal_year"])[1:]
item_details = frappe.db.sql("""
select
sum(soi.stock_qty * (st.allocated_percentage/100)) as qty,
sum(soi.base_net_amount * (st.allocated_percentage/100)) as amount,
st.sales_person, MONTHNAME(so.transaction_date) as month_name
from
`tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
where
soi.parent=so.name and so.docstatus=1 and st.parent=so.name
and so.transaction_date>=%s and so.transaction_date<=%s
and exists(select name from `tabSales Person` where lft >= %s and rgt <= %s and name=st.sales_person)
and exists(select name from `tabItem Group` where lft >= %s and rgt <= %s and name=soi.item_group)
group by
sales_person, month_name
""",
(start_date, end_date, all_sales_persons[sales_person].lft, all_sales_persons[sales_person].rgt,
item_groups[target_item_group].lft, item_groups[target_item_group].rgt), as_dict=1)
actual_details = {}
for d in item_details:
actual_details.setdefault(d.month_name, frappe._dict({
"quantity" : 0,
"amount" : 0
}))
value_dict = actual_details[d.month_name]
value_dict.quantity += flt(d.qty)
value_dict.amount += flt(d.amount)
return actual_details
def validate_supplier_invoice(self):
if self.bill_date:
if getdate(self.bill_date) > getdate(self.posting_date):
frappe.throw(_("Supplier Invoice Date cannot be greater than Posting Date"))
if self.bill_no:
if cint(frappe.db.get_single_value("Accounts Settings", "check_supplier_invoice_uniqueness")):
fiscal_year = get_fiscal_year(self.posting_date, company=self.company, as_dict=True)
pi = frappe.db.sql('''select name from `tabPurchase Invoice`
where
bill_no = %(bill_no)s
and supplier = %(supplier)s
and name != %(name)s
and docstatus < 2
and posting_date between %(year_start_date)s and %(year_end_date)s''', {
"bill_no": self.bill_no,
"supplier": self.supplier,
"name": self.name,
"year_start_date": fiscal_year.year_start_date,
"year_end_date": fiscal_year.year_end_date
})
if pi:
pi = pi[0][0]
frappe.throw(_("Supplier Invoice No exists in Purchase Invoice {0}".format(pi)))
def get_start_end_dates(payroll_frequency, start_date=None, company=None):
'''Returns dict of start and end dates for given payroll frequency based on start_date'''
if payroll_frequency == "Monthly" or payroll_frequency == "Bimonthly" or payroll_frequency == "":
fiscal_year = get_fiscal_year(start_date, company=company)[0]
month = "%02d" % getdate(start_date).month
m = get_month_details(fiscal_year, month)
if payroll_frequency == "Bimonthly":
if getdate(start_date).day <= 15:
start_date = m['month_start_date']
end_date = m['month_mid_end_date']
else:
start_date = m['month_mid_start_date']
end_date = m['month_end_date']
else:
start_date = m['month_start_date']
end_date = m['month_end_date']
if payroll_frequency == "Weekly":
end_date = add_days(start_date, 6)
if payroll_frequency == "Fortnightly":
end_date = add_days(start_date, 13)
if payroll_frequency == "Daily":
end_date = start_date
return frappe._dict({
'start_date': start_date, 'end_date': end_date
})
def make_new_document(ref_wrapper, date_field, posting_date):
from erpnext.accounts.utils import get_fiscal_year
new_document = frappe.copy_doc(ref_wrapper)
mcount = month_map[ref_wrapper.recurring_type]
from_date = get_next_date(ref_wrapper.from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(ref_wrapper.from_date)) == cstr(ref_wrapper.from_date)) and \
(cstr(get_last_day(ref_wrapper.to_date)) == cstr(ref_wrapper.to_date)):
to_date = get_last_day(get_next_date(ref_wrapper.to_date, mcount))
else:
to_date = get_next_date(ref_wrapper.to_date, mcount)
new_document.update({
date_field: posting_date,
"from_date": from_date,
"to_date": to_date,
"fiscal_year": get_fiscal_year(posting_date)[0],
"owner": ref_wrapper.owner,
})
if ref_wrapper.doctype == "Sales Order":
new_document.update({
"delivery_date": get_next_date(ref_wrapper.delivery_date, mcount,
cint(ref_wrapper.repeat_on_day_of_month))
})
new_document.submit()
return new_document
def validate_schedule_date_for_holiday_list(self, schedule_date, sales_person):
from erpnext.accounts.utils import get_fiscal_year
validated = False
fy_details = ""
try:
fy_details = get_fiscal_year(date=schedule_date, verbose=0)
except Exception:
pass
if fy_details and fy_details[0]:
# check holiday list in employee master
holiday_list = frappe.db.sql_list("""select h.holiday_date from `tabEmployee` emp,
`tabSales Person` sp, `tabHoliday` h, `tabHoliday List` hl
where sp.name=%s and emp.name=sp.employee
and hl.name=emp.holiday_list and
h.parent=hl.name and
hl.fiscal_year=%s""", (sales_person, fy_details[0]))
if not holiday_list:
# check global holiday list
holiday_list = frappe.db.sql("""select h.holiday_date from
`tabHoliday` h, `tabHoliday List` hl
where h.parent=hl.name and ifnull(hl.is_default, 0) = 1
and hl.fiscal_year=%s""", fy_details[0])
if not validated and holiday_list:
if schedule_date in holiday_list:
schedule_date = add_days(schedule_date, -1)
else:
validated = True
return schedule_date
def get_dashboard_info(party_type, party):
current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True)
company = frappe.db.get_default("company") or frappe.get_all("Company")[0].name
party_account_currency = get_party_account_currency(party_type, party, company)
company_default_currency = get_default_currency() \
or frappe.db.get_value('Company', company, 'default_currency')
if party_account_currency==company_default_currency:
total_field = "base_grand_total"
else:
total_field = "grand_total"
doctype = "Sales Invoice" if party_type=="Customer" else "Purchase Invoice"
billing_this_year = frappe.db.sql("""
select sum({0})
from `tab{1}`
where {2}=%s and docstatus=1 and posting_date between %s and %s
""".format(total_field, doctype, party_type.lower()),
(party, current_fiscal_year.year_start_date, current_fiscal_year.year_end_date))
total_unpaid = frappe.db.sql("""
select sum(debit_in_account_currency) - sum(credit_in_account_currency)
from `tabGL Entry`
where party_type = %s and party=%s""", (party_type, party))
info = {}
info["billing_this_year"] = flt(billing_this_year[0][0]) if billing_this_year else 0
info["currency"] = party_account_currency
info["total_unpaid"] = flt(total_unpaid[0][0]) if total_unpaid else 0
if party_type == "Supplier":
info["total_unpaid"] = -1 * info["total_unpaid"]
return info
def set_missing_values(self, for_validate=False): for fieldname in ["posting_date", "transaction_date"]: if not self.get(fieldname) and self.meta.get_field(fieldname): self.set(fieldname, today()) if not self.fiscal_year: self.fiscal_year = get_fiscal_year(self.get(fieldname))[0] break
def get_achieved_details(filters, sales_person, item_groups):
start_date, end_date = get_fiscal_year(fiscal_year = filters["fiscal_year"])[1:]
lft, rgt = frappe.get_value("Sales Person", sales_person, ["lft", "rgt"])
item_details = frappe.db.sql("""
select
soi.item_code, sum(soi.qty * (st.allocated_percentage/100)) as qty,
sum(soi.base_net_amount * (st.allocated_percentage/100)) as amount,
st.sales_person, MONTHNAME(so.transaction_date) as month_name
from
`tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
where
soi.parent=so.name and so.docstatus=1 and st.parent=so.name
and so.transaction_date>=%s and so.transaction_date<=%s
and exists(select name from `tabSales Person` where lft >= %s and rgt <= %s and name=st.sales_person)
group by
sales_person, item_code, month_name
""",
(start_date, end_date, lft, rgt), as_dict=1)
item_actual_details = {}
for d in item_details:
item_group = item_groups[d.item_code]
item_actual_details.setdefault(item_group, frappe._dict()).setdefault(d.month_name,\
frappe._dict({
"quantity" : 0,
"amount" : 0
}))
value_dict = item_actual_details[item_group][d.month_name]
value_dict.quantity += flt(d.qty)
value_dict.amount += flt(d.amount)
return item_actual_details
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()
if not fiscal_year: fiscal_year = get_fiscal_year(posting_date)[0]
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 = 'Yes' %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 status = 'Available' 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,
'fiscal_year' : fiscal_year,
'is_cancelled' : 'No',
'batch_no' : '',
'serial_no' : ''
}
sle_doc = frappe.get_doc(sle_dict)
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 get_actual_expense(self, monthly_end_date):
return get_actual_expense({
"account": "_Test Account Cost for Goods Sold - _TC",
"cost_center": "_Test Cost Center - _TC",
"monthly_end_date": monthly_end_date,
"company": "_Test Company",
"fiscal_year": get_fiscal_year(monthly_end_date)[0]
})
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 get_start_date(period, accumulated_values, company):
if not accumulated_values and period.get('from_date'):
return period['from_date']
start_date = period["year_start_date"]
if accumulated_values:
start_date = get_fiscal_year(period.to_date, company=company)[1]
return start_date
def validate_posting_date(self):
from erpnext.accounts.utils import get_fiscal_year
self.year_start_date = get_fiscal_year(self.posting_date, self.fiscal_year)[1]
pce = frappe.db.sql("""select name from `tabPeriod Closing Voucher`
where posting_date > %s and fiscal_year = %s and docstatus = 1""",
(self.posting_date, self.fiscal_year))
if pce and pce[0][0]:
frappe.throw(_("Another Period Closing Entry {0} has been made after {1}").format(pce[0][0], self.posting_date))
def test_closing_entry(self):
year_start_date = get_fiscal_year(today())[1]
make_journal_entry("_Test Bank - _TC", "Sales - _TC", 400,
"_Test Cost Center - _TC", posting_date=now(), submit=True)
make_journal_entry("_Test Account Cost for Goods Sold - _TC",
"_Test Bank - _TC", 600, "_Test Cost Center - _TC", posting_date=now(), submit=True)
random_expense_account = frappe.db.sql("""
select t1.account,
sum(t1.debit) - sum(t1.credit) as balance,
sum(t1.debit_in_account_currency) - sum(t1.credit_in_account_currency) \
as balance_in_account_currency
from `tabGL Entry` t1, `tabAccount` t2
where t1.account = t2.name and t2.root_type = 'Expense'
and t2.docstatus < 2 and t2.company = '_Test Company'
and t1.posting_date between %s and %s
group by t1.account
having sum(t1.debit) > sum(t1.credit)
limit 1""", (year_start_date, today()), as_dict=True)
profit_or_loss = frappe.db.sql("""select sum(t1.debit) - sum(t1.credit) as balance
from `tabGL Entry` t1, `tabAccount` t2
where t1.account = t2.name and t2.report_type = 'Profit and Loss'
and t2.docstatus < 2 and t2.company = '_Test Company'
and t1.posting_date between %s and %s""", (year_start_date, today()))
profit_or_loss = flt(profit_or_loss[0][0]) if profit_or_loss else 0
pcv = self.make_period_closing_voucher()
# Check value for closing account
gle_amount_for_closing_account = frappe.db.sql("""select debit - credit
from `tabGL Entry` where voucher_type='Period Closing Voucher' and voucher_no=%s
and account = '_Test Account Reserves and Surplus - _TC'""", pcv.name)
gle_amount_for_closing_account = flt(gle_amount_for_closing_account[0][0]) \
if gle_amount_for_closing_account else 0
self.assertEqual(gle_amount_for_closing_account, profit_or_loss)
if random_expense_account:
# Check posted value for teh above random_expense_account
gle_for_random_expense_account = frappe.db.sql("""
select debit - credit as amount,
debit_in_account_currency - credit_in_account_currency
as amount_in_account_currency
from `tabGL Entry`
where voucher_type='Period Closing Voucher' and voucher_no=%s and account =%s""",
(pcv.name, random_expense_account[0].account), as_dict=True)
self.assertEqual(gle_for_random_expense_account[0].amount, -1*random_expense_account[0].balance)
self.assertEqual(gle_for_random_expense_account[0].amount_in_account_currency,
-1*random_expense_account[0].balance_in_account_currency)
def validate_expense_against_budget(args):
args = frappe._dict(args)
if args.get('company') and not args.fiscal_year:
args.fiscal_year = get_fiscal_year(args.get('posting_date'), company=args.get('company'))[0]
frappe.flags.exception_approver_role = frappe.get_cached_value('Company',
args.get('company'), 'exception_budget_approver_role')
if not args.account:
args.account = args.get("expense_account")
if not (args.get('account') and args.get('cost_center')) and args.item_code:
args.cost_center, args.account = get_item_details(args)
if not (args.cost_center or args.project) and not args.account:
return
for budget_against in ['project', 'cost_center']:
if (args.get(budget_against) and args.account
and frappe.db.get_value("Account", {"name": args.account, "root_type": "Expense"})):
if args.project and budget_against == 'project':
condition = "and b.project='%s'" % frappe.db.escape(args.project)
args.budget_against_field = "Project"
elif args.cost_center and budget_against == 'cost_center':
cc_lft, cc_rgt = frappe.db.get_value("Cost Center", args.cost_center, ["lft", "rgt"])
condition = """and exists(select name from `tabCost Center`
where lft<=%s and rgt>=%s and name=b.cost_center)""" % (cc_lft, cc_rgt)
args.budget_against_field = "Cost Center"
args.budget_against = args.get(budget_against)
budget_records = frappe.db.sql("""
select
b.{budget_against_field} as budget_against, ba.budget_amount, b.monthly_distribution,
ifnull(b.applicable_on_material_request, 0) as for_material_request,
ifnull(applicable_on_purchase_order,0) as for_purchase_order,
ifnull(applicable_on_booking_actual_expenses,0) as for_actual_expenses,
b.action_if_annual_budget_exceeded, b.action_if_accumulated_monthly_budget_exceeded,
b.action_if_annual_budget_exceeded_on_mr, b.action_if_accumulated_monthly_budget_exceeded_on_mr,
b.action_if_annual_budget_exceeded_on_po, b.action_if_accumulated_monthly_budget_exceeded_on_po
from
`tabBudget` b, `tabBudget Account` ba
where
b.name=ba.parent and b.fiscal_year=%s
and ba.account=%s and b.docstatus=1
{condition}
""".format(condition=condition,
budget_against_field=frappe.scrub(args.get("budget_against_field"))),
(args.fiscal_year, args.account), as_dict=True)
if budget_records:
validate_budget_records(args, budget_records)
def get_period(self, posting_date): if self.filters.range == 'Weekly': period = "Week " + str(posting_date.isocalendar()[1]) + " " + str(posting_date.year) elif self.filters.range == 'Monthly': period = str(self.months[posting_date.month - 1]) + " " + str(posting_date.year) elif self.filters.range == 'Quarterly': period = "Quarter " + str(((posting_date.month-1)//3)+1) +" " + str(posting_date.year) else: year = get_fiscal_year(posting_date, company=self.filters.company) period = str(year[0]) return period
def execute(filters=None):
finyrfrom, finyrto = get_fiscal_year(fiscal_year=filters["fiscal_year"])[1:]
data = get_report_data(
financial_year_from=str(finyrfrom),
financial_year_to=str(finyrto),
company=filters["company"],
expand_levels=filters.expand_levels,
)
columns = get_report_columns(financial_year_from=finyrfrom, financial_year_to=finyrto)
return columns, data
def make_holiday_list(self):
fiscal_year = get_fiscal_year(nowdate(), company="_Test Company")
if not frappe.db.get_value("Holiday List", "Salary Slip Test Holiday List"):
holiday_list = frappe.get_doc({
"doctype": "Holiday List",
"holiday_list_name": "Salary Slip Test Holiday List",
"from_date": fiscal_year[1],
"to_date": fiscal_year[2],
"weekly_off": "Sunday"
}).insert()
holiday_list.get_weekly_off_dates()
holiday_list.save()
def get_written_down_when_selling_fixed_asset(fixed_asset, saledate, company, saleamount):
saleamount = float(saleamount)
saledate=datetime.strptime(saledate, "%Y-%m-%d").date()
from erpnext.accounts.utils import get_fiscal_year
financial_year_from, financial_year_to = get_fiscal_year(saledate)[1:]
depreciation = Depreciation(str(financial_year_from), str(financial_year_to), company, fixed_asset)
value = depreciation.calculate_written_down_on(saledate, saleamount)
if value > 0:
return value
frappe.throw("Either Asset Already Sold OR No Record Found OR Trying to sell an Asset which was bought in the current Fiscal Year")
def make_period_closing_voucher(self):
pcv = frappe.get_doc({
"doctype": "Period Closing Voucher",
"closing_account_head": "_Test Account Reserves and Surplus - _TC",
"company": "_Test Company",
"fiscal_year": get_fiscal_year(today())[0],
"posting_date": today(),
"remarks": "test"
})
pcv.insert()
pcv.submit()
return pcv
def get_achieved_details(filters, sales_person, all_sales_persons,
target_item_group, item_groups):
start_date, end_date = get_fiscal_year(
fiscal_year=filters["fiscal_year"])[1:]
item_details = frappe.db.sql("""
SELECT st.sales_person, MONTHNAME(so.transaction_date) as month_name,
CASE
WHEN so.status = "Closed" THEN sum(soi.delivered_qty * soi.conversion_factor * (st.allocated_percentage/100))
ELSE sum(soi.stock_qty * (st.allocated_percentage/100))
END as qty,
CASE
WHEN so.status = "Closed" THEN sum(soi.delivered_qty * soi.conversion_factor * soi.base_net_rate * (st.allocated_percentage/100))
ELSE sum(soi.base_net_amount * (st.allocated_percentage/100))
END as amount
from
`tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
where
soi.parent=so.name and so.docstatus=1 and st.parent=so.name
and so.transaction_date>=%s and so.transaction_date<=%s
and exists(SELECT name from `tabSales Person` where lft >= %s and rgt <= %s and name=st.sales_person)
and exists(SELECT name from `tabItem Group` where lft >= %s and rgt <= %s and name=soi.item_group)
group by
sales_person, month_name
""", (start_date, end_date, all_sales_persons[sales_person].lft,
all_sales_persons[sales_person].rgt,
item_groups[target_item_group].lft,
item_groups[target_item_group].rgt),
as_dict=1)
actual_details = {}
for d in item_details:
actual_details.setdefault(d.month_name,
frappe._dict({
"quantity": 0,
"amount": 0
}))
value_dict = actual_details[d.month_name]
value_dict.quantity += flt(d.qty)
value_dict.amount += flt(d.amount)
return actual_details
def get_actual_data(filters, item_groups, sales_users_or_territory_data,
date_field, sales_field):
fiscal_year = get_fiscal_year(fiscal_year=filters.get("fiscal_year"),
as_dict=1)
dates = [fiscal_year.year_start_date, fiscal_year.year_end_date]
select_field = "`tab{0}`.{1}".format(filters.get("doctype"), sales_field)
child_table = "`tab{0}`".format(filters.get("doctype") + " Item")
if sales_field == "sales_person":
select_field = "`tabSales Team`.sales_person"
child_table = "`tab{0}`, `tabSales Team`".format(
filters.get("doctype") + " Item")
cond = """`tabSales Team`.parent = `tab{0}`.name and
`tabSales Team`.sales_person in ({1}) """.format(
filters.get("doctype"),
",".join(["%s"] * len(sales_users_or_territory_data)))
else:
cond = "`tab{0}`.{1} in ({2})".format(
filters.get("doctype"), sales_field,
",".join(["%s"] * len(sales_users_or_territory_data)))
return frappe.db.sql(
""" SELECT `tab{child_doc}`.item_group,
`tab{child_doc}`.stock_qty, `tab{child_doc}`.base_net_amount,
{select_field}, `tab{parent_doc}`.{date_field}
FROM `tab{parent_doc}`, {child_table}
WHERE
`tab{child_doc}`.parent = `tab{parent_doc}`.name
and `tab{parent_doc}`.docstatus = 1 and {cond}
and `tab{child_doc}`.item_group in ({item_groups})
and `tab{parent_doc}`.{date_field} between %s and %s""".format(
cond=cond,
date_field=date_field,
select_field=select_field,
child_table=child_table,
parent_doc=filters.get("doctype"),
child_doc=filters.get("doctype") + " Item",
item_groups=",".join(["%s"] * len(item_groups)),
),
tuple(sales_users_or_territory_data + item_groups + dates),
as_dict=1,
)
def test_monthly_budget_crossed_for_po(self):
budget = make_budget(applicable_on_purchase_order=1,
action_if_accumulated_monthly_budget_exceeded_on_po="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)
po = create_purchase_order(
transaction_date=nowdate(), do_not_submit=True)
po.set_missing_values()
self.assertRaises(BudgetError, po.submit)
budget.load_from_db()
budget.cancel()
po.cancel()
def execute():
try:
year_start_date = get_fiscal_year(today())[1]
except:
return
if year_start_date:
items = frappe.db.sql("""select distinct item_code, warehouse from `tabStock Ledger Entry`
where ifnull(serial_no, '') != '' and actual_qty > 0 and incoming_rate=0""", as_dict=1)
for d in items:
try:
update_entries_after({
"item_code": d.item_code,
"warehouse": d.warehouse,
"posting_date": year_start_date
}, allow_zero_rate=True)
except:
pass
def create_stock_reconciliation(**args):
args = frappe._dict(args)
sr = frappe.new_doc("Stock Reconciliation")
sr.posting_date = args.posting_date or nowdate()
sr.posting_time = args.posting_time or nowtime()
sr.company = args.company or "_Test Company"
sr.fiscal_year = get_fiscal_year(sr.posting_date)[0]
sr.expense_account = args.expense_account or \
("Stock Adjustment - _TC" if frappe.get_all("Stock Ledger Entry") else "Temporary Opening - _TC")
sr.cost_center = args.cost_center or "_Test Cost Center - _TC"
sr.append("items", {
"item_code": args.item_code or "_Test Item",
"warehouse": args.warehouse or "_Test Warehouse - _TC",
"qty": args.qty,
"valuation_rate": args.rate
})
sr.submit()
return sr
def make_stock_entry(**args):
from erpnext.accounts.utils import get_fiscal_year
s = frappe.new_doc("Stock Entry")
args = frappe._dict(args)
if args.posting_date:
s.posting_date = args.posting_date
if args.posting_time:
s.posting_time = args.posting_time
if not args.purpose:
if args.source and args.target:
s.purpose = "Material Transfer"
elif args.source:
s.purpose = "Material Issue"
else:
s.purpose = "Material Receipt"
else:
s.purpose = args.purpose
s.company = args.company or "_Test Company"
s.fiscal_year = get_fiscal_year(s.posting_date)[0]
s.purchase_receipt_no = args.purchase_receipt_no
s.delivery_note_no = args.delivery_note_no
s.sales_invoice_no = args.sales_invoice_no
s.difference_account = args.difference_account or "Stock Adjustment - _TC"
s.append("items", {
"item_code": args.item or args.item_code or "_Test Item",
"s_warehouse": args.from_warehouse or args.source,
"t_warehouse": args.to_warehouse or args.target,
"qty": args.qty,
"basic_rate": args.basic_rate,
"expense_account": args.expense_account or "Stock Adjustment - _TC",
"conversion_factor": 1.0,
"cost_center": "_Test Cost Center - _TC"
})
if not args.do_not_save:
s.insert()
if not args.do_not_submit:
s.submit()
return s
def make_period_closing_voucher(self):
pcv = frappe.get_doc({
"doctype":
"Period Closing Voucher",
"closing_account_head":
"_Test Account Reserves and Surplus - _TC",
"company":
"_Test Company",
"fiscal_year":
get_fiscal_year(today(), company="_Test Company")[0],
"posting_date":
today(),
"remarks":
"test"
})
pcv.insert()
pcv.submit()
return pcv
def validate_posting_date(self):
from erpnext.accounts.utils import get_fiscal_year, validate_fiscal_year
validate_fiscal_year(self.posting_date,
self.fiscal_year,
label=_("Posting Date"),
doc=self)
self.year_start_date = get_fiscal_year(self.posting_date,
self.fiscal_year)[1]
pce = frappe.db.sql(
"""select name from `tabPeriod Closing Voucher`
where posting_date > %s and fiscal_year = %s and docstatus = 1""",
(self.posting_date, self.fiscal_year))
if pce and pce[0][0]:
frappe.throw(
_("Another Period Closing Entry {0} has been made after {1}").
format(pce[0][0], self.posting_date))
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."))
actual_lwp = self.calculate_lwp(holidays, working_days)
if not lwp:
lwp = actual_lwp
elif lwp != actual_lwp:
frappe.msgprint(
_("Leave Without Pay does not match with approved Leave Application records"
))
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 get_achieved_details(filters):
start_date, end_date = get_fiscal_year(
fiscal_year=filters["fiscal_year"])[1:]
item_details = frappe.db.sql(
"""select soi.item_code, soi.qty, soi.base_amount, so.transaction_date,
st.sales_person, MONTHNAME(so.transaction_date) as month_name
from `tabSales Order Item` soi, `tabSales Order` so, `tabSales Team` st
where soi.parent=so.name and so.docstatus=1 and
st.parent=so.name and so.transaction_date>=%s and
so.transaction_date<=%s""" % ('%s', '%s'), (start_date, end_date),
as_dict=1)
item_actual_details = {}
for d in item_details:
item_actual_details.setdefault(d.sales_person, {}).setdefault(\
get_item_group(d.item_code), []).append(d)
return item_actual_details
def execute(filters=None):
filters = filters if isinstance(filters,
frappe._dict) else frappe._dict(filters)
if not filters:
filters.setdefault('fiscal_year', get_fiscal_year(nowdate())[0])
filters.setdefault('company', frappe.db.get_default("company"))
region = frappe.db.get_value("Company",
filters={"name": filters.company},
fieldname=["country"])
if region != 'United States':
return [], []
data = []
columns = get_columns()
data = frappe.db.sql("""
SELECT
s.supplier_group as "supplier_group",
gl.party AS "supplier",
s.tax_id as "tax_id",
SUM(gl.debit_in_account_currency) AS "payments"
FROM
`tabGL Entry` gl
INNER JOIN `tabSupplier` s
WHERE
s.name = gl.party
AND s.irs_1099 = 1
AND gl.fiscal_year = %(fiscal_year)s
AND gl.party_type = "Supplier"
GROUP BY
gl.party
ORDER BY
gl.party DESC
""", {
"fiscal_year": filters.fiscal_year,
"supplier_group": filters.supplier_group,
"company": filters.company
},
as_dict=True)
return columns, data
def get_payments(self):
if not self.member:
frappe.throw(_('Please select a Member first.'))
fiscal_year = get_fiscal_year(fiscal_year=self.fiscal_year,
as_dict=True)
memberships = frappe.db.get_all('Membership', {
'member':
self.member,
'from_date': [
'between',
(fiscal_year.year_start_date, fiscal_year.year_end_date)
],
'to_date': [
'between',
(fiscal_year.year_start_date, fiscal_year.year_end_date)
],
'membership_status': ('!=', 'Cancelled')
}, ['from_date', 'amount', 'name', 'invoice', 'payment_id'],
order_by='from_date')
if not memberships:
frappe.msgprint(
_('No Membership Payments found against the Member {0}').
format(self.member))
total = 0
self.payments = []
for doc in memberships:
self.append(
'payments', {
'date': doc.from_date,
'amount': doc.amount,
'invoice_id': doc.invoice,
'razorpay_payment_id': doc.payment_id,
'membership': doc.name
})
total += flt(doc.amount)
self.total = total
def get_gl_dict(self, args, account_currency=None):
"""this method populates the common properties of a gl entry record"""
gl_dict = frappe._dict({
'company':
self.company,
'posting_date':
self.posting_date,
'fiscal_year':
get_fiscal_year(self.posting_date, company=self.company)[0],
'voucher_type':
self.doctype,
'voucher_no':
self.name,
'remarks':
self.get("remarks"),
'debit':
0,
'credit':
0,
'debit_in_account_currency':
0,
'credit_in_account_currency':
0,
'is_opening':
self.get("is_opening") or "No",
'party_type':
None,
'party':
None
})
gl_dict.update(args)
if not account_currency:
account_currency = get_account_currency(gl_dict.account)
if self.doctype not in ["Journal Entry", "Period Closing Voucher"]:
self.validate_account_currency(gl_dict.account, account_currency)
set_balance_in_account_currency(gl_dict, account_currency,
self.get("conversion_rate"),
self.company_currency)
return gl_dict
def get_sl_entries(self, d, args):
company = frappe.db.get_value("Purchase Receipt", self.reference_name,
"company")
sl_dict = frappe._dict({
"item_code":
d.get("item_code", None),
"warehouse":
d.get("warehouse", None),
"posting_date":
self.report_date,
"posting_time":
self.report_time,
'fiscal_year':
get_fiscal_year(self.report_date, company)[0],
"voucher_type":
self.doctype,
"voucher_no":
self.name,
"voucher_detail_no":
d.name,
"actual_qty":
(self.docstatus == 1 and 1 or -1) * flt(d.get("stock_qty")),
"stock_uom":
frappe.db.get_value("Item",
args.get("item_code") or d.get("item_code"),
"stock_uom"),
"incoming_rate":
0,
"company":
company,
"batch_no":
cstr(d.get("batch_no")).strip(),
"serial_no":
d.get("serial_no"),
"project":
d.get("project") or self.get('project'),
"is_cancelled":
1 if self.docstatus == 2 else 0
})
sl_dict.update(args)
return sl_dict
def get_sl_entries(self, d, args):
sl_dict = frappe._dict({
"item_code":
d.get("item_code", None),
"warehouse":
d.get("warehouse", None),
"posting_date":
self.posting_date,
"posting_time":
self.posting_time,
'fiscal_year':
get_fiscal_year(self.posting_date, company=self.company)[0],
"voucher_type":
self.doctype,
"voucher_no":
self.name,
"voucher_detail_no":
d.name,
"actual_qty":
(self.docstatus == 1 and 1 or -1) * flt(d.get("stock_qty")),
"stock_uom":
frappe.db.get_value("Item",
args.get("item_code") or d.get("item_code"),
"stock_uom"),
"incoming_rate":
0,
"company":
self.company,
"batch_no":
cstr(d.get("batch_no")).strip(),
"serial_no":
d.get("serial_no"),
"project":
d.get("project") or self.get('project'),
"is_cancelled":
self.docstatus == 2 and "Yes" or "No",
"package_tag":
cstr(d.get("package_tag"))
})
sl_dict.update(args)
return sl_dict
def get_period(posting_date, filters):
months = [
"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct",
"Nov", "Dec"
]
if filters.range == 'Weekly':
period = "Week " + str(posting_date.isocalendar()[1]) + " " + str(
posting_date.year)
elif filters.range == 'Monthly':
period = str(months[posting_date.month - 1]) + " " + str(
posting_date.year)
elif filters.range == 'Quarterly':
period = "Quarter " + str((
(posting_date.month - 1) // 3) + 1) + " " + str(posting_date.year)
else:
year = get_fiscal_year(posting_date, company=filters.company)
period = str(year[2])
return period
def make_period_closing_voucher(self, submit=True):
surplus_account = create_account()
cost_center = create_cost_center("Test Cost Center 1")
pcv = frappe.get_doc(
{
"doctype": "Period Closing Voucher",
"transaction_date": today(),
"posting_date": today(),
"company": "Test PCV Company",
"fiscal_year": get_fiscal_year(today(), company="Test PCV Company")[0],
"cost_center": cost_center,
"closing_account_head": surplus_account,
"remarks": "test",
}
)
pcv.insert()
if submit:
pcv.submit()
return pcv
def get_period(self, date): months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"] if self.filters.range == "Weekly": period = "Week " + str(date.isocalendar()[1]) elif self.filters.range == "Monthly": period = str(months[date.month - 1]) elif self.filters.range == "Quarterly": period = "Quarter " + str(((date.month - 1) // 3) + 1) else: year = get_fiscal_year(date, self.filters.company) period = str(year[0]) if ( getdate(self.filters.from_date).year != getdate(self.filters.to_date).year and self.filters.range != "Yearly" ): period += " " + str(date.year) return period
def make_new_document(reference_doc, date_field, posting_date):
from erpnext.accounts.utils import get_fiscal_year
new_document = frappe.copy_doc(reference_doc, ignore_no_copy=True)
mcount = month_map[reference_doc.recurring_type]
from_date = get_next_date(reference_doc.from_date, mcount)
# get last day of the month to maintain period if the from date is first day of its own month
# and to date is the last day of its own month
if (cstr(get_first_day(reference_doc.from_date)) == cstr(reference_doc.from_date)) and \
(cstr(get_last_day(reference_doc.to_date)) == cstr(reference_doc.to_date)):
to_date = get_last_day(get_next_date(reference_doc.to_date, mcount))
else:
to_date = get_next_date(reference_doc.to_date, mcount)
new_document.update({
date_field: posting_date,
"from_date": from_date,
"to_date": to_date,
"fiscal_year": get_fiscal_year(posting_date)[0]
})
# copy document fields
for fieldname in ("owner", "recurring_type", "repeat_on_day_of_month",
"recurring_id", "notification_email_address",
"is_recurring", "end_date", "title", "naming_series",
"select_print_heading", "ignore_pricing_rule",
"posting_time", "remarks"):
if new_document.meta.get_field(fieldname):
new_document.set(fieldname, reference_doc.get(fieldname))
# copy item fields
for i, item in enumerate(new_document.items):
for fieldname in ("page_break", ):
item.set(fieldname, reference_doc.items[i].get(fieldname))
new_document.run_method("on_recurring", reference_doc=reference_doc)
new_document.submit()
return new_document
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 set_total_expense_zero(posting_date,
budget_against_field=None,
budget_against_CC=None):
if budget_against_field == "project":
budget_against = frappe.db.get_value("Project",
{"project_name": "_Test Project"})
else:
budget_against = budget_against_CC or "_Test Cost Center - _TC"
fiscal_year = get_fiscal_year(nowdate())[0]
args = frappe._dict({
"account": "_Test Account Cost for Goods Sold - _TC",
"cost_center": "_Test Cost Center - _TC",
"monthly_end_date": posting_date,
"company": "_Test Company",
"fiscal_year": fiscal_year,
"budget_against_field": budget_against_field,
})
if not args.get(budget_against_field):
args[budget_against_field] = budget_against
existing_expense = get_actual_expense(args)
if existing_expense:
if budget_against_field == "cost_center":
make_journal_entry("_Test Account Cost for Goods Sold - _TC",
"_Test Bank - _TC",
-existing_expense,
"_Test Cost Center - _TC",
posting_date=nowdate(),
submit=True)
elif budget_against_field == "project":
make_journal_entry("_Test Account Cost for Goods Sold - _TC",
"_Test Bank - _TC",
-existing_expense,
"_Test Cost Center - _TC",
submit=True,
project=budget_against,
posting_date=nowdate())
def set_tax_withholding_category(company):
accounts = []
fiscal_year = None
abbr = frappe.get_value("Company", company, "abbr")
tds_account = frappe.get_value("Account", 'TDS Payable - {0}'.format(abbr),
'name')
if company and tds_account:
accounts = [dict(company=company, account=tds_account)]
try:
fiscal_year = get_fiscal_year(today(), verbose=0, company=company)[0]
except FiscalYearError:
pass
docs = get_tds_details(accounts, fiscal_year)
for d in docs:
try:
doc = frappe.get_doc(d)
doc.flags.ignore_permissions = True
doc.flags.ignore_mandatory = True
doc.insert()
except frappe.DuplicateEntryError:
doc = frappe.get_doc("Tax Withholding Category", d.get("name"))
if accounts:
doc.append("accounts", accounts[0])
if fiscal_year:
# if fiscal year don't match with any of the already entered data, append rate row
fy_exist = [
k for k in doc.get('rates')
if k.get('fiscal_year') == fiscal_year
]
if not fy_exist:
doc.append("rates", d.get('rates')[0])
doc.flags.ignore_permissions = True
doc.flags.ignore_mandatory = True
doc.save()
def get_period(self, date):
months = [
'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep',
'Oct', 'Nov', 'Dec'
]
if self.filters.range == 'Weekly':
period = 'Week ' + str(date.isocalendar()[1])
elif self.filters.range == 'Monthly':
period = str(months[date.month - 1])
elif self.filters.range == 'Quarterly':
period = 'Quarter ' + str(((date.month - 1) // 3) + 1)
else:
year = get_fiscal_year(date, self.filters.company)
period = str(year[0])
if getdate(self.filters.from_date).year != getdate(
self.filters.to_date).year and self.filters.range != 'Yearly':
period += ' ' + str(date.year)
return period
def test_payroll_frequency(self): fiscal_year = get_fiscal_year(nowdate(), company=erpnext.get_default_company())[0] month = "%02d" % getdate(nowdate()).month m = get_month_details(fiscal_year, month) for payroll_frequency in ["Monthly", "Bimonthly", "Fortnightly", "Weekly", "Daily"]: make_employee(payroll_frequency + "*****@*****.**") ss = make_employee_salary_slip(payroll_frequency + "*****@*****.**", payroll_frequency) if payroll_frequency == "Monthly": self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Bimonthly": if getdate(ss.start_date).day <= 15: self.assertEqual(ss.end_date, m['month_mid_end_date']) else: self.assertEqual(ss.end_date, m['month_end_date']) elif payroll_frequency == "Fortnightly": self.assertEqual(ss.end_date, add_days(nowdate(),13)) elif payroll_frequency == "Weekly": self.assertEqual(ss.end_date, add_days(nowdate(),6)) elif payroll_frequency == "Daily": self.assertEqual(ss.end_date, nowdate())
def autoname(self):
if hasattr(self, 'force_name') and self.force_name:
self.name = self.force_name
return
if self.posting_date < '2017-07-01':
company_abbr = frappe.db.get_value("Company", self.company, "abbr")
b_type = 'V' if self.bill_type == 'VAT' else 'R'
naming_series = '{}#{}#'.format(company_abbr, b_type)
self.name = make_autoname(naming_series + '.#####')
else:
company_abbr = frappe.db.get_value("Company", self.company, "abbr")
fiscal_year = account_utils.get_fiscal_year(
self.get("posting_date"))[0]
naming_series = "{}{}/.".format(company_abbr, fiscal_year[2:])
suffix_count = 16 - len(naming_series) + 1
suffix = '#' * suffix_count
self.name = make_autoname(naming_series + suffix)
def add_data(w, args):
from erpnext.accounts.utils import get_fiscal_year
dates = get_dates(args)
employees = get_active_employees()
existing_attendance_records = get_existing_attendance_records(args)
for date in dates:
for employee in employees:
existing_attendance = {}
if existing_attendance_records \
and tuple([date, employee.name]) in existing_attendance_records:
existing_attendance = existing_attendance_records[tuple([date, employee.name])]
row = [
existing_attendance and existing_attendance.name or "",
employee.name, employee.employee_name, date,
existing_attendance and existing_attendance.status or "",
get_fiscal_year(date)[0], employee.company,
existing_attendance and existing_attendance.naming_series or get_naming_series(),
]
w.writerow(row)
return w
def get_dashboard_info(party_type, party):
current_fiscal_year = get_fiscal_year(nowdate(), as_dict=True)
company = frappe.db.get_default("company") or frappe.get_all(
"Company")[0].name
party_account_currency = get_party_account_currency(
party_type, party, company)
company_default_currency = get_default_currency() \
or frappe.db.get_value('Company', company, 'default_currency')
if party_account_currency == company_default_currency:
total_field = "base_grand_total"
else:
total_field = "grand_total"
doctype = "Sales Invoice" if party_type == "Customer" else "Purchase Invoice"
billing_this_year = frappe.db.sql(
"""
select sum({0})
from `tab{1}`
where {2}=%s and docstatus=1 and posting_date between %s and %s
""".format(total_field, doctype, party_type.lower()),
(party, current_fiscal_year.year_start_date,
current_fiscal_year.year_end_date))
total_unpaid = frappe.db.sql(
"""
select sum(debit_in_account_currency) - sum(credit_in_account_currency)
from `tabGL Entry`
where party_type = %s and party=%s""", (party_type, party))
info = {}
info["billing_this_year"] = flt(
billing_this_year[0][0]) if billing_this_year else 0
info["currency"] = party_account_currency
info["total_unpaid"] = flt(total_unpaid[0][0]) if total_unpaid else 0
if party_type == "Supplier":
info["total_unpaid"] = -1 * info["total_unpaid"]
return info
def get_actual_data(filters, item_groups, sales_users_or_territory_data,
date_field, sales_field):
fiscal_year = get_fiscal_year(fiscal_year=filters.get("fiscal_year"),
as_dict=1)
dates = [fiscal_year.year_start_date, fiscal_year.year_end_date]
select_field = "`tab{0}`.{1}".format(filters.get("doctype"), sales_field)
child_table = "`tab{0}`".format(filters.get("doctype") + ' Item')
if sales_field == 'sales_person':
select_field = "`tabSales Team`.sales_person"
child_table = "`tab{0}`, `tabSales Team`".format(
filters.get("doctype") + ' Item')
cond = """`tabSales Team`.parent = `tab{0}`.name and
`tabSales Team`.sales_person in ({1}) """.format(
filters.get("doctype"),
','.join(['%s'] * len(sales_users_or_territory_data)))
else:
cond = "`tab{0}`.{1} in ({2})".format(
filters.get("doctype"), sales_field,
','.join(['%s'] * len(sales_users_or_territory_data)))
# Finbyz Changes Start: in below query for removing item group condition
return frappe.db.sql(""" SELECT `tab{child_doc}`.item_group,
`tab{child_doc}`.stock_qty, `tab{child_doc}`.base_net_amount,
{select_field}, `tab{parent_doc}`.{date_field}
FROM `tab{parent_doc}`, {child_table}
WHERE
`tab{child_doc}`.parent = `tab{parent_doc}`.name
and `tab{parent_doc}`.docstatus = 1 and {cond}
and `tab{parent_doc}`.{date_field} between %s and %s""".format(
cond=cond,
date_field=date_field,
select_field=select_field,
child_table=child_table,
parent_doc=filters.get("doctype"),
child_doc=filters.get("doctype") + ' Item',
),
tuple(sales_users_or_territory_data + dates),
as_dict=1)
def get_party_tax_withholding_details(ref_doc, tax_withholding_category=None):
pan_no = ''
suppliers = []
if not tax_withholding_category:
tax_withholding_category, pan_no = frappe.db.get_value(
'Supplier', ref_doc.supplier, ['tax_withholding_category', 'pan'])
if not tax_withholding_category:
return
if not pan_no:
pan_no = frappe.db.get_value('Supplier', ref_doc.supplier, 'pan')
# Get others suppliers with the same PAN No
if pan_no:
suppliers = [
d.name for d in frappe.get_all(
'Supplier', fields=['name'], filters={'pan': pan_no})
]
if not suppliers:
suppliers.append(ref_doc.supplier)
fy = get_fiscal_year(ref_doc.posting_date, company=ref_doc.company)
tax_details = get_tax_withholding_details(tax_withholding_category, fy[0],
ref_doc.company)
if not tax_details:
frappe.throw(
_('Please set associated account in Tax Withholding Category {0} against Company {1}'
).format(tax_withholding_category, ref_doc.company))
tds_amount = get_tds_amount(suppliers, ref_doc.net_total, ref_doc.company,
tax_details, fy, ref_doc.posting_date, pan_no)
tax_row = get_tax_row(tax_details, tds_amount)
return tax_row
def irs_1099_print(filters):
if not filters:
frappe._dict({
"company": frappe.db.get_default("Company"),
"fiscal_year": frappe.db.get_default("Fiscal Year"),
})
else:
filters = frappe._dict(json.loads(filters))
fiscal_year_doc = get_fiscal_year(fiscal_year=filters.fiscal_year,
as_dict=True)
fiscal_year = cstr(fiscal_year_doc.year_start_date.year)
company_address = get_payer_address_html(filters.company)
company_tin = frappe.db.get_value("Company", filters.company, "tax_id")
columns, data = execute(filters)
template = frappe.get_doc("Print Format", "IRS 1099 Form").html
output = PdfFileWriter()
for row in data:
row["fiscal_year"] = fiscal_year
row["company"] = filters.company
row["company_tin"] = company_tin
row["payer_street_address"] = company_address
row["recipient_street_address"], row[
"recipient_city_state"] = get_street_address_html(
"Supplier", row.supplier)
row["payments"] = fmt_money(row["payments"],
precision=0,
currency="USD")
pdf = get_pdf(render_template(template, row),
output=output if output else None)
frappe.local.response.filename = (
f"{filters.fiscal_year} {filters.company} IRS 1099 Forms{IRS_1099_FORMS_FILE_EXTENSION}"
)
frappe.local.response.filecontent = read_multi_pdf(output)
frappe.local.response.type = "download"
