def get_outstanding_invoices(party_type, party, account, condition=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
if party_type=="Customer":
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"
invoice_list = frappe.db.sql("""select
voucher_no, voucher_type, posting_date,
ifnull(sum({dr_or_cr}), 0) as invoice_amount,
(
select
ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` payment_gl_entry
where
payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
and payment_gl_entry.party_type = invoice_gl_entry.party_type
and payment_gl_entry.party = invoice_gl_entry.party
and payment_gl_entry.account = invoice_gl_entry.account
and {payment_dr_or_cr} > 0
) as payment_amount
from
`tabGL Entry` invoice_gl_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 != 'Journal Entry'))
group by voucher_type, voucher_no
having (invoice_amount - payment_amount) > 0.005""".format(
dr_or_cr = dr_or_cr,
payment_dr_or_cr = payment_dr_or_cr,
condition = condition or ""
), {
"party_type": party_type,
"party": party,
"account": account,
}, as_dict=True)
for d in invoice_list:
outstanding_invoices.append({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': flt(d.payment_amount),
'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision)
})
return outstanding_invoices
def make_payment_entry(self):
self.check_permission('write')
total_salary_amount = self.get_total_salary_amount()
default_payroll_payable_account = self.get_default_payroll_payable_account()
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
if total_salary_amount and total_salary_amount.rounded_total:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Bank Entry'
journal_entry.user_remark = _('Payment of salary from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
payment_amount = flt(total_salary_amount.rounded_total, precision)
journal_entry.set("accounts", [
{
"account": self.payment_account,
"credit_in_account_currency": payment_amount
},
{
"account": default_payroll_payable_account,
"debit_in_account_currency": payment_amount,
"reference_type": self.doctype,
"reference_name": self.name
}
])
return journal_entry.as_dict()
else:
frappe.msgprint(
_("There are no submitted Salary Slips to process."),
title="Error", indicator="red"
)
def create_journal_entry(self, je_payment_amount, user_remark):
default_payroll_payable_account = self.get_default_payroll_payable_account()
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Bank Entry'
journal_entry.user_remark = _('Payment of {0} from {1} to {2}')\
.format(user_remark, self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
payment_amount = flt(je_payment_amount, precision)
journal_entry.set("accounts", [
{
"account": self.payment_account,
"credit_in_account_currency": payment_amount
},
{
"account": default_payroll_payable_account,
"debit_in_account_currency": payment_amount,
"reference_type": self.doctype,
"reference_name": self.name
}
])
journal_entry.save(ignore_permissions = True)
def get_outstanding_invoices(amount_query, account, party_type, party):
all_outstanding_vouchers = []
outstanding_voucher_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date,
ifnull(sum({amount_query}), 0) as invoice_amount
from
`tabGL Entry`
where
account = %s and party_type=%s and party=%s and {amount_query} > 0
and (CASE
WHEN voucher_type = 'Journal Entry'
THEN ifnull(against_voucher, '') = ''
ELSE 1=1
END)
group by voucher_type, voucher_no
""".format(amount_query = amount_query), (account, party_type, party), as_dict = True)
for d in outstanding_voucher_list:
payment_amount = frappe.db.sql("""
select ifnull(sum({amount_query}), 0)
from
`tabGL Entry`
where
account = %s and party_type=%s and party=%s and {amount_query} < 0
and against_voucher_type = %s and ifnull(against_voucher, '') = %s
""".format(**{
"amount_query": amount_query
}), (account, party_type, party, d.voucher_type, d.voucher_no))
payment_amount = -1*payment_amount[0][0] if payment_amount else 0
precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
if d.invoice_amount > payment_amount:
all_outstanding_vouchers.append({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount, precision),
'outstanding_amount': flt(d.invoice_amount - payment_amount, precision)
})
return all_outstanding_vouchers
def make_payment(self):
# default_payroll_payable_account = self.get_default_payroll_payable_account()
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = ("Social insurance Renew")
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
total_amount = 0.0
for amount in self.social_insurance_date:
total_amount += float(amount.amount_for_month)
journal_entry.append(
"accounts",
{
"account": self.account,
"debit_in_account_currency": float(
amount.amount_for_month),
"party_type": "Employee",
"party": amount.employee,
"cost_center": self.cost_center
},
)
journal_entry.append(
"accounts", {
"account": self.paid_account,
"credit_in_account_currency": total_amount,
"reference_type": self.doctype,
"cost_center": self.cost_center
})
journal_entry.save(ignore_permissions=True)
self.has_gl = "1"
self.save()
return ("done")
def get_stock_and_account_balance(account=None, posting_date=None, company=None):
if not posting_date:
posting_date = nowdate()
warehouse_account = get_warehouse_account_map(company)
account_balance = get_balance_on(
account, posting_date, in_account_currency=False, ignore_account_permission=True
)
related_warehouses = [
wh
for wh, wh_details in warehouse_account.items()
if wh_details.account == account and not wh_details.is_group
]
total_stock_value = 0.0
for warehouse in related_warehouses:
value = get_stock_value_on(warehouse, posting_date)
total_stock_value += value
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
return flt(account_balance, precision), flt(total_stock_value, precision), related_warehouses
def get_raw_materials(self, bom_dict,non_stock_item=0):
""" Get raw materials considering sub-assembly items
{
"item_code": [qty_required, description, stock_uom, min_order_qty]
}
"""
item_list = []
precision = frappe.get_precision("BOM Item", "stock_qty")
for bom, so_wise_qty in bom_dict.items():
bom_wise_item_details = {}
if self.use_multi_level_bom and self.only_raw_materials and self.include_subcontracted:
# get all raw materials with sub assembly childs
# Did not use qty_consumed_per_unit in the query, as it leads to rounding loss
for d in frappe.db.sql("""select fb.item_code,
ifnull(sum(fb.stock_qty/ifnull(bom.quantity, 1)), 0) as qty,
fb.description, fb.stock_uom, item.min_order_qty
from `tabBOM Explosion Item` fb, `tabBOM` bom, `tabItem` item
where bom.name = fb.parent and item.name = fb.item_code
and (item.is_sub_contracted_item = 0 or ifnull(item.default_bom, "")="")
""" + ("and item.is_stock_item = 1","")[non_stock_item] + """
and fb.docstatus<2 and bom.name=%(bom)s
group by fb.item_code, fb.stock_uom""", {"bom":bom}, as_dict=1):
bom_wise_item_details.setdefault(d.item_code, d)
else:
# Get all raw materials considering SA items as raw materials,
# so no childs of SA items
bom_wise_item_details = self.get_subitems(bom_wise_item_details, bom,1, \
self.use_multi_level_bom,self.only_raw_materials, self.include_subcontracted,non_stock_item)
for item, item_details in bom_wise_item_details.items():
for so_qty in so_wise_qty:
item_list.append([item, flt(flt(item_details.qty) * so_qty[1], precision),
item_details.description, item_details.stock_uom, item_details.min_order_qty,
so_qty[0]])
self.make_items_dict(item_list)
def create_disbursement_journal_entry(doc, method):
'''
This function is to be used to create disbursement journal. Primarily developed for creating disbursement journal for Loan
'''
#frappe.msgprint("Method fired: " + method)
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Cash Entry'
journal_entry.user_remark = _('Payment of {0} disbursed on {1} starting from {2}')\
.format(doc.name, doc.disbursement_date, doc.repayment_start_date)
journal_entry.company = doc.company
# If loan is from application than disbursement date is not set the use posting date.
if (doc.disbursement_date):
journal_entry.posting_date = doc.disbursement_date
else:
journal_entry.posting_date = doc.posting_date
payment_amount = flt(doc.loan_amount, precision)
journal_entry.set("accounts", [
{
"account": doc.payment_account,
"credit_in_account_currency": payment_amount,
"reference_type": doc.doctype,
"reference_name": doc.name
},
{
"account": doc.loan_account,
"debit_in_account_currency": payment_amount,
"reference_type": doc.doctype,
"reference_name": doc.name
}
])
journal_entry.save(ignore_permissions = True)
frappe.msgprint("Disbursement Journal: " + journal_entry.name + " has been created.")
def get_gl_entries(self,
warehouse_account=None,
default_expense_account=None,
default_cost_center=None):
if not warehouse_account:
warehouse_account = get_warehouse_account_map(self.company)
sle_map = self.get_stock_ledger_details()
voucher_details = self.get_voucher_details(default_expense_account,
default_cost_center,
sle_map)
gl_list = []
warehouse_with_no_account = []
precision = frappe.get_precision("GL Entry",
"debit_in_account_currency")
for item_row in voucher_details:
sle_list = sle_map.get(item_row.name)
if sle_list:
for sle in sle_list:
if warehouse_account.get(sle.warehouse):
# from warehouse account
self.check_expense_account(item_row)
# If the item does not have the allow zero valuation rate flag set
# and ( valuation rate not mentioned in an incoming entry
# or incoming entry not found while delivering the item),
# try to pick valuation rate from previous sle or Item master and update in SLE
# Otherwise, throw an exception
if not sle.stock_value_difference and self.doctype != "Stock Reconciliation" \
and not item_row.get("allow_zero_valuation_rate"):
sle = self.update_stock_ledger_entries(sle)
# expense account/ target_warehouse / source_warehouse
if item_row.get('target_warehouse'):
warehouse = item_row.get('target_warehouse')
expense_account = warehouse_account[warehouse][
"account"]
else:
expense_account = item_row.expense_account
gl_list.append(
self.get_gl_dict(
{
"account":
warehouse_account[
sle.warehouse]["account"],
"against":
expense_account,
"cost_center":
item_row.cost_center,
"project":
item_row.project or self.get('project'),
"remarks":
self.get("remarks")
or "Accounting Entry for Stock",
"debit":
flt(sle.stock_value_difference, precision),
"is_opening":
item_row.get("is_opening")
or self.get("is_opening") or "No",
},
warehouse_account[sle.warehouse]
["account_currency"],
item=item_row))
gl_list.append(
self.get_gl_dict(
{
"account":
expense_account,
"against":
warehouse_account[
sle.warehouse]["account"],
"cost_center":
item_row.cost_center,
"project":
item_row.project or self.get('project'),
"remarks":
self.get("remarks")
or "Accounting Entry for Stock",
"credit":
flt(sle.stock_value_difference, precision),
"project":
item_row.get("project")
or self.get("project"),
"is_opening":
item_row.get("is_opening")
or self.get("is_opening") or "No"
},
item=item_row))
elif sle.warehouse not in warehouse_with_no_account:
warehouse_with_no_account.append(sle.warehouse)
if warehouse_with_no_account:
for wh in warehouse_with_no_account:
if frappe.db.get_value("Warehouse", wh, "company"):
frappe.throw(
_("Warehouse {0} is not linked to any account, please mention the account in the warehouse record or set default inventory account in company {1}."
).format(wh, self.company))
return process_gl_map(gl_list)
def create_journal_entry2(self ,salary_slip_name_list):
default_payroll_payable_account = self.get_default_payroll_payable_account()
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Payment of {0} from {1} to {2}')\
.format("salary", self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
total_amount = 0
for salary_slip_name in salary_slip_name_list:
salary_slip_total = 0
salary_slip = frappe.get_doc("Salary Slip", salary_slip_name[0])
for sal_detail in salary_slip.earnings:
employee = salary_slip.employee
is_flexible_benefit, only_tax_impact, creat_separate_je, statistical_component = frappe.db.get_value("Salary Component", sal_detail.salary_component,
['is_flexible_benefit', 'only_tax_impact', 'create_separate_payment_entry_against_benefit_claim', 'statistical_component'])
if only_tax_impact != 1 and statistical_component != 1:
if is_flexible_benefit == 1 and creat_separate_je == 1:
pass
# self.create_journal_entry(sal_detail.amount, sal_detail.salary_component ,employee)
else:
salary_slip_total += sal_detail.amount
for sal_detail in salary_slip.deductions:
statistical_component = frappe.db.get_value("Salary Component", sal_detail.salary_component, 'statistical_component')
if statistical_component != 1:
salary_slip_total -= sal_detail.amount
total_amount += salary_slip_total
# frappe.msgprint(str(salary_slip_total))
# frappe.throw(total_amount)
# journal_entry.set
# doc.append
journal_entry.append("accounts",
{
"account": self.resevable_acoount,
"party_type" : "Employee",
"party" : employee ,
"credit_in_account_currency":salary_slip_total,
"cost_center":self.cost_center
},
)
journal_entry.append("accounts",
{
"account": default_payroll_payable_account,
"debit_in_account_currency": total_amount,
"reference_type": self.doctype,
"reference_name": self.name,
"cost_center":self.cost_center
})
# if salary_slip_total > 0:
# self.create_journal_entry(salary_slip_total, "salary" ,employee)
# journal_entry.set("accounts", [
# {
# "account": default_payroll_payable_account,
# "debit_in_account_currency": total_amount,
# "reference_type": self.doctype,
# "reference_name": self.name
# }
# ])
journal_entry.save(ignore_permissions = True)
self.has_gl =1
self.save()
def make_accrual_jv_entry(self):
self.check_permission('write')
earnings = self.get_salary_component_total(
component_type="earnings") or {}
deductions = self.get_salary_component_total(
component_type="deductions") or {}
default_payroll_payable_account = self.get_default_payroll_payable_account(
)
loan_details = self.get_loan_details()
jv_name = ""
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounts = []
payable_amount = 0
# Earnings
for acc, amount in earnings.items():
payable_amount += flt(amount, precision)
accounts.append({
"account":
acc,
"debit_in_account_currency":
flt(amount, precision),
"cost_center":
self.cost_center,
"project":
self.project
})
# Deductions
for acc, amount in deductions.items():
payable_amount -= flt(amount, precision)
accounts.append({
"account":
acc,
"credit_in_account_currency":
flt(amount, precision),
"cost_center":
self.cost_center,
"project":
self.project
})
# Loan
for data in loan_details:
accounts.append({
"account":
data.loan_account,
"credit_in_account_currency":
data.principal_amount
})
if data.interest_amount and not data.interest_income_account:
frappe.throw(
_("Select interest income account in loan {0}").format(
data.loan))
if data.interest_income_account and data.interest_amount:
accounts.append({
"account": data.interest_income_account,
"credit_in_account_currency": data.interest_amount,
"cost_center": self.cost_center,
"project": self.project
})
payable_amount -= flt(data.total_payment, precision)
# Payable amount
accounts.append({
"account":
default_payroll_payable_account,
"credit_in_account_currency":
flt(payable_amount, precision)
})
journal_entry.set("accounts", accounts)
journal_entry.title = default_payroll_payable_account
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name=jv_name)
except Exception as e:
frappe.msgprint(e)
return jv_name
def get_outstanding_invoices(party_type,
party,
account,
condition=None,
include_negative_outstanding=False):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice",
"outstanding_amount") or 2
if erpnext.get_party_account_type(party_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, 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 (against_voucher = '' or against_voucher is null) and voucher_type != 'Payment Entry'
{condition}
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 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)
diff = abs(outstanding_amount
) if include_negative_outstanding else outstanding_amount
if diff > 0.5 / (10**precision):
if not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no,
"posting_date" if party_type == "Employee" else "due_date")
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': due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k:
(k['outstanding_amount'] > 0, k['due_date'] or getdate(nowdate())))
return outstanding_invoices
def get():
return frappe.get_precision(dt, field)
def get_outstanding_invoices(party_type, party, account, condition=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
if party_type in ("Customer", "Student"):
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"
invoice = 'Sales Invoice' if party_type == 'Customer' else 'Purchase Invoice'
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount,
(
select ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` payment_gl_entry
where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
and if(invoice_gl_entry.voucher_type='Journal Entry',
payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no,
payment_gl_entry.against_voucher = invoice_gl_entry.against_voucher)
and payment_gl_entry.party_type = invoice_gl_entry.party_type
and payment_gl_entry.party = invoice_gl_entry.party
and payment_gl_entry.account = invoice_gl_entry.account
and {payment_dr_or_cr} > 0
) as payment_amount
from
`tabGL Entry` invoice_gl_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
having (invoice_amount - payment_amount) > 0.005
order by posting_date, name""".format(dr_or_cr=dr_or_cr,
invoice=invoice,
payment_dr_or_cr=payment_dr_or_cr,
condition=condition or ""), {
"party_type": party_type,
"party": party,
"account": account,
},
as_dict=True)
for d in invoice_list:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no,
"posting_date" if party_type == "Employee" else "due_date")
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':
flt(d.payment_amount),
'outstanding_amount':
flt(d.invoice_amount - d.payment_amount, precision),
'due_date':
due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def create_additional_salary_journal(doc, method):
#frappe.msgprint("Method fired is: " + str(method))
if (frappe.get_value("Salary Component", doc.salary_component,
"create_cash_journal")):
salary_component = frappe.get_doc("Salary Component",
doc.salary_component)
cash_account = frappe.db.get_single_value(
"Payware Settings",
"default_account_for_additional_component_cash_journal")
component_account = None
try:
component_account = frappe.db.get_value(
"Salary Component Account", {
"parent": doc.salary_component,
"company": doc.company
}, "default_account")
if not component_account:
frappe.throw("Ther is no account")
except Exception as e:
component_account = frappe.db.get_value(
"Salary Component Account", {
"parent": doc.salary_component,
"company": doc.company
}, "account")
#frappe.msgprint("Expense account is: " + str(component_account))
if method == "on_submit":
dr_account = component_account
cr_account = cash_account
elif method == "on_cancel":
dr_account = cash_account
cr_account = component_account
else:
frappe.msgprint(
"Unknown method on create_additional_salary_journal")
return
#frappe.msgprint("Method fired: " + method)
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Cash Entry'
journal_entry.user_remark = _('{2} by {1} for {3}').format(
doc.doctype, doc.name, doc.salary_component, doc.employee_name)
journal_entry.company = doc.company
journal_entry.posting_date = doc.payroll_date
payment_amount = flt(doc.amount, precision)
journal_entry.set("accounts",
[{
"account": dr_account,
"debit_in_account_currency": payment_amount
}, {
"account": cr_account,
"credit_in_account_currency": payment_amount
}])
journal_entry.save(ignore_permissions=True)
if method == "on_submit":
frappe.set_value(doc.doctype, doc.name, "journal_name",
journal_entry.name)
msg_to_print = doc.doctype + " journal " + journal_entry.name + " has been created."
elif method == "on_cancel":
msg_to_print = doc.doctype + " reverse journal " + journal_entry.name + " has been created."
frappe.msgprint(msg_to_print)
if (doc.auto_created_based_on):
frappe.set_value("Additional Salary", doc.auto_created_based_on,
"last_transaction_amount", doc.amount)
def create_loan_repayment_jv(doc, method):
loan = frappe.get_doc("Loan", doc.loan)
if method == "on_submit":
dr_account = loan.payment_account
cr_account = loan.loan_account
elif method == "on_cancel":
dr_account = loan.loan_account
cr_account = loan.payment_account
else:
frappe.msgprint("Unknown method on create_loan_repayment_jv")
return
#frappe.msgprint("Method fired: " + method)
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Cash Entry'
journal_entry.user_remark = _('{0} - {1} on {2}').format(
doc.doctype, doc.name, doc.payment_date)
journal_entry.company = doc.company
journal_entry.posting_date = doc.payment_date
payment_amount = flt(doc.payment_amount, precision)
journal_entry.set("accounts", [{
"account": dr_account,
"debit_in_account_currency": payment_amount,
"reference_type": loan.doctype,
"reference_name": loan.name
}, {
"account": cr_account,
"credit_in_account_currency": payment_amount,
"reference_type": loan.doctype,
"reference_name": loan.name
}])
journal_entry.save(ignore_permissions=True)
# Create records in NFS child doctype of Loan doctype
if method == "on_submit":
#frappe.msgprint("loan nfs repayment appending...")
loan_nfs_row = loan.append("loan_repayments_not_from_salary")
loan_nfs_row.nfs_loan_repayment = doc.name
loan_nfs_row.company = doc.company
loan_nfs_row.payment_date = doc.payment_date
loan_nfs_row.payment_amount = doc.payment_amount
loan.save()
elif method == "on_cancel":
# Delete record from loan related to this repayment
for repayment in frappe.get_all("Loan NFS Repayments", "name",
{"nfs_loan_repayment": doc.name}):
#frappe.msgprint("doc.name: " + str(repayment.name) + " for loan repayment: " + doc.name)
frappe.db.sql(
"""update `tabLoan NFS Repayments` set docstatus = 0 where name = %s""",
repayment.name)
frappe.delete_doc("Loan NFS Repayments", repayment.name)
# Update loan of Repayment Schedule child doctype of Loan doctype and set the balances right as per date
redo_repayment_schedule(loan.name)
set_repayment_period(loan.name)
calculate_totals(loan.name)
if method == "on_submit":
frappe.set_value(doc.doctype, doc.name, "journal_name",
journal_entry.name)
msg_to_print = doc.doctype + " journal " + journal_entry.name + " has been created."
elif method == "on_cancel":
msg_to_print = doc.doctype + " reverse journal " + journal_entry.name + " has been created."
frappe.msgprint(msg_to_print)
def make_accural_jv_entry(self):
#self.check_permission('write')
earnings = get_salary_component_total(self,component_type = "earnings") or {}
deductions = get_salary_component_total(self,component_type = "deductions") or {}
default_payroll_payable_account = get_default_payroll_payable_account(self)
loan_details = get_loan_details(self)
jv_name = ""
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accural Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = nowdate()
accounts = []
payable_amount = 0
#HELKYDS
contasal = 0
empresa = frappe.get_doc('Company',self.company)
contaseg_soc = frappe.db.sql(""" SELECT name from `tabAccount` where company = %s and name like '7252%%' """,(empresa.name),as_dict=False)
print contaseg_soc
if (contaseg_soc == ()):
print "VAZIO"
print "VAZIO"
print "VAZIO"
print "VAZIO"
contaseg_soc = frappe.db.sql(""" SELECT name from `tabAccount` where company = %s and name like '5.10.80.10.10.20.90%%' """,(empresa.name),as_dict=False)
print contaseg_soc
ss_list = get_sal_slip_list(self,ss_status=1)
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print "POOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOO"
print ss_list
saliliquido = 0
for x in ss_list:
print x
ss_obj = frappe.get_doc("Salary Slip",x[0])
print ss_obj.salario_iliquido
saliliquido = saliliquido + flt(ss_obj.salario_iliquido)
# =============
# Earnings
for acc, amount in earnings.items():
payable_amount += flt(amount, precision)
accounts.append({
"account": acc,
"debit_in_account_currency": flt(amount, precision),
"cost_center": self.cost_center,
"project": contasal_prj
})
#HELKYDS
conta = acc;
# 72210000 or 5.10.80.10.10.20.90
print "VEvervegtertasdfasfdsafsadf"
print "VEvervegtertasdfasfdsafsadf"
print "EARNINGS"
print acc
print conta.find('72210000')
print conta.find('5.10.80.10.10.20.10')
if (conta.find('72210000') !=-1):
contasal = acc
contasal_amt = round(saliliquido * 0.08) # round(amt * 0.08)
# contasal_prj = self['project']
print "CONTA 72210000"
print contaseg_soc[0][0]
print amount
print contasal_amt
print payable_amount
elif (conta.find('5.10.80.10.10.20.10') !=-1):
contasal = acc
contasal_amt = round(saliliquido * 0.08) # round(amt * 0.08)
print "CONTA 5.10.80.10.10.20.10"
print contaseg_soc[0][0]
print amount
print contasal_amt
print payable_amount
#Acrescenta a conta da Seguranca Social
print "CENTRO CUSTO SEG. SOCIAL "
segsocial = frappe.db.sql(""" SELECT name from `tabCost Center` where company = %s and cost_center_name = 'seguranca social' """,(empresa.name),as_dict=False)
print "Seg. social"
print segsocial[0][0]
if (contasal != 0):
print "ADIICINAEIIII o DEBITO "
print contasal_amt
accounts.append({
"account": contaseg_soc[0][0],
"debit_in_account_currency": contasal_amt,
"cost_center": segsocial[0][0], #contasal_cent, # segsocial[0][0],
"project": contasal_prj
})
payable_amount = payable_amount+contasal_amt
#contasal = 0
print payable_amount
# ============
# Deductions
for acc, amount in deductions.items():
payable_amount -= flt(amount, precision)
accounts.append({
"account": acc,
"credit_in_account_currency": flt(amount, precision),
"cost_center": self.cost_center,
"project": contasal_prj
})
#HELKYDS
conta = acc;
# 34610000 or 2.80.40.20 2.80.20.20.20
if (conta.find('34610000') !=-1):
contasal = acc
#contasal_amt = round(amt * 0.08)
# contasal_cent = self['cost_center']
# contasal_prj = self['project']
print "CONTA 34610000"
print acc
print amount
print contasal_amt
print payable_amount
elif (conta.find('2.80.20.20.20') !=-1):
contasal = acc
#contasal_amt = round(amt * 0.08)
# contasal_cent = self['cost_center']
# contasal_prj = self['project']
print "CONTA 2.80.20.20.20"
print acc
print amount
print contasal_amt
print payable_amount
#Acrescenta a conta do DEBITO da Seguranca Social
if (contasal != 0):
accounts.append({
"account": contasal,
"credit_in_account_currency": contasal_amt,
"cost_center": self.cost_center,
"project": contasal_prj
})
payable_amount = payable_amount-contasal_amt
contasal = 0
print "ADIICINAEIIII o CREDITO "
print contasal_amt
print payable_amount
# ==========
# Employee loan
for data in loan_details:
accounts.append({
"account": data.employee_loan_account,
"credit_in_account_currency": data.principal_amount
})
accounts.append({
"account": data.interest_income_account,
"credit_in_account_currency": data.interest_amount,
"cost_center": contasal_cent,
"project": contasal_prj
})
payable_amount -= flt(data.total_payment, precision)
# Payable amount
accounts.append({
"account": default_payroll_payable_account,
"credit_in_account_currency": flt(payable_amount, precision)
})
journal_entry.set("accounts", accounts)
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
update_salary_slip_status(self,jv_name = jv_name)
except Exception as e:
frappe.msgprint(e)
return jv_name
def make_accrual_jv_entry(self):
self.check_permission('write')
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounts = []
payable_amount = 0
jv_name = ""
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
default_payroll_payable_account = self.get_default_payroll_payable_account(
)
old_branch = self.branch
if not self.branch:
branchs = frappe.get_all('Branch', fields=["name", "cost_center"])
bulk_mode = True
else:
branchs = [self.branch]
bulk_mode = False
for branch in branchs:
if bulk_mode:
cost_center = branch.cost_center
self.branch = branch.name
else:
cost_center = self.cost_center
self.branch = branch
earnings = self.get_salary_component_total(
component_type="earnings") or {}
deductions = self.get_salary_component_total(
component_type="deductions") or {}
if earnings or deductions:
# Earnings
for acc, amount in earnings.items():
payable_amount += flt(amount, precision)
accounts.append({
"account":
acc,
"debit_in_account_currency":
flt(amount, precision),
"party_type":
'',
"cost_center":
cost_center,
"project":
self.project,
"user_remark":
_('Accrual Journal Entry for salaries from {0} to {1} branch: {2}'
).format(self.start_date, self.end_date, self.branch)
})
# Deductions
for acc, amount in deductions.items():
payable_amount -= flt(amount, precision)
accounts.append({
"account":
acc,
"credit_in_account_currency":
flt(amount, precision),
"cost_center":
cost_center,
"party_type":
'',
"project":
self.project
})
self.branch = old_branch
# Payable amount
accounts.append({
"account":
default_payroll_payable_account,
"credit_in_account_currency":
flt(payable_amount, precision),
"party_type":
'',
})
journal_entry.set("accounts", accounts)
journal_entry.title = default_payroll_payable_account
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name=jv_name)
except Exception as e:
frappe.msgprint(e)
return jv_name
def get_outstanding_invoices(party_type, party, account, condition=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
if party_type=="Customer":
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"
invoice_list = frappe.db.sql("""select
voucher_no, voucher_type, posting_date,
ifnull(sum({dr_or_cr}), 0) as invoice_amount,
(
select
ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` payment_gl_entry
where
payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
and payment_gl_entry.party_type = invoice_gl_entry.party_type
and payment_gl_entry.party = invoice_gl_entry.party
and payment_gl_entry.account = invoice_gl_entry.account
and {payment_dr_or_cr} > 0
) as payment_amount
from
`tabGL Entry` invoice_gl_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 != 'Journal Entry'))
group by voucher_type, voucher_no
having (invoice_amount - payment_amount) > 0.005""".format(
dr_or_cr = dr_or_cr,
payment_dr_or_cr = payment_dr_or_cr,
condition = condition or ""
), {
"party_type": party_type,
"party": party,
"account": account,
}, as_dict=True)
for d in invoice_list:
outstanding_invoices.append({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': flt(d.payment_amount),
'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision),
'due_date': frappe.db.get_value(d.voucher_type, d.voucher_no, "due_date")
})
return outstanding_invoices
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, account_type = frappe.get_cached_value(
"Account", account, ["root_type", "account_type"]
)
party_account_type = "Receivable" if root_type == "Asset" else "Payable"
party_account_type = account_type or party_account_type
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,
account_currency as currency
from
`tabGL Entry`
where
party_type = %(party_type)s and party = %(party)s
and account = %(account)s and {dr_or_cr} > 0
and is_cancelled=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 != ''
and is_cancelled=0
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,
"currency": d.currency,
}
)
)
outstanding_invoices = sorted(
outstanding_invoices, key=lambda k: k["due_date"] or getdate(nowdate())
)
return outstanding_invoices
def make_accrual_jv_entry(self):
self.check_permission('write')
earnings = self.get_salary_component_total(component_type = "earnings") or {}
deductions = self.get_salary_component_total(component_type = "deductions") or {}
default_payroll_payable_account = self.get_default_payroll_payable_account()
loan_details = self.get_loan_details()
jv_name = ""
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounts = []
payable_amount = 0
# Earnings
for acc, amount in earnings.items():
payable_amount += flt(amount, precision)
accounts.append({
"account": acc,
"debit_in_account_currency": flt(amount, precision),
"cost_center": self.cost_center,
"project": self.project
})
# Deductions
for acc, amount in deductions.items():
payable_amount -= flt(amount, precision)
accounts.append({
"account": acc,
"credit_in_account_currency": flt(amount, precision),
"cost_center": self.cost_center,
"project": self.project
})
# Loan
for data in loan_details:
accounts.append({
"account": data.loan_account,
"credit_in_account_currency": data.principal_amount
})
if data.interest_amount and not data.interest_income_account:
frappe.throw(_("Select interest income account in loan {0}").format(data.loan))
if data.interest_income_account and data.interest_amount:
accounts.append({
"account": data.interest_income_account,
"credit_in_account_currency": data.interest_amount,
"cost_center": self.cost_center,
"project": self.project
})
payable_amount -= flt(data.total_payment, precision)
# Payable amount
accounts.append({
"account": default_payroll_payable_account,
"credit_in_account_currency": flt(payable_amount, precision)
})
journal_entry.set("accounts", accounts)
journal_entry.title = default_payroll_payable_account
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name = jv_name)
except Exception as e:
frappe.msgprint(e)
return jv_name
def get_outstanding_invoices(party_type, party, account, condition=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount")
if party_type=="Customer":
dr_or_cr = "debit_in_account_currency - credit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.credit_in_account_currency - payment_gl_entry.debit_in_account_currency"
else:
dr_or_cr = "credit_in_account_currency - debit_in_account_currency"
payment_dr_or_cr = "payment_gl_entry.debit_in_account_currency - payment_gl_entry.credit_in_account_currency"
invoice = 'Sales Invoice' if party_type == 'Customer' else 'Purchase Invoice'
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_date, ifnull(sum({dr_or_cr}), 0) as invoice_amount,
(
case when (voucher_type = 'Sales Invoice' or voucher_type = 'Purchase Invoice')
then (select due_date from `tab{invoice}` where name = voucher_no)
else posting_date end
) as due_date,
(
select ifnull(sum({payment_dr_or_cr}), 0)
from `tabGL Entry` payment_gl_entry
where payment_gl_entry.against_voucher_type = invoice_gl_entry.voucher_type
and payment_gl_entry.against_voucher = invoice_gl_entry.voucher_no
and payment_gl_entry.party_type = invoice_gl_entry.party_type
and payment_gl_entry.party = invoice_gl_entry.party
and payment_gl_entry.account = invoice_gl_entry.account
and {payment_dr_or_cr} > 0
) as payment_amount
from
`tabGL Entry` invoice_gl_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
having (invoice_amount - payment_amount) > 0.005
order by posting_date, name""".format(
dr_or_cr = dr_or_cr,
invoice = invoice,
payment_dr_or_cr = payment_dr_or_cr,
condition = condition or ""
), {
"party_type": party_type,
"party": party,
"account": account,
}, as_dict=True)
for d in invoice_list:
outstanding_invoices.append(frappe._dict({
'voucher_no': d.voucher_no,
'voucher_type': d.voucher_type,
'due_date': d.due_date,
'posting_date': d.posting_date,
'invoice_amount': flt(d.invoice_amount),
'payment_amount': flt(d.payment_amount),
'outstanding_amount': flt(d.invoice_amount - d.payment_amount, precision),
'due_date': frappe.db.get_value(d.voucher_type, d.voucher_no,
"posting_date" if party_type=="Employee" else "due_date"),
}))
outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def get_outstanding_invoices(party_type, party, account, condition=None, limit=None):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice", "outstanding_amount") or 2
if erpnext.get_party_account_type(party_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"
invoice = 'Sales Invoice' if erpnext.get_party_account_type(party_type) == 'Receivable' else 'Purchase Invoice'
held_invoices = get_held_invoices(party_type, party)
limit_cond = "limit %s" % limit if limit else ""
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_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 {limit_cond}""".format(
dr_or_cr=dr_or_cr,
invoice = invoice,
condition=condition or "",
limit_cond = limit_cond
), {
"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 not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no, "posting_date" if party_type == "Employee" else "due_date")
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': due_date
})
)
outstanding_invoices = sorted(outstanding_invoices, key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def get_debit_field_precision(self):
if not frappe.flags.debit_field_precision:
frappe.flags.debit_field_precision = frappe.get_precision(
"GL Entry", "debit_in_account_currency")
return frappe.flags.debit_field_precision
def make_accrual_jv_entry(self):
self.check_permission('write')
earnings = self.get_salary_component_total(
component_type="earnings") or {}
deductions = self.get_salary_component_total(
component_type="deductions") or {}
payroll_payable_account = self.payroll_payable_account
jv_name = ""
precision = frappe.get_precision("Journal Entry Account",
"debit_in_account_currency")
if earnings or deductions:
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounts = []
currencies = []
payable_amount = 0
multi_currency = 0
company_currency = erpnext.get_company_currency(self.company)
# Earnings
for acc_cc, amount in earnings.items():
exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry(
acc_cc[0], amount, company_currency, currencies)
payable_amount += flt(amount, precision)
accounts.append({
"account":
acc_cc[0],
"debit_in_account_currency":
flt(amt, precision),
"exchange_rate":
flt(exchange_rate),
"party_type":
'',
"cost_center":
acc_cc[1] or self.cost_center,
"project":
self.project
})
# Deductions
for acc_cc, amount in deductions.items():
exchange_rate, amt = self.get_amount_and_exchange_rate_for_journal_entry(
acc_cc[0], amount, company_currency, currencies)
payable_amount -= flt(amount, precision)
accounts.append({
"account":
acc_cc[0],
"credit_in_account_currency":
flt(amt, precision),
"exchange_rate":
flt(exchange_rate),
"cost_center":
acc_cc[1] or self.cost_center,
"party_type":
'',
"project":
self.project
})
# Payable amount
exchange_rate, payable_amt = self.get_amount_and_exchange_rate_for_journal_entry(
payroll_payable_account, payable_amount, company_currency,
currencies)
accounts.append({
"account":
payroll_payable_account,
"credit_in_account_currency":
flt(payable_amt, precision),
"exchange_rate":
flt(exchange_rate),
"party_type":
'',
"cost_center":
self.cost_center
})
journal_entry.set("accounts", accounts)
if len(currencies) > 1:
multi_currency = 1
journal_entry.multi_currency = multi_currency
journal_entry.title = payroll_payable_account
journal_entry.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name=jv_name)
except Exception as e:
if type(e) in (str, list, tuple):
frappe.msgprint(e)
raise
return jv_name
def get_outstanding_invoices(party_type,
party,
account,
condition=None,
limit=1000):
outstanding_invoices = []
precision = frappe.get_precision("Sales Invoice",
"outstanding_amount") or 2
if erpnext.get_party_account_type(party_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"
invoice = 'Sales Invoice' if erpnext.get_party_account_type(
party_type) == 'Receivable' else 'Purchase Invoice'
held_invoices = get_held_invoices(party_type, party)
limit_cond = "limit %s" % (limit or 1000)
invoice_list = frappe.db.sql("""
select
voucher_no, voucher_type, posting_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 {limit_cond}""".format(dr_or_cr=dr_or_cr,
invoice=invoice,
condition=condition or "",
limit_cond=limit_cond),
{
"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 not d.voucher_type == "Purchase Invoice" or d.voucher_no not in held_invoices:
due_date = frappe.db.get_value(
d.voucher_type, d.voucher_no,
"posting_date" if party_type == "Employee" else "due_date")
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': due_date
}))
outstanding_invoices = sorted(
outstanding_invoices,
key=lambda k: k['due_date'] or getdate(nowdate()))
return outstanding_invoices
def make_accrual_jv_entry(self):
scc=1 if self.currency==frappe.db.get_value("Company", {'name':self.company}, "default_currency") else 0
self.check_permission('write')
deductions = self.get_salary_component_total(component_type = "deductions") or {}
default_payroll_payable_account = self.get_default_payroll_payable_account()
jv_name = ""
precision = frappe.get_precision("Journal Entry Account", "debit_in_account_currency")
##precision = 20
##frappe.msgprint(_("precision= {0}")
## .format(precision))
ss_list = self.get_sal_slip_list(ss_status=1)
journal_entry = frappe.new_doc('Journal Entry')
journal_entry.voucher_type = 'Journal Entry'
journal_entry.user_remark = _('Accrual Journal Entry for salaries from {0} to {1}')\
.format(self.start_date, self.end_date)
journal_entry.company = self.company
journal_entry.posting_date = self.posting_date
accounts = []
earn=0.0
ded=0.0
loa=0.0
pay=0.0
paycc=0.0
for ss in ss_list:
designation=frappe.get_cached_value('Employee',{"name": ss[1]}, "designation")
earnings = self.get_salary_component_total(component_type = "earnings",employee=ss[1]) or {}
# Earnings
for ear in sorted(earnings):
accounts.append({
"account": earnings[ear].get('account'),
"user_remark": _('{0} - {1}').format(designation,earnings[ear].get('scd')),
"debit_in_account_currency": flt(earnings[ear].get('amount'), precision),
"debit": flt(((flt(earnings[ear].get('amount'), precision))*(1 if scc else self.conversion_rate)), precision),
"conversion_rate": 1 if scc else self.conversion_rate,
"cost_center": earnings[ear].get('cost_center',self.cost_center),
"project": earnings[ear].get('project',self.project),
"project_activities": earnings[ear].get('project_activities',self.project_activities),
"customer": earnings[ear].get('customer',self.customer)
})
earn+=flt(((flt(earnings[ear].get('amount'), precision))*(1 if scc else self.conversion_rate)), precision)
# Loan
loan_details = self.get_loan_details(employee=ss[1])
for data in loan_details:
accounts.append({
"account": data.loan_account,
"credit_in_account_currency": data.principal_amount,
#"credit": flt(data.principal_amount if scc else (data.principal_amount*self.conversion_rate),precision),
"conversion_rate": 1 if scc else self.conversion_rate,
"party_type": "Employee",
"party": data.employee,
"cost_center": self.cost_center,
"project": self.project,
"project_activities": self.project_activities,
"customer": self.customer
})
loa+=flt(data.principal_amount if scc else (data.principal_amount*self.cr),precision)
if data.interest_amount and not data.interest_income_account:
frappe.throw(_("Select interest income account in loan {0}").format(data.loan))
if data.interest_income_account and data.interest_amount:
accounts.append({
"account": data.interest_income_account,
"credit_in_account_currency": data.interest_amount,
"party_type": "Employee",
"party": data.employee
})
# Payroll Payable amount
if self.is_payable==1:
accounts.append({
"account": default_payroll_payable_account,
"credit_in_account_currency": ss[2],
"credit": flt(ss[2] if scc else (ss[2]*self.conversion_rate),precision),
"conversion_rate": 1 if scc else self.conversion_rate,
"party_type": "Employee",
"party": ss[1],
"cost_center": self.cost_center,
"project": self.project,
"project_activities": self.project_activities,
"customer": self.customer
})
pay+=flt(ss[2], precision)
paycc+=flt(ss[2] if scc else (ss[2]*self.conversion_rate),precision)
# Deductions
if deductions:
for dede in deductions:
accounts.append({
"account": deductions[dede].get('account'),
"user_remark": _('{0}').format(deductions[dede].get('scd')),
"credit_in_account_currency": flt(deductions[dede].get('amount'), precision),
"credit": flt((flt(deductions[dede].get('amount'), precision)*(1 if scc else self.conversion_rate)),precision),
"conversion_rate": 1 if scc else self.conversion_rate,
"cost_center": self.cost_center,
"project": self.project,
"project_activities": self.project_activities,
"customer": self.customer
})
##ded+=flt(deductions[dede].get('amount') if scc else (deductions[dede].get('amount')*self.conversion_rate),precision)
ded+=flt((flt(deductions[dede].get('amount'), precision)*(1 if scc else self.conversion_rate)),precision)
# Payroll amount
if self.is_payable==0:
accounts.append({
"account": default_payroll_payable_account,
"credit_in_account_currency": flt(pay,precision),
"credit": flt((flt(pay,precision)*(1 if scc else self.conversion_rate)),precision),
"conversion_rate": 1 if scc else self.conversion_rate,
"cost_center": self.cost_center,
"project": self.project,
"project_activities": self.project_activities,
"customer": self.customer
})
paycc=flt((flt(pay,precision)*(1 if scc else self.conversion_rate)),precision)
# Writeoff
if earn!=(loa+paycc+ded):
##frappe.msgprint(_("Not Equal = {0}").format(flt((earn-(loa+paycc+ded)), precision)))
accounts.append({
"account": self.get_default_round_off_account(),
"credit_in_account_currency": flt(self.difference, precision),
#"credit": (earn-(loa+paycc+ded)),
"conversion_rate": 1,
"cost_center": self.cost_center,
"project": self.project,
"project_activities": self.project_activities,
"customer": self.customer
})
if not accounts:
frappe.msgprint(_("There is no Submitted Salary Slip or may be its Acrrualed")
.format(earn,ded,loa,pay))
#frappe.msgprint(_("earn={0},ded={1},loa={2},pay={3},diff={4}")
# .format(flt(earn, precision),flt(ded, precision),flt(loa, precision),flt(paycc, precision),flt((earn-(loa+paycc+ded)), precision)))
journal_entry.set("accounts", accounts)
journal_entry.title = default_payroll_payable_account
journal_entry.save()
self.accrual_jv=journal_entry.name
self.save()
try:
journal_entry.submit()
jv_name = journal_entry.name
self.update_salary_slip_status(jv_name = jv_name)
except Exception as e:
frappe.msgprint(e)
frappe.msgprint(_("Journal Entry submitted for Payroll Entry period from {0} to {1}")
.format(self.start_date, self.end_date))
