def add_price(self, price_list=None):
'''Add a new price'''
if not price_list:
price_list = (frappe.db.get_single_value('Selling Settings',
'selling_price_list')
or frappe.db.get_value('Price List',
_('Standard Selling')))
if price_list:
item_price = frappe.get_doc({
"doctype":
"Item Price",
"price_list":
price_list,
"item_code":
self.name,
"uom":
self.stock_uom,
"brand":
self.brand,
"currency":
erpnext.get_default_currency(),
"price_list_rate":
self.standard_rate
})
item_price.insert()
def make_purchase_invoice(self, item, item_details):
pi = frappe.new_doc("Purchase Invoice")
pi.posting_date = today()
pi.posting_time = now()
pi.company = self.company
pi.supplier = item.supplier
pi.currency = get_default_currency()
pi.conversion_rate = 1
pi.taxes_and_charges = item.purchase_taxes_and_charges_template
pi.append(
"items",
{
"item_code": item.item,
"rate": item.amount,
"qty": 1,
"expense_account": item_details.get("expense_account"),
"conversion_factor": 1.0,
"stock_uom": item_details.get("stock_uom"),
},
)
pi.set_taxes()
pi.calculate_taxes_and_totals()
pi.insert()
pi.submit()
item.db_set("reference_doc", "Purchase Invoice")
item.db_set("reference", pi.name)
def execute(useremail, customer_company, filter_year):
columns, data = [], []
useremail = get_session_customer(useremail)
if useremail == []:
return data
if customer_company == None or customer_company == '':
customer_company = useremail[0]["customer"]
if filter_year == None or filter_year == '':
filter_year = get_filter_year()[0]["year"]
columns = get_columns()
linked_list = get_linked(customer_company, filter_year)
customer = useremail[0]["customer"]
total_row = 0.0
cur = erpnext.get_default_currency()
for d in linked_list:
row = []
row = [
d.Customer,
fmt_money(d.Grand_Total_Cashback, precision=2, currency=cur),
d.Month_Year
]
total_row += d.Grand_Total_Cashback
data.append(row)
data.append([])
data.append([
"Cashback Grand Total",
fmt_money(total_row, precision=2, currency=cur), ""
])
return data
def create_customer(doc):
customer = frappe.get_doc({
"doctype":
"Customer",
"customer_name":
doc.title,
"customer_group":
"Student"
or frappe.db.get_single_value("Selling Settings", "customer_group"),
"territory":
frappe.db.get_single_value("Selling Settings", "territory"),
"customer_type":
"Individual",
"default_currency":
get_default_currency(),
"default_price_list":
frappe.db.get_single_value("Selling Settings", "selling_price_list"),
"language":
frappe.db.get_single_value("System Settings", "language"),
"student":
doc.name,
}).insert(ignore_permissions=True, ignore_mandatory=True)
frappe.db.set_value("Student", doc.name, "customer", customer.name)
frappe.msgprint(_("Customer {0} is created.").format(customer.name),
alert=True)
return customer.name
def make_salary_structure(salary_structure, payroll_frequency, employee=None, dont_submit=False, other_details=None,
test_tax=False, company=None, currency=erpnext.get_default_currency(), payroll_period=None):
if test_tax:
frappe.db.sql("""delete from `tabSalary Structure` where name=%s""",(salary_structure))
if not frappe.db.exists('Salary Structure', salary_structure):
details = {
"doctype": "Salary Structure",
"name": salary_structure,
"company": company or erpnext.get_default_company(),
"earnings": make_earning_salary_component(test_tax=test_tax, company_list=["_Test Company"]),
"deductions": make_deduction_salary_component(test_tax=test_tax, company_list=["_Test Company"]),
"payroll_frequency": payroll_frequency,
"payment_account": get_random("Account", filters={'account_currency': currency}),
"currency": currency
}
if other_details and isinstance(other_details, dict):
details.update(other_details)
salary_structure_doc = frappe.get_doc(details)
salary_structure_doc.insert()
if not dont_submit:
salary_structure_doc.submit()
else:
salary_structure_doc = frappe.get_doc("Salary Structure", salary_structure)
if employee and not frappe.db.get_value("Salary Structure Assignment",
{'employee':employee, 'docstatus': 1}) and salary_structure_doc.docstatus==1:
create_salary_structure_assignment(employee, salary_structure, company=company, currency=currency,
payroll_period=payroll_period)
return salary_structure_doc
def create_salary_structure_assignment(
employee,
salary_structure,
from_date=None,
company=None,
currency=erpnext.get_default_currency()):
if frappe.db.exists("Salary Structure Assignment", {"employee": employee}):
frappe.db.sql(
"""delete from `tabSalary Structure Assignment` where employee=%s""",
(employee))
payroll_period = create_payroll_period()
create_tax_slab(payroll_period,
allow_tax_exemption=True,
currency=currency)
salary_structure_assignment = frappe.new_doc("Salary Structure Assignment")
salary_structure_assignment.employee = employee
salary_structure_assignment.base = 50000
salary_structure_assignment.variable = 5000
salary_structure_assignment.from_date = from_date or add_days(
nowdate(), -1)
salary_structure_assignment.salary_structure = salary_structure
salary_structure_assignment.currency = currency
salary_structure_assignment.payroll_payable_account = get_payable_account(
company)
salary_structure_assignment.company = company or erpnext.get_default_company(
)
salary_structure_assignment.save(ignore_permissions=True)
salary_structure_assignment.income_tax_slab = "Tax Slab: _Test Payroll Period"
salary_structure_assignment.submit()
return salary_structure_assignment
def get_ordered_to_be_billed_data(args):
doctype, party = args.get('doctype'), args.get('party')
child_tab = doctype + " Item"
precision = get_field_precision(
frappe.get_meta(child_tab).get_field("billed_amt"),
currency=get_default_currency()) or 2
project_field = get_project_field(doctype, party)
return frappe.db.sql("""
Select
`{parent_tab}`.name, `{parent_tab}`.status, `{parent_tab}`.{date_field}, `{parent_tab}`.{party}, `{parent_tab}`.{party}_name,
{project_field}, `{child_tab}`.item_code, `{child_tab}`.base_amount,
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)),
(`{child_tab}`.base_amount - (`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1))),
`{child_tab}`.item_name, `{child_tab}`.description, `{parent_tab}`.company
from
`{parent_tab}`, `{child_tab}`
where
`{parent_tab}`.name = `{child_tab}`.parent and `{parent_tab}`.docstatus = 1 and `{parent_tab}`.status != 'Closed'
and `{child_tab}`.amount > 0 and round(`{child_tab}`.billed_amt *
ifnull(`{parent_tab}`.conversion_rate, 1), {precision}) < `{child_tab}`.base_amount
order by
`{parent_tab}`.{order} {order_by}
""".format(parent_tab='tab' + doctype,
child_tab='tab' + child_tab,
precision=precision,
party=party,
date_field=args.get('date'),
project_field=project_field,
order=args.get('order'),
order_by=args.get('order_by')))
def test_salary_structures_assignment(self):
company_currency = erpnext.get_default_currency()
salary_structure = make_salary_structure("Salary Structure Sample",
"Monthly",
currency=company_currency)
employee = "*****@*****.**"
employee_doc_name = make_employee(employee)
# clear the already assigned stuctures
frappe.db.sql(
"""delete from `tabSalary Structure Assignment` where employee=%s and salary_structure=%s """,
("*****@*****.**", salary_structure.name),
)
# test structure_assignment
salary_structure.assign_salary_structure(employee=employee_doc_name,
from_date="2013-01-01",
base=5000,
variable=200)
salary_structure_assignment = frappe.get_doc(
"Salary Structure Assignment", {
"employee": employee_doc_name,
"from_date": "2013-01-01"
})
self.assertEqual(salary_structure_assignment.docstatus, 1)
self.assertEqual(salary_structure_assignment.base, 5000)
self.assertEqual(salary_structure_assignment.variable, 200)
def make_deliv_note(self,order_items, isum):
#frappe.msgprint(self.customer)
delivery_note = frappe.new_doc('Delivery Note')
delivery_note.update({
"customer": self.customer,
"customer_name": self.customer_name,
"company": self.company,
"price_list_currency":get_default_currency() or frappe.get_cached_value('Company', company, 'default_currency'),
"ignore_pricing_rule":0,
"conversion_rate":1,
"plc_conversion_rate":1,
"total":isum,
"net_total":isum,
"base_total":isum,
"base_net_total":isum,
"base_grand_total":isum,
"base_rounded_total":isum,
"grand_total":isum,
"rounded_total":isum,
"items": order_items,
"maintenance_order":self.name
})
delivery_note.set_missing_values(for_validate = True)
delivery_note.insert(ignore_permissions=True)
delivery_note.submit()
self.delivery_note = delivery_note.name
frappe.msgprint(_("Delivery Note {0} created as Completed".format(delivery_note.name)), alert=True)
def execute():
frappe.reload_doc("accounts", "doctype", "journal_entry")
frappe.db.sql(
""" update `tabJournal Entry` set total_amount_currency = %s
where ifnull(multi_currency, 0) = 0
and (pay_to_recd_from is not null or pay_to_recd_from != "") """,
get_default_currency())
for je in frappe.db.sql(
""" select name from `tabJournal Entry` where multi_currency = 1
and (pay_to_recd_from is not null or pay_to_recd_from != "")""",
as_dict=1):
doc = frappe.get_doc("Journal Entry", je.name)
for d in doc.get('accounts'):
if d.party_type and d.party:
total_amount_currency = d.account_currency
elif frappe.db.get_value("Account", d.account,
"account_type") in ["Bank", "Cash"]:
total_amount_currency = d.account_currency
frappe.db.set_value("Journal Entry",
je.name,
"total_amount_currency",
total_amount_currency,
update_modified=False)
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 execute():
frappe.reload_doc("accounts", "doctype", "journal_entry")
frappe.db.sql(
""" update `tabJournal Entry` set total_amount_currency = %s
where ifnull(multi_currency, 0) = 0
and (pay_to_recd_from is not null or pay_to_recd_from != "") """,
get_default_currency(),
)
for je in frappe.db.sql(
""" select name from `tabJournal Entry` where multi_currency = 1
and (pay_to_recd_from is not null or pay_to_recd_from != "")""",
as_dict=1,
):
doc = frappe.get_doc("Journal Entry", je.name)
for d in doc.get("accounts"):
if d.party_type and d.party:
total_amount_currency = d.account_currency
elif frappe.db.get_value("Account", d.account, "account_type") in ["Bank", "Cash"]:
total_amount_currency = d.account_currency
frappe.db.set_value(
"Journal Entry", je.name, "total_amount_currency", total_amount_currency, update_modified=False
)
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 add_price(self, price_list=None):
"""Add a new price"""
if not price_list:
price_list = frappe.db.get_single_value(
"Selling Settings",
"selling_price_list") or frappe.db.get_value(
"Price List", _("Standard Selling"))
if price_list:
item_price = frappe.get_doc({
"doctype":
"Item Price",
"price_list":
price_list,
"item_code":
self.name,
"uom":
self.stock_uom,
"brand":
self.brand,
"currency":
erpnext.get_default_currency(),
"price_list_rate":
self.standard_rate,
})
item_price.insert()
def get_columns():
currency = get_default_currency()
return [{
"label": _("Territory"),
"fieldname": "territory",
"fieldtype": "Link",
"options": "Territory"
}, {
"label": _("Opportunity Amount"),
"fieldname": "opportunity_amount",
"fieldtype": "Currency",
"options": currency
}, {
"label": _("Quotation Amount"),
"fieldname": "quotation_amount",
"fieldtype": "Currency",
"options": currency
}, {
"label": _("Order Amount"),
"fieldname": "order_amount",
"fieldtype": "Currency",
"options": currency
}, {
"label": _("Billing Amount"),
"fieldname": "billing_amount",
"fieldtype": "Currency",
"options": currency
}]
def load_dashboard_info(self):
company_default_currency = get_default_currency()
allocated_amount_against_order = flt(
frappe.db.get_value(
"Sales Team",
{
"docstatus": 1,
"parenttype": "Sales Order",
"sales_person": self.sales_person_name
},
"sum(allocated_amount)",
))
allocated_amount_against_invoice = flt(
frappe.db.get_value(
"Sales Team",
{
"docstatus": 1,
"parenttype": "Sales Invoice",
"sales_person": self.sales_person_name
},
"sum(allocated_amount)",
))
info = {}
info["allocated_amount_against_order"] = allocated_amount_against_order
info[
"allocated_amount_against_invoice"] = allocated_amount_against_invoice
info["currency"] = company_default_currency
self.set_onload("dashboard_info", info)
def test_duplicate_entry_for_payroll_period(self):
declaration = frappe.get_doc({
"doctype":
"Employee Tax Exemption Declaration",
"employee":
frappe.get_value("Employee",
{"user_id": "*****@*****.**"}, "name"),
"company":
erpnext.get_default_company(),
"payroll_period":
"_Test Payroll Period",
"currency":
erpnext.get_default_currency(),
"declarations": [
dict(exemption_sub_category="_Test Sub Category",
exemption_category="_Test Category",
amount=100000),
dict(exemption_sub_category="_Test1 Sub Category",
exemption_category="_Test Category",
amount=50000),
]
}).insert()
duplicate_declaration = frappe.get_doc({
"doctype":
"Employee Tax Exemption Declaration",
"employee":
frappe.get_value("Employee",
{"user_id": "*****@*****.**"}, "name"),
"company":
erpnext.get_default_company(),
"payroll_period":
"_Test Payroll Period",
"currency":
erpnext.get_default_currency(),
"declarations": [
dict(exemption_sub_category="_Test Sub Category",
exemption_category="_Test Category",
amount=100000)
]
})
self.assertRaises(DuplicateDeclarationError,
duplicate_declaration.insert)
duplicate_declaration.employee = frappe.get_value(
"Employee", {"user_id": "*****@*****.**"}, "name")
self.assertTrue(duplicate_declaration.insert)
def create_tax_slab(payroll_period, effective_date=None, allow_tax_exemption=False, dont_submit=False, currency=None,
company=None):
if not currency:
currency = erpnext.get_default_currency()
if company:
currency = erpnext.get_company_currency(company)
slabs = [
{
"from_amount": 250000,
"to_amount": 500000,
"percent_deduction": 5,
"condition": "annual_taxable_earning > 500000"
},
{
"from_amount": 500001,
"to_amount": 1000000,
"percent_deduction": 20
},
{
"from_amount": 1000001,
"percent_deduction": 30
}
]
income_tax_slab_name = frappe.db.get_value(
"Income Tax Slab", {"currency": currency})
if not income_tax_slab_name:
income_tax_slab = frappe.new_doc("Income Tax Slab")
income_tax_slab.name = "Tax Slab: " + \
payroll_period.name + " " + cstr(currency)
income_tax_slab.effective_from = effective_date or add_days(
payroll_period.start_date, -2)
income_tax_slab.company = company or ''
income_tax_slab.currency = currency
if allow_tax_exemption:
income_tax_slab.allow_tax_exemption = 1
income_tax_slab.standard_tax_exemption_amount = 50000
for item in slabs:
income_tax_slab.append("slabs", item)
income_tax_slab.append("other_taxes_and_charges", {
"description": "cess",
"percent": 4
})
income_tax_slab.save()
if not dont_submit:
income_tax_slab.submit()
return income_tax_slab.name
else:
return income_tax_slab_name
def validate_task_billing_details(doc, method):
'''
validate Task
Validate billable amounts with Project Estimate
'''
if flt(doc.progress) < flt(doc.progress_billed):
frappe.throw(
_('Task Progress cannot be less than the percentage already Invoiced'
))
if doc.is_milestone and flt(doc.billable_amount) <= 0:
frappe.throw(_('Billable Amount is mandatory for Milestone Tasks'))
if doc.is_milestone and not doc.project:
frappe.throw(_('Project is mandatory for Milestone Tasks'))
tasks = frappe.db.get_all(
'Task',
filters={
'project': doc.project,
'is_milestone': 1,
'name': ['!=', doc.name]
},
fields=['sum(billable_amount) as total_billable_amount'])
max_total_billable = flt(
frappe.db.get_value('Project', doc.project, 'total_sales_amount'))
if max_total_billable <= 0:
max_total_billable = flt(
frappe.db.get_value('Project', doc.project, 'estimated_costing'))
total_tasks_billable = flt(doc.billable_amount) + flt(
tasks[0].total_billable_amount) if tasks and tasks[0] else 0
if max_total_billable > 0 and total_tasks_billable > max_total_billable:
frappe.throw(_(
'Total billable amount ({}) for all Milestone Tasks exceeds the Estimated Cost / Total Sales Amount of Project ({})'
).format(
frappe.utils.fmt_money(abs(total_tasks_billable),
currency=get_default_currency()),
frappe.utils.fmt_money(abs(max_total_billable),
currency=get_default_currency())),
title=_('Did not Save'))
def create_benefit_claim(employee, payroll_period, amount, component):
claim_date = add_months(payroll_period.start_date, random.randint(0, 11))
frappe.get_doc({
"doctype": "Employee Benefit Claim",
"employee": employee,
"claimed_amount": amount,
"claim_date": claim_date,
"earning_component": component,
"currency": erpnext.get_default_currency()
}).submit()
return claim_date
def create_additional_salary(employee, payroll_period, amount):
salary_date = add_months(payroll_period.start_date, random.randint(0, 11))
frappe.get_doc({
"doctype": "Additional Salary",
"employee": employee,
"company": erpnext.get_default_company(),
"salary_component": "Performance Bonus",
"payroll_date": salary_date,
"amount": amount,
"type": "Earning",
"currency": erpnext.get_default_currency()
}).submit()
return salary_date
def load_dashboard_info(self):
company_default_currency = get_default_currency()
allocated_amount = frappe.db.sql("""
select sum(allocated_amount)
from `tabSales Team`
where sales_person = %s and docstatus=1 and parenttype = 'Sales Invoice'
""",(self.sales_person_name))
info = {}
info["allocated_amount"] = flt(allocated_amount[0][0]) if allocated_amount else 0
info["currency"] = company_default_currency
self.set_onload('dashboard_info', info)
def get_additional_salary(emp_id):
create_salary_component("Recurring Salary Component")
add_sal = frappe.new_doc("Additional Salary")
add_sal.employee = emp_id
add_sal.salary_component = "Recurring Salary Component"
add_sal.is_recurring = 1
add_sal.from_date = add_days(nowdate(), -50)
add_sal.to_date = add_days(nowdate(), 180)
add_sal.amount = 5000
add_sal.currency = erpnext.get_default_currency()
add_sal.save()
add_sal.submit()
return add_sal
def create_salary_structure_assignment(
employee,
salary_structure,
from_date=None,
company=None,
currency=erpnext.get_default_currency(),
payroll_period=None,
base=None,
allow_duplicate=False,
):
if not allow_duplicate and frappe.db.exists("Salary Structure Assignment",
{"employee": employee}):
frappe.db.sql(
"""delete from `tabSalary Structure Assignment` where employee=%s""",
(employee))
if not payroll_period:
payroll_period = create_payroll_period()
income_tax_slab = frappe.db.get_value("Income Tax Slab",
{"currency": currency})
if not income_tax_slab:
income_tax_slab = create_tax_slab(payroll_period,
allow_tax_exemption=True,
currency=currency)
salary_structure_assignment = frappe.new_doc("Salary Structure Assignment")
salary_structure_assignment.employee = employee
salary_structure_assignment.base = base or 50000
salary_structure_assignment.variable = 5000
if not from_date:
from_date = get_first_day(nowdate())
joining_date = frappe.get_cached_value("Employee", employee,
"date_of_joining")
if date_diff(joining_date, from_date) > 0:
from_date = joining_date
salary_structure_assignment.from_date = from_date
salary_structure_assignment.salary_structure = salary_structure
salary_structure_assignment.currency = currency
salary_structure_assignment.payroll_payable_account = get_payable_account(
company)
salary_structure_assignment.company = company or erpnext.get_default_company(
)
salary_structure_assignment.save(ignore_permissions=True)
salary_structure_assignment.income_tax_slab = income_tax_slab
salary_structure_assignment.submit()
return salary_structure_assignment
def add_price(self, price_list=None):
'''Add a new price'''
if not price_list:
price_list = (frappe.db.get_single_value('Selling Settings', 'selling_price_list')
or frappe.db.get_value('Price List', _('Standard Selling')))
if price_list:
item_price = frappe.get_doc({
"doctype": "Item Price",
"price_list": price_list,
"item_code": self.name,
"currency": erpnext.get_default_currency(),
"price_list_rate": self.standard_rate
})
item_price.insert()
def load_dashboard_info(self):
company_default_currency = get_default_currency()
allocated_amount = frappe.db.sql("""
select sum(allocated_amount)
from `tabSales Team`
where sales_person = %s and docstatus=1 and parenttype = 'Sales Order'
""",(self.sales_person_name))
info = {}
info["allocated_amount"] = flt(allocated_amount[0][0]) if allocated_amount else 0
info["currency"] = company_default_currency
self.set_onload('dashboard_info', info)
def set_missing_customer_details(self):
if not self.customer_group:
self.customer_group = frappe.db.get_single_value('Selling Settings', 'customer_group') or get_root_of('Customer Group')
if not self.territory:
self.territory = frappe.db.get_single_value('Selling Settings', 'territory') or get_root_of('Territory')
if not self.default_price_list:
self.default_price_list = frappe.db.get_single_value('Selling Settings', 'selling_price_list')
if not self.customer_group or not self.territory or not self.default_price_list:
frappe.msgprint(_('Please set defaults for Customer Group, Territory and Selling Price List in Selling Settings'), alert=True)
if not self.default_currency:
self.default_currency = get_default_currency()
if not self.language:
self.language = frappe.db.get_single_value('System Settings', 'language')
def create_exemption_declaration(employee, payroll_period):
create_exemption_category()
declaration = frappe.get_doc({
"doctype": "Employee Tax Exemption Declaration",
"employee": employee,
"payroll_period": payroll_period,
"company": erpnext.get_default_company(),
"currency": erpnext.get_default_currency()
})
declaration.append("declarations", {
"exemption_sub_category": "_Test Sub Category",
"exemption_category": "_Test Category",
"amount": 100000
})
declaration.submit()
def create_proof_submission(employee, payroll_period, amount):
submission_date = add_months(payroll_period.start_date, random.randint(0, 11))
proof_submission = frappe.get_doc({
"doctype": "Employee Tax Exemption Proof Submission",
"employee": employee,
"payroll_period": payroll_period.name,
"submission_date": submission_date,
"currency": erpnext.get_default_currency()
})
proof_submission.append("tax_exemption_proofs", {
"exemption_sub_category": "_Test Sub Category",
"exemption_category": "_Test Category",
"type_of_proof": "Test", "amount": amount
})
proof_submission.submit()
return submission_date
def load_dashboard_info(self):
billing_this_year = frappe.db.sql("""
select sum(debit_in_account_currency) - sum(credit_in_account_currency), account_currency
from `tabGL Entry`
where voucher_type='Sales Invoice' and party_type = 'Customer'
and party=%s and fiscal_year = %s""",
(self.name, frappe.db.get_default("fiscal_year")))
total_unpaid = frappe.db.sql("""select sum(outstanding_amount)
from `tabSales Invoice`
where customer=%s and docstatus = 1""", self.name)
info = {}
info["billing_this_year"] = billing_this_year[0][0] if billing_this_year else 0
info["currency"] = billing_this_year[0][1] if billing_this_year else get_default_currency()
info["total_unpaid"] = total_unpaid[0][0] if total_unpaid else 0
self.set_onload('dashboard_info', info)
def load_dashboard_info(self):
billing_this_year = frappe.db.sql("""
select sum(debit_in_account_currency) - sum(credit_in_account_currency), account_currency
from `tabGL Entry`
where voucher_type='Sales Invoice' and party_type = 'Customer'
and party=%s and fiscal_year = %s""",
(self.name, frappe.db.get_default("fiscal_year")))
total_unpaid = frappe.db.sql("""select sum(outstanding_amount)
from `tabSales Invoice`
where customer=%s and docstatus = 1""", self.name)
info = {}
info["billing_this_year"] = billing_this_year[0][0] if billing_this_year else 0
info["currency"] = billing_this_year[0][1] if billing_this_year else get_default_currency()
info["total_unpaid"] = total_unpaid[0][0] if total_unpaid else 0
self.set_onload('dashboard_info', info)
def make_sale_invoice(self,order_items,debit_to,isum):
sales_invoice = frappe.new_doc('Sales Invoice')
sales_invoice.update({
"customer": self.customer,
"customer_name": self.customer_name,
"company": self.company,
"debit_to":debit_to,
"is_pos": 0,
"due_date":self.end_date,
"price_list_currency":get_default_currency() or frappe.get_cached_value('Company', company, 'default_currency'),
"ignore_pricing_rule":0,
"conversion_rate":1,
"plc_conversion_rate":1,
"total":isum,
"net_total":isum,
"base_total":isum,
"base_net_total":isum,
"base_grand_total":isum,
"base_rounded_total":isum,
"grand_total":isum,
"rounded_total":isum,
'items': order_items,
"maintenance_order":self.name,
'payment_schedule':[{
'due_date': self.end_date,
'payment_amount':isum
}]
})
sales_invoice.customer = self.customer
#sales_invoice.set_missing_values(for_validate = True)
#sales_invoice.flags.ignore_validate = True
#sales_invoice.flags.ignore_mandatory = True
#sales_invoice.flags.ignore_validate_update_after_submit = True
#sales_invoice.flags.ignore_links = True
sales_invoice.insert(ignore_permissions=True)
sales_invoice.submit()
frappe.db.sql("update `tabMaintenance Order` set invoice='{0}' where name ='{1}' ".format(sales_invoice.name,self.name));
self.invoice = sales_invoice.name
frappe.db.commit()
for i in order_items:
frappe.db.sql('update `tabSales Invoice Item` set delivery_note=%s where parent=%s',(self.delivery_note,sales_invoice.name))
frappe.msgprint(_("Sales Invoice {0} created as paid".format(sales_invoice.name)), alert=True)
def create_tax_slab(payroll_period,
effective_date=None,
allow_tax_exemption=False,
dont_submit=False,
currency=erpnext.get_default_currency()):
frappe.db.sql("""delete from `tabIncome Tax Slab`""")
slabs = [{
"from_amount": 250000,
"to_amount": 500000,
"percent_deduction": 5,
"condition": "annual_taxable_earning > 500000"
}, {
"from_amount": 500001,
"to_amount": 1000000,
"percent_deduction": 20
}, {
"from_amount": 1000001,
"percent_deduction": 30
}]
income_tax_slab = frappe.new_doc("Income Tax Slab")
income_tax_slab.name = "Tax Slab: " + payroll_period.name
income_tax_slab.effective_from = effective_date or add_days(
payroll_period.start_date, -2)
income_tax_slab.currency = currency
if allow_tax_exemption:
income_tax_slab.allow_tax_exemption = 1
income_tax_slab.standard_tax_exemption_amount = 50000
for item in slabs:
income_tax_slab.append("slabs", item)
income_tax_slab.append("other_taxes_and_charges", {
"description": "cess",
"percent": 4
})
income_tax_slab.save()
if not dont_submit:
income_tax_slab.submit()
def get_ordered_to_be_billed_data(args):
doctype, party = args.get('doctype'), args.get('party')
child_tab = doctype + " Item"
precision = get_field_precision(
frappe.get_meta(child_tab).get_field("billed_amt"),
currency=get_default_currency()) or 2
project_field = get_project_field(doctype, party)
# >>> Vamsi; 13012018; Excluding return items in 'Delivered Items To Be Billed' report
if ('additional_where' in args):
additional_where = """ and `{parent_tab}`.name not in (select `{parent_tab}`.return_against from `{parent_tab}` where `{parent_tab}`.is_return=1 )""".format(
parent_tab='tab' + doctype)
else:
additional_where = ""
# <<< Vamsi; 13012018; Excluding return items in 'Delivered Items To Be Billed' report
return frappe.db.sql("""
Select
`{parent_tab}`.name, `{parent_tab}`.{date_field}, `{parent_tab}`.{party}, `{parent_tab}`.{party}_name,
{project_field}, `{child_tab}`.item_code, `{child_tab}`.base_amount,
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)),
(`{child_tab}`.base_amount - (`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1))),
`{child_tab}`.item_name, `{child_tab}`.description, `{parent_tab}`.company
from
`{parent_tab}`, `{child_tab}`
where
`{parent_tab}`.name = `{child_tab}`.parent and `{parent_tab}`.docstatus = 1 and `{parent_tab}`.status != 'Closed'
and `{child_tab}`.amount > 0 and round(`{child_tab}`.billed_amt *
ifnull(`{parent_tab}`.conversion_rate, 1), {precision}) < `{child_tab}`.base_amount {uyn_custom_condition}
order by
`{parent_tab}`.{order} {order_by}
""".format(parent_tab='tab' + doctype,
child_tab='tab' + child_tab,
precision=precision,
party=party,
date_field=args.get('date'),
project_field=project_field,
order=args.get('order'),
order_by=args.get('order_by'),
uyn_custom_condition=additional_where))
def test_duplicate_category_in_declaration(self):
declaration = frappe.get_doc({
"doctype":
"Employee Tax Exemption Declaration",
"employee":
frappe.get_value("Employee",
{"user_id": "*****@*****.**"}, "name"),
"company":
erpnext.get_default_company(),
"payroll_period":
"_Test Payroll Period",
"currency":
erpnext.get_default_currency(),
"declarations": [
dict(exemption_sub_category="_Test Sub Category",
exemption_category="_Test Category",
amount=100000),
dict(exemption_sub_category="_Test Sub Category",
exemption_category="_Test Category",
amount=50000)
]
})
self.assertRaises(frappe.ValidationError, declaration.save)
def get_ordered_to_be_billed_data(args):
doctype, party = args.get("doctype"), args.get("party")
child_tab = doctype + " Item"
precision = (
get_field_precision(frappe.get_meta(child_tab).get_field("billed_amt"), currency=get_default_currency()) or 2
)
project_field = get_project_field(doctype, party)
return frappe.db.sql(
"""
Select
`{parent_tab}`.name, `{parent_tab}`.{date_field}, `{parent_tab}`.{party}, `{parent_tab}`.{party}_name,
{project_field}, `{child_tab}`.item_code, `{child_tab}`.base_amount,
(`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1)),
(`{child_tab}`.base_amount - (`{child_tab}`.billed_amt * ifnull(`{parent_tab}`.conversion_rate, 1))),
`{child_tab}`.item_name, `{child_tab}`.description, `{parent_tab}`.company
from
`{parent_tab}`, `{child_tab}`
where
`{parent_tab}`.name = `{child_tab}`.parent and `{parent_tab}`.docstatus = 1 and `{parent_tab}`.status != 'Closed'
and `{child_tab}`.amount > 0 and round(`{child_tab}`.billed_amt *
ifnull(`{parent_tab}`.conversion_rate, 1), {precision}) < `{child_tab}`.base_amount
order by
`{parent_tab}`.{order} {order_by}
""".format(
parent_tab="tab" + doctype,
child_tab="tab" + child_tab,
precision=precision,
party=party,
date_field=args.get("date"),
project_field=project_field,
order=args.get("order"),
order_by=args.get("order_by"),
)
)
