def _query(self, query): if self.term.value: values = utils.commasplit(self.term.value) colname = self.colname() if len(values) > 1: query = query.sub_expr(conjunction='OR') for value in values: query.where('%s ILIKE %%s' % colname, dbobj.wild(value)) else: query.where('%s ILIKE %%s' % colname, dbobj.wild(self.term.value))
def query(self): query = globals.db.query('bulletins', order_by='post_date') if self.title: or_query = query.sub_expr('OR') or_query.where('title ILIKE %s', dbobj.wild(self.title)) or_query.where('synopsis ILIKE %s', dbobj.wild(self.title)) if self.group_id: query.join('JOIN group_bulletins USING (bulletin_id)') query.where('group_id = %s', int(self.group_id)) if self.before: query.where('post_date < %s', self.before) if self.after: query.where('post_date > %s', self.after) return query
def query(self): query = globals.db.query('workqueues', order_by='name') query.where('user_id is null AND unit_id is null') if self.name: name_like = dbobj.wild(self.name) query.where('name ILIKE %s or description ILIKE %s', name_like, name_like) return query
def query(self): query = globals.db.query('workqueues', order_by = 'name') query.where('user_id is null AND unit_id is null') if self.name: name_like = dbobj.wild(self.name) query.where('name ILIKE %s or description ILIKE %s', name_like, name_like) return query
def q(query, field): value = getattr(self, field) if value: if value == '!': query.where('persons.%s IS NULL' % field) else: query.where('persons.%s ILIKE %%s' % field, dbobj.wild(value))
def new_query(self): self.query = dbobj.Query(self.foreign_table_desc, order_by = self.search_col) if self.search_term: if type(self.search_col) not in (list, tuple): cols = [self.search_col] else: cols = self.search_col for col in cols: self.query.where('%s ILIKE %%s' % col, dbobj.wild(self.search_term))
def search(self): self.clear_search_result() try: query = self.db.query(self.table) if self.filter: query.where(self.filter) if self.term: query.where('%s ilike %%s' % self.col, dbobj.wild(self.term)) self.result = query.fetchall(limit=100) except dbobj.DatabaseError, e: self.search_error = str(e)
def query(self): query = globals.db.query('forms', order_by = 'label') if self.form: form_like = dbobj.wild(self.form) query.where('forms.label ILIKE %s or forms.name ILIKE %s', form_like, form_like) if self.syndrome_id: query.join('JOIN syndrome_forms' ' ON (syndrome_forms.form_label = forms.label)') query.where('syndrome_forms.syndrome_id = %s', int(self.syndrome_id)) return query
def query(self): query = globals.db.query('syndrome_types', order_by='name') if self.name: query.where('name ILIKE %s', dbobj.wild(self.name)) if self.enabled == 'enabled': query.where('enabled = True') elif self.enabled == 'disabled': query.where('enabled = False') if self.group_id: query.join('JOIN group_syndromes USING (syndrome_id)') query.where('group_id = %s', int(self.group_id)) return query
def query(self): query = globals.db.query('forms', order_by='label') if self.form: form_like = dbobj.wild(self.form) query.where('forms.label ILIKE %s or forms.name ILIKE %s', form_like, form_like) if self.syndrome_id: query.join('JOIN syndrome_forms' ' ON (syndrome_forms.form_label = forms.label)') query.where('syndrome_forms.syndrome_id = %s', int(self.syndrome_id)) return query
def query(self): query = globals.db.query('users', order_by='users.username') if self.enabled == 'enabled': query.where('users.enabled') elif self.enabled == 'disabled': query.where('not users.enabled') if self.enabled == 'deleted': query.where('users.deleted') if self.name and self.name[-1] not in '*%': self.name += '*' else: query.where('not users.deleted') if self.name: query.where('users.username ILIKE %s', dbobj.wild(self.name)) if self.fullname: query.where('users.fullname ILIKE %s', dbobj.wild(self.fullname)) if self.title: query.where('users.title ILIKE %s', dbobj.wild(self.title)) if self.unit: query.join('JOIN unit_users USING (user_id)') query.join('JOIN units USING (unit_id)') query.where('units.name ILIKE %s', dbobj.wild(self.unit)) if self.sponsor: query.join('JOIN users AS su' ' ON (su.user_id = users.sponsoring_user_id)') or_expr = query.sub_expr('OR') or_expr.where('su.username ILIKE %s', dbobj.wild(self.sponsor)) or_expr.where('su.fullname ILIKE %s', dbobj.wild(self.sponsor)) return query
def search(self): self.clear_search_result() try: table_dict = self.pt_set.get_slave_cache() table_dict.query(order_by = self.label_col) if self.search_term: table_dict.where('%s ilike %%s' % self.label_col, dbobj.wild(self.search_term)) if self.filter: table_dict.where(self.filter) self.pt_available = table_dict.fetch_preload(limit=100) except dbobj.DatabaseError, e: self.search_error = str(e)
def search_one(query, term, keycol, *cols): assert len(cols) > 0 try: return int(term) except ValueError: if len(cols) > 1: likequery = query.sub_expr('OR') else: likequery = query for col in cols: likequery.where('%s ILIKE %%s' % col, dbobj.wild(term)) keys = query.fetchcols(keycol) if not keys: abort('%r not found' % term) if len(keys) > 1: labels = query.fetchcols(cols[0]) abort('%r matches multiple values: %s' % (term, ', '.join(labels))) return keys[0]
def new_query(self, term=None): self.reset() self.term = term self.query = globals.db.query('users', order_by=self.order_by) #self.query.where('enabled') self.query.where('not deleted') self.query.where('privacy IS NOT NULL') self.query.where("privacy != '!'") if self.term: if '*' in self.term: term_like = dbobj.wild(self.term) else: term_like = '%%%s%%' % self.term orquery = self.query.sub_expr('OR') orquery.where('fullname ILIKE %s', term_like) orquery.where('username ILIKE %s', term_like) orquery.where('title ILIKE %s', term_like) orquery.where('agency ILIKE %s', term_like) orquery.where('expertise ILIKE %s', term_like)
self.DOB, self.DOB_prec) except datetime.Error, e: raise Error('Date of birth/age: %s' % e) if sex: and_query.where('persons.sex ilike %s', self.sex) else: if self.surname or self.given_names or sex or self.DOB: and_query = or_query.sub_expr(conjunction='AND') names = [] if self.surname: names.extend(self.surname.split()) if self.given_names: names.extend(self.given_names.split()) if names: for word in names: word = dbobj.wild(word) name_expr = and_query.sub_expr(conjunction='OR') name_expr.where('surname ILIKE %s', word) name_expr.where('given_names ILIKE %s', word) try: agelib.dob_query(and_query, 'persons.DOB', self.DOB, self.DOB_prec) except datetime.Error, e: raise Error('Date of birth/age: %s' % e) if sex: and_query.where('persons.sex ilike %s', self.sex) q(or_query, 'home_phone') q(or_query, 'work_phone') q(or_query, 'mobile_phone') q(or_query, 'fax_phone') q(or_query, 'e_mail')