global link_Template
link_Template= Template("<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script>$text</a>")
-
def run_query(s,t):
c = make_cursor()
return c.execute(s,t)
else:
return row[0] + "," +str(row[1])
-def print_age_count(row,newLine):
+def print_people(n):
+ if n>1:
+ return ' people '
+ else:
+ return ' person '
+
+def print_age_child_count(row,newLine):
if newLine == '<br>':
script = "age.py?age="+str(row[0])
link = link_Template.substitute(script = \
script, text = row[0])
- out = str(row[1])
- if row[1]>1:
- out = out + ' people '
- else:
- out = out + ' person '
+ out = str(row[1])+print_people(row[1])
out = out + 'had children at age '+ link + newLine
return out
else:
return print_row(row,newLine)
+def print_age_death_count(row,newLine):
+ if newLine =='<br>':
+ script = "ageDeath.py?age="+str(row[0])
+ link = link_Template.substitute(script = script,text = row[0])
+ out = str(row[1])+print_people(row[1])
+ out = out + "died at age " + link + newLine
+ return out
+ else:
+ return print_row(row,newLine)
def print_name_count(row,newLine):
if newLine=='<br>':
out =out + terr_html(row[0],newLine) +newLine
return out
+def list_people_parents():
+ s = "SELECT name,id"\
+ +" FROM people"\
+ +" ORDER BY id;"
+
+ output = []
+ for row in run_query(s,()):
+ t = "SELECT parentid"\
+ +" FROM parents"\
+ +" WHERE id = ?;"
+
+ u = "SELECT name,id"\
+ +" FROM people"\
+ +" WHERE id = ?";
+
+ parents =[]
+ for r in run_query(t,(row[1],)):
+ parentID = r[0]
+ hasParent = 0
+ for q in run_query(u,(r[0],)):
+ parents.append(q[0] + ' ' + str(q[1]))
+ hasParent=1
+ if hasParent==0:
+ parents.append(r[0] + ' p' +\
+ str(row[1]))
+
+ spouses=[]
+ v = "SELECT name,idb"\
+ +" FROM marriages LEFT JOIN people"\
+ +" ON idb = id"\
+ +" WHERE ida = ?"
+ for r in run_query(v,(row[1],)):
+ if r[0]!=None:
+ spouses.append(r[0]+ ' '+str(r[1]))
+ else:
+ spouses.append(r[1] + ' s' +\
+ str(row[1]))
+
+
+ myName = row[0]
+ myID = str(row[1])
+ output.append([myName+ ' '+ myID,parents,spouses])
+ return output
+
def list_people(newLine):
s = "SELECT name,id,bornyear"\
out = ''
for row in run_query(s,()):
- out = out + print_age_count(row,newLine)
+ out = out + print_age_child_count(row,newLine)
return out
return out
+def count_age_at_death(newLine):
+ s = "select diedYear-bornYear as age,count(*)"\
+ +" FROM people"\
+ +" WHERE diedYear<>0 AND bornYear<>0"\
+ +" GROUP BY age;"
+ out=''
+ for row in run_query(s,()):
+ out = out + print_age_death_count(row,newLine)
+
+ return out
+def people_died_at_age(age,newLine):
+ s = "SELECT diedYear-bornYear as age, name,ID"\
+ +" FROM people"\
+ +" WHERE age = ? AND bornYear<>0 AND diedYear<>0;"
+ t = (int(age),)
+ out =''
+ out = 'These people died at age ' +str(age) + ' :'
+ for row in run_query(s,t):
+ out = out +newLine
+ out = out + name_html([row[1],row[2]],newLine)
+ return out
def all_ancestors(personID,newLine):
#find parents
allAncestors.append(row[1])
trackLevel.append(level)
ancestors = newA
- if len(ancestors)>0:
- out = out+thisout
-
+ out = out+thisout
+
+ image = "<img src = ancestorGraph.py?id="+str(personID)+">"
+ out = out+newLine + image+newLine
return [out, allAncestors,trackLevel]
+" WHERE id=?"
out = out + newLine + 'Most Recent Common Ancestors:' + newLine
+ mrca = []
for a in indexA:
t = (common[a],)
+ mrca.append(common[a])
out = out + print_tagged_query('',s,t,newLine)
if a!=indexA[-1]:
out = out + 'and' + newLine
related = relationship(al,bl,names)
out = out+newLine + related
+
+ image = "<img src = jointAncestorGraph.py?id="+str(IDA)\
+ +"&id2="+str(IDB) + "&LA=" + str(min(aLevels)) \
+ +"&LB=" + str(min(bLevels))+">"
+
+
+
+ out = out+newLine + image+newLine
+
return [out,common,related]
def relationship(level1, level2,names):
for row in run_query(s,t):
output = output + 'ID: '+str(row[0]) +newLine
output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
+ name = row[1]
output = output + 'Born: '+row[3] + newLine
bornYear = row[4]
- output = output + 'Died: '+row[5] + newLine
+ output = output + 'Died: '+row[5] + ", aged " \
+ +str(row[6]-row[4]) +newLine
s = "SELECT * FROM styles WHERE ID = ?"
for row in run_query(s,t):
+" parents LEFT JOIN people"\
+" ON parents.parentID = people.ID"\
+" WHERE parents.ID = ?"
+
+ parents =[]
for row in run_query(s,t):
output = output + print_tagged_name('Parent',row,newLine)
+ if row[0]!=None:
+ parents.append(row[0] + ' ' + row[1])
+ else:
+ parents.append(row[1] + ' p' + personID)
#find spouses
s = "SELECT people.NAME, marriages.IDb from"\
+" marriages LEFT JOIN people"\
+" ON people.ID = marriages.IDb"\
- +" WHERE marriages.IDa = ?"
+ +" WHERE marriages.IDa = ?"\
+ +" ORDER BY IDb;"
+ spouses = []
for row in run_query(s,t):
+ if row[0]!=None:
+ spouses.append(row[0] + ' '+str(row[1]))
+ else:
+ spouses.append(row[1] + ' s' + personID)
output = output + newLine
output = output + print_tagged_name('Spouse',row,newLine)
output = output + relationship_html(personID,row[1],newLine)
s = "SELECT people.NAME, marriages.IDa from"\
+" marriages LEFT JOIN people"\
+" ON people.ID = marriages.IDa"\
- +" WHERE marriages.IDb = ?"
+ +" WHERE marriages.IDb = ?"\
+ +" ORDER BY IDa;"
for row in run_query(s,t):
+ if row[0]!=None:
+ spouses.append(row[0] + ' '+str(row[1]))
+ else:
+ spouses.append(row[1] + ' s' + personID)
output = output + newLine
output = output + print_tagged_name('Spouse',row,newLine)
output = output + relationship_html(personID,row[1],newLine)
+" WHERE parents.parentID = ?"\
+" ORDER BY people.bornYear;"
+ children = []
+ ops =[]
for row in run_query(s,t):
output = output + print_tagged_name('Child',row,newLine)
-
+ children.append(row[0] + ' ' + str(row[1]))
+
#find children's other parent
u = "Select people.NAME, parents.parentID FROM"\
- +" parents LEFT JOIN people"\
+ +" parents INNER JOIN people"\
+" ON people.ID = parents.parentID"\
- +" WHERE parents.ID = ? AND parents.parentID <> ?"
+ +" WHERE parents.ID = ? AND parents.parentID <>?;"
ids = (row[1],t[0])
+ op = 0
for r in run_query(u,ids):
output = output + print_tagged_name('With',r,newLine)
+ op = 1
+ ops.append(r[0] + ' ' + str(r[1]))
+ if op==0:
+ ops.append('?' + ' s' + personID)
#age when child born
if row[2] !=0 and bornYear != 0:
age = row[2]-bornYear
output = output[:-4] + " at the age of "+str(age) + newLine
- output = output + newLine
+
+ Self = name +' ' + str(personID)
+
+ image = "smallGraph.py?Self="+Self
+ for p in parents:
+ image = image + '&p='+p
+ for c in children:
+ image = image + '&c='+c
+ for op in ops:
+ if op !=None:
+ image = image + '&op='+op
+ for s in spouses:
+ if s !=None:
+ image = image + '&s='+str(s)
+
+ image = image.replace(' ','%20')
+ image ="<img src ="+ image + '>'
+
+
+ output = newLine+ output+ image+newLine
+
+
return output
~naath / familyTree.git / blobdiff