def get_overlapping_dates(self):
if not self.name:
self.name = "New Shift Request"
shift = frappe.qb.DocType("Shift Request")
query = (frappe.qb.from_(shift).select(
shift.name,
shift.shift_type).where((shift.employee == self.employee)
& (shift.docstatus < 2)
& (shift.name != self.name)))
if self.to_date:
query = query.where(
Criterion.any([
Criterion.any([
shift.to_date.isnull(),
((self.from_date >= shift.from_date) &
(self.from_date <= shift.to_date)),
]),
Criterion.any([
((self.to_date >= shift.from_date) &
(self.to_date <= shift.to_date)),
shift.from_date.between(self.from_date, self.to_date),
]),
]))
else:
query = query.where(shift.to_date.isnull()
| ((self.from_date >= shift.from_date)
& (self.from_date <= shift.to_date)))
return query.run(as_dict=True)
def get_duplicate_attendance_record(employee,
attendance_date,
shift,
name=None):
attendance = frappe.qb.DocType("Attendance")
query = (frappe.qb.from_(attendance).select(
attendance.name).where((attendance.employee == employee)
& (attendance.docstatus < 2)))
if shift:
query = query.where(
Criterion.any([
Criterion.all([
((attendance.shift.isnull()) | (attendance.shift == "")),
(attendance.attendance_date == attendance_date),
]),
Criterion.all([
((attendance.shift.isnotnull()) |
(attendance.shift != "")),
(attendance.attendance_date == attendance_date),
(attendance.shift == shift),
]),
]))
else:
query = query.where((attendance.attendance_date == attendance_date))
if name:
query = query.where(attendance.name != name)
return query.run(as_dict=True)
def get_additional_salaries(employee, start_date, end_date, component_type):
from frappe.query_builder import Criterion
comp_type = "Earning" if component_type == "earnings" else "Deduction"
additional_sal = frappe.qb.DocType("Additional Salary")
component_field = additional_sal.salary_component.as_("component")
overwrite_field = additional_sal.overwrite_salary_structure_amount.as_(
"overwrite")
additional_salary_list = (
frappe.qb.from_(additional_sal).select(
additional_sal.name,
component_field,
additional_sal.type,
additional_sal.amount,
additional_sal.is_recurring,
overwrite_field,
additional_sal.deduct_full_tax_on_selected_payroll_date,
).where((additional_sal.employee == employee)
& (additional_sal.docstatus == 1)
& (additional_sal.type == comp_type)).
where(
Criterion.any([
Criterion.all(
[ # is recurring and additional salary dates fall within the payroll period
additional_sal.is_recurring == 1,
additional_sal.from_date <= end_date,
additional_sal.to_date >= end_date,
]),
Criterion.all(
[ # is not recurring and additional salary's payroll date falls within the payroll period
additional_sal.is_recurring == 0,
additional_sal.payroll_date[start_date:end_date],
]),
])).run(as_dict=True))
additional_salaries = []
components_to_overwrite = []
for d in additional_salary_list:
if d.overwrite:
if d.component in components_to_overwrite:
frappe.throw(
_("Multiple Additional Salaries with overwrite property exist for Salary Component {0} between {1} and {2}."
).format(frappe.bold(d.component), start_date, end_date),
title=_("Error"),
)
components_to_overwrite.append(d.component)
additional_salaries.append(d)
return additional_salaries
def check_duplicates(self):
item_price = frappe.qb.DocType("Item Price")
query = (frappe.qb.from_(item_price).select(
item_price.price_list_rate).where(
(item_price.item_code == self.item_code)
& (item_price.price_list == self.price_list)
& (item_price.name != self.name)))
data_fields = (
"uom",
"valid_from",
"valid_upto",
"customer",
"supplier",
"batch_no",
)
number_fields = ["packing_unit"]
for field in data_fields:
if self.get(field):
query = query.where(item_price[field] == self.get(field))
else:
query = query.where(
Criterion.any([
item_price[field].isnull(),
Cast_(item_price[field], "varchar") == "",
]))
for field in number_fields:
if self.get(field):
query = query.where(item_price[field] == self.get(field))
else:
query = query.where(
Criterion.any([
item_price[field].isnull(),
item_price[field] == 0,
]))
price_list_rate = query.run(as_dict=True)
if price_list_rate:
frappe.throw(
_("Item Price appears multiple times based on Price List, Supplier/Customer, Currency, Item, Batch, UOM, Qty, and Dates."
),
ItemPriceDuplicateItem,
)
def get_so_with_invoices(filters):
"""
Get Sales Order with payment terms template with their associated Invoices
"""
sorders = []
so = qb.DocType("Sales Order")
ps = qb.DocType("Payment Schedule")
soi = qb.DocType("Sales Order Item")
conditions = get_conditions(filters)
filter_criterions = build_filter_criterions(filters)
datediff = query_builder.CustomFunction("DATEDIFF",
["cur_date", "due_date"])
ifelse = query_builder.CustomFunction("IF", ["condition", "then", "else"])
query_so = (qb.from_(so).join(soi).on(soi.parent == so.name).join(ps).on(
ps.parent == so.name).select(
so.name,
so.customer,
so.transaction_date.as_("submitted"),
ifelse(
datediff(ps.due_date, functions.CurDate()) < 0, "Overdue",
"Unpaid").as_("status"),
ps.payment_term,
ps.description,
ps.due_date,
ps.invoice_portion,
ps.base_payment_amount,
ps.paid_amount,
).where(
(so.docstatus == 1)
& (so.payment_terms_template != "NULL")
& (so.company == conditions.company)
& (so.transaction_date[conditions.start_date:conditions.end_date])
).where(Criterion.all(filter_criterions)).orderby(
so.name, so.transaction_date, ps.due_date))
sorders = query_so.run(as_dict=True)
invoices = []
if sorders != []:
soi = qb.DocType("Sales Order Item")
si = qb.DocType("Sales Invoice")
sii = qb.DocType("Sales Invoice Item")
query_inv = (qb.from_(sii).right_join(si).on(
si.name == sii.parent).inner_join(soi).on(
soi.name == sii.so_detail).select(
sii.sales_order, sii.parent.as_("invoice"),
si.base_grand_total.as_("invoice_amount")).where(
(sii.sales_order.isin([x.name for x in sorders]))
& (si.docstatus == 1)).groupby(sii.parent))
invoices = query_inv.run(as_dict=True)
return sorders, invoices
def get_shifts_for_date(employee: str, for_timestamp: datetime) -> List[Dict[str, str]]:
"""Returns list of shifts with details for given date"""
assignment = frappe.qb.DocType("Shift Assignment")
return (
frappe.qb.from_(assignment)
.select(assignment.name, assignment.shift_type)
.where(
(assignment.employee == employee)
& (assignment.docstatus == 1)
& (assignment.status == "Active")
& (assignment.start_date <= getdate(for_timestamp.date()))
& (
Criterion.any(
[
assignment.end_date.isnull(),
(assignment.end_date.isnotnull() & (getdate(for_timestamp.date()) <= assignment.end_date)),
]
)
)
)
).run(as_dict=True)
def get_cards_for_user(doctype, txt, searchfield, start, page_len, filters):
meta = frappe.get_meta(doctype)
searchfields = meta.get_search_fields()
search_conditions = []
if not frappe.db.exists("DocType", doctype):
return
numberCard = DocType("Number Card")
if txt:
search_conditions = [
numberCard[field].like("%{txt}%".format(txt=txt))
for field in searchfields
]
condition_query = frappe.db.query.build_conditions(doctype, filters)
return (condition_query.select(
numberCard.name, numberCard.label, numberCard.document_type).where(
(numberCard.owner == frappe.session.user)
| (numberCard.is_public == 1)).where(
Criterion.any(search_conditions))).run()