def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
if filters.has_key('warehouse'):
return webnotes.conn.sql("""select batch_no from `tabStock Ledger Entry` sle
where item_code = '%(item_code)s'
and warehouse = '%(warehouse)s'
and ifnull(is_cancelled, 'No') = 'No'
and batch_no like '%(txt)s'
and exists(select * from `tabBatch`
where name = sle.batch_no
and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
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 """ % {'item_code': filters['item_code'],
'warehouse': filters['warehouse'], 'posting_date': filters['posting_date'],
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
else:
return webnotes.conn.sql("""select name from tabBatch
where docstatus != 2
and item = '%(item_code)s'
and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
and name like '%(txt)s'
%(mcond)s
order by name desc
limit %(start)s, %(page_len)s""" % {'item_code': filters['item_code'],
'posting_date': filters['posting_date'], 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield),'start': start,
'page_len': page_len})
def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
if filters.has_key('warehouse'):
return webnotes.conn.sql("""select batch_no from `tabStock Ledger Entry` sle
where item_code = '%(item_code)s'
and warehouse = '%(warehouse)s'
and batch_no like '%(txt)s'
and exists(select * from `tabBatch`
where name = sle.batch_no
and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
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 """ % {'item_code': filters['item_code'],
'warehouse': filters['warehouse'], 'posting_date': filters['posting_date'],
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
else:
return webnotes.conn.sql("""select name from tabBatch
where docstatus != 2
and item = '%(item_code)s'
and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
and name like '%(txt)s'
%(mcond)s
order by name desc
limit %(start)s, %(page_len)s""" % {'item_code': filters['item_code'],
'posting_date': filters['posting_date'], 'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield),'start': start,
'page_len': page_len})
def gl_entry_details(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql(
"""select gle.voucher_no, gle.posting_date,
gle.%(account_type)s 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.is_cancelled, 'No') = 'No'
and (ifnull(gle.against_voucher, '') = ''
or ifnull(gle.against_voucher, '') = gle.voucher_no )
and ifnull(gle.%(account_type)s, 0) > 0
and (select ifnull(abs(sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))), 0)
from `tabGL Entry`
where against_voucher_type = '%(dt)s'
and against_voucher = gle.voucher_no
and voucher_no != gle.voucher_no
and ifnull(is_cancelled, 'No') = 'No')
!= abs(ifnull(gle.debit, 0) - ifnull(gle.credit, 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"],
"account_type": filters['account_type'],
'mcond': get_match_cond(doctype, searchfield),
'txt': "%%%s%%" % txt,
"start": start,
"page_len": page_len
})
def gl_entry_details(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select gle.voucher_no, gle.posting_date,
gle.%(account_type)s 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 ifnull(gle.%(account_type)s, 0) > 0
and (select ifnull(abs(sum(ifnull(debit, 0)) - sum(ifnull(credit, 0))), 0)
from `tabGL Entry`
where 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)
)
%(mcond)s
ORDER BY gle.posting_date desc, gle.voucher_no desc
limit %(start)s, %(page_len)s""" % {
"dt":filters["dt"],
"acc":filters["acc"],
"account_type": filters['account_type'],
'mcond':get_match_cond(doctype, searchfield),
'txt': "%%%s%%" % txt,
"start": start,
"page_len": page_len
})
def get_new_item_code(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, description from tabItem
where is_stock_item="No" and is_sales_item="Yes"
and name not in (select name from `tabSales BOM`) and %s like %s
%s limit %s, %s""" % (searchfield, "%s",
get_match_cond(doctype, searchfield),"%s", "%s"),
("%%%s%%" % txt, start, page_len))
def get_new_item_code(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, description from tabItem
where is_stock_item="No" and is_sales_item="Yes"
and name not in (select name from `tabSales BOM`) and %s like "%s"
%s limit %s, %s""" % (searchfield, "%s",
get_match_cond(doctype, searchfield),"%s", "%s"),
("%%%s%%" % txt, start, page_len))
def get_lead(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql ("""select `tabLead`.name, `tabLead`.lead_name FROM `tabLead`
where `tabLead`.%(key)s like "%(txt)s"
%(mcond)s
order by `tabLead`.`name` asc
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 webnotes.conn.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, searchfield),
"%(start)s, %(page_len)s"), {"txt": "%%%s%%" % txt, "start":
start, "page_len": page_len}, as_list=True)
def query_purchase_return_doc(doctype, txt, searchfield, start, page_len, filters):
return webnotes.conn.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, searchfield),
"%(start)s, %(page_len)s"), {"txt": "%%%s%%" % txt, "start":
start, "page_len": page_len}, as_list=True)
def get_project(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.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 item_details(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, item_name, description from `tabItem`
where name in ( select item_code FROM `tabDelivery Note Item`
where parent= %s
and ifnull(qty, 0) > ifnull(packed_qty, 0))
and %s like "%s" %s
limit %s, %s """ % ("%s", searchfield, "%s",
get_match_cond(doctype, searchfield), "%s", "%s"),
(filters["delivery_note"], "%%%s%%" % txt, start, page_len))
def item_details(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, item_name, description from `tabItem`
where name in ( select item_code FROM `tabDelivery Note Item`
where parent= %s
and ifnull(qty, 0) > ifnull(packed_qty, 0))
and %s like "%s" %s
limit %s, %s """ % ("%s", searchfield, "%s",
get_match_cond(doctype, searchfield), "%s", "%s"),
(filters["delivery_note"], "%%%s%%" % txt, start, page_len))
def get_lead(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql(""" select name, lead_name from `tabLead`
where docstatus < 2
and (%(key)s like "%(txt)s"
or lead_name like "%(txt)s"
or company_name like "%(txt)s")
%(mcond)s
order by lead_name asc
limit %(start)s, %(page_len)s """ % {'key': searchfield,'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start,
'page_len': page_len})
def get_lead(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql(""" select name, lead_name from `tabLead`
where docstatus < 2
and (%(key)s like "%(txt)s"
or lead_name like "%(txt)s"
or company_name like "%(txt)s")
%(mcond)s
order by lead_name asc
limit %(start)s, %(page_len)s """ % {'key': searchfield,'txt': "%%%s%%" % txt,
'mcond':get_match_cond(doctype, searchfield), 'start': start,
'page_len': page_len})
def get_income_account(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.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 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_user(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabProfile`
where ifnull(enabled, 0)=1
and docstatus < 2
and (%(key)s like "%(txt)s"
or concat_ws(' ', first_name, middle_name, last_name) like "%(txt)s")
%(mcond)s
limit %(start)s, %(page_len)s """ % {'key': searchfield,
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
def get_user(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.sql("""select name, concat_ws(' ', first_name, middle_name, last_name)
from `tabProfile`
where ifnull(enabled, 0)=1
and docstatus < 2
and (%(key)s like "%(txt)s"
or concat_ws(' ', first_name, middle_name, last_name) like "%(txt)s")
%(mcond)s
limit %(start)s, %(page_len)s """ % {'key': searchfield,
'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
'start': start, 'page_len': page_len})
def get_project(doctype, txt, searchfield, start, page_len, filters):
from controllers.queries import get_match_cond
return webnotes.conn.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 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["item_code"],
"s_warehouse": filters["s_warehouse"],
"posting_date": filters["posting_date"],
"txt": "%%%s%%" % txt,
"mcond": get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len,
}
if filters.get("s_warehouse"):
batch_nos = webnotes.conn.sql(
"""select batch_no
from `tabStock Ledger Entry` sle
where item_code = '%(item_code)s'
and warehouse = '%(s_warehouse)s'
and ifnull(is_cancelled, 'No') = 'No'
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 webnotes.conn.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 query_sales_return_doc(doctype, txt, searchfield, start, page_len, filters):
conditions = ""
if doctype == "Sales Invoice":
conditions = "and update_stock=1"
return webnotes.conn.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, searchfield), "%(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 webnotes.conn.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, searchfield), "%(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 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 webnotes.conn.sql("""select tabAccount.name from `tabAccount`
where (tabAccount.debit_or_credit="Debit"
or tabAccount.account_type = "Expense 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 controllers.queries import get_match_cond
return webnotes.conn.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 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 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 webnotes.conn.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_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['item_code'],
's_warehouse': filters['s_warehouse'],
'posting_date': filters['posting_date'],
'txt': "%%%s%%" % txt,
'mcond': get_match_cond(doctype, searchfield),
"start": start,
"page_len": page_len
}
if filters.get("s_warehouse"):
batch_nos = webnotes.conn.sql("""select batch_no
from `tabStock Ledger Entry` sle
where item_code = '%(item_code)s'
and warehouse = '%(s_warehouse)s'
and ifnull(is_cancelled, 'No') = 'No'
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 webnotes.conn.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)