def get_loan_amount(filters):
total_amount = 0
for doctype in ["Loan Disbursement", "Loan Repayment"]:
loan_doc = frappe.qb.DocType(doctype)
ifnull = CustomFunction("IFNULL", ["value", "default"])
if doctype == "Loan Disbursement":
amount_field = Sum(loan_doc.disbursed_amount)
posting_date = (loan_doc.disbursement_date).as_("posting_date")
account = loan_doc.disbursement_account
salary_condition = loan_doc.docstatus == 1
else:
amount_field = Sum(loan_doc.amount_paid)
posting_date = (loan_doc.posting_date).as_("posting_date")
account = loan_doc.payment_account
salary_condition = loan_doc.repay_from_salary == 0
amount = (frappe.qb.from_(loan_doc).select(amount_field).where(
loan_doc.docstatus == 1).where(salary_condition).where(
account == filters.get("account")).where(
posting_date > getdate(filters.get("report_date"))).where(
ifnull(loan_doc.clearance_date, "4000-01-01") <=
getdate(filters.get("report_date"))).run()[0][0])
total_amount += flt(amount)
return total_amount
def get_batch_incoming_rate(
item_code, warehouse, batch_no, posting_date, posting_time, creation=None
):
sle = frappe.qb.DocType("Stock Ledger Entry")
timestamp_condition = CombineDatetime(sle.posting_date, sle.posting_time) < CombineDatetime(
posting_date, posting_time
)
if creation:
timestamp_condition |= (
CombineDatetime(sle.posting_date, sle.posting_time)
== CombineDatetime(posting_date, posting_time)
) & (sle.creation < creation)
batch_details = (
frappe.qb.from_(sle)
.select(Sum(sle.stock_value_difference).as_("batch_value"), Sum(sle.actual_qty).as_("batch_qty"))
.where(
(sle.item_code == item_code)
& (sle.warehouse == warehouse)
& (sle.batch_no == batch_no)
& (sle.is_cancelled == 0)
)
.where(timestamp_condition)
).run(as_dict=True)
if batch_details and batch_details[0].batch_qty:
return batch_details[0].batch_value / batch_details[0].batch_qty
def get_leave_allocation_records(employee, date, leave_type=None):
"""Returns the total allocated leaves and carry forwarded leaves based on ledger entries"""
Ledger = frappe.qb.DocType("Leave Ledger Entry")
cf_leave_case = (
frappe.qb.terms.Case().when(Ledger.is_carry_forward == "1", Ledger.leaves).else_(0)
)
sum_cf_leaves = Sum(cf_leave_case).as_("cf_leaves")
new_leaves_case = (
frappe.qb.terms.Case().when(Ledger.is_carry_forward == "0", Ledger.leaves).else_(0)
)
sum_new_leaves = Sum(new_leaves_case).as_("new_leaves")
query = (
frappe.qb.from_(Ledger)
.select(
sum_cf_leaves,
sum_new_leaves,
Min(Ledger.from_date).as_("from_date"),
Max(Ledger.to_date).as_("to_date"),
Ledger.leave_type,
)
.where(
(Ledger.from_date <= date)
& (Ledger.to_date >= date)
& (Ledger.docstatus == 1)
& (Ledger.transaction_type == "Leave Allocation")
& (Ledger.employee == employee)
& (Ledger.is_expired == 0)
& (Ledger.is_lwp == 0)
)
)
if leave_type:
query = query.where((Ledger.leave_type == leave_type))
query = query.groupby(Ledger.employee, Ledger.leave_type)
allocation_details = query.run(as_dict=True)
allocated_leaves = frappe._dict()
for d in allocation_details:
allocated_leaves.setdefault(
d.leave_type,
frappe._dict(
{
"from_date": d.from_date,
"to_date": d.to_date,
"total_leaves_allocated": flt(d.cf_leaves) + flt(d.new_leaves),
"unused_leaves": d.cf_leaves,
"new_leaves_allocated": d.new_leaves,
"leave_type": d.leave_type,
}
),
)
return allocated_leaves
def update_reserved_qty_for_sub_contracting(self):
# reserved qty
po = frappe.qb.DocType("Purchase Order")
supplied_item = frappe.qb.DocType("Purchase Order Item Supplied")
reserved_qty_for_sub_contract = (
frappe.qb.from_(po).from_(supplied_item).select(
Sum(Coalesce(supplied_item.required_qty, 0))).where(
(supplied_item.rm_item_code == self.item_code)
& (po.name == supplied_item.parent)
& (po.docstatus == 1)
& (po.is_subcontracted)
& (po.status != "Closed")
& (po.per_received < 100)
& (supplied_item.reserve_warehouse == self.warehouse))
).run()[0][0] or 0.0
se = frappe.qb.DocType("Stock Entry")
se_item = frappe.qb.DocType("Stock Entry Detail")
if frappe.db.field_exists("Stock Entry", "is_return"):
qty_field = (Case().when(se.is_return == 1, se_item.transfer_qty *
-1).else_(se_item.transfer_qty))
else:
qty_field = se_item.transfer_qty
materials_transferred = (
frappe.qb.from_(se).from_(se_item).from_(po).select(
Sum(qty_field)).where(
(se.docstatus == 1)
& (se.purpose == "Send to Subcontractor")
& (Coalesce(se.purchase_order, "") != "")
& ((se_item.item_code == self.item_code) |
(se_item.original_item == self.item_code))
& (se.name == se_item.parent)
& (po.name == se.purchase_order)
& (po.docstatus == 1)
& (po.is_subcontracted == 1)
& (po.status != "Closed")
& (po.per_received < 100))).run()[0][0] or 0.0
if reserved_qty_for_sub_contract > materials_transferred:
reserved_qty_for_sub_contract = reserved_qty_for_sub_contract - materials_transferred
else:
reserved_qty_for_sub_contract = 0
self.db_set("reserved_qty_for_sub_contract",
reserved_qty_for_sub_contract)
self.set_projected_qty()
self.db_set("projected_qty", self.projected_qty)
def get_data(filters):
mr = frappe.qb.DocType("Material Request")
mr_item = frappe.qb.DocType("Material Request Item")
query = (frappe.qb.from_(mr).join(mr_item).on(
mr_item.parent == mr.name).select(
mr.name.as_("material_request"),
mr.transaction_date.as_("date"),
mr_item.schedule_date.as_("required_date"),
mr_item.item_code.as_("item_code"),
Sum(Coalesce(mr_item.stock_qty, 0)).as_("qty"),
Coalesce(mr_item.stock_uom, "").as_("uom"),
Sum(Coalesce(mr_item.ordered_qty, 0)).as_("ordered_qty"),
Sum(Coalesce(mr_item.received_qty, 0)).as_("received_qty"),
(Sum(Coalesce(mr_item.stock_qty, 0)) -
Sum(Coalesce(mr_item.received_qty, 0))).as_("qty_to_receive"),
Sum(Coalesce(mr_item.received_qty, 0)).as_("received_qty"),
(Sum(Coalesce(mr_item.stock_qty, 0)) -
Sum(Coalesce(mr_item.ordered_qty, 0))).as_("qty_to_order"),
mr_item.item_name,
mr_item.description,
mr.company,
).where((mr.material_request_type == "Purchase")
& (mr.docstatus == 1)
& (mr.status != "Stopped")
& (mr.per_received < 100)))
query = get_conditions(filters, query, mr,
mr_item) # add conditional conditions
query = query.groupby(mr.name,
mr_item.item_code).orderby(mr.transaction_date,
mr.schedule_date)
data = query.run(as_dict=True)
return data
def get_reserved_qty_for_production(item_code: str, warehouse: str) -> float:
"""Get total reserved quantity for any item in specified warehouse"""
wo = frappe.qb.DocType("Work Order")
wo_item = frappe.qb.DocType("Work Order Item")
return (
frappe.qb.from_(wo)
.from_(wo_item)
.select(
Sum(
Case()
.when(wo.skip_transfer == 0, wo_item.required_qty - wo_item.transferred_qty)
.else_(wo_item.required_qty - wo_item.consumed_qty)
)
)
.where(
(wo_item.item_code == item_code)
& (wo_item.parent == wo.name)
& (wo.docstatus == 1)
& (wo_item.source_warehouse == warehouse)
& (wo.status.notin(["Stopped", "Completed", "Closed"]))
& (
(wo_item.required_qty > wo_item.transferred_qty)
| (wo_item.required_qty > wo_item.consumed_qty)
)
)
).run()[0][0] or 0.0
def get_leave_summary(employee: str, filters: Filters) -> Dict[str, float]:
"""Returns a dict of leave type and corresponding leaves taken by employee like:
{'leave_without_pay': 1.0, 'sick_leave': 2.0}
"""
Attendance = frappe.qb.DocType("Attendance")
day_case = frappe.qb.terms.Case().when(Attendance.status == "Half Day",
0.5).else_(1)
sum_leave_days = Sum(day_case).as_("leave_days")
leave_details = (frappe.qb.from_(Attendance).select(
Attendance.leave_type, sum_leave_days).where(
(Attendance.employee == employee)
& (Attendance.docstatus == 1)
& (Attendance.company == filters.company)
& ((Attendance.leave_type.isnotnull())
| (Attendance.leave_type != ""))
& (Extract("month", Attendance.attendance_date) == filters.month)
& (Extract("year", Attendance.attendance_date) == filters.year)).
groupby(Attendance.leave_type)).run(as_dict=True)
leaves = {}
for d in leave_details:
leave_type = frappe.scrub(d.leave_type)
leaves[leave_type] = d.leave_days
return leaves
def get_attendance_summary_and_days(employee: str,
filters: Filters) -> Tuple[Dict, List]:
Attendance = frappe.qb.DocType("Attendance")
present_case = (frappe.qb.terms.Case().when(
((Attendance.status == "Present") |
(Attendance.status == "Work From Home")), 1).else_(0))
sum_present = Sum(present_case).as_("total_present")
absent_case = frappe.qb.terms.Case().when(Attendance.status == "Absent",
1).else_(0)
sum_absent = Sum(absent_case).as_("total_absent")
leave_case = frappe.qb.terms.Case().when(Attendance.status == "On Leave",
1).else_(0)
sum_leave = Sum(leave_case).as_("total_leaves")
half_day_case = frappe.qb.terms.Case().when(
Attendance.status == "Half Day", 0.5).else_(0)
sum_half_day = Sum(half_day_case).as_("total_half_days")
summary = (frappe.qb.from_(Attendance).select(
sum_present,
sum_absent,
sum_leave,
sum_half_day,
).where((Attendance.docstatus == 1)
& (Attendance.employee == employee)
& (Attendance.company == filters.company)
& (Extract("month", Attendance.attendance_date) == filters.month)
& (Extract("year", Attendance.attendance_date) == filters.year))
).run(as_dict=True)
days = (frappe.qb.from_(Attendance).select(
Extract("day", Attendance.attendance_date).as_("day_of_month")
).distinct().where(
(Attendance.docstatus == 1)
& (Attendance.employee == employee)
& (Attendance.company == filters.company)
& (Extract("month", Attendance.attendance_date) == filters.month)
& (Extract("year", Attendance.attendance_date) == filters.year))).run(
pluck=True)
return summary[0], days
def set_total_advance_paid(self):
gle = frappe.qb.DocType("GL Entry")
paid_amount = (frappe.qb.from_(gle).select(
Sum(gle.debit).as_("paid_amount")).where(
(gle.against_voucher_type == "Employee Advance")
& (gle.against_voucher == self.name)
& (gle.party_type == "Employee")
& (gle.party == self.employee)
& (gle.docstatus == 1)
& (gle.is_cancelled == 0))).run(
as_dict=True)[0].paid_amount or 0
return_amount = (frappe.qb.from_(gle).select(
Sum(gle.credit).as_("return_amount")).where(
(gle.against_voucher_type == "Employee Advance")
& (gle.voucher_type != "Expense Claim")
& (gle.against_voucher == self.name)
& (gle.party_type == "Employee")
& (gle.party == self.employee)
& (gle.docstatus == 1)
& (gle.is_cancelled == 0))).run(
as_dict=True)[0].return_amount or 0
if paid_amount != 0:
paid_amount = flt(paid_amount) / flt(self.exchange_rate)
if return_amount != 0:
return_amount = flt(return_amount) / flt(self.exchange_rate)
if flt(paid_amount) > self.advance_amount:
frappe.throw(
_("Row {0}# Paid Amount cannot be greater than requested advance amount"
),
EmployeeAdvanceOverPayment,
)
if flt(return_amount) > self.paid_amount - self.claimed_amount:
frappe.throw(_("Return amount cannot be greater unclaimed amount"))
self.db_set("paid_amount", paid_amount)
self.db_set("return_amount", return_amount)
self.set_status(update=True)
def get_ctc(self):
# Get total earnings from existing salary slip
ss = frappe.qb.DocType("Salary Slip")
existing_ss = frappe._dict(
(frappe.qb.from_(ss).select(ss.employee,
Sum(ss.base_gross_pay).as_("amount")).
where(ss.docstatus == 1).where(
ss.employee.isin(list(self.employees.keys()))).where(
ss.start_date >= self.payroll_period_start_date).where(
ss.end_date <= self.payroll_period_end_date).groupby(
ss.employee)).run())
for employee in list(self.employees.keys()):
future_ss_earnings = self.get_future_earnings(employee)
ctc = flt(existing_ss.get(employee)) + future_ss_earnings
self.employees[employee].setdefault("ctc", ctc)
def get_leave_allocation_for_period(employee,
leave_type,
from_date,
to_date,
exclude_allocation=None):
from frappe.query_builder.functions import Sum
Allocation = frappe.qb.DocType("Leave Allocation")
return (frappe.qb.from_(Allocation).select(
Sum(Allocation.total_leaves_allocated).as_("total_allocated_leaves")).
where((Allocation.employee == employee)
& (Allocation.leave_type == leave_type)
& (Allocation.docstatus == 1)
& (Allocation.name != exclude_allocation)
& ((Allocation.from_date.between(from_date, to_date))
| (Allocation.to_date.between(from_date, to_date))
| ((Allocation.from_date < from_date) &
(Allocation.to_date > to_date))))).run()[0][0] or 0.0
def get_tax_exemptions(self, source):
# Get category-wise exmeptions based on submitted proofs or declarations
if source == "Employee Tax Exemption Proof Submission":
child_doctype = "Employee Tax Exemption Proof Submission Detail"
else:
child_doctype = "Employee Tax Exemption Declaration Category"
max_exemptions = self.get_max_exemptions_based_on_category()
par = frappe.qb.DocType(source)
child = frappe.qb.DocType(child_doctype)
records = (frappe.qb.from_(par).inner_join(child).on(
par.name == child.parent).select(
par.employee, child.exemption_category,
Sum(child.amount).as_("amount")
).where(par.docstatus == 1).where(
par.employee.isin(list(self.employees.keys()))).where(
par.payroll_period == self.filters.payroll_period).groupby(
par.employee,
child.exemption_category)).run(as_dict=True)
for d in records:
if not self.employees[d.employee]["allow_tax_exemption"]:
continue
if source == "Employee Tax Exemption Declaration" and d.employee in self.employees_with_proofs:
continue
amount = flt(d.amount)
max_eligible_amount = flt(max_exemptions.get(d.exemption_category))
if max_eligible_amount and amount > max_eligible_amount:
amount = max_eligible_amount
self.employees[d.employee].setdefault(scrub(d.exemption_category),
amount)
self.add_to_total_exemption(d.employee, amount)
if (source == "Employee Tax Exemption Proof Submission"
and d.employee not in self.employees_with_proofs):
self.employees_with_proofs.append(d.employee)
def set_transferred_qty_in_job_card_item(self, ste_doc):
from frappe.query_builder.functions import Sum
def _validate_over_transfer(row, transferred_qty):
"Block over transfer of items if not allowed in settings."
required_qty = frappe.db.get_value("Job Card Item",
row.job_card_item,
"required_qty")
is_excess = flt(transferred_qty) > flt(required_qty)
if is_excess:
frappe.throw(
_("Row #{0}: Cannot transfer more than Required Qty {1} for Item {2} against Job Card {3}"
).format(row.idx, frappe.bold(required_qty),
frappe.bold(row.item_code), ste_doc.job_card),
title=_("Excess Transfer"),
exc=JobCardOverTransferError,
)
for row in ste_doc.items:
if not row.job_card_item:
continue
sed = frappe.qb.DocType("Stock Entry Detail")
se = frappe.qb.DocType("Stock Entry")
transferred_qty = (frappe.qb.from_(sed).join(se).on(
sed.parent == se.name).select(Sum(sed.qty)).where(
(sed.job_card_item == row.job_card_item)
& (se.docstatus == 1)
& (se.purpose == "Material Transfer for Manufacture"))
).run()[0][0]
allow_excess = frappe.db.get_single_value(
"Manufacturing Settings", "job_card_excess_transfer")
if not allow_excess:
_validate_over_transfer(row, transferred_qty)
frappe.db.set_value("Job Card Item", row.job_card_item,
"transferred_qty", flt(transferred_qty))
def get_total_deducted_tax(self):
self.add_column("Total Tax Deducted")
ss = frappe.qb.DocType("Salary Slip")
ss_ded = frappe.qb.DocType("Salary Detail")
records = (
frappe.qb.from_(ss).inner_join(ss_ded).on(
ss.name == ss_ded.parent).select(
ss.employee,
Sum(ss_ded.amount).as_("amount")).where(
ss.docstatus == 1).where(
ss.employee.isin(list(
self.employees.keys()))).where(
ss_ded.parentfield == "deductions").
where(ss_ded.variable_based_on_taxable_salary == 1).where(
ss.start_date >= self.payroll_period_start_date).where(
ss.end_date <= self.payroll_period_end_date).groupby(
ss.employee)).run(as_dict=True)
for d in records:
self.employees[d.employee].setdefault("total_tax_deducted",
d.amount)
def get_pos_reserved_batch_qty(filters):
import json
from frappe.query_builder.functions import Sum
if isinstance(filters, str):
filters = json.loads(filters)
p = frappe.qb.DocType("POS Invoice").as_("p")
item = frappe.qb.DocType("POS Invoice Item").as_("item")
sum_qty = Sum(item.qty).as_("qty")
reserved_batch_qty = (frappe.qb.from_(p).from_(item).select(sum_qty).where(
(p.name == item.parent)
& (p.consolidated_invoice.isnull())
& (p.status != "Consolidated")
& (p.docstatus == 1)
& (item.docstatus == 1)
& (item.item_code == filters.get("item_code"))
& (item.warehouse == filters.get("warehouse"))
& (item.batch_no == filters.get("batch_no"))).run())
flt_reserved_batch_qty = flt(reserved_batch_qty[0][0])
return flt_reserved_batch_qty
def get_tax_exempted_earnings_and_deductions(self):
tax_exempted_components = self.get_tax_exempted_components()
# Get component totals from existing salary slips
ss = frappe.qb.DocType("Salary Slip")
ss_comps = frappe.qb.DocType("Salary Detail")
records = (
frappe.qb.from_(ss).inner_join(ss_comps).on(
ss.name == ss_comps.parent).select(
ss.name, ss.employee, ss_comps.salary_component,
Sum(ss_comps.amount).as_("amount")).where(
ss.docstatus == 1).where(
ss.employee.isin(list(self.employees.keys()))).
where(
ss_comps.salary_component.isin(tax_exempted_components)).where(
ss.start_date >= self.payroll_period_start_date).where(
ss.end_date <= self.payroll_period_end_date).groupby(
ss.employee,
ss_comps.salary_component)).run(as_dict=True)
existing_ss_exemptions = frappe._dict()
for d in records:
existing_ss_exemptions.setdefault(d.employee, {}).setdefault(
scrub(d.salary_component), d.amount)
for employee in list(self.employees.keys()):
if not self.employees[employee]["allow_tax_exemption"]:
continue
exemptions = existing_ss_exemptions.get(employee, {})
self.add_exemptions_from_future_salary_slips(employee, exemptions)
self.employees[employee].update(exemptions)
total_exemptions = sum(list(exemptions.values()))
self.add_to_total_exemption(employee, total_exemptions)
def _get_account_type_based_data(
filters, account_names, period_list, accumulated_values, opening_balances=0
):
if not account_names or not account_names[0] or not type(account_names[0]) == str:
# only proceed if account_names is a list of account names
return {}
from erpnext.accounts.report.cash_flow.cash_flow import get_start_date
company = filters.company
data = {}
total = 0
GLEntry = frappe.qb.DocType("GL Entry")
Account = frappe.qb.DocType("Account")
for period in period_list:
start_date = get_start_date(period, accumulated_values, company)
account_subquery = (
frappe.qb.from_(Account)
.where((Account.name.isin(account_names)) | (Account.parent_account.isin(account_names)))
.select(Account.name)
.as_("account_subquery")
)
if opening_balances:
date_info = dict(date=start_date)
months_map = {"Monthly": -1, "Quarterly": -3, "Half-Yearly": -6}
years_map = {"Yearly": -1}
if months_map.get(filters.periodicity):
date_info.update(months=months_map[filters.periodicity])
else:
date_info.update(years=years_map[filters.periodicity])
if accumulated_values:
start, end = add_to_date(start_date, years=-1), add_to_date(period["to_date"], years=-1)
else:
start, end = add_to_date(**date_info), add_to_date(**date_info)
start, end = get_date_str(start), get_date_str(end)
else:
start, end = start_date if accumulated_values else period["from_date"], period["to_date"]
start, end = get_date_str(start), get_date_str(end)
result = (
frappe.qb.from_(GLEntry)
.select(Sum(GLEntry.credit) - Sum(GLEntry.debit))
.where(
(GLEntry.company == company)
& (GLEntry.posting_date >= start)
& (GLEntry.posting_date <= end)
& (GLEntry.voucher_type != "Period Closing Voucher")
& (GLEntry.account.isin(account_subquery))
)
).run()
if result and result[0]:
gl_sum = result[0][0]
else:
gl_sum = 0
total += flt(gl_sum)
data.setdefault(period["key"], flt(gl_sum))
data["total"] = total
return data
def sum(self, dt, fieldname, filters=None, **kwargs):
return self.query.build_conditions(dt, filters=filters).select(
Sum(Column(fieldname))).run(**kwargs)[0][0] or 0
def update_billed_amount_based_on_so(so_detail, update_modified=True):
from frappe.query_builder.functions import Sum
# Billed against Sales Order directly
si_item = frappe.qb.DocType("Sales Invoice Item").as_("si_item")
sum_amount = Sum(si_item.amount).as_("amount")
billed_against_so = (frappe.qb.from_(si_item).select(sum_amount).where(
(si_item.so_detail == so_detail)
& ((si_item.dn_detail.isnull()) | (si_item.dn_detail == ""))
& (si_item.docstatus == 1)).run())
billed_against_so = billed_against_so and billed_against_so[0][0] or 0
# Get all Delivery Note Item rows against the Sales Order Item row
dn = frappe.qb.DocType("Delivery Note").as_("dn")
dn_item = frappe.qb.DocType("Delivery Note Item").as_("dn_item")
dn_details = (frappe.qb.from_(dn).from_(dn_item).select(
dn_item.name, dn_item.amount, dn_item.si_detail,
dn_item.parent).where((dn.name == dn_item.parent)
& (dn_item.so_detail == so_detail)
& (dn.docstatus == 1)
& (dn.is_return == 0)).orderby(
dn.posting_date, dn.posting_time,
dn.name).run(as_dict=True))
updated_dn = []
for dnd in dn_details:
billed_amt_agianst_dn = 0
# If delivered against Sales Invoice
if dnd.si_detail:
billed_amt_agianst_dn = flt(dnd.amount)
billed_against_so -= billed_amt_agianst_dn
else:
# Get billed amount directly against Delivery Note
billed_amt_agianst_dn = frappe.db.sql(
"""select sum(amount) from `tabSales Invoice Item`
where dn_detail=%s and docstatus=1""",
dnd.name,
)
billed_amt_agianst_dn = billed_amt_agianst_dn and billed_amt_agianst_dn[
0][0] or 0
# Distribute billed amount directly against SO between DNs based on FIFO
if billed_against_so and billed_amt_agianst_dn < dnd.amount:
pending_to_bill = flt(dnd.amount) - billed_amt_agianst_dn
if pending_to_bill <= billed_against_so:
billed_amt_agianst_dn += pending_to_bill
billed_against_so -= pending_to_bill
else:
billed_amt_agianst_dn += billed_against_so
billed_against_so = 0
frappe.db.set_value(
"Delivery Note Item",
dnd.name,
"billed_amt",
billed_amt_agianst_dn,
update_modified=update_modified,
)
updated_dn.append(dnd.parent)
return updated_dn