Example #1
0
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})
Example #2
0
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})
Example #3
0
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
		})
Example #5
0
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))
Example #6
0
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))
Example #7
0
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})
Example #8
0
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)
Example #9
0
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)
Example #10
0
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})
Example #11
0
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))
Example #12
0
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))
Example #13
0
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})
Example #15
0
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})
Example #17
0
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})
Example #18
0
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
        })
Example #19
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["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
        )
Example #20
0
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)
Example #21
0
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)
Example #22
0
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)})
Example #23
0
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)})
Example #25
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['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)