def get_new_item_code(doctype, txt, searchfield, start, page_len, filters):
from erpbee.controllers.queries import get_match_cond
return frappe.db.sql(
"""select name, item_name, description from tabItem
where is_stock_item=0 and name not in (select name from `tabProduct Bundle`)
and %s like %s %s limit %s, %s""" %
(searchfield, "%s", get_match_cond(doctype), "%s", "%s"),
("%%%s%%" % txt, start, page_len))
def item_details(doctype, txt, searchfield, start, page_len, filters):
from erpbee.controllers.queries import get_match_cond
return frappe.db.sql(
"""select name, item_name, description from `tabItem`
where name in ( select item_code FROM `tabDelivery Note Item`
where parent= %s)
and %s like "%s" %s
limit %s, %s """ %
("%s", searchfield, "%s", get_match_cond(doctype), "%s", "%s"),
((filters
or {}).get("delivery_note"), "%%%s%%" % txt, start, page_len))
def load_invoice_items(self):
conditions = ""
if self.filters.company:
conditions += " and company = %(company)s"
if self.filters.from_date:
conditions += " and posting_date >= %(from_date)s"
if self.filters.to_date:
conditions += " and posting_date <= %(to_date)s"
if self.filters.group_by == "Sales Person":
sales_person_cols = ", sales.sales_person, sales.allocated_amount, sales.incentives"
sales_team_table = "left join `tabSales Team` sales on sales.parent = `tabSales Invoice`.name"
else:
sales_person_cols = ""
sales_team_table = ""
if self.filters.get("sales_invoice"):
conditions += " and `tabSales Invoice`.name = %(sales_invoice)s"
if self.filters.get("item_code"):
conditions += " and `tabSales Invoice Item`.item_code = %(item_code)s"
self.si_list = frappe.db.sql(
"""
select
`tabSales Invoice Item`.parenttype, `tabSales Invoice Item`.parent,
`tabSales Invoice`.posting_date, `tabSales Invoice`.posting_time,
`tabSales Invoice`.project, `tabSales Invoice`.update_stock,
`tabSales Invoice`.customer, `tabSales Invoice`.customer_group,
`tabSales Invoice`.territory, `tabSales Invoice Item`.item_code,
`tabSales Invoice Item`.item_name, `tabSales Invoice Item`.description,
`tabSales Invoice Item`.warehouse, `tabSales Invoice Item`.item_group,
`tabSales Invoice Item`.brand, `tabSales Invoice Item`.dn_detail,
`tabSales Invoice Item`.delivery_note, `tabSales Invoice Item`.stock_qty as qty,
`tabSales Invoice Item`.base_net_rate, `tabSales Invoice Item`.base_net_amount,
`tabSales Invoice Item`.name as "item_row", `tabSales Invoice`.is_return,
`tabSales Invoice Item`.cost_center
{sales_person_cols}
from
`tabSales Invoice` inner join `tabSales Invoice Item`
on `tabSales Invoice Item`.parent = `tabSales Invoice`.name
{sales_team_table}
where
`tabSales Invoice`.docstatus=1 and `tabSales Invoice`.is_opening!='Yes' {conditions} {match_cond}
order by
`tabSales Invoice`.posting_date desc, `tabSales Invoice`.posting_time desc"""
.format(conditions=conditions,
sales_person_cols=sales_person_cols,
sales_team_table=sales_team_table,
match_cond=get_match_cond('Sales Invoice')),
self.filters,
as_dict=1)
def get_project(doctype, txt, searchfield, start, page_len, filters):
from erpbee.controllers.queries import get_match_cond
return frappe.db.sql(""" select name from `tabProject`
where %(key)s like %(txt)s
%(mcond)s
order by name
limit %(start)s, %(page_len)s""" % {
'key': searchfield,
'txt': frappe.db.escape('%' + txt + '%'),
'mcond':get_match_cond(doctype),
'start': start,
'page_len': page_len
})
def item_query(doctype, txt, searchfield, start, page_len, filters):
meta = frappe.get_meta("Item", cached=True)
searchfields = meta.get_search_fields()
order_by = "idx desc, name, item_name"
fields = ["name", "item_group", "item_name", "description"]
fields.extend([field for field in searchfields
if not field in ["name", "item_group", "description"]])
searchfields = searchfields + [field for field in [searchfield or "name", "item_code", "item_group", "item_name"]
if not field in searchfields]
query_filters = {
"disabled": 0,
"ifnull(end_of_life, '5050-50-50')": (">", today())
}
or_cond_filters = {}
if txt:
for s_field in searchfields:
or_cond_filters[s_field] = ("like", "%{0}%".format(txt))
barcodes = frappe.get_all("Item Barcode",
fields=["distinct parent as item_code"],
filters = {"barcode": ("like", "%{0}%".format(txt))})
barcodes = [d.item_code for d in barcodes]
if barcodes:
or_cond_filters["name"] = ("in", barcodes)
for cond in get_match_cond(doctype, as_condition=False):
for key, value in cond.items():
if key == doctype:
key = "name"
query_filters[key] = ("in", value)
if filters and filters.get("item_code"):
has_variants = frappe.get_cached_value("Item", filters.get("item_code"), "has_variants")
if not has_variants:
query_filters["has_variants"] = 0
return frappe.get_all("Item",
fields = fields, filters=query_filters,
or_filters = or_cond_filters, order_by=order_by,
limit_start=start, limit_page_length=page_len, as_list=1)
def get_events(start, end, filters=None):
"""Returns events for Gantt / Calendar view rendering.
:param start: Start date-time.
:param end: End date-time.
:param filters: Filters (JSON).
"""
filters = json.loads(filters)
from frappe.desk.calendar import get_event_conditions
conditions = get_event_conditions("Timesheet", filters)
return frappe.db.sql("""select `tabTimesheet Detail`.name as name,
`tabTimesheet Detail`.docstatus as status, `tabTimesheet Detail`.parent as parent,
from_time as start_date, hours, activity_type,
`tabTimesheet Detail`.project, to_time as end_date,
CONCAT(`tabTimesheet Detail`.parent, ' (', ROUND(hours,2),' hrs)') as title
from `tabTimesheet Detail`, `tabTimesheet`
where `tabTimesheet Detail`.parent = `tabTimesheet`.name
and `tabTimesheet`.docstatus < 2
and (from_time <= %(end)s and to_time >= %(start)s) {conditions} {match_cond}
""".format(conditions=conditions, match_cond = get_match_cond('Timesheet')),
{
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})