""" Classes to handle actions associated with animals
Copyright PR Hardman 2008-2011. All rights reserved.
Licence - see licence.txt
"""
import time
import re
import genlib
from html import animalhtml
class AnimalResources(genlib.Resource):
""" The base Animal and Flock Resource class.
This class provides methods for working with animal and flock resources
from the database tables.
This class should be sub-classed by the applications so that they can
customise the behaviour of their resources.
"""
#
# Flock query templates
#
flock_by_fullname = ("select f.flock_no from %s.flock f "
"where LOWER(f.flock_name) = ?;")
flocks_with_prefix = ("select f.flock_no from %s.flock_prefix f "
"where f.tag_prefix = ?;")
select_flocks_like_name = ("select f.flock_no from %s.flock f "
"where lower(f.sort_name) LIKE ?;")
data_for_list = ("select f.flock_no, f.flock_name, "
"cast('no' as text) as mva, cast('no' as text) as scrapie_mon, "
"cp.tag_prefix, "
"(p.title || ' ' || p.initials || ' ' || p.surname) as owner_name, "
"p.surname "
"from %s.flock f "
"join %s.current_flock_owner co on f.flock_no = co.flock_no "
" join %s.person p on co.owner_person_id = p.person_id "
" join %s.current_flock_prefix cp on f.flock_no = cp.flock_no "
" where f.flock_no in %s order by %s %s;")
# These fields must be selected by the data_for_list query
list_order = {'flkno': 'flock_no', 'name': 'flock_name',
'mva': 'mva', 'smon': 'scrapie_mon', 'prefix': 'tag_prefix',
'owner': 'surname'}
# +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++#
# Animal Query templates
#
#~ ped_query = ("select * from %s.sheep_data s where s.regn_no = ?;")
ped_query = ("SELECT s.*, of.flock_name as org_flock_name, "
"rf.flock_name as regn_flock_name, et.tag_prefix, "
"et.indiv_no as tag_no, ot.tag_prefix as original_tag_prefix, "
"ot.indiv_no as original_tag_no, ei.eid, i.result_code, "
"p.allele_1, p.allele_2 "
"FROM %s.sheep s JOIN %s.flock of ON s.originating_flock = of.flock_no "
"JOIN %s.flock rf ON s.registering_flock = rf.flock_no "
"JOIN (select * from %s.current_ear_tag where regn_no = ?) et "
"ON s.regn_no = et.regn_no "
"JOIN (select * from %s.original_ear_tag where regn_no = ?) ot "
"ON s.regn_no = ot.regn_no "
"LEFT JOIN (select * from %s.current_eid where regn_no = ?) ei "
"ON s.regn_no = ei.regn_no "
"LEFT JOIN (select * from %s.current_inspection where regn_no = ?) i "
"ON s.regn_no = i.regn_no "
"LEFT JOIN (select * from %s.current_prp where regn_no = ?) p "
"ON s.regn_no = p.regn_no "
"where s.regn_no = ?;")
progeny_root = ("select * from %s.sheep_data s where s.regn_no = ?;")
progeny_prog_sire = ("select s.regn_no, s.sort_dob, s.sex "
"from %s.sheep s where s.sire_no = ? order by s.regn_no;")
progeny_prog_dam = ("select s.regn_no, s.sort_dob, s.sex "
"from %s.sheep s where s.dam_no = ? order by s.regn_no;")
# This query is in the progeny loop so must be optimised - the use of
# sheep_progeny_data view is too slow on SQLite since that engine only
# applies the 'where' clause after all the data has been collected.
progeny_data = ("SELECT s.*, of.flock_name as org_flock_name, "
"i.result_code, p.allele_1, p.allele_2 "
"FROM %s.sheep s "
"JOIN %s.flock of ON s.originating_flock = of.flock_no "
"LEFT JOIN (select * from %s.current_inspection where regn_no = ?) i "
"ON s.regn_no = i.regn_no "
"LEFT JOIN (select * from %s.current_prp where regn_no = ?) p "
"ON s.regn_no = p.regn_no "
"where s.regn_no = ?;")
get_animal = ("select s.regn_no from %s.sheep s where s.regn_no = ?;")
regn_no_from_tag = ("select e.regn_no from %s.ear_tag e "
"where e.tag_prefix = ? and e.indiv_no = ? "
"order by e.regn_no;")
regn_no_from_num_tag = ("select e.regn_no from %s.ear_tag e "
"where e.tag_prefix = ? and e.num_indiv_no = ? "
"order by e.regn_no;")
animals_with_prefix = ("select e.regn_no from %s.ear_tag e "
"where e.tag_prefix = ? order by e.regn_no;")
regn_no_from_name = ("select s.regn_no from %s.sheep s "
"where LOWER(s.sheep_name) LIKE ? "
"order by s.regn_no;")
regn_no_from_fullname = ("select s.regn_no from %s.sheep s "
"where s.originating_flock = ? "
"and LOWER(s.sheep_name) LIKE ? "
"and s.register_code <> 'X' "
"order by s.regn_no;")
regn_no_from_flock = ("select s.regn_no from %s.sheep s "
"where s.originating_flock = ? "
"and s.register_code <> 'X' "
"order by s.regn_no;")
def __init__(self, config):
""" Initialise the instance attributes """
super(AnimalResources, self).__init__(config)
print "Instantiating AnimalResources"
# Callables, primarily to allow for different species
self.html_view_page = animalhtml.sheep_view_html
self.html_form_page = animalhtml.sheep_form_html
# HTML/XML templates and unconditional fragments
self.pedcert_tmpl_name = 'pedcert_tmpl.odt'
def __del__(self):
""" Clean up """
print "AnimalResources instance about to be destroyed"
super(AnimalResources, self).__del__()
def parse_id(self, search_arg):
""" Present to allow this module to be compiled.
Must be replaced with a breed specific method
"""
return '', {}
def get_regn_nos_from_arg(self, arg):
""" Return a list of registration numbers matching a parsed search arg
The form of the parsed arg is breed dependant and is defined in the
breed's parse_id() method.
"""
regn_nos = self.dblib.query_column(self.get_animal % self.schema, (arg,))
return regn_nos
def find_animal(self, search_arg):
""" Return a list of registration numbers matching the search string.
The search string may be a registration number, an ear tag or a full
or partial name.
Return a message if a non-fatal error is found.
"""
dup = ''
regn_nos = []
if re.search("[0-9]", search_arg):
# Assume it's an regn_no or tag - pass any exception upwards
dup, parse_data = self.parse_id(search_arg)
if parse_data['idmode'] == self.ID_MODE_REGN_NO:
# Handle a 'registration number'
regn_nos = self.get_regn_nos_from_arg(parse_data['leader'])
elif parse_data['idmode'] == self.ID_MODE_TAG:
# Handle an ear tag - lookup the regn number from an ear-tag
if parse_data['trailer'].isdigit():
regn_nos = self.dblib.query_column(self.regn_no_from_num_tag %
self.schema, (parse_data['leader'], parse_data['trailer']))
else:
regn_nos = self.dblib.query_column(self.regn_no_from_tag %
self.schema, (parse_data['leader'], parse_data['trailer']))
else:
# Handle a Prefix
regn_nos = self.dblib.query_column(self.animals_with_prefix %
self.schema, (parse_data['leader'],))
else:
# Handle an animal name.
# Use the algorithm from Paradox FINDSHEEP.Search()
flockno = ''
words = search_arg.split()
pos = -1
try:
pos = words.index('of')
except ValueError:
pass
if pos != -1:
# May be a flock suffix..
flockname = ' '.join((words[pos:]))
flockno = self.get_flock_by_fullname(flockname)
if flockno:
animalname = ' '.join((words[0:pos]))
if not flockno:
# Either no 'of', or search for a suffix failed.
# Hunt for a flock no
namelist = []
indx = 0
for word in words:
namelist.append(word)
indx += 1
flockno = self.get_flock_by_fullname(
' '.join(namelist))
if flockno:
break
if not flockno:
# Assume the search string is just a animal name
animalname = search_arg
else:
animalname = ' '.join((words[indx:]))
if animalname != '':
animalname.replace('*', '%')
if flockno == '':
regn_nos = self.dblib.query_column(self.regn_no_from_name %
self.schema, (animalname.lower(),))
elif animalname == '':
regn_nos = self.dblib.query_column(self.regn_no_from_flock %
self.schema, (flockno,))
else:
regn_nos = self.dblib.query_column(self.regn_no_from_fullname %
self.schema, (flockno, animalname.lower()))
return dup, regn_nos
def get_pedigree(self, animal_no, generations=4, count=False):
""" Get an animal's pedigree data.
The default behaviour is to build a dictionary of data dicts.
The dict's keys are a number representing the position in the
pedigree reading from left to right and top to bottom
(in the conventional horizontal tree),
ie a two generation pedigree would have nodes 1, 2 and 3,
a four generation nodes 1 - 15, etc.
"""
# Initialise the variables
currgen = 1
queue = [(animal_no, currgen, 1)]
ped_dict = {}
# Iterate through the ancestors
while len(queue) > 0:
curranc = queue.pop(0)
regn_no = curranc[0]
currgen = curranc[1]
node = curranc[2]
# Adam and Eve are queued and processed,
# but their 'parents' are not queued
#~ datarow = self.dblib.query_row(self.ped_query % self.schema,
#~ (regn_no,), asdict=True)
datarow = self.dblib.query_row(self.ped_query %
((self.schema,) * 8), ((regn_no,) * 6), asdict=True)
if not count:
ped_dict[(2**(currgen - 1) + node) -1] = datarow
else:
if (datarow['regn_no'] == self.ADAM or
datarow['regn_no'] == self.EVE):
ped_dict['bad_parent'] = True
# Queue the sire/dam nodes if the maximum depth has not been reached
# Must queue both parents even if one is Adam or Eve as the nodes
# must increment in twos and the test is more efficient elsewhere.
if regn_no not in (self.ADAM, self.EVE):
if currgen < generations:
node *= 2
queue.append((datarow['sire_no'], currgen + 1, node - 1))
queue.append((datarow['dam_no'], currgen + 1, node))
return ped_dict
def get_progeny(self, animal_no, limit=True, generations=4):
"""Get an animal's progeny.
The progeny are returned as a dictionary of data dicts.
The dictionary keys are a sequential number and have no intrinsic
significance, except that key 0 is the subject (root) animal.
The root animal's data dict contains the animal's full data.
Progenys' data dicts contain a subset of the full data.
In all cases the data dict has two items 'gen', which indicates
generation of the progeny fron the root animal, and 'progeny' which
gives the number of progeny of the progeny.
"""
# Set the boundary year - any progeny not bred and born before this
# year won't be added to the tree
bdy_year = time.localtime(time.time())[0] - 3
# Insert the root node in the queue
item = 0
rootrow = self.dblib.query_row(self.progeny_root % self.schema,
(animal_no,), asdict=True)
if not rootrow:
raise ValueError("Registration number '%s' not found in database" %
animal_no)
rootrow.update({'gen':0, 'progeny': 0}) # To make all rows the same
queue = [(animal_no, 0, str(rootrow['sort_dob']), rootrow['sex'])]
progeny_dict = {item: rootrow}
progenylist = {}
# Traverse the progeny
while len(queue) > 0:
q_item = queue.pop(0)
regn_no = q_item[0]
currgen = q_item[1]
sort_dob = q_item[2]
sex = q_item[3]
# Check to see if we've already got this animal
if progenylist.has_key(regn_no):
continue
# Check for progeny of this animal
if sex == 'M':
cmd = self.progeny_prog_sire
else:
cmd = self.progeny_prog_dam
progeny = self.dblib.query_all_rows(cmd % self.schema,
(regn_no,), asdicts=True)
num_prog = len(progeny)
if limit and num_prog == 0 and int(sort_dob[0:4]) < bdy_year:
continue
if currgen != 0:
datarow = self.dblib.query_row(self.progeny_data %
((self.schema,) * 4), ((regn_no,) * 3),
asdict = True)
if not datarow:
raise ValueError("Registration number '%s' not found in "
"database" % regn_no)
datarow.update({'gen':currgen,
'progeny': num_prog,
'tag_prefix': '',
'tag_no': ''})
progeny_dict[item] = datarow
progenylist[regn_no] = item
item += 1
# Insert the progeny in the queues
if num_prog != 0 and currgen < generations:
for row in progeny:
queue.append((row['regn_no'], currgen + 1,
str(row['sort_dob']), row['sex']))
return progeny_dict