def fetch_serial_numbers(filters, qty, do_not_include=None):
if do_not_include is None:
do_not_include = []
batch_nos = filters.get("batch_no")
expiry_date = filters.get("expiry_date")
serial_no = frappe.qb.DocType("Serial No")
query = (frappe.qb.from_(serial_no).select(serial_no.name).where(
(serial_no.item_code == filters["item_code"])
& (serial_no.warehouse == filters["warehouse"])
& (Coalesce(serial_no.sales_invoice, "") == "")
& (Coalesce(serial_no.delivery_document_no, "") == "")).orderby(
serial_no.creation).limit(qty or 1))
if do_not_include:
query = query.where(serial_no.name.notin(do_not_include))
if batch_nos:
query = query.where(serial_no.batch_no.isin(batch_nos))
if expiry_date:
batch = frappe.qb.DocType("Batch")
query = (query.left_join(batch).on(
serial_no.batch_no == batch.name).where(
Coalesce(batch.expiry_date, "4000-12-31") >= expiry_date))
serial_numbers = query.run(as_dict=True)
return serial_numbers
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 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(self):
WebPageView = frappe.qb.DocType("Web Page View")
count_all = Count("*").as_("count")
case = frappe.qb.terms.Case().when(WebPageView.is_unique == "1", "1")
count_is_unique = Count(case).as_("unique_count")
return (frappe.qb.from_(WebPageView).select(
"path", count_all, count_is_unique).where(
Coalesce(WebPageView.creation, "0001-01-01")
[self.filters.from_date:self.filters.to_date]).groupby(
WebPageView.path).orderby("count",
Order=frappe.qb.desc)).run()
def execute():
frappe.reload_doc("desk", "doctype", "todo")
ToDo = frappe.qb.DocType("ToDo")
assignees = GroupConcat("owner").distinct().as_("assignees")
assignments = (frappe.qb.from_(ToDo).select(
ToDo.name, ToDo.reference_type,
assignees).where(Coalesce(ToDo.reference_type, "") != "").where(
Coalesce(ToDo.reference_name, "") != "").where(
ToDo.status != "Cancelled").groupby(
ToDo.reference_type,
ToDo.reference_name)).run(as_dict=True)
for doc in assignments:
assignments = doc.assignees.split(",")
frappe.db.set_value(doc.reference_type,
doc.reference_name,
"_assign",
frappe.as_json(assignments),
update_modified=False)
def execute():
frappe.reload_doc("maintenance", "doctype", "maintenance_visit")
# Updates the Maintenance Schedule link to fetch serial nos
from frappe.query_builder.functions import Coalesce
mvp = frappe.qb.DocType("Maintenance Visit Purpose")
mv = frappe.qb.DocType("Maintenance Visit")
frappe.qb.update(mv).join(mvp).on(mvp.parent == mv.name).set(
mv.maintenance_schedule,
Coalesce(mvp.prevdoc_docname,
"")).where((mv.maintenance_type == "Scheduled")
& (mvp.prevdoc_docname.notnull())
& (mv.docstatus < 2)).run(as_dict=1)
def get_delivered_serial_nos(serial_nos):
"""
Returns serial numbers that delivered from the list of serial numbers
"""
from frappe.query_builder.functions import Coalesce
SerialNo = frappe.qb.DocType("Serial No")
serial_nos = get_serial_nos(serial_nos)
query = (frappe.qb.select(SerialNo.name).from_(SerialNo).where(
(SerialNo.name.isin(serial_nos))
& (Coalesce(SerialNo.delivery_document_type, "") != "")))
result = query.run()
if result and len(result) > 0:
delivered_serial_nos = [row[0] for row in result]
return delivered_serial_nos
def get_sal_slip_list(self, ss_status, as_dict=False):
"""
Returns list of salary slips based on selected criteria
"""
ss = frappe.qb.DocType("Salary Slip")
ss_list = (
frappe.qb.from_(ss)
.select(ss.name, ss.salary_structure)
.where(
(ss.docstatus == ss_status)
& (ss.start_date >= self.start_date)
& (ss.end_date <= self.end_date)
& (ss.payroll_entry == self.name)
& ((ss.journal_entry.isnull()) | (ss.journal_entry == ""))
& (Coalesce(ss.salary_slip_based_on_timesheet, 0) == self.salary_slip_based_on_timesheet)
)
).run(as_dict=as_dict)
return ss_list