def read_persons_from_pracsoft_file(filename=None, encoding='ascii'):
pats_file = io.open(filename, mode='rt', encoding=encoding)
dtos = []
for line in pats_file:
if len(line) < PATIENTS_IN_line_len:
continue # perhaps raise Exception ?
dto = gmPerson.cDTO_person()
dto.external_ids = [{
'PracSoft No.': line[0:9].strip(),
'issuer': 'AU PracSoft application'
}, {
'CRN': line[166:180].replace(' ', ''),
'issuer': 'Centrelink (AU)'
}, {
'DVA': line[180:194].replace(' ', ''),
'issuer': "Department of Veteran's Affairs (AU)"
}, {
'AU-Medicare': line[153:166].replace(' ', ''),
'issuer': 'HIC (AU)'
}]
dto.title = gmTools.capitalize(line[9:14].strip(), gmTools.CAPS_FIRST)
dto.firstnames = gmTools.capitalize(line[44:74].strip(),
gmTools.CAPS_NAMES)
dto.lastnames = gmTools.capitalize(line[14:44].strip(),
gmTools.CAPS_NAMES)
dto.gender = line[223].lower()
dob = time.strptime(line[143:153].strip(), PATIENTS_IN_dob_format)
dto.dob = pyDT.datetime(dob.tm_year,
dob.tm_mon,
dob.tm_mday,
tzinfo=gmDateTime.gmCurrentLocalTimezone)
# this is the home address
dto.street = gmTools.capitalize(line[74:114].strip(),
gmTools.CAPS_FIRST)
dto.zip = line[139:143].strip()
dto.urb = line[114:139].strip()
dto.comms = [ # types must correspond to GNUmed database comm type
{
'homephone': line[194:208].replace(' ', '')
}, {
'workphone': line[208:222].replace(' ', '')
}
]
dto.pracsoft_billing_flag = line[222] # P=pensioner R=repatriation
dtos.append(dto)
return dtos
def __queries_for_firstname_with_comma(self, raw):
"""Generate search queries for [ , <alpha> ] search terms."""
if regex.match(",\s*\w+$", raw.strip()) is None:
return []
_log.debug("[%s]: a firstname" % raw)
tmp = self._normalize_soundalikes(raw.strip(' ,'))
cmd = """
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT *, %(match)s AS match_type FROM ((
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.firstnames ~ %(first)s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.firstnames ~ %(first_w_caps)s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE lower(dem.names.firstnames) ~ lower(%(first)s) and d_vap.pk_identity = dem.names.id_identity
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list"""
args = {
'match': _('first name'),
'first': '^' + tmp,
'first_w_caps': '^' + gmTools.capitalize(tmp, mode = gmTools.CAPS_NAMES)
}
return [{'cmd': cmd, 'args': args}]
def __queries_for_LASTNAME(self, raw):
"""Generate search queries for [ <ALPHA> ] search terms."""
if regex.match("\w+$", raw) is None:
return []
if raw != raw.upper():
# not all UPPERCASE
return []
_log.debug("[%s]: a lastname" % raw)
tmp = self._normalize_soundalikes(raw)
cmd = """
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT *, %(match)s AS match_type FROM ((
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.lastnames ~ %(last_w_caps)s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE lower(dem.names.lastnames) ~ lower(%(last)s) and d_vap.pk_identity = dem.names.id_identity
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list"""
args = {
'match': _('last name'),
'last': '^' + tmp,
'last_w_caps': '^' + gmTools.capitalize(tmp.lower(), mode=gmTools.CAPS_NAMES)
}
return [{'cmd': cmd, 'args': args}]
def __queries_for_LASTNAME(self, raw):
"""Generate search queries for [ <ALPHA> ] search terms."""
if regex.match("\w+$", raw) is None:
return []
if raw != raw.upper():
# not all UPPERCASE
return []
_log.debug("[%s]: a lastname" % raw)
tmp = self._normalize_soundalikes(raw)
cmd = """
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT *, %(match)s AS match_type FROM ((
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.lastnames ~ %(last_w_caps)s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE lower(dem.names.lastnames) ~ lower(%(last)s) and d_vap.pk_identity = dem.names.id_identity
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list"""
args = {
'match': _('last name'),
'last': '^' + tmp,
'last_w_caps': '^' + gmTools.capitalize(tmp.lower(), mode=gmTools.CAPS_NAMES)
}
return [{'cmd': cmd, 'args': args}]
def __queries_for_firstname_with_comma(self, raw):
"""Generate search queries for [ , <alpha> ] search terms."""
if regex.match(",\s*\w+$", raw.strip()) is None:
return []
_log.debug("[%s]: a firstname" % raw)
tmp = self._normalize_soundalikes(raw.strip(' ,'))
cmd = """
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT *, %(match)s AS match_type FROM ((
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.firstnames ~ %(first)s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.firstnames ~ %(first_w_caps)s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE lower(dem.names.firstnames) ~ lower(%(first)s) and d_vap.pk_identity = dem.names.id_identity
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list"""
args = {
'match': _('first name'),
'first': '^' + tmp,
'first_w_caps': '^' + gmTools.capitalize(tmp, mode = gmTools.CAPS_NAMES)
}
return [{'cmd': cmd, 'args': args}]
def read_persons_from_pracsoft_file(filename=None, encoding='ascii'):
pats_file = io.open(filename, mode = 'rt', encoding = encoding)
dtos = []
for line in pats_file:
if len(line) < PATIENTS_IN_line_len:
continue # perhaps raise Exception ?
dto = gmPerson.cDTO_person()
dto.external_ids = [
{'PracSoft No.': line[0:9].strip(), 'issuer': 'AU PracSoft application'},
{'CRN': line[166:180].replace(' ', ''), 'issuer': 'Centrelink (AU)'},
{'DVA': line[180:194].replace(' ', ''), 'issuer': "Department of Veteran's Affairs (AU)"},
{'AU-Medicare': line[153:166].replace(' ', ''), 'issuer': 'HIC (AU)'}
]
dto.title = gmTools.capitalize(line[9:14].strip(), gmTools.CAPS_FIRST)
dto.firstnames = gmTools.capitalize(line[44:74].strip(), gmTools.CAPS_NAMES)
dto.lastnames = gmTools.capitalize(line[14:44].strip(), gmTools.CAPS_NAMES)
dto.gender = line[223].lower()
dob = time.strptime(line[143:153].strip(), PATIENTS_IN_dob_format)
dto.dob = pyDT.datetime(dob.tm_year, dob.tm_mon, dob.tm_mday, tzinfo = gmDateTime.gmCurrentLocalTimezone)
# this is the home address
dto.street = gmTools.capitalize(line[74:114].strip(), gmTools.CAPS_FIRST)
dto.zip = line[139:143].strip()
dto.urb = line[114:139].strip()
dto.comms = [ # types must correspond to GNUmed database comm type
{'homephone': line[194:208].replace(' ', '')},
{'workphone': line[208:222].replace(' ', '')}
]
dto.pracsoft_billing_flag = line[222] # P=pensioner R=repatriation
dtos.append(dto)
return dtos
def _on_text_update (self, event): """Internal handler for wx.EVT_TEXT. Called when text was changed by user or by SetValue(). """ if self.suppress_text_update_smarts: self.suppress_text_update_smarts = False return self._adjust_data_after_text_update() self._current_match_candidates = [] val = self.GetValue().strip() ins_point = self.GetInsertionPoint() # if empty string then hide list dropdown window # we also don't need a timer event then if val == u'': self._hide_picklist() self.__timer.Stop() else: new_val = gmTools.capitalize(text = val, mode = self.capitalisation_mode) if new_val != val: self.suppress_text_update_smarts = True super(cPhraseWheelBase, self).SetValue(new_val) if ins_point > len(new_val): self.SetInsertionPointEnd() else: self.SetInsertionPoint(ins_point) # FIXME: SetSelection() ? # start timer for delayed match retrieval self.__timer.Start(oneShot = True, milliseconds = self.picklist_delay) # notify interested parties for callback in self._on_modified_callbacks: callback() return
def _generate_queries_de(self, search_term=None):
if search_term is None:
return []
# check to see if we get away with a simple query ...
queries = self._generate_simple_query(search_term)
if len(queries) > 0:
_log.debug('[%s]: search term with a simple, unambigous structure' % search_term)
return queries
# no we don't
_log.debug('[%s]: not a search term with a simple, unambigous structure' % search_term)
search_term = search_term.strip().strip(',').strip(';').strip()
normalized = self._normalize_soundalikes(search_term)
queries = []
# "<CHARS>" - single name part
# yes, I know, this is culture specific (did you read the docs ?)
if regex.match("^(\s|\t)*[a-zäöüßéáúóçøA-ZÄÖÜÇØ]+(\s|\t)*$", search_term):
_log.debug("[%s]: a single name part", search_term)
# there's no intermediate whitespace due to the regex
cmd = """
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT * FROM ((
-- lastname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.lastnames) ~* lower(%s)
) union all (
-- firstname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s)
) union all (
-- nickname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s)
) union all (
-- anywhere in name
SELECT
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames || ' ' || coalesce(n.preferred, '')) ~* lower(%s)
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list
"""
tmp = normalized.strip()
args = []
args.append(_('lastname'))
args.append('^' + tmp)
args.append(_('firstname'))
args.append('^' + tmp)
args.append(_('nickname'))
args.append('^' + tmp)
args.append(_('any name part'))
args.append(tmp)
queries.append ({
'cmd': cmd,
'args': args
})
return queries
# try to split on (major) part separators
major_parts = regex.split(',|;', normalized)
# ignore empty parts
major_parts = [ p.strip() for p in major_parts if p.strip() != '' ]
# only one "major" part ? (i.e. no ",;" ?)
if len(major_parts) == 1:
_log.debug('[%s]: only one non-empty part after splitting by , or ; ("major" part)', normalized)
queries = self.__generate_queries_from_single_major_part(part = normalized)
if len(queries) > 0:
return queries
return self._generate_dumb_brute_query(search_term)
# more than one major part (separated by ';,')
# this else is not needed
else:
_log.debug('[%s]: more than one non-empty part after splitting by , or ; ("major" parts)', normalized)
# parse into name and date parts
date_parts = []
name_parts = []
name_count = 0
for part in major_parts:
if part.strip() == '':
continue
# any digits ?
if regex.search("\d+", part):
# FIXME: parse out whitespace *not* adjacent to a *word*
date_parts.append(part)
else:
tmp = part.strip()
tmp = regex.split("\s+|\t+", tmp)
name_count = name_count + len(tmp)
name_parts.append(tmp)
_log.debug('found %s character (name) parts and %s number (date ?) parts', len(name_parts), len(date_parts))
where_parts = []
# first, handle name parts
# special case: "<date(s)>, <name> <name>, <date(s)>"
if (len(name_parts) == 1) and (name_count == 2):
# usually "first last"
where_parts.append ({
'conditions': "firstnames ~ %s and lastnames ~ %s",
'args': [_('names: first last'), '^' + gmTools.capitalize(name_parts[0][0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0][1], mode=gmTools.CAPS_NAMES)]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [_('names: first last'), '^' + name_parts[0][0], '^' + name_parts[0][1]]
})
# but sometimes "last first""
where_parts.append ({
'conditions': "firstnames ~ %s and lastnames ~ %s",
'args': [_('names: last, first'), '^' + gmTools.capitalize(name_parts[0][1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0][0], mode=gmTools.CAPS_NAMES)]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [_('names: last, first'), '^' + name_parts[0][1], '^' + name_parts[0][0]]
})
# or even substrings anywhere in name
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s) OR lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), name_parts[0][0], name_parts[0][1]]
})
# special case: "<date(s)>, <name(s)>, <name(s)>, <date(s)>"
elif len(name_parts) == 2:
# usually "last, first"
where_parts.append ({
'conditions': "firstnames ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[1])), '^' + ' '.join(map(gmTools.capitalize, name_parts[0]))]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[1]), '^' + ' '.join(name_parts[0])]
})
# but sometimes "first, last"
where_parts.append ({
'conditions': "firstnames ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[0])), '^' + ' '.join(map(gmTools.capitalize, name_parts[1]))]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[0]), '^' + ' '.join(name_parts[1])]
})
# and sometimes "last, nick"
where_parts.append ({
'conditions': "preferred ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[1])), '^' + ' '.join(map(gmTools.capitalize, name_parts[0]))]
})
where_parts.append ({
'conditions': "lower(preferred) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[1]), '^' + ' '.join(name_parts[0])]
})
# or even substrings anywhere in name
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s) AND lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), ' '.join(name_parts[0]), ' '.join(name_parts[1])]
})
# big trouble - arbitrary number of names
else:
# FIXME: deep magic, not sure of rationale ...
if len(name_parts) == 1:
for part in name_parts[0]:
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
else:
tmp = []
for part in name_parts:
tmp.append(' '.join(part))
for part in tmp:
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
# secondly handle date parts
# FIXME: this needs a considerable smart-up !
if len(date_parts) == 1:
if len(where_parts) == 0:
where_parts.append ({
'conditions': "dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('date of birth'), date_parts[0].replace(',', '.')]
})
if len(where_parts) > 0:
where_parts[0]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[0]['args'].append(date_parts[0].replace(',', '.'))
where_parts[0]['args'][0] += ', ' + _('date of birth')
if len(where_parts) > 1:
where_parts[1]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[1]['args'].append(date_parts[0].replace(',', '.'))
where_parts[1]['args'][0] += ', ' + _('date of birth')
elif len(date_parts) > 1:
if len(where_parts) == 0:
where_parts.append ({
'conditions': "dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('date of birth/death'), date_parts[0].replace(',', '.'), date_parts[1].replace(',', '.')]
})
if len(where_parts) > 0:
where_parts[0]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[0]['args'].append(date_parts[0].replace(',', '.'), date_parts[1].replace(',', '.'))
where_parts[0]['args'][0] += ', ' + _('date of birth/death')
if len(where_parts) > 1:
where_parts[1]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[1]['args'].append(date_parts[0].replace(',', '.'), date_parts[1].replace(',', '.'))
where_parts[1]['args'][0] += ', ' + _('date of birth/death')
# and finally generate the queries ...
for where_part in where_parts:
queries.append ({
'cmd': "SELECT *, %%s::text AS match_type FROM dem.v_active_persons WHERE %s" % where_part['conditions'],
'args': where_part['args']
})
return queries
return []
def __generate_queries_from_single_major_part(self, part=None):
# split on whitespace
parts_list = regex.split("\s+|\t+", part)
# ignore empty parts
parts_list = [ p.strip() for p in parts_list if p.strip() != '' ]
# parse into name/date parts
date_count = 0
name_parts = []
for part in parts_list:
# any digit signifies a date, FIXME: what about "<40" ?
if regex.search("\d", part):
date_count = date_count + 1
date_part = part
else:
name_parts.append(part)
# exactly 1 word ?
if len(parts_list) == 1:
return []
# exactly 2 words ?
if len(parts_list) == 2:
if date_count > 0:
# FIXME: either "name date" or "date date"
_log.error("don't know how to generate queries for [%s]" % search_term)
return []
# no date = "first last" or "last first"
queries = []
# assumption: first last
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [_('name: first-last'), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: first-last'), '^' + name_parts[0], '^' + name_parts[1]]
})
# assumption: last first
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [_('name: last-first'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: last-first'), '^' + name_parts[1], '^' + name_parts[0]]
})
# assumption: last nick
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.preferred ~ %s AND n.lastnames ~ %s",
'args': [_('name: last-nick'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: last-nick'), '^' + name_parts[1], '^' + name_parts[0]]
})
# name parts anywhere inside name - third order query ...
queries.append ({
'cmd': """SELECT DISTINCT ON (id_identity)
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
-- name_parts[0]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
-- name_parts[1]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)""",
'args': [_('name'), name_parts[0], name_parts[1]]
})
return queries
# exactly 3 words ?
if len(parts_list) == 3:
if date_count != 1:
# FIXME: "name name name" or "name date date"
return []
# special case: 3 words, exactly 1 of them a date, no ",;"
# assumption: first, last, dob - first order
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: first-last, date of birth'), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), date_part.replace(',', '.')]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: first-last, date of birth'), '^' + name_parts[0], '^' + name_parts[1], date_part.replace(',', '.')]
})
# assumption: last, first, dob - second order query
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: last-first, date of birth'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), date_part.replace(',', '.')]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: last-first, dob'), '^' + name_parts[1], '^' + name_parts[0], date_part.replace(',', '.')]
})
# name parts anywhere in name - third order query ...
queries.append ({
'cmd': """SELECT DISTINCT ON (id_identity)
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)
""",
'args': [_('name, date of birth'), name_parts[0], name_parts[1], date_part.replace(',', '.')]
})
return queries
return []
def __generate_queries_from_single_major_part(self, part=None):
# split on whitespace
parts_list = regex.split("\s+|\t+", part)
# ignore empty parts
parts_list = [ p.strip() for p in parts_list if p.strip() != '' ]
# parse into name/date parts
date_count = 0
name_parts = []
for part in parts_list:
# any digit signifies a date, FIXME: what about "<40" ?
if regex.search("\d", part):
date_count = date_count + 1
date_part = part
else:
name_parts.append(part)
# exactly 1 word ?
if len(parts_list) == 1:
return []
# exactly 2 words ?
if len(parts_list) == 2:
if date_count > 0:
# FIXME: either "name date" or "date date"
_log.error("don't know how to generate queries for [%s]" % search_term)
return []
# no date = "first last" or "last first"
queries = []
# assumption: first last
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [_('name: first-last'), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: first-last'), '^' + name_parts[0], '^' + name_parts[1]]
})
# assumption: last first
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [_('name: last-first'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: last-first'), '^' + name_parts[1], '^' + name_parts[0]]
})
# assumption: last nick
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.preferred ~ %s AND n.lastnames ~ %s",
'args': [_('name: last-nick'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: last-nick'), '^' + name_parts[1], '^' + name_parts[0]]
})
# name parts anywhere inside name - third order query ...
queries.append ({
'cmd': """SELECT DISTINCT ON (id_identity)
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
-- name_parts[0]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
-- name_parts[1]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)""",
'args': [_('name'), name_parts[0], name_parts[1]]
})
return queries
# exactly 3 words ?
if len(parts_list) == 3:
if date_count != 1:
# FIXME: "name name name" or "name date date"
return []
# special case: 3 words, exactly 1 of them a date, no ",;"
# assumption: first, last, dob - first order
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: first-last, date of birth'), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), date_part.replace(',', '.')]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: first-last, date of birth'), '^' + name_parts[0], '^' + name_parts[1], date_part.replace(',', '.')]
})
# assumption: last, first, dob - second order query
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: last-first, date of birth'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), date_part.replace(',', '.')]
})
queries.append ({
'cmd': "SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: last-first, dob'), '^' + name_parts[1], '^' + name_parts[0], date_part.replace(',', '.')]
})
# name parts anywhere in name - third order query ...
queries.append ({
'cmd': """SELECT DISTINCT ON (id_identity)
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)
""",
'args': [_('name, date of birth'), name_parts[0], name_parts[1], date_part.replace(',', '.')]
})
return queries
return []
def _generate_queries_de(self, search_term=None):
if search_term is None:
return []
# check to see if we get away with a simple query ...
queries = self._generate_simple_query(search_term)
if len(queries) > 0:
_log.debug('[%s]: search term with a simple, unambigous structure' % search_term)
return queries
# no we don't
_log.debug('[%s]: not a search term with a simple, unambigous structure' % search_term)
search_term = search_term.strip().strip(',').strip(';').strip()
normalized = self._normalize_soundalikes(search_term)
queries = []
# "<CHARS>" - single name part
# yes, I know, this is culture specific (did you read the docs ?)
if regex.match("^(\s|\t)*[a-zäöüßéáúóçøA-ZÄÖÜÇØ]+(\s|\t)*$", search_term):
_log.debug("[%s]: a single name part", search_term)
# there's no intermediate whitespace due to the regex
cmd = """
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT * FROM ((
-- lastname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.lastnames) ~* lower(%s)
) union all (
-- firstname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s)
) union all (
-- nickname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s)
) union all (
-- anywhere in name
SELECT
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames || ' ' || coalesce(n.preferred, '')) ~* lower(%s)
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list
"""
tmp = normalized.strip()
args = []
args.append(_('lastname'))
args.append('^' + tmp)
args.append(_('firstname'))
args.append('^' + tmp)
args.append(_('nickname'))
args.append('^' + tmp)
args.append(_('any name part'))
args.append(tmp)
queries.append ({
'cmd': cmd,
'args': args
})
return queries
# try to split on (major) part separators
major_parts = regex.split(',|;', normalized)
# ignore empty parts
major_parts = [ p.strip() for p in major_parts if p.strip() != '' ]
# only one "major" part ? (i.e. no ",;" ?)
if len(major_parts) == 1:
_log.debug('[%s]: only one non-empty part after splitting by , or ; ("major" part)', normalized)
queries = self.__generate_queries_from_single_major_part(part = normalized)
if len(queries) > 0:
return queries
return self._generate_dumb_brute_query(search_term)
# more than one major part (separated by ';,')
# this else is not needed
else:
_log.debug('[%s]: more than one non-empty part after splitting by , or ; ("major" parts)', normalized)
# parse into name and date parts
date_parts = []
name_parts = []
name_count = 0
for part in major_parts:
if part.strip() == '':
continue
# any digits ?
if regex.search("\d+", part):
# FIXME: parse out whitespace *not* adjacent to a *word*
date_parts.append(part)
else:
tmp = part.strip()
tmp = regex.split("\s+|\t+", tmp)
name_count = name_count + len(tmp)
name_parts.append(tmp)
_log.debug('found %s character (name) parts and %s number (date ?) parts', len(name_parts), len(date_parts))
where_parts = []
# first, handle name parts
# special case: "<date(s)>, <name> <name>, <date(s)>"
if (len(name_parts) == 1) and (name_count == 2):
# usually "first last"
where_parts.append ({
'conditions': "firstnames ~ %s and lastnames ~ %s",
'args': [_('names: first last'), '^' + gmTools.capitalize(name_parts[0][0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0][1], mode=gmTools.CAPS_NAMES)]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [_('names: first last'), '^' + name_parts[0][0], '^' + name_parts[0][1]]
})
# but sometimes "last first""
where_parts.append ({
'conditions': "firstnames ~ %s and lastnames ~ %s",
'args': [_('names: last, first'), '^' + gmTools.capitalize(name_parts[0][1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0][0], mode=gmTools.CAPS_NAMES)]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [_('names: last, first'), '^' + name_parts[0][1], '^' + name_parts[0][0]]
})
# or even substrings anywhere in name
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s) OR lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), name_parts[0][0], name_parts[0][1]]
})
# special case: "<date(s)>, <name(s)>, <name(s)>, <date(s)>"
elif len(name_parts) == 2:
# usually "last, first"
where_parts.append ({
'conditions': "firstnames ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[1])), '^' + ' '.join(map(gmTools.capitalize, name_parts[0]))]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[1]), '^' + ' '.join(name_parts[0])]
})
# but sometimes "first, last"
where_parts.append ({
'conditions': "firstnames ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[0])), '^' + ' '.join(map(gmTools.capitalize, name_parts[1]))]
})
where_parts.append ({
'conditions': "lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[0]), '^' + ' '.join(name_parts[1])]
})
# and sometimes "last, nick"
where_parts.append ({
'conditions': "preferred ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[1])), '^' + ' '.join(map(gmTools.capitalize, name_parts[0]))]
})
where_parts.append ({
'conditions': "lower(preferred) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[1]), '^' + ' '.join(name_parts[0])]
})
# or even substrings anywhere in name
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s) AND lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), ' '.join(name_parts[0]), ' '.join(name_parts[1])]
})
# big trouble - arbitrary number of names
else:
# FIXME: deep magic, not sure of rationale ...
if len(name_parts) == 1:
for part in name_parts[0]:
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
else:
tmp = []
for part in name_parts:
tmp.append(' '.join(part))
for part in tmp:
where_parts.append ({
'conditions': "lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
# secondly handle date parts
# FIXME: this needs a considerable smart-up !
if len(date_parts) == 1:
if len(where_parts) == 0:
where_parts.append ({
'conditions': "dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('date of birth'), date_parts[0].replace(',', '.')]
})
if len(where_parts) > 0:
where_parts[0]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[0]['args'].append(date_parts[0].replace(',', '.'))
where_parts[0]['args'][0] += ', ' + _('date of birth')
if len(where_parts) > 1:
where_parts[1]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[1]['args'].append(date_parts[0].replace(',', '.'))
where_parts[1]['args'][0] += ', ' + _('date of birth')
elif len(date_parts) > 1:
if len(where_parts) == 0:
where_parts.append ({
'conditions': "dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('date of birth/death'), date_parts[0].replace(',', '.'), date_parts[1].replace(',', '.')]
})
if len(where_parts) > 0:
where_parts[0]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[0]['args'].append(date_parts[0].replace(',', '.'), date_parts[1].replace(',', '.'))
where_parts[0]['args'][0] += ', ' + _('date of birth/death')
if len(where_parts) > 1:
where_parts[1]['conditions'] += " AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[1]['args'].append(date_parts[0].replace(',', '.'), date_parts[1].replace(',', '.'))
where_parts[1]['args'][0] += ', ' + _('date of birth/death')
# and finally generate the queries ...
for where_part in where_parts:
queries.append ({
'cmd': "SELECT *, %%s::text AS match_type FROM dem.v_active_persons WHERE %s" % where_part['conditions'],
'args': where_part['args']
})
return queries
return []
def _generate_queries_de(self, search_term=None):
if search_term is None:
return []
# check to see if we get away with a simple query ...
queries = self._generate_simple_query(search_term)
if len(queries) > 0:
_log.debug(
'[%s]: search term with a simple, unambigous structure' %
search_term)
return queries
# no we don't
_log.debug(
'[%s]: not a search term with a simple, unambigous structure' %
search_term)
search_term = search_term.strip().strip(u',').strip(u';').strip()
normalized = self._normalize_soundalikes(search_term)
queries = []
# "<CHARS>" - single name part
# yes, I know, this is culture specific (did you read the docs ?)
if regex.match(u"^(\s|\t)*[a-zäöüßéáúóçøA-ZÄÖÜÇØ]+(\s|\t)*$",
search_term,
flags=regex.UNICODE):
_log.debug("[%s]: a single name part", search_term)
# there's no intermediate whitespace due to the regex
cmd = u"""
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT * FROM ((
-- lastname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.lastnames) ~* lower(%s)
) union all (
-- firstname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s)
) union all (
-- nickname
SELECT d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n
WHERE d_vap.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s)
) union all (
-- anywhere in name
SELECT
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames || ' ' || coalesce(n.preferred, '')) ~* lower(%s)
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list
"""
tmp = normalized.strip()
args = []
args.append(_('lastname'))
args.append('^' + tmp)
args.append(_('firstname'))
args.append('^' + tmp)
args.append(_('nickname'))
args.append('^' + tmp)
args.append(_('any name part'))
args.append(tmp)
queries.append({'cmd': cmd, 'args': args})
return queries
# try to split on (major) part separators
parts_list = regex.split(u",|;", normalized)
# ignore empty parts
parts_list = [p.strip() for p in parts_list if p.strip() != u'']
# only one "major" part ? (i.e. no ",;" ?)
if len(parts_list) == 1:
# re-split on whitespace
sub_parts_list = regex.split(u"\s*|\t*", normalized)
# ignore empty parts
sub_parts_list = [
p.strip() for p in sub_parts_list if p.strip() != u''
]
# parse into name/date parts
date_count = 0
name_parts = []
for part in sub_parts_list:
# skip empty parts
if part.strip() == u'':
continue
# any digit signifies a date
# FIXME: what about "<40" ?
if regex.search(u"\d", part, flags=regex.UNICODE):
date_count = date_count + 1
date_part = part
else:
name_parts.append(part)
# exactly 2 words ?
if len(sub_parts_list) == 2:
# no date = "first last" or "last first"
if date_count == 0:
# assumption: first last
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [
_('name: first-last'), '^' + gmTools.capitalize(
name_parts[0], mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[1],
mode=gmTools.CAPS_NAMES)
]
})
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [
_('name: first-last'), '^' + name_parts[0],
'^' + name_parts[1]
]
})
# assumption: last first
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [
_('name: last-first'), '^' + gmTools.capitalize(
name_parts[1], mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[0],
mode=gmTools.CAPS_NAMES)
]
})
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [
_('name: last-first'), '^' + name_parts[1],
'^' + name_parts[0]
]
})
# assumption: last nick
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.preferred ~ %s AND n.lastnames ~ %s",
'args': [
_('name: last-nick'), '^' + gmTools.capitalize(
name_parts[1], mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[0],
mode=gmTools.CAPS_NAMES)
]
})
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [
_('name: last-nick'), '^' + name_parts[1],
'^' + name_parts[0]
]
})
# name parts anywhere inside name - third order query ...
queries.append({
'cmd':
u"""SELECT DISTINCT ON (id_identity)
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
-- name_parts[0]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
-- name_parts[1]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)""",
'args': [_('name'), name_parts[0], name_parts[1]]
})
return queries
# FIXME: either "name date" or "date date"
_log.error("don't know how to generate queries for [%s]" %
search_term)
return queries
# exactly 3 words ?
if len(sub_parts_list) == 3:
# special case: 3 words, exactly 1 of them a date, no ",;"
if date_count == 1:
# assumption: first, last, dob - first order
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [
_('names: first-last, date of birth'),
'^' + gmTools.capitalize(name_parts[0],
mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[1],
mode=gmTools.CAPS_NAMES),
date_part.replace(u',', u'.')
]
})
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [
_('names: first-last, date of birth'),
'^' + name_parts[0], '^' + name_parts[1],
date_part.replace(u',', u'.')
]
})
# assumption: last, first, dob - second order query
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [
_('names: last-first, date of birth'),
'^' + gmTools.capitalize(name_parts[1],
mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[0],
mode=gmTools.CAPS_NAMES),
date_part.replace(u',', u'.')
]
})
queries.append({
'cmd':
u"SELECT DISTINCT ON (id_identity) d_vap.*, %s::text AS match_type FROM dem.v_active_persons d_vap, dem.names n WHERE d_vap.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [
_('names: last-first, dob'), '^' + name_parts[1],
'^' + name_parts[0],
date_part.replace(u',', u'.')
]
})
# name parts anywhere in name - third order query ...
queries.append({
'cmd':
u"""SELECT DISTINCT ON (id_identity)
d_vap.*,
%s::text AS match_type
FROM
dem.v_active_persons d_vap,
dem.names n
WHERE
d_vap.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)
""",
'args': [
_('name, date of birth'), name_parts[0],
name_parts[1],
date_part.replace(u',', u'.')
]
})
return queries
# FIXME: "name name name" or "name date date"
queries.append(self._generate_dumb_brute_query(search_term))
return queries
# FIXME: no ',;' but neither "name name" nor "name name date"
queries.append(self._generate_dumb_brute_query(search_term))
return queries
# more than one major part (separated by ';,')
else:
# parse into name and date parts
date_parts = []
name_parts = []
name_count = 0
for part in parts_list:
if part.strip() == u'':
continue
# any digits ?
if regex.search(u"\d+", part, flags=regex.UNICODE):
# FIXME: parse out whitespace *not* adjacent to a *word*
date_parts.append(part)
else:
tmp = part.strip()
tmp = regex.split(u"\s*|\t*", tmp)
name_count = name_count + len(tmp)
name_parts.append(tmp)
where_parts = []
# first, handle name parts
# special case: "<date(s)>, <name> <name>, <date(s)>"
if (len(name_parts) == 1) and (name_count == 2):
# usually "first last"
where_parts.append({
'conditions':
u"firstnames ~ %s and lastnames ~ %s",
'args': [
_('names: first last'), '^' + gmTools.capitalize(
name_parts[0][0], mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[0][1],
mode=gmTools.CAPS_NAMES)
]
})
where_parts.append({
'conditions':
u"lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [
_('names: first last'), '^' + name_parts[0][0],
'^' + name_parts[0][1]
]
})
# but sometimes "last first""
where_parts.append({
'conditions':
u"firstnames ~ %s and lastnames ~ %s",
'args': [
_('names: last, first'), '^' + gmTools.capitalize(
name_parts[0][1], mode=gmTools.CAPS_NAMES),
'^' + gmTools.capitalize(name_parts[0][0],
mode=gmTools.CAPS_NAMES)
]
})
where_parts.append({
'conditions':
u"lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [
_('names: last, first'), '^' + name_parts[0][1],
'^' + name_parts[0][0]
]
})
# or even substrings anywhere in name
where_parts.append({
'conditions':
u"lower(firstnames || ' ' || lastnames) ~* lower(%s) OR lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), name_parts[0][0], name_parts[0][1]]
})
# special case: "<date(s)>, <name(s)>, <name(s)>, <date(s)>"
elif len(name_parts) == 2:
# usually "last, first"
where_parts.append({
'conditions':
u"firstnames ~ %s AND lastnames ~ %s",
'args': [
_('name: last, first'),
'^' + ' '.join(map(gmTools.capitalize, name_parts[1])),
'^' + ' '.join(map(gmTools.capitalize, name_parts[0]))
]
})
where_parts.append({
'conditions':
u"lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [
_('name: last, first'), '^' + ' '.join(name_parts[1]),
'^' + ' '.join(name_parts[0])
]
})
# but sometimes "first, last"
where_parts.append({
'conditions':
u"firstnames ~ %s AND lastnames ~ %s",
'args': [
_('name: last, first'),
'^' + ' '.join(map(gmTools.capitalize, name_parts[0])),
'^' + ' '.join(map(gmTools.capitalize, name_parts[1]))
]
})
where_parts.append({
'conditions':
u"lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [
_('name: last, first'), '^' + ' '.join(name_parts[0]),
'^' + ' '.join(name_parts[1])
]
})
# and sometimes "last, nick"
where_parts.append({
'conditions':
u"preferred ~ %s AND lastnames ~ %s",
'args': [
_('name: last, first'),
'^' + ' '.join(map(gmTools.capitalize, name_parts[1])),
'^' + ' '.join(map(gmTools.capitalize, name_parts[0]))
]
})
where_parts.append({
'conditions':
u"lower(preferred) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [
_('name: last, first'), '^' + ' '.join(name_parts[1]),
'^' + ' '.join(name_parts[0])
]
})
# or even substrings anywhere in name
where_parts.append({
'conditions':
u"lower(firstnames || ' ' || lastnames) ~* lower(%s) AND lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [
_('name'), ' '.join(name_parts[0]),
' '.join(name_parts[1])
]
})
# big trouble - arbitrary number of names
else:
# FIXME: deep magic, not sure of rationale ...
if len(name_parts) == 1:
for part in name_parts[0]:
where_parts.append({
'conditions':
u"lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
else:
tmp = []
for part in name_parts:
tmp.append(' '.join(part))
for part in tmp:
where_parts.append({
'conditions':
u"lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
# secondly handle date parts
# FIXME: this needs a considerable smart-up !
if len(date_parts) == 1:
if len(where_parts) == 0:
where_parts.append({
'conditions':
u"dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [
_('date of birth'),
date_parts[0].replace(u',', u'.')
]
})
if len(where_parts) > 0:
where_parts[0][
'conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[0]['args'].append(date_parts[0].replace(
u',', u'.'))
where_parts[0]['args'][0] += u', ' + _('date of birth')
if len(where_parts) > 1:
where_parts[1][
'conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[1]['args'].append(date_parts[0].replace(
u',', u'.'))
where_parts[1]['args'][0] += u', ' + _('date of birth')
elif len(date_parts) > 1:
if len(where_parts) == 0:
where_parts.append({
'conditions':
u"dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [
_('date of birth/death'),
date_parts[0].replace(u',', u'.'),
date_parts[1].replace(u',', u'.')
]
})
if len(where_parts) > 0:
where_parts[0][
'conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[0]['args'].append(
date_parts[0].replace(u',', u'.'),
date_parts[1].replace(u',', u'.'))
where_parts[0]['args'][0] += u', ' + _(
'date of birth/death')
if len(where_parts) > 1:
where_parts[1][
'conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[1]['args'].append(
date_parts[0].replace(u',', u'.'),
date_parts[1].replace(u',', u'.'))
where_parts[1]['args'][0] += u', ' + _(
'date of birth/death')
# and finally generate the queries ...
for where_part in where_parts:
queries.append({
'cmd':
u"SELECT *, %%s::text AS match_type FROM dem.v_active_persons WHERE %s"
% where_part['conditions'],
'args':
where_part['args']
})
return queries
return []
def _generate_simple_query(self, raw):
"""Compose queries if search term seems unambigous."""
queries = []
raw = raw.strip().rstrip(u',').rstrip(u';').strip()
# "<digits>" - GNUmed patient PK or DOB
if regex.match(u"^(\s|\t)*\d+(\s|\t)*$", raw, flags=regex.UNICODE):
_log.debug("[%s]: a PK or DOB" % raw)
tmp = raw.strip()
queries.append({
'cmd':
u"SELECT *, %s::text AS match_type FROM dem.v_active_persons WHERE pk_identity = %s ORDER BY lastnames, firstnames, dob",
'args': [_('internal patient ID'), tmp]
})
if len(tmp) > 7: # DOB needs at least 8 digits
queries.append({
'cmd':
u"SELECT *, %s::text AS match_type FROM dem.v_active_persons WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'),
tmp.replace(',', '.')]
})
queries.append({
'cmd': u"""
SELECT vba.*, %s::text AS match_type
FROM
dem.lnk_identity2ext_id li2ext_id,
dem.v_active_persons vba
WHERE
vba.pk_identity = li2ext_id.id_identity and lower(li2ext_id.external_id) ~* lower(%s)
ORDER BY
lastnames, firstnames, dob
""",
'args': [_('external patient ID'), tmp]
})
return queries
# "<d igi ts>" - DOB or patient PK
if regex.match(u"^(\d|\s|\t)+$", raw, flags=regex.UNICODE):
_log.debug("[%s]: a DOB or PK" % raw)
queries.append({
'cmd':
u"SELECT *, %s::text AS match_type FROM dem.v_active_persons WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'),
raw.replace(',', '.')]
})
tmp = raw.replace(u' ', u'')
tmp = tmp.replace(u'\t', u'')
queries.append({
'cmd':
u"SELECT *, %s::text AS match_type FROM dem.v_active_persons WHERE pk_identity LIKE %s%%",
'args': [_('internal patient ID'), tmp]
})
return queries
# "#<di git s>" - GNUmed patient PK
if regex.match(u"^(\s|\t)*#(\d|\s|\t)+$", raw, flags=regex.UNICODE):
_log.debug("[%s]: a PK or external ID" % raw)
tmp = raw.replace(u'#', u'')
tmp = tmp.strip()
tmp = tmp.replace(u' ', u'')
tmp = tmp.replace(u'\t', u'')
# this seemingly stupid query ensures the PK actually exists
queries.append({
'cmd':
u"SELECT *, %s::text AS match_type FROM dem.v_active_persons WHERE pk_identity = %s ORDER BY lastnames, firstnames, dob",
'args': [_('internal patient ID'), tmp]
})
# but might also be an external ID
tmp = raw.replace(u'#', u'')
tmp = tmp.strip()
tmp = tmp.replace(u' ', u'***DUMMY***')
tmp = tmp.replace(u'\t', u'***DUMMY***')
tmp = tmp.replace(u'***DUMMY***', u'(\s|\t|-|/)*')
queries.append({
'cmd': u"""
SELECT vba.*, %s::text AS match_type FROM dem.lnk_identity2ext_id li2ext_id, dem.v_active_persons vba
WHERE vba.pk_identity = li2ext_id.id_identity and lower(li2ext_id.external_id) ~* lower(%s)
ORDER BY lastnames, firstnames, dob""",
'args': [_('external patient ID'), tmp]
})
return queries
# "#<di/git s or c-hars>" - external ID
if regex.match(u"^(\s|\t)*#.+$", raw, flags=regex.UNICODE):
_log.debug("[%s]: an external ID" % raw)
tmp = raw.replace(u'#', u'')
tmp = tmp.strip()
tmp = tmp.replace(u' ', u'***DUMMY***')
tmp = tmp.replace(u'\t', u'***DUMMY***')
tmp = tmp.replace(u'-', u'***DUMMY***')
tmp = tmp.replace(u'/', u'***DUMMY***')
tmp = tmp.replace(u'***DUMMY***', u'(\s|\t|-|/)*')
queries.append({
'cmd': u"""
SELECT
vba.*,
%s::text AS match_type
FROM
dem.lnk_identity2ext_id li2ext_id,
dem.v_active_persons vba
WHERE
vba.pk_identity = li2ext_id.id_identity
AND
lower(li2ext_id.external_id) ~* lower(%s)
ORDER BY
lastnames, firstnames, dob""",
'args': [_('external patient ID'), tmp]
})
return queries
# digits interspersed with "./-" or blank space - DOB
if regex.match(
u"^(\s|\t)*\d+(\s|\t|\.|\-|/)*\d+(\s|\t|\.|\-|/)*\d+(\s|\t|\.)*$",
raw,
flags=regex.UNICODE):
_log.debug("[%s]: a DOB" % raw)
tmp = raw.strip()
while u'\t\t' in tmp:
tmp = tmp.replace(u'\t\t', u' ')
while u' ' in tmp:
tmp = tmp.replace(u' ', u' ')
# apparently not needed due to PostgreSQL smarts...
#tmp = tmp.replace('-', '.')
#tmp = tmp.replace('/', '.')
queries.append({
'cmd':
u"SELECT *, %s AS match_type FROM dem.v_active_persons WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'),
tmp.replace(',', '.')]
})
return queries
# " , <alpha>" - first name
if regex.match(u"^(\s|\t)*,(\s|\t)*([^0-9])+(\s|\t)*$",
raw,
flags=regex.UNICODE):
_log.debug("[%s]: a firstname" % raw)
tmp = self._normalize_soundalikes(raw[1:].strip())
cmd = u"""
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT *, %s AS match_type FROM ((
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.firstnames ~ %s and d_vap.pk_identity = dem.names.id_identity
) union all (
SELECT d_vap.*
FROM dem.names, dem.v_active_persons d_vap
WHERE dem.names.firstnames ~ %s and d_vap.pk_identity = dem.names.id_identity
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list"""
queries.append({
'cmd':
cmd,
'args': [
_('first name'),
'^' + gmTools.capitalize(tmp, mode=gmTools.CAPS_NAMES),
'^' + tmp
]
})
return queries
# "*|$<...>" - DOB
if regex.match(u"^(\s|\t)*(\*|\$).+$", raw, flags=regex.UNICODE):
_log.debug("[%s]: a DOB" % raw)
tmp = raw.replace(u'*', u'')
tmp = tmp.replace(u'$', u'')
queries.append({
'cmd':
u"SELECT *, %s AS match_type FROM dem.v_active_persons WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'),
tmp.replace(u',', u'.')]
})
return queries
return queries # = []
def read_persons_from_msva_file(filename=None, encoding=None): if encoding is None: encoding = MSVA_encoding pats_file = io.open(filename, mode = 'rt', encoding = encoding) dtos = [] for line in pats_file: if len(line) < MSVA_line_len: continue # perhaps raise Exception ? dto = gmPerson.cDTO_person() dto.source = 'Med.Manager/CA' dto.firstnames = '%s %s' % ( gmTools.capitalize(line[:20].strip(), gmTools.CAPS_FIRST_ONLY), # should be _NAMES gmTools.capitalize(line[20:22].strip(), gmTools.CAPS_FIRST_ONLY) # should be _NAMES ) dto.lastnames = gmTools.capitalize(line[22:47].strip(), gmTools.CAPS_FIRST_ONLY) # should be _NAMES region = line[59:61] dto.remember_external_id ( name = 'PHN (%s.CA)' % region, value = line[47:57], issuer = 'MOH (%s.CA)' % region ) dob = time.strptime(line[65:73].strip(), MSVA_dob_format) dto.dob = pyDT.datetime(dob.tm_year, dob.tm_mon, dob.tm_mday, tzinfo = gmDateTime.gmCurrentLocalTimezone) dto.gender = line[83].lower() dto.remember_external_id ( name = 'MM (CA) Chart #', value = line[84:92], issuer = 'Medical Manager (CA) application' ) # this is the home address street = '%s // %s' % ( gmTools.capitalize(line[92:117].strip(), gmTools.CAPS_FIRST), gmTools.capitalize(line[117:142].strip(), gmTools.CAPS_FIRST) ) dto.remember_address ( number = '?', street = street, urb = line[142:167], region_code = line[167:169], zip = line[169:178], country_code = 'CA' ) # channel types must correspond to GNUmed database comm type dto.remember_comm_channel(channel = 'homephone', url = line[178:188]) dto.remember_comm_channel(channel = 'workphone', url = line[188:198]) dto.remember_external_id ( name = 'Social Insurance Number', value = line[198:207], issuer = 'Canada' ) dtos.append(dto) pats_file.close() return dtos
def read_persons_from_msva_file(filename=None, encoding=None):
if encoding is None:
encoding = MSVA_encoding
pats_file = io.open(filename, mode='rt', encoding=encoding)
dtos = []
for line in pats_file:
if len(line) < MSVA_line_len:
continue # perhaps raise Exception ?
dto = gmPerson.cDTO_person()
dto.source = 'Med.Manager/CA'
dto.firstnames = '%s %s' % (
gmTools.capitalize(line[:20].strip(),
gmTools.CAPS_FIRST_ONLY), # should be _NAMES
gmTools.capitalize(line[20:22].strip(),
gmTools.CAPS_FIRST_ONLY) # should be _NAMES
)
dto.lastnames = gmTools.capitalize(
line[22:47].strip(), gmTools.CAPS_FIRST_ONLY) # should be _NAMES
region = line[59:61]
dto.remember_external_id(name='PHN (%s.CA)' % region,
value=line[47:57],
issuer='MOH (%s.CA)' % region)
dob = time.strptime(line[65:73].strip(), MSVA_dob_format)
dto.dob = pyDT.datetime(dob.tm_year,
dob.tm_mon,
dob.tm_mday,
tzinfo=gmDateTime.gmCurrentLocalTimezone)
dto.gender = line[83].casefold()
dto.remember_external_id(name='MM (CA) Chart #',
value=line[84:92],
issuer='Medical Manager (CA) application')
# this is the home address
street = '%s // %s' % (
gmTools.capitalize(line[92:117].strip(), gmTools.CAPS_FIRST),
gmTools.capitalize(line[117:142].strip(), gmTools.CAPS_FIRST))
dto.remember_address(number='?',
street=street,
urb=line[142:167],
region_code=line[167:169],
zip=line[169:178],
country_code='CA')
# channel types must correspond to GNUmed database comm type
dto.remember_comm_channel(channel='homephone', url=line[178:188])
dto.remember_comm_channel(channel='workphone', url=line[188:198])
dto.remember_external_id(name='Social Insurance Number',
value=line[198:207],
issuer='Canada')
dtos.append(dto)
pats_file.close()
return dtos
def _generate_simple_query(self, raw):
"""Compose queries if search term seems unambigous."""
queries = []
raw = raw.strip().rstrip(u',').rstrip(u';').strip()
# "<digits>" - GNUmed patient PK or DOB
if regex.match(u"^(\s|\t)*\d+(\s|\t)*$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a PK or DOB" % raw)
tmp = raw.strip()
queries.append ({
'cmd': u"SELECT *, %s::text AS match_type FROM dem.v_basic_person WHERE pk_identity = %s ORDER BY lastnames, firstnames, dob",
'args': [_('internal patient ID'), tmp]
})
if len(tmp) > 7: # DOB needs at least 8 digits
queries.append ({
'cmd': u"SELECT *, %s::text AS match_type FROM dem.v_basic_person WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'), tmp.replace(',', '.')]
})
queries.append ({
'cmd': u"""
SELECT vba.*, %s::text AS match_type
FROM
dem.lnk_identity2ext_id li2ext_id,
dem.v_basic_person vba
WHERE
vba.pk_identity = li2ext_id.id_identity and lower(li2ext_id.external_id) ~* lower(%s)
ORDER BY
lastnames, firstnames, dob
""",
'args': [_('external patient ID'), tmp]
})
return queries
# "<d igi ts>" - DOB or patient PK
if regex.match(u"^(\d|\s|\t)+$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a DOB or PK" % raw)
queries.append ({
'cmd': u"SELECT *, %s::text AS match_type FROM dem.v_basic_person WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'), raw.replace(',', '.')]
})
tmp = raw.replace(u' ', u'')
tmp = tmp.replace(u'\t', u'')
queries.append ({
'cmd': u"SELECT *, %s::text AS match_type FROM dem.v_basic_person WHERE pk_identity LIKE %s%%",
'args': [_('internal patient ID'), tmp]
})
return queries
# "#<di git s>" - GNUmed patient PK
if regex.match(u"^(\s|\t)*#(\d|\s|\t)+$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a PK or external ID" % raw)
tmp = raw.replace(u'#', u'')
tmp = tmp.strip()
tmp = tmp.replace(u' ', u'')
tmp = tmp.replace(u'\t', u'')
# this seemingly stupid query ensures the PK actually exists
queries.append ({
'cmd': u"SELECT *, %s::text AS match_type FROM dem.v_basic_person WHERE pk_identity = %s ORDER BY lastnames, firstnames, dob",
'args': [_('internal patient ID'), tmp]
})
# but might also be an external ID
tmp = raw.replace(u'#', u'')
tmp = tmp.strip()
tmp = tmp.replace(u' ', u'***DUMMY***')
tmp = tmp.replace(u'\t', u'***DUMMY***')
tmp = tmp.replace(u'***DUMMY***', u'(\s|\t|-|/)*')
queries.append ({
'cmd': u"""
SELECT vba.*, %s::text AS match_type FROM dem.lnk_identity2ext_id li2ext_id, dem.v_basic_person vba
WHERE vba.pk_identity = li2ext_id.id_identity and lower(li2ext_id.external_id) ~* lower(%s)
ORDER BY lastnames, firstnames, dob""",
'args': [_('external patient ID'), tmp]
})
return queries
# "#<di/git s or c-hars>" - external ID (or PUPIC)
if regex.match(u"^(\s|\t)*#.+$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: an external ID" % raw)
tmp = raw.replace(u'#', u'')
tmp = tmp.strip()
tmp = tmp.replace(u' ', u'***DUMMY***')
tmp = tmp.replace(u'\t', u'***DUMMY***')
tmp = tmp.replace(u'-', u'***DUMMY***')
tmp = tmp.replace(u'/', u'***DUMMY***')
tmp = tmp.replace(u'***DUMMY***', u'(\s|\t|-|/)*')
queries.append ({
'cmd': u"""
SELECT
vba.*,
%s::text AS match_type
FROM
dem.lnk_identity2ext_id li2ext_id,
dem.v_basic_person vba
WHERE
vba.pk_identity = li2ext_id.id_identity
AND
lower(li2ext_id.external_id) ~* lower(%s)
ORDER BY
lastnames, firstnames, dob""",
'args': [_('external patient ID'), tmp]
})
return queries
# digits interspersed with "./-" or blank space - DOB
if regex.match(u"^(\s|\t)*\d+(\s|\t|\.|\-|/)*\d+(\s|\t|\.|\-|/)*\d+(\s|\t|\.)*$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a DOB" % raw)
tmp = raw.strip()
while u'\t\t' in tmp: tmp = tmp.replace(u'\t\t', u' ')
while u' ' in tmp: tmp = tmp.replace(u' ', u' ')
# apparently not needed due to PostgreSQL smarts...
#tmp = tmp.replace('-', '.')
#tmp = tmp.replace('/', '.')
queries.append ({
'cmd': u"SELECT *, %s AS match_type FROM dem.v_basic_person WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'), tmp.replace(',', '.')]
})
return queries
# " , <alpha>" - first name
if regex.match(u"^(\s|\t)*,(\s|\t)*([^0-9])+(\s|\t)*$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a firstname" % raw)
tmp = self._normalize_soundalikes(raw[1:].strip())
cmd = u"""
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT *, %s AS match_type FROM ((
SELECT vbp.*
FROM dem.names, dem.v_basic_person vbp
WHERE dem.names.firstnames ~ %s and vbp.pk_identity = dem.names.id_identity
) union all (
SELECT vbp.*
FROM dem.names, dem.v_basic_person vbp
WHERE dem.names.firstnames ~ %s and vbp.pk_identity = dem.names.id_identity
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list"""
queries.append ({
'cmd': cmd,
'args': [_('first name'), '^' + gmTools.capitalize(tmp, mode=gmTools.CAPS_NAMES), '^' + tmp]
})
return queries
# "*|$<...>" - DOB
if regex.match(u"^(\s|\t)*(\*|\$).+$", raw, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a DOB" % raw)
tmp = raw.replace(u'*', u'')
tmp = tmp.replace(u'$', u'')
queries.append ({
'cmd': u"SELECT *, %s AS match_type FROM dem.v_basic_person WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) ORDER BY lastnames, firstnames, dob",
'args': [_('date of birth'), tmp.replace(u',', u'.')]
})
return queries
return queries # = []
def _generate_queries_de(self, search_term=None):
if search_term is None:
return []
# check to see if we get away with a simple query ...
queries = self._generate_simple_query(search_term)
if len(queries) > 0:
_log.debug('[%s]: search term with a simple, unambigous structure' % search_term)
return queries
# no we don't
_log.debug('[%s]: not a search term with a simple, unambigous structure' % search_term)
search_term = search_term.strip().strip(u',').strip(u';').strip()
normalized = self._normalize_soundalikes(search_term)
queries = []
# "<CHARS>" - single name part
# yes, I know, this is culture specific (did you read the docs ?)
if regex.match(u"^(\s|\t)*[a-zäöüßéáúóçøA-ZÄÖÜÇØ]+(\s|\t)*$", search_term, flags = regex.LOCALE | regex.UNICODE):
_log.debug("[%s]: a single name part", search_term)
# there's no intermediate whitespace due to the regex
cmd = u"""
SELECT DISTINCT ON (pk_identity) * FROM (
SELECT * FROM ((
-- lastname
SELECT vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n
WHERE vbp.pk_identity = n.id_identity and lower(n.lastnames) ~* lower(%s)
) union all (
-- firstname
SELECT vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n
WHERE vbp.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s)
) union all (
-- nickname
SELECT vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n
WHERE vbp.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s)
) union all (
-- anywhere in name
SELECT
vbp.*,
%s::text AS match_type
FROM
dem.v_basic_person vbp,
dem.names n
WHERE
vbp.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames || ' ' || coalesce(n.preferred, '')) ~* lower(%s)
)) AS super_list ORDER BY lastnames, firstnames, dob
) AS sorted_list
"""
tmp = normalized.strip()
args = []
args.append(_('lastname'))
args.append('^' + tmp)
args.append(_('firstname'))
args.append('^' + tmp)
args.append(_('nickname'))
args.append('^' + tmp)
args.append(_('any name part'))
args.append(tmp)
queries.append ({
'cmd': cmd,
'args': args
})
return queries
# try to split on (major) part separators
parts_list = regex.split(u",|;", normalized)
# ignore empty parts
parts_list = [ p.strip() for p in parts_list if p.strip() != u'' ]
# only one "major" part ? (i.e. no ",;" ?)
if len(parts_list) == 1:
# re-split on whitespace
sub_parts_list = regex.split(u"\s*|\t*", normalized)
# ignore empty parts
sub_parts_list = [ p.strip() for p in sub_parts_list if p.strip() != u'' ]
# parse into name/date parts
date_count = 0
name_parts = []
for part in sub_parts_list:
# skip empty parts
if part.strip() == u'':
continue
# any digit signifies a date
# FIXME: what about "<40" ?
if regex.search(u"\d", part, flags = regex.LOCALE | regex.UNICODE):
date_count = date_count + 1
date_part = part
else:
name_parts.append(part)
# exactly 2 words ?
if len(sub_parts_list) == 2:
# no date = "first last" or "last first"
if date_count == 0:
# assumption: first last
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [_('name: first-last'), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: first-last'), '^' + name_parts[0], '^' + name_parts[1]]
})
# assumption: last first
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s",
'args': [_('name: last-first'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: last-first'), '^' + name_parts[1], '^' + name_parts[0]]
})
print "before nick"
print queries
# assumption: last nick
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and n.preferred ~ %s AND n.lastnames ~ %s",
'args': [_('name: last-nick'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES)]
})
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and lower(n.preferred) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s)",
'args': [_('name: last-nick'), '^' + name_parts[1], '^' + name_parts[0]]
})
print "after nick"
print queries
# name parts anywhere inside name - third order query ...
queries.append ({
'cmd': u"""SELECT DISTINCT ON (id_identity)
vbp.*,
%s::text AS match_type
FROM
dem.v_basic_person vbp,
dem.names n
WHERE
vbp.pk_identity = n.id_identity
AND
-- name_parts[0]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
-- name_parts[1]
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)""",
'args': [_('name'), name_parts[0], name_parts[1]]
})
return queries
# FIXME: either "name date" or "date date"
_log.error("don't know how to generate queries for [%s]" % search_term)
return queries
# exactly 3 words ?
if len(sub_parts_list) == 3:
# special case: 3 words, exactly 1 of them a date, no ",;"
if date_count == 1:
# assumption: first, last, dob - first order
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: first-last, date of birth'), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), date_part.replace(u',', u'.')]
})
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: first-last, date of birth'), '^' + name_parts[0], '^' + name_parts[1], date_part.replace(u',', u'.')]
})
# assumption: last, first, dob - second order query
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and n.firstnames ~ %s AND n.lastnames ~ %s AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: last-first, date of birth'), '^' + gmTools.capitalize(name_parts[1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0], mode=gmTools.CAPS_NAMES), date_part.replace(u',', u'.')]
})
queries.append ({
'cmd': u"SELECT DISTINCT ON (id_identity) vbp.*, %s::text AS match_type FROM dem.v_basic_person vbp, dem.names n WHERE vbp.pk_identity = n.id_identity and lower(n.firstnames) ~* lower(%s) AND lower(n.lastnames) ~* lower(%s) AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('names: last-first, dob'), '^' + name_parts[1], '^' + name_parts[0], date_part.replace(u',', u'.')]
})
# name parts anywhere in name - third order query ...
queries.append ({
'cmd': u"""SELECT DISTINCT ON (id_identity)
vbp.*,
%s::text AS match_type
FROM
dem.v_basic_person vbp,
dem.names n
WHERE
vbp.pk_identity = n.id_identity
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
lower(n.firstnames || ' ' || n.lastnames) ~* lower(%s)
AND
dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)
""",
'args': [_('name, date of birth'), name_parts[0], name_parts[1], date_part.replace(u',', u'.')]
})
return queries
# FIXME: "name name name" or "name date date"
queries.append(self._generate_dumb_brute_query(search_term))
return queries
# FIXME: no ',;' but neither "name name" nor "name name date"
queries.append(self._generate_dumb_brute_query(search_term))
return queries
# more than one major part (separated by ';,')
else:
# parse into name and date parts
date_parts = []
name_parts = []
name_count = 0
for part in parts_list:
if part.strip() == u'':
continue
# any digits ?
if regex.search(u"\d+", part, flags = regex.LOCALE | regex.UNICODE):
# FIXME: parse out whitespace *not* adjacent to a *word*
date_parts.append(part)
else:
tmp = part.strip()
tmp = regex.split(u"\s*|\t*", tmp)
name_count = name_count + len(tmp)
name_parts.append(tmp)
where_parts = []
# first, handle name parts
# special case: "<date(s)>, <name> <name>, <date(s)>"
if (len(name_parts) == 1) and (name_count == 2):
# usually "first last"
where_parts.append ({
'conditions': u"firstnames ~ %s and lastnames ~ %s",
'args': [_('names: first last'), '^' + gmTools.capitalize(name_parts[0][0], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0][1], mode=gmTools.CAPS_NAMES)]
})
where_parts.append ({
'conditions': u"lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [_('names: first last'), '^' + name_parts[0][0], '^' + name_parts[0][1]]
})
# but sometimes "last first""
where_parts.append ({
'conditions': u"firstnames ~ %s and lastnames ~ %s",
'args': [_('names: last, first'), '^' + gmTools.capitalize(name_parts[0][1], mode=gmTools.CAPS_NAMES), '^' + gmTools.capitalize(name_parts[0][0], mode=gmTools.CAPS_NAMES)]
})
where_parts.append ({
'conditions': u"lower(firstnames) ~* lower(%s) and lower(lastnames) ~* lower(%s)",
'args': [_('names: last, first'), '^' + name_parts[0][1], '^' + name_parts[0][0]]
})
# or even substrings anywhere in name
where_parts.append ({
'conditions': u"lower(firstnames || ' ' || lastnames) ~* lower(%s) OR lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), name_parts[0][0], name_parts[0][1]]
})
# special case: "<date(s)>, <name(s)>, <name(s)>, <date(s)>"
elif len(name_parts) == 2:
# usually "last, first"
where_parts.append ({
'conditions': u"firstnames ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[1])), '^' + ' '.join(map(gmTools.capitalize, name_parts[0]))]
})
where_parts.append ({
'conditions': u"lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[1]), '^' + ' '.join(name_parts[0])]
})
# but sometimes "first, last"
where_parts.append ({
'conditions': u"firstnames ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[0])), '^' + ' '.join(map(gmTools.capitalize, name_parts[1]))]
})
where_parts.append ({
'conditions': u"lower(firstnames) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[0]), '^' + ' '.join(name_parts[1])]
})
# and sometimes "last, nick"
where_parts.append ({
'conditions': u"preferred ~ %s AND lastnames ~ %s",
'args': [_('name: last, first'), '^' + ' '.join(map(gmTools.capitalize, name_parts[1])), '^' + ' '.join(map(gmTools.capitalize, name_parts[0]))]
})
where_parts.append ({
'conditions': u"lower(preferred) ~* lower(%s) AND lower(lastnames) ~* lower(%s)",
'args': [_('name: last, first'), '^' + ' '.join(name_parts[1]), '^' + ' '.join(name_parts[0])]
})
# or even substrings anywhere in name
where_parts.append ({
'conditions': u"lower(firstnames || ' ' || lastnames) ~* lower(%s) AND lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), ' '.join(name_parts[0]), ' '.join(name_parts[1])]
})
# big trouble - arbitrary number of names
else:
# FIXME: deep magic, not sure of rationale ...
if len(name_parts) == 1:
for part in name_parts[0]:
where_parts.append ({
'conditions': u"lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
else:
tmp = []
for part in name_parts:
tmp.append(' '.join(part))
for part in tmp:
where_parts.append ({
'conditions': u"lower(firstnames || ' ' || lastnames) ~* lower(%s)",
'args': [_('name'), part]
})
# secondly handle date parts
# FIXME: this needs a considerable smart-up !
if len(date_parts) == 1:
if len(where_parts) == 0:
where_parts.append ({
'conditions': u"dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('date of birth'), date_parts[0].replace(u',', u'.')]
})
if len(where_parts) > 0:
where_parts[0]['conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[0]['args'].append(date_parts[0].replace(u',', u'.'))
where_parts[0]['args'][0] += u', ' + _('date of birth')
if len(where_parts) > 1:
where_parts[1]['conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)"
where_parts[1]['args'].append(date_parts[0].replace(u',', u'.'))
where_parts[1]['args'][0] += u', ' + _('date of birth')
elif len(date_parts) > 1:
if len(where_parts) == 0:
where_parts.append ({
'conditions': u"dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
'args': [_('date of birth/death'), date_parts[0].replace(u',', u'.'), date_parts[1].replace(u',', u'.')]
})
if len(where_parts) > 0:
where_parts[0]['conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[0]['args'].append(date_parts[0].replace(u',', u'.'), date_parts[1].replace(u',', u'.'))
where_parts[0]['args'][0] += u', ' + _('date of birth/death')
if len(where_parts) > 1:
where_parts[1]['conditions'] += u" AND dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone) AND dem.date_trunc_utc('day'::text, dem.identity.deceased) = dem.date_trunc_utc('day'::text, %s::timestamp with time zone)",
where_parts[1]['args'].append(date_parts[0].replace(u',', u'.'), date_parts[1].replace(u',', u'.'))
where_parts[1]['args'][0] += u', ' + _('date of birth/death')
# and finally generate the queries ...
for where_part in where_parts:
queries.append ({
'cmd': u"SELECT *, %%s::text AS match_type FROM dem.v_basic_person WHERE %s" % where_part['conditions'],
'args': where_part['args']
})
return queries
return []
