def gl_entry_details(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
return frappe.db.sql(
"""select gle.voucher_no, gle.posting_date,
gle.debit, gle.credit from `tabGL Entry` gle
where gle.account = '%(acc)s'
and gle.voucher_type = '%(dt)s'
and gle.voucher_no like '%(txt)s'
and (ifnull(gle.against_voucher, '') = ''
or ifnull(gle.against_voucher, '') = gle.voucher_no )
and (select ifnull(abs(sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))), 0)
from `tabGL Entry`
where account = '%(acc)s'
and against_voucher_type = '%(dt)s'
and against_voucher = gle.voucher_no
and voucher_no != gle.voucher_no)
!= abs(ifnull(gle.debit, 0) - ifnull(gle.credit, 0))
and if(gle.voucher_type='Sales Invoice', ifnull((select is_pos from `tabSales Invoice`
where name=gle.voucher_no), 0), 0)=0
%(mcond)s
ORDER BY gle.posting_date desc, gle.voucher_no desc
limit %(start)s, %(page_len)s""" % {
"dt": filters["dt"],
"acc": filters["acc"],
'mcond': get_match_cond(doctype),
'txt': "%%%s%%" % txt,
"start": start,
"page_len": page_len
})
def load_invoice_items(self):
conditions = ""
if self.filters.company:
conditions += " and company = %(company)s"
if self.filters.from_date:
conditions += " and `tabSales Order`.transaction_date >= %(from_date)s"
if self.filters.to_date:
conditions += " and `tabSales Order`.transaction_date <= %(to_date)s"
if self.filters.get("sales_order"):
conditions += " and `tabSales Order`.name = %(sales_order)s"
self.si_list = frappe.db.sql("""
select
`tabSales Order Item`.parenttype, `tabSales Order Item`.parent,
`tabSales Order`.transaction_date,
`tabSales Order`.project,
`tabSales Order`.customer, `tabSales Order`.customer_group,
`tabSales Order`.territory, `tabSales Order Item`.item_code,
`tabSales Order Item`.item_name, `tabSales Order Item`.custom_valuation_rate,
`tabSales Order Item`.description,
`tabSales Order Item`.warehouse, `tabSales Order Item`.item_group,
`tabSales Order Item`.brand, `tabSales Order Item`.stock_qty as qty,
`tabSales Order Item`.base_net_rate, `tabSales Order Item`.base_net_amount,
`tabSales Order Item`.name as "item_row"
from
`tabSales Order` inner join `tabSales Order Item`
on `tabSales Order Item`.parent = `tabSales Order`.name
where
`tabSales Order`.docstatus=1 {conditions} {match_cond}
order by
`tabSales Order`.transaction_date desc
""".format(conditions=conditions, match_cond=get_match_cond('Sales Order')),
self.filters,
as_dict=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,
employee_name,
`tabTimesheet`.from_date as from_date,
`tabTimesheet`.total_hours as hours,
activity,
`tabTimesheet`.project,
`tabTimesheet`.to_date as to_date,
CONCAT(employee_name,' \n Projekt: ',`tabTimesheet`.project,'\n Aktivita: ',activity,' \n Úloha: ',`tabTimesheet`.task_name,'\n', ' (', ROUND(total_hours,2),' hrs)') as title
FROM `tabTimesheet`, `tabTimesheet Detail`
WHERE `tabTimesheet Detail`.parent = `tabTimesheet`.name
AND `tabTimesheet`.docstatus < 2
AND (from_date <= %(end)s and to_date >= %(start)s) {conditions} {match_cond}
ORDER BY employee_name
""".format(conditions=conditions, match_cond=get_match_cond('Timesheet')), {
"start": start,
"end": end
},
as_dict=True,
update={"allDay": 0})
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 = ""
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"
{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_return != 1 {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 loan_linked_application_query(doctype, txt, searchfield, start, page_len,
filters):
return frappe.db.sql(
"""select name, party_type, party_name, company from `tabLoan Application`
where docstatus < 2
and status = 'Approved'
and name in (select tabLoan.loan_application
from tabLoan
where
tabLoan.loan_application = `tabLoan Application`.name
and
tabLoan.docstatus < 2)
and ({key} like %(txt)s
or party_type like %(txt)s
or party_name like %(txt)s
or company like %(txt)s)
{mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, party_type), locate(%(_txt)s, party_type), 99999),
if(locate(%(_txt)s, party_name), locate(%(_txt)s, party_name), 99999),
if(locate(%(_txt)s, company), locate(%(_txt)s, company), 99999),
name, party_name
limit %(start)s, %(page_len)s""".format(**{
'key': searchfield,
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len
})
def get_project(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
meta = frappe.get_meta(doctype)
searchfields = meta.get_search_fields()
search_columns = ", " + ", ".join(searchfields) if searchfields else ""
search_cond = " or " + " or ".join(field + " like %(txt)s"
for field in searchfields)
return frappe.db.sql(
""" select name {search_columns} from `tabProject`
where %(key)s like %(txt)s
%(mcond)s
{search_condition}
order by name
limit %(start)s, %(page_len)s""".format(search_columns=search_columns,
search_condition=search_cond),
{
"key": searchfield,
"txt": "%" + txt + "%",
"mcond": get_match_cond(doctype),
"start": start,
"page_len": page_len,
},
)
def get_loans(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql(
"""SELECT
`tabLoan`.name,
`tabLoan`.party_type,
`tabLoan`.party,
`tabLoan`.party_name
FROM `tabLoan`
INNER JOIN `tabCustom Loan`
ON `tabLoan`.loan_type = `tabCustom Loan`.name
WHERE `tabCustom Loan`.asset_type = %(asset_type)s
AND ({key} LIKE %(txt)s)
{mcond}
ORDER BY
if(locate(%(_txt)s, `tabLoan`.name), locate(%(_txt)s, `tabLoan`.name), 99999),
if(locate(%(_txt)s, `tabLoan`.party), locate(%(_txt)s, `tabLoan`.party), 99999),
if(locate(%(_txt)s, `tabLoan`.party_name), locate(%(_txt)s, `tabLoan`.party_name), 99999),
`tabLoan`.name, `tabLoan`.party
LIMIT %(start)s, %(page_len)s""".format(
**{
'key': "`tab{0}`.{1}".format(doctype, searchfield),
'mcond': get_match_cond(doctype)
}), {
'txt': "%%%s%%" % txt,
'_txt': txt.replace("%", ""),
'start': start,
'page_len': page_len,
'asset_type': filters.get("asset_type")
})
def cust_query(self, inv_name, conditions, sales_person_cols,
sales_team_table):
return frappe.db.sql("""
select
`inv_item`.parenttype, `inv_item`.parent,
`si`.posting_date, `si`.posting_time,
`si`.project, `si`.update_stock,
`si`.customer, `si`.customer_group,
`si`.territory, `inv_item`.item_code,
`inv_item`.item_name, `inv_item`.description,
`inv_item`.warehouse, `inv_item`.item_group,
`inv_item`.brand, `inv_item`.dn_detail,
`inv_item`.delivery_note, `inv_item`.stock_qty as qty,
`inv_item`.base_net_rate, `inv_item`.base_amount,
`inv_item`.name as "item_row", `si`.is_return,
`inv_item`.cost_center
{sales_person_cols}
from
`tab{inv_name}` si inner join `tab{inv_name} Item` inv_item
on `inv_item`.parent = si.name
{sales_team_table}
where
`si`.is_opening!='Yes' {conditions} {match_cond}
order by
`si`.posting_date desc, `si`.posting_time desc""".format(
inv_name=inv_name,
conditions=conditions,
sales_person_cols=sales_person_cols,
sales_team_table=sales_team_table,
match_cond=get_match_cond(inv_name)),
self.filters,
as_dict=1)
def expense_account_query(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
if not filters: filters = {}
condition = ""
if filters.get("company"):
condition += "and tabAccount.company = %(company)s"
list = []
data = frappe.db.sql("""select name from `tabAccount`""")
for acc in data:
data1 = frappe.db.sql(
"""select name from `tabAccount` where parent_account=%s""",
acc[0])
if not data1:
list.append(acc[0])
return frappe.db.sql(
"""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type in ("Expense Account", "Fixed Asset", "Temporary"))
and tabAccount.name in %(list)s
and tabAccount.is_group=0
and tabAccount.docstatus!=2
and tabAccount.{key} LIKE %(txt)s
{condition} {match_condition}""".format(
condition=condition,
key=frappe.db.escape(searchfield),
match_condition=get_match_cond(doctype)), {
'company': filters.get("company", ""),
'txt': "%%%s%%" % frappe.db.escape(txt),
'list': list
})
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)
conditions = get_conditions(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, 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},
)
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})
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_non_bundled_item_code(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
return frappe.db.sql("""select name, item_name, description from tabItem
where is_product_bundle=0 and is_sales_item=1
and %s like %s %s limit %s, %s""" % (searchfield, "%s",
get_match_cond(doctype),"%s", "%s"),
("%%%s%%" % txt, start, page_len))
def get_new_item_code(doctype, txt, searchfield, start, page_len, filters):
from erpnext.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 get_new_item_code(doctype, txt, searchfield, start, page_len, filters):
from erpnext.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 query_purchase_return_doc(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select name, supplier, supplier_name
from `tab%s` where docstatus = 1
and (`%s` like %%(txt)s
or `supplier` like %%(txt)s) %s
order by name, supplier, supplier_name
limit %s""" % (doctype, searchfield, get_match_cond(doctype),
"%(start)s, %(page_len)s"), {"txt": "%%%s%%" % txt, "start":
start, "page_len": page_len}, as_list=True)
def item_details(doctype, txt, searchfield, start, page_len, filters):
from erpnext.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 item_details(doctype, txt, searchfield, start, page_len, filters):
from erpnext.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 get_project(doctype, txt, searchfield, start, page_len, filters):
from erpnext.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': "%%%s%%" % frappe.db.escape(txt), 'mcond':get_match_cond(doctype),
'start': start, 'page_len': page_len})
def get_project(doctype, txt, searchfield, start, page_len, filters):
from erpnext.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': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
def query_purchase_return_doc(doctype, txt, searchfield, start, page_len, filters):
return frappe.db.sql("""select name, supplier, supplier_name
from `tab%s` where docstatus = 1
and (`%s` like %%(txt)s
or `supplier` like %%(txt)s) %s
order by name, supplier, supplier_name
limit %s""" % (doctype, searchfield, get_match_cond(doctype),
"%(start)s, %(page_len)s"), {"txt": "%%%s%%" % txt, "start":
start, "page_len": page_len}, as_list=True)
def get_data(filters):
conditions = ""
if filters.from_date:
conditions += " and `tabSales Invoice`.posting_date >= %(from_date)s"
if filters.to_date:
conditions += " and `tabSales Invoice`.posting_date <= %(to_date)s"
if filters.store:
conditions += " and `tabSales Invoice`.store = %(store)s"
columns = [
_("Store") + ":Link/Store:120",
_("Sales Invoice") + ":Link/Sales Invoice:120",
_("Posting Date") + ":Date:120",
_("Mode Of Payment") + "::120",
_("Customer") + ":Link/Customer:120",
_("City") + "::120",
_("Country") + "::120",
_("Postal Code") + "::120",
_("Address") + "::120",
_("Tax ID") + "::120",
_("Total") + "::120",
_("Tax") + "::120",
_("Grand Total") + "::120"
]
if not filters.from_date and not filters.to_date:
frappe.throw(_("Please select From Date and To Date"))
data = frappe.db.sql(
"""
SELECT
`tabSales Invoice`.store,
`tabSales Invoice`.name,
`tabSales Invoice`.posting_date,
`tabSales Invoice`.mode_of_payment,
`tabSales Invoice`.customer,
`tabAddress`.city,
`tabAddress`.country,
`tabAddress`.pincode,
`tabAddress`.address_line1,
`tabSales Invoice`.tax_id,
`tabSales Invoice`.total,
CONCAT(ROUND((`tabSales Invoice`.total_taxes_and_charges / `tabSales Invoice`.total * 100), 2), "%%"),
`tabSales Invoice`.grand_total
FROM
`tabSales Invoice` LEFT JOIN `tabAddress`
ON `tabSales Invoice`.customer_address = `tabAddress`.name
WHERE
`tabSales Invoice`.docstatus = 1 {conditions} {match_cond}
ORDER BY
`tabSales Invoice`.posting_date desc, `tabSales Invoice`.posting_time desc
""".format(conditions=conditions, match_cond=get_match_cond('Sales Invoice')),
filters)
return columns, data
def item_query(doctype, txt, searchfield, start, page_len, filters):
conditions = []
return frappe.db.sql("""select tabItem.name,
if(length(tabItem.item_name) > 40,
concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
if(length(tabItem.description) > 40, \
concat(substr(tabItem.description, 1, 40), "..."), description) as decription,
CASE tabItem.item_group
when "Plumbing" then
concat("<b>DI-IN:</b> ",ifnull(tabItem.diameter_in_inches, 0), " <b>DI-MM:</b> ", ifnull(tabItem.diameter_in_millimeter,0)," <b>TH:</b> ", ifnull(tabItem.thickness,0)," <b>SI:</b> ", ifnull(tabItem.size,0))
when "Pump" then
concat("<b>DI-IN:</b> ",ifnull(tabItem.diameter_in_inches, 0), " <b>DI-MM:</b> ", ifnull(tabItem.diameter_in_millimeter,0)," <b>O-IN:</b> ", ifnull(tabItem.outlet_in_inches,0)," <b>O-MM:</b> ", ifnull(tabItem.outlet_in_millimeter,0)," <b>HP:</b> ", ifnull(tabItem.hp,0)," <b>PH:</b> ", ifnull(tabItem.phase,0)," <b>ST:</b> ", ifnull(tabItem.stage,0))
when "Electrical" or "Unit Component" or "Auto Pump System" then
concat("<b>RR:</b> ",ifnull(tabItem.relay_range, 0), " <b>SI:</b>",ifnull(tabItem.size,0))
else
concat(" ")
END as phase
from tabItem
where tabItem.docstatus < 2
and ifnull(tabItem.has_variants, 0)=0
and tabItem.disabled=0
and (tabItem.end_of_life > %(today)s or ifnull(tabItem.end_of_life, '0000-00-00')='0000-00-00')
and (tabItem.`{key}` LIKE %(txt)s
or tabItem.item_name LIKE %(txt)s
or tabItem.description LIKE %(txt)s
or tabItem.variant_of_item LIKE %(txt)s
or tabItem.diameter_in_inches LIKE %(txt)s
or tabItem.outlet_in_inches LIKE %(txt)s
or tabItem.stage LIKE %(txt)s
or tabItem.thickness LIKE %(txt)s
or tabItem.size LIKE %(txt)s
or tabItem.diameter_in_millimeter LIKE %(txt)s
or tabItem.outlet_in_millimeter LIKE %(txt)s
or tabItem.phase LIKE %(txt)s
or tabItem.hp LIKE %(txt)s
or tabItem.relay_range LIKE %(txt)s)
{fcond} {mcond}
order by
if(locate(%(_txt)s, name), locate(%(_txt)s, name), 99999),
if(locate(%(_txt)s, item_name), locate(%(_txt)s, item_name), 99999),
name, item_name
limit %(start)s, %(page_len)s """.format(key=searchfield,
fcond=get_filters_cond(doctype, filters, conditions),
mcond=get_match_cond(doctype)),
{
"today": nowdate(),
"txt": "%%%s%%" % txt,
"_txt": txt.replace("%", ""),
"start": start,
"page_len": page_len
})
def query_sales_return_doc(doctype, txt, searchfield, start, page_len, filters):
conditions = ""
if doctype == "Sales Invoice":
conditions = "and update_stock=1"
return frappe.db.sql("""select name, customer, customer_name
from `tab%s` where docstatus = 1
and (`%s` like %%(txt)s
or `customer` like %%(txt)s) %s %s
order by name, customer, customer_name
limit %s""" % (doctype, searchfield, conditions,
get_match_cond(doctype), "%(start)s, %(page_len)s"),
{"txt": "%%%s%%" % txt, "start": start, "page_len": page_len},
as_list=True)
def query_sales_return_doc(doctype, txt, searchfield, start, page_len, filters):
conditions = ""
if doctype == "Sales Invoice":
conditions = "and update_stock=1"
return frappe.db.sql("""select name, customer, customer_name
from `tab%s` where docstatus = 1
and (`%s` like %%(txt)s
or `customer` like %%(txt)s) %s %s
order by name, customer, customer_name
limit %s""" % (doctype, searchfield, conditions,
get_match_cond(doctype), "%(start)s, %(page_len)s"),
{"txt": "%%%s%%" % txt, "start": start, "page_len": page_len},
as_list=True)
def get_expense_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# expense account can be any Debit account,
# but can also be a Liability account with account_type='Expense Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type = "Expense Account")
and tabAccount.group_or_ledger="Ledger"
and tabAccount.docstatus!=2
and tabAccount.company = '%(company)s'
and tabAccount.%(key)s LIKE '%(txt)s'
%(mcond)s""" % {'company': filters['company'], 'key': searchfield,
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype)})
def get_expense_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# expense account can be any Debit account,
# but can also be a Liability account with account_type='Expense Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type in ("Expense Account", "Fixed Asset", "Temporary"))
and tabAccount.is_group=0
and tabAccount.docstatus!=2
and tabAccount.company = %(company)s
and tabAccount.{key} LIKE %(txt)s
{mcond}""".format( key=frappe.db.escape(searchfield), mcond=get_match_cond(doctype) ),
{ 'company': filters['company'], 'txt': "%%%s%%" % frappe.db.escape(txt) })
def get_expense_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# expense account can be any Debit account,
# but can also be a Liability account with account_type='Expense Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type in ("Expense Account", "Fixed Asset", "Temporary"))
and tabAccount.is_group=0
and tabAccount.docstatus!=2
and tabAccount.company = '%(company)s'
and tabAccount.%(key)s LIKE '%(txt)s'
%(mcond)s""" % {'company': filters['company'], 'key': searchfield,
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype)})
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# income account can be any Credit account,
# but can also be a Asset account with account_type='Income Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type in ("Income Account", "Temporary"))
and tabAccount.is_group=0
and tabAccount.docstatus!=2
and tabAccount.company = '%(company)s'
and tabAccount.%(key)s LIKE '%(txt)s'
%(mcond)s""" % {'company': filters['company'], 'key': searchfield,
'txt': "%%%s%%" % frappe.db.escape(txt), 'mcond':get_match_cond(doctype)})
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# income account can be any Credit account,
# but can also be a Asset account with account_type='Income Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type = "Income Account")
and tabAccount.group_or_ledger="Ledger"
and tabAccount.docstatus!=2
and ifnull(tabAccount.master_type, "")=""
and ifnull(tabAccount.master_name, "")=""
and tabAccount.company = %(company)s
and tabAccount.{key} LIKE %(txt)s
{mcond}""".format(key=searchfield, mcond=get_match_cond(doctype)),
{'company': filters['company'], 'txt': "%%{0}%%".format(txt)})
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# income account can be any Credit account,
# but can also be a Asset account with account_type='Income Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type = "Income Account")
and tabAccount.group_or_ledger="Ledger"
and tabAccount.docstatus!=2
and ifnull(tabAccount.master_type, "")=""
and ifnull(tabAccount.master_name, "")=""
and tabAccount.company = %(company)s
and tabAccount.{key} LIKE %(txt)s
{mcond}""".format(key=searchfield, mcond=get_match_cond(doctype)),
{'company': filters['company'], 'txt': "%%{0}%%".format(txt)})
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)
conditions = get_conditions(filters)
return frappe.db.sql("""select `tabTimesheet Detail`.name as name, `tabTimesheet Detail`.parent as parent,
from_time, hours, activity_type, project, to_time from `tabTimesheet Detail`,
`tabTimesheet` where `tabTimesheet Detail`.parent = `tabTimesheet`.name and
(from_time between %(start)s and %(end)s) {conditions}
{match_cond}""".format(conditions=conditions, match_cond = get_match_cond('Timesheet')),
{
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})
def get_sales_order(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
if not filters: filters = {}
condition = ""
if filters.get("inquiry"):
condition += "and soi.inquiry = %(inquiry)s"
return frappe.db.sql(
"""select distinct(soi.parent) from `tabSales Order Item` soi
where soi.docstatus='1'
and soi.parent LIKE %(txt)s
{condition} {match_condition}""".format(
condition=condition, match_condition=get_match_cond(doctype)), {
'inquiry': filters.get("inquiry", ""),
'txt': "%%%s%%" % frappe.db.escape(txt)
})
def collection_entry_query(doctype, txt, searchfield, start, page_len,
filters):
from erpnext.controllers.queries import get_match_cond
condition = ""
return frappe.db.sql(
"""select t1.name, t1.machine_number, t1.site from `tabCollection Entry` t1
left join `tabCollection Counting` t2 on t2.collection_entry = t1.name
where t2.name IS NULL and t1.entry_type = "Collection Entry"
and (t1.`{key}` LIKE %(txt)s
{condition} {match_condition}
or t1.machine_number LIKE %(txt)s
{condition} {match_condition}
)
order by t1.idx desc, t1.name""".format(
condition=condition,
match_condition=get_match_cond(doctype),
key=searchfield), {'txt': "%%%s%%" % frappe.db.escape(txt)})
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)
conditions = get_conditions(filters)
return frappe.db.sql("""select `tabTimesheet Detail`.name as name, `tabTimesheet Detail`.parent as parent,
from_time, hours, activity_type, project, to_time from `tabTimesheet Detail`,
`tabTimesheet` where `tabTimesheet Detail`.parent = `tabTimesheet`.name and
(from_time between %(start)s and %(end)s) {conditions}
{match_cond}""".format(conditions=conditions, match_cond = get_match_cond('Timesheet')),
{
"start": start,
"end": end
}, as_dict=True, update={"allDay": 0})
def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
if not filters.get("posting_date"):
filters["posting_date"] = nowdate()
batch_nos = None
args = {
'item_code': filters.get("item_code"),
's_warehouse': filters.get('s_warehouse'),
'posting_date': filters.get('posting_date'),
'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype),
"start": start,
"page_len": page_len
}
if filters.get("s_warehouse"):
batch_nos = frappe.db.sql("""select batch_no
from `tabStock Ledger Entry` sle
where item_code = '%(item_code)s'
and warehouse = '%(s_warehouse)s'
and batch_no like '%(txt)s'
and exists(select * from `tabBatch`
where name = sle.batch_no
and (ifnull(expiry_date, '2099-12-31') >= %(posting_date)s
or expiry_date = '')
and docstatus != 2)
%(mcond)s
group by batch_no having sum(actual_qty) > 0
order by batch_no desc
limit %(start)s, %(page_len)s """
% args)
if batch_nos:
return batch_nos
else:
return frappe.db.sql("""select name from `tabBatch`
where item = '%(item_code)s'
and docstatus < 2
and (ifnull(expiry_date, '2099-12-31') >= %(posting_date)s
or expiry_date = '' or expiry_date = "0000-00-00")
%(mcond)s
order by name desc
limit %(start)s, %(page_len)s
""" % args)
def get_project(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
meta = frappe.get_meta(doctype)
searchfields = meta.get_search_fields()
search_columns = ", " + ", ".join(searchfields) if searchfields else ''
search_cond = " or " + " or ".join(
[field + " like %(txt)s" for field in searchfields])
return frappe.db.sql(
""" select name {search_columns} from `tabProject`
where %(key)s like %(txt)s
%(mcond)s
{search_condition}
order by name
limit %(start)s, %(page_len)s""".format(search_columns=search_columns,
search_condition=search_cond), {
'key': searchfield,
'txt': '%' + txt + '%',
'mcond': get_match_cond(doctype),
'start': start,
'page_len': page_len
})
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# income account can be any Credit account,
# but can also be a Asset account with account_type='Income Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql(
"""select tabAccount.name from `tabAccount`
where (tabAccount.debit_or_credit="Credit"
or tabAccount.account_type = "Income Account")
and tabAccount.group_or_ledger="Ledger"
and tabAccount.docstatus!=2
and ifnull(tabAccount.master_type, "")=""
and ifnull(tabAccount.master_name, "")=""
and tabAccount.company = '%(company)s'
and tabAccount.%(key)s LIKE '%(txt)s'
%(mcond)s""" % {
'company': filters['company'],
'key': searchfield,
'txt': "%%%s%%" % txt,
'mcond': get_match_cond(doctype, searchfield)
})
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from erpnext.controllers.queries import get_match_cond
# income account can be any Credit account,
# but can also be a Asset account with account_type='Income Account' in special circumstances.
# Hence the first condition is an "OR"
return frappe.db.sql(
"""select tabAccount.name from `tabAccount`
where (tabAccount.report_type = "Profit and Loss"
or tabAccount.account_type in ("Income Account", "Temporary"))
and tabAccount.is_group=0
and tabAccount.docstatus!=2
and tabAccount.company = '%(company)s'
and tabAccount.%(key)s LIKE '%(txt)s'
%(mcond)s"""
% {
"company": filters["company"],
"key": searchfield,
"txt": "%%%s%%" % frappe.db.escape(txt),
"mcond": get_match_cond(doctype),
}
)
def get_data(filters):
conditions = ""
if filters.from_date:
conditions += " and `tabSales Invoice`.posting_date >= %(from_date)s"
if filters.to_date:
conditions += " and `tabSales Invoice`.posting_date <= %(to_date)s"
if filters.store:
conditions += " and `tabSales Invoice`.store = %(store)s"
columns = [
_("Store") + ":Link/Store:120",
_("Mode Of Payment") + "::120",
_("Grand Total") + "::120"
]
if not filters.from_date and not filters.to_date:
frappe.throw(_("Please select From Date and To Date"))
data = frappe.db.sql(
"""
SELECT
`tabSales Invoice`.store,
`tabSales Invoice`.mode_of_payment,
SUM(`tabSales Invoice`.grand_total)
FROM
`tabSales Invoice`
WHERE
`tabSales Invoice`.docstatus = 1 {conditions} {match_cond}
GROUP BY `tabSales Invoice`.store, `tabSales Invoice`.mode_of_payment
ORDER BY
`tabSales Invoice`.posting_date desc, `tabSales Invoice`.posting_time desc
""".format(conditions=conditions, match_cond=get_match_cond('Sales Invoice')),
filters)
return columns, data
