blob: ed44f9b02dd9f1ece57d6dfa6c8acd9b59bdbee8 [file] [log] [blame]
Rushabh Mehtae67d1fb2013-08-05 14:59:54 +05301# Copyright (c) 2013, Web Notes Technologies Pvt. Ltd.
2# License: GNU General Public License v3. See license.txt
Saurabh02875592013-07-08 18:45:55 +05303
4from __future__ import unicode_literals
5import webnotes
Anand Doshi36b63a42013-09-03 15:33:56 +05306from webnotes.widgets.reportview import get_match_cond
Saurabh02875592013-07-08 18:45:55 +05307
8def get_filters_cond(doctype, filters, conditions):
9 if filters:
10 if isinstance(filters, dict):
Saurabhf52dc072013-07-10 13:07:49 +053011 filters = filters.items()
12 flt = []
13 for f in filters:
Anand Doshi17350b82013-08-01 15:45:23 +053014 if isinstance(f[1], basestring) and f[1][0] == '!':
Saurabhf52dc072013-07-10 13:07:49 +053015 flt.append([doctype, f[0], '!=', f[1][1:]])
16 else:
17 flt.append([doctype, f[0], '=', f[1]])
Saurabh02875592013-07-08 18:45:55 +053018
19 from webnotes.widgets.reportview import build_filter_conditions
Saurabh2deca5f2013-07-10 14:31:29 +053020 build_filter_conditions(flt, conditions)
Saurabh02875592013-07-08 18:45:55 +053021 cond = ' and ' + ' and '.join(conditions)
22 else:
23 cond = ''
24 return cond
25
Saurabh02875592013-07-08 18:45:55 +053026 # searches for active employees
27def employee_query(doctype, txt, searchfield, start, page_len, filters):
28 return webnotes.conn.sql("""select name, employee_name from `tabEmployee`
Saurabha29b6922013-07-10 16:06:31 +053029 where status = 'Active'
30 and docstatus < 2
31 and (%(key)s like "%(txt)s"
32 or employee_name like "%(txt)s")
33 %(mcond)s
Saurabh02875592013-07-08 18:45:55 +053034 order by
35 case when name like "%(txt)s" then 0 else 1 end,
36 case when employee_name like "%(txt)s" then 0 else 1 end,
Saurabha29b6922013-07-10 16:06:31 +053037 name
38 limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
Saurabh02875592013-07-08 18:45:55 +053039 'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
40
41 # searches for leads which are not converted
42def lead_query(doctype, txt, searchfield, start, page_len, filters):
43 return webnotes.conn.sql("""select name, lead_name, company_name from `tabLead`
Saurabha29b6922013-07-10 16:06:31 +053044 where docstatus < 2
45 and ifnull(status, '') != 'Converted'
46 and (%(key)s like "%(txt)s"
47 or lead_name like "%(txt)s"
48 or company_name like "%(txt)s")
49 %(mcond)s
Saurabh02875592013-07-08 18:45:55 +053050 order by
51 case when name like "%(txt)s" then 0 else 1 end,
52 case when lead_name like "%(txt)s" then 0 else 1 end,
53 case when company_name like "%(txt)s" then 0 else 1 end,
Saurabha29b6922013-07-10 16:06:31 +053054 lead_name asc
55 limit %(start)s, %(page_len)s""" % {'key': searchfield, 'txt': "%%%s%%" % txt,
Saurabh02875592013-07-08 18:45:55 +053056 'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
57
58 # searches for customer
59def customer_query(doctype, txt, searchfield, start, page_len, filters):
60 cust_master_name = webnotes.defaults.get_user_default("cust_master_name")
Saurabhf52dc072013-07-10 13:07:49 +053061
Saurabh02875592013-07-08 18:45:55 +053062 if cust_master_name == "Customer Name":
63 fields = ["name", "customer_group", "territory"]
64 else:
65 fields = ["name", "customer_name", "customer_group", "territory"]
Saurabhf52dc072013-07-10 13:07:49 +053066
Saurabh02875592013-07-08 18:45:55 +053067 fields = ", ".join(fields)
68
Saurabha29b6922013-07-10 16:06:31 +053069 return webnotes.conn.sql("""select %(field)s from `tabCustomer`
70 where docstatus < 2
71 and (%(key)s like "%(txt)s"
72 or customer_name like "%(txt)s")
73 %(mcond)s
Saurabh02875592013-07-08 18:45:55 +053074 order by
75 case when name like "%(txt)s" then 0 else 1 end,
76 case when customer_name like "%(txt)s" then 0 else 1 end,
Saurabha29b6922013-07-10 16:06:31 +053077 name, customer_name
78 limit %(start)s, %(page_len)s""" % {'field': fields,'key': searchfield,
Saurabh02875592013-07-08 18:45:55 +053079 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
80 'start': start, 'page_len': page_len})
81
82# searches for supplier
83def supplier_query(doctype, txt, searchfield, start, page_len, filters):
84 supp_master_name = webnotes.defaults.get_user_default("supp_master_name")
85 if supp_master_name == "Supplier Name":
86 fields = ["name", "supplier_type"]
87 else:
88 fields = ["name", "supplier_name", "supplier_type"]
89 fields = ", ".join(fields)
90
Saurabha29b6922013-07-10 16:06:31 +053091 return webnotes.conn.sql("""select %(field)s from `tabSupplier`
92 where docstatus < 2
93 and (%(key)s like "%(txt)s"
94 or supplier_name like "%(txt)s")
95 %(mcond)s
Saurabh02875592013-07-08 18:45:55 +053096 order by
97 case when name like "%(txt)s" then 0 else 1 end,
98 case when supplier_name like "%(txt)s" then 0 else 1 end,
Saurabha29b6922013-07-10 16:06:31 +053099 name, supplier_name
100 limit %(start)s, %(page_len)s """ % {'field': fields,'key': searchfield,
Saurabh02875592013-07-08 18:45:55 +0530101 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield), 'start': start,
102 'page_len': page_len})
Nabin Hait9a380ef2013-07-16 17:24:17 +0530103
104def tax_account_query(doctype, txt, searchfield, start, page_len, filters):
105 return webnotes.conn.sql("""select name, parent_account, debit_or_credit
106 from tabAccount
107 where tabAccount.docstatus!=2
108 and (account_type in (%s) or
109 (ifnull(is_pl_account, 'No') = 'Yes' and debit_or_credit = %s) )
110 and group_or_ledger = 'Ledger'
111 and company = %s
112 and `%s` LIKE %s
113 limit %s, %s""" %
114 (", ".join(['%s']*len(filters.get("account_type"))),
115 "%s", "%s", searchfield, "%s", "%s", "%s"),
116 tuple(filters.get("account_type") + [filters.get("debit_or_credit"),
117 filters.get("company"), "%%%s%%" % txt, start, page_len]))
Saurabh02875592013-07-08 18:45:55 +0530118
119def item_query(doctype, txt, searchfield, start, page_len, filters):
120 conditions = []
Saurabhf52dc072013-07-10 13:07:49 +0530121
Saurabh02875592013-07-08 18:45:55 +0530122 return webnotes.conn.sql("""select tabItem.name,
123 if(length(tabItem.item_name) > 40,
124 concat(substr(tabItem.item_name, 1, 40), "..."), item_name) as item_name,
125 if(length(tabItem.description) > 40, \
126 concat(substr(tabItem.description, 1, 40), "..."), description) as decription
Saurabha29b6922013-07-10 16:06:31 +0530127 from tabItem
Anand Doshi9eb28682013-07-29 13:07:30 +0530128 where tabItem.docstatus<2
Saurabha29b6922013-07-10 16:06:31 +0530129 and (tabItem.%(key)s LIKE "%(txt)s"
130 or tabItem.item_name LIKE "%(txt)s")
131 %(fcond)s %(mcond)s
132 limit %(start)s,%(page_len)s """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
Saurabh02875592013-07-08 18:45:55 +0530133 'fcond': get_filters_cond(doctype, filters, conditions),
Anand Doshi9eb28682013-07-29 13:07:30 +0530134 'mcond': get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
Saurabh02875592013-07-08 18:45:55 +0530135
136def bom(doctype, txt, searchfield, start, page_len, filters):
137 conditions = []
Saurabhf52dc072013-07-10 13:07:49 +0530138
Saurabh02875592013-07-08 18:45:55 +0530139 return webnotes.conn.sql("""select tabBOM.name, tabBOM.item
140 from tabBOM
Saurabha29b6922013-07-10 16:06:31 +0530141 where tabBOM.docstatus=1
142 and tabBOM.is_active=1
Nabin Haitcfecd2b2013-07-11 17:49:18 +0530143 and tabBOM.%(key)s like "%(txt)s"
Saurabha29b6922013-07-10 16:06:31 +0530144 %(fcond)s %(mcond)s
145 limit %(start)s, %(page_len)s """ % {'key': searchfield, 'txt': "%%%s%%" % txt,
146 'fcond': get_filters_cond(doctype, filters, conditions),
147 'mcond':get_match_cond(doctype, searchfield), 'start': start, 'page_len': page_len})
Saurabh02875592013-07-08 18:45:55 +0530148
Saurabh02875592013-07-08 18:45:55 +0530149def get_project_name(doctype, txt, searchfield, start, page_len, filters):
150 cond = ''
151 if filters['customer']:
Saurabhab462d22013-07-09 16:18:52 +0530152 cond = '(`tabProject`.customer = "' + filters['customer'] + '" or ifnull(`tabProject`.customer,"")="") and'
Saurabha29b6922013-07-10 16:06:31 +0530153
Saurabh02875592013-07-08 18:45:55 +0530154 return webnotes.conn.sql("""select `tabProject`.name from `tabProject`
Saurabha29b6922013-07-10 16:06:31 +0530155 where `tabProject`.status not in ("Completed", "Cancelled")
156 and %(cond)s `tabProject`.name like "%(txt)s" %(mcond)s
157 order by `tabProject`.name asc
158 limit %(start)s, %(page_len)s """ % {'cond': cond,'txt': "%%%s%%" % txt,
Nabin Haitda46a372013-07-15 17:52:10 +0530159 'mcond':get_match_cond(doctype, searchfield),'start': start, 'page_len': page_len})
Anand Doshi17350b82013-08-01 15:45:23 +0530160
161def get_delivery_notes_to_be_billed(doctype, txt, searchfield, start, page_len, filters):
162 return webnotes.conn.sql("""select `tabDelivery Note`.name, `tabDelivery Note`.customer_name
163 from `tabDelivery Note`
Anand Doshi1dadf352013-09-03 16:21:01 +0530164 where `tabDelivery Note`.`%(key)s` like %(txt)s and
165 `tabDelivery Note`.docstatus = 1 %(fcond)s and
Anand Doshi17350b82013-08-01 15:45:23 +0530166 (ifnull((select sum(qty) from `tabDelivery Note Item` where
167 `tabDelivery Note Item`.parent=`tabDelivery Note`.name), 0) >
168 ifnull((select sum(qty) from `tabSales Invoice Item` where
Anand Doshi1dadf352013-09-03 16:21:01 +0530169 `tabSales Invoice Item`.docstatus = 1 and
Anand Doshi17350b82013-08-01 15:45:23 +0530170 `tabSales Invoice Item`.delivery_note=`tabDelivery Note`.name), 0))
171 %(mcond)s order by `tabDelivery Note`.`%(key)s` asc
172 limit %(start)s, %(page_len)s""" % {
173 "key": searchfield,
174 "fcond": get_filters_cond(doctype, filters, []),
175 "mcond": get_match_cond(doctype),
176 "start": "%(start)s", "page_len": "%(page_len)s", "txt": "%(txt)s"
Nabin Haitd1fd1e22013-10-18 12:29:11 +0530177 }, { "start": start, "page_len": page_len, "txt": ("%%%s%%" % txt) })
178
179def get_batch_no(doctype, txt, searchfield, start, page_len, filters):
180 from controllers.queries import get_match_cond
181
182 if filters.has_key('warehouse'):
183 return webnotes.conn.sql("""select batch_no from `tabStock Ledger Entry` sle
184 where item_code = '%(item_code)s'
185 and warehouse = '%(warehouse)s'
186 and batch_no like '%(txt)s'
187 and exists(select * from `tabBatch`
188 where name = sle.batch_no
189 and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
190 and docstatus != 2)
191 %(mcond)s
192 group by batch_no having sum(actual_qty) > 0
193 order by batch_no desc
194 limit %(start)s, %(page_len)s """ % {'item_code': filters['item_code'],
195 'warehouse': filters['warehouse'], 'posting_date': filters['posting_date'],
196 'txt': "%%%s%%" % txt, 'mcond':get_match_cond(doctype, searchfield),
197 'start': start, 'page_len': page_len})
198 else:
199 return webnotes.conn.sql("""select name from tabBatch
200 where docstatus != 2
201 and item = '%(item_code)s'
202 and (ifnull(expiry_date, '')='' or expiry_date >= '%(posting_date)s')
203 and name like '%(txt)s'
204 %(mcond)s
205 order by name desc
206 limit %(start)s, %(page_len)s""" % {'item_code': filters['item_code'],
207 'posting_date': filters['posting_date'], 'txt': "%%%s%%" % txt,
208 'mcond':get_match_cond(doctype, searchfield),'start': start,
209 'page_len': page_len})