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):
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('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('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 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('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 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)
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')
