familyTree/askQuestion.py

   1 #!/usr/bin/python
   2
   3 import sqlite3
   4 import findYear
   5 from string import Template
   6 import cgi
   7 import re
   8
   9 global link_Template
  10 link_Template= Template(\
  11         "<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script"\
  12         +" title=$title>$text</a>")
  13 def add_quotes(s):
  14         s = str(s)
  15         return "'"+s+"'"
  16
  17 def run_query(s,t):
  18         c = make_cursor()
  19         return c.execute(s,t)
  20
  21 def print_row(row,newLine):
  22         out = ''
  23         for item in row:
  24                 out = out + str(item)+'|'
  25         return out[:-1] + newLine
  26
  27 def print_query(s,t,newLine):
  28         printMe = ''
  29         for row in run_query(s,t):
  30                 printMe = printMe + print_row(row,newLine)
  31         return printMe
  32
  33 def is_number(s):
  34     try:
  35         float(s)
  36         return 1
  37     except ValueError:
  38         return 0
  39
  40 def print_tagged_query(relationship,s,t,newLine):
  41         mine = ''
  42         for row in run_query(s,t):
  43                 mine = mine + print_tagged_name(relationship,row,newLine)
  44         return mine
  45
  46
  47 def relationship_html(ID,ID2,newLine):
  48         if newLine=='<br>':
  49                 relationship = common_ancestors(ID,ID2,newLine)[2]
  50
  51                 if relationship[-11:] != 'not related':
  52                         script = "ancestors.py?ID=%s&ID2=%s" % (ID,ID2)
  53                         url = link_Template.substitute\
  54                                 (script = script,title = "Common ancestors"\
  55                                         ,text = "Common ancestors")
  56                         return relationship + ' '+url + newLine
  57                 else:
  58                         return relationship + newLine
  59         else:
  60                 return ''
  61
  62 def terr_html(terr,newLine,start,stop):
  63         if newLine=='<br>':
  64                 if start == 0 and stop ==0:
  65                         myTitle = add_quotes(terr)
  66
  67                 else:
  68                         s = "SELECT name,people.id"\
  69                         +" FROM people INNER JOIN territories"\
  70                         +" ON people.id = territories.id"\
  71                         +" WHERE territory = ? AND stopyear <= ?"\
  72                         +" ORDER BY startyear DESC;"
  73
  74                         t = (terr,start)
  75                         myTitle = ''
  76                         for row in run_query(s,t):
  77                                 myTitle += "previous - %s,%s" \
  78                                 % (row[0],row[1])
  79                                 break
  80
  81                         u = "SELECT name,people.id"\
  82                         +" FROM people INNER JOIN territories"\
  83                         +" ON people.id = territories.id"\
  84                         +" WHERE territory = ? AND startyear >= ?"\
  85                         +" ORDER BY startyear;"
  86
  87                         v = (terr,stop)
  88                         for r in run_query(u,v):
  89                                 myTitle += '&#xA next - %s,%s'\
  90                                 %(r[0],r[1])
  91                                 break
  92
  93                         myTitle = add_quotes(myTitle)
  94
  95                 return link_Template.substitute(\
  96                         script = "territory.py?terr="+terr, title=myTitle,\
  97                         text = terr)
  98         else:
  99                 return terr
 100 def name_html(row,html):
 101         if html=='<br>':
 102                 html=1
 103         elif html=='\n':
 104                 html=0
 105
 106         if row[0] == None:
 107                 return row[1]
 108         elif row[1]==0:
 109                 return row[0]
 110         else:
 111                 if html==1:
 112                         script = "person.py?ID=%s" %(row[1])
 113                         name = row[0]
 114                         return link_Template.substitute(script = script\
 115                                 ,title = add_quotes(name),text = name)
 116                 else:
 117                         return "%s,%s" % (row[0],row[1])
 118
 119 def print_people(n):
 120         if n>1:
 121                 return 'people'
 122         else:
 123                 return 'person'
 124 def print_children(n):
 125         if n>1:
 126                 return 'children'
 127         else:
 128                 return 'child'
 129
 130 def print_age_child_count(row,newLine):
 131         if newLine == '<br>':
 132                 script = "listAge.py?age=%s" % (row[0])
 133                 link = link_Template.substitute(script = \
 134                         script, title = add_quotes(row[0]), text = row[0])
 135
 136                 out = '%s %s had children at age %s %s'\
 137                  % (row[1],print_people(row[1]),link,newLine)
 138                 return out
 139         else:
 140                 return print_row(row,newLine)
 141
 142 def print_age_death_count(row,newLine):
 143         if newLine =='<br>':
 144                 script = "listAgeDeath.py?age=%s" % (row[0])
 145                 link = link_Template.substitute(script = script,\
 146                         title = add_quotes(row[0]),text = row[0])
 147                 out = "%s %s died at age %s %s " \
 148                         %(row[1],print_people(row[1]), link,newLine)
 149                 return out
 150         else:
 151                 return print_row(row,newLine)
 152
 153 def print_name_count(row,newLine):
 154         if newLine=='<br>':
 155                 script = "name.py?name=%s" % (row[0])
 156                 link = link_Template.substitute(script =\
 157                         script, title = add_quotes(row[0]),text = row[0])
 158                 return "%s people called %s%s"\
 159                         %(row[1],link, newLine)
 160         else:
 161                 return print_row(row,newLine)
 162
 163 def print_children_count(row,newLine):
 164         out = "%s %s had " % (row[0],print_people(row[0]))
 165
 166         if newLine == '<br>':
 167                 script = "listChildCount.py?nc="+str(row[1])
 168                 link = link_Template.substitute(script =\
 169                         script, title = add_quotes(row[1]),text = row[1])
 170         else:
 171                 link = str(row[1])
 172
 173         out += "%s %s %s" % (link,print_children(row[1]),newLine)
 174
 175         return out
 176
 177 def print_tagged_name(relationship,row,newLine):
 178         if row[0]==None:
 179                 out = relationship + " not yet entered: " + row[1]
 180                 out = "%s not yet entered: %s" % (relationship,row[1])
 181         else:
 182                 if newLine == '<br>':
 183                         html = 1
 184                 else:
 185                         html=0
 186                 if relationship =='':
 187                         out = '%s   ' % (name_html(row,html))
 188                 else:
 189                         out = relationship + ": " + name_html(row,html)
 190                         out = '%s: %s' % (relationship,name_html(row,html))
 191         return out + newLine
 192
 193 def month_numbers(monthN):
 194         if monthN == 0:
 195                 month ='unknown month'
 196         elif monthN == 1:
 197                 month ='January'
 198         elif monthN==2:
 199                 month ='February'
 200         elif monthN==3:
 201                 month ='March'
 202         elif monthN==4:
 203                 month ='April'
 204         elif monthN==5:
 205                 month ='May'
 206         elif monthN==6:
 207                 month ='June'
 208         elif monthN==7:
 209                 month ='July'
 210         elif monthN==8:
 211                 month ='August'
 212         elif monthN==9:
 213                 month ='September'
 214         elif monthN==10:
 215                 month ='October'
 216         elif monthN==11:
 217                 month ='November'
 218         elif monthN==12:
 219                 month ='December'
 220         else:
 221                 month = 'Incorrectly entered month ' + str(monthN)
 222         return month
 223
 224 def ordinal_numbers(number):
 225         number = int(number)
 226         out = '%d' % (number)
 227         if number % 10==1 and number/10 % 10 !=1:
 228                 out +='st'
 229         elif number % 10==2 and number/10 % 10 !=1:
 230                 out +='nd'
 231         elif number % 10==3 and number/10 % 10 !=1:
 232                 out +='rd'
 233         else:
 234                 out +='th'
 235         return out
 236
 237 def list_territories(newLine):
 238         s = "SELECT DISTINCT territory"\
 239         +" FROM territories"\
 240         +" ORDER BY territory;"
 241
 242         out = ''
 243         for row in run_query(s,()):
 244                 out += terr_html(row[0],newLine,0,0) +newLine
 245         return out
 246
 247 def list_people_parents():
 248         s = "SELECT name,id"\
 249                 +" FROM people"\
 250                 +" ORDER BY id;"
 251
 252         output = []
 253         for row in run_query(s,()):
 254
 255                 ID = row[1]
 256                 [parents, parentIDs,parentNames] = find_parents(ID)
 257                 [spouses,spousesID,spousesNames] = find_spouses(ID)
 258
 259                 [self,myID,myName] = find_person(ID)
 260                 output.append([self,parents,spouses])
 261         return output
 262
 263
 264 def list_people(newLine):
 265         s = "SELECT name,id,bornyear"\
 266         +" FROM people"\
 267         +" ORDER BY bornyear;"
 268
 269         out = ''
 270         year = 0
 271         out = out + 'born in unknown year:' +newLine
 272         for row in run_query(s,()):
 273                 if row[2]!=0 and row[2]/100==0:
 274                         out +='%sborn in 1st century:%s' % (newLine,newLine)
 275
 276                 if row[2]/100!=year/100:
 277                         century = row[2]/100 + 1
 278                         out +='%sborn in %s century: %s'\
 279                          %(newLine,ordinal_numbers(century),newLine)
 280
 281
 282                 out+= name_html(row,newLine) +newLine
 283
 284                 if row[2] == 0: #unknown year
 285
 286                         t = (row[1],) #person ID
 287
 288
 289                         #died
 290                         u = "SELECT diedyear FROM people WHERE ID = ?;"
 291
 292                         bornAfter = 0
 293                         bornBefore = 0
 294                         for r in run_query(u,t):
 295                                 if r[0] !=0:
 296                                         bornAfter = r[0] -100
 297                                         bornBefore = r[0]
 298                                         out += "Died: %s %s"\
 299                                         %(r[0],newLine)
 300                         #find children
 301                         u = "Select people.bornYear from"\
 302                                 +" people INNER JOIN parents"\
 303                                 +" ON people.ID = parents.ID"\
 304                                 +" WHERE parents.parentID = ?"\
 305                                 + " ORDER BY people.bornYear;"
 306
 307                         hadChild=[]
 308
 309                         for r in run_query(u,t):
 310                                 if r[0] != 0:
 311                                         hadChild.append(r[0])
 312
 313                         if len(hadChild)!=0:
 314                                 out += "had children in: "
 315                                 for c in hadChild:
 316                                         out+= '%s,' % (c)
 317                                 out = out[:-1] + newLine
 318                                 bornBefore = hadChild[0]-12
 319                                 if bornAfter==0:
 320                                         bornAfter = hadChild[0]-100
 321
 322                         u = "Select styles.startYear, styles.style from"\
 323                                 +" people INNER JOIN styles"\
 324                                 +" ON people.ID = styles.ID"\
 325                                 +" WHERE people.ID = ? and"\
 326                                 +" styles.startYear <>0"\
 327                                 +" ORDER BY styles.startYear;"
 328
 329                         for r in run_query(u,t):
 330                                 out += "%s from %s %s"\
 331                                         %(r[1],r[0],newLine)
 332                                 if bornAfter ==0:
 333                                         bornAfter = r[0] -100
 334                                 break
 335
 336                         if bornAfter!=0:
 337                                 if bornBefore == 0:
 338                                         out += "probably born after %s"\
 339                                                 %(bornAfter)
 340                                 else:
 341                                         out +="probably born between %s and %s"\
 342                                                 %(bornAfter, bornBefore)
 343                                 out = out + newLine
 344
 345                 year = row[2]
 346         return out
 347
 348 def count_names(newLine):
 349         s = "SELECT firstName, count(*)"\
 350         +" FROM people"\
 351         +" GROUP BY firstName"\
 352         +" ORDER BY count(*) DESC;"
 353
 354         out = ''
 355         for row in run_query(s,()):
 356                 out += print_name_count(row,newLine)
 357
 358         return out
 359
 360
 361
 362 def count_children(newLine):
 363
 364         s = "SELECT count(*),nc"\
 365         +" FROM ("\
 366         +" SELECT count(*) AS nc"\
 367         +" FROM parents"\
 368         +" GROUP BY parentID"\
 369         +" HAVING parentID <>'?'"\
 370         +" AND parentID <> '0')"\
 371         +" GROUP BY nc;"
 372
 373         out = ''
 374         for row in run_query(s,()):
 375                 out += print_children_count(row,newLine)
 376         return out
 377
 378 def parents_with_children(nChildren,newLine):
 379         s = "SELECT name,parentID"\
 380         + " FROM parents"\
 381         + " LEFT JOIN people"\
 382         + " ON parentID = people.ID"\
 383         + " GROUP BY parentID"\
 384         + " HAVING count(*) = ?"\
 385         + " AND parentID <> 0"\
 386         + " ORDER BY bornYear;"
 387
 388
 389         u = "SELECT count(*)"\
 390         +" FROM  parents INNER JOIN people"\
 391         +" ON parents.ID = people.ID"\
 392         +" WHERE parentID = ?"\
 393         +" AND (diedyear-bornyear>? OR died='present');"
 394
 395         out = "People who had %s %s:%s" \
 396                 %(nChildren,print_children(nChildren),newLine)
 397
 398         for row in run_query(s,(nChildren,)):
 399                 out += name_html(row,newLine)
 400                 for r in run_query(u,(row[1],1)):
 401                         out += " %d  survived infancy" % r[0]
 402                 for r in run_query(u,(row[1],18)):
 403                         out += " and %s survived to adulthood" % r[0]
 404                 out = out +newLine
 405
 406         return out
 407
 408 def search_name(name,newLine):
 409         s = "SELECT name, ID"\
 410         +" FROM people"\
 411         +" WHERE name LIKE ?;"
 412
 413         IDs=[]
 414         names=[]
 415
 416         out = 'Names starting with %s:%s' % (name,newLine)
 417         t = (name + '%',)
 418         fullIDs=[]
 419         for row in run_query(s,t):
 420                 out += name_html(row,newLine) + newLine
 421                 fullIDs.append(row[1])
 422                 names.append(row[0])
 423                 IDs.append(row[1])
 424
 425         t = ('%' + name + '%',)
 426         out += '%sNames containing %s:%s' %(newLine,name,newLine)
 427         for row in run_query(s,t):
 428                 if row[1] not in fullIDs:
 429                         out += name_html(row,newLine) + newLine
 430                         names.append(row[0])
 431                         IDs.append(row[1])
 432
 433         s = "SELECT name,people.ID,style"\
 434         +" FROM people INNER JOIN styles"\
 435         +" ON styles.id = people.id"\
 436         +" WHERE style LIKE ?;"
 437         out += '%sStyles containing %s:%s' %(newLine,name,newLine)
 438         for row in run_query(s,t):
 439                 out +="%s %s %s" %(name_html(row,newLine),row[2],newLine)
 440         return [out,names,IDs]
 441
 442
 443 def people_with_name(name,newLine):
 444         s = "SELECT name, ID"\
 445         +" FROM people"\
 446         +" WHERE firstname = ?;"
 447
 448         out = ''
 449
 450         t = (name,)
 451
 452         for row in run_query(s,t):
 453                 out += name_html(row,newLine) + newLine
 454
 455         return out
 456
 457 def count_birth_month(newLine):
 458         s = "SELECT bornMonth, count(*)"\
 459                 +" FROM people"\
 460                 +" GROUP BY bornMonth"\
 461                 +" ORDER BY bornMonth;"
 462
 463         t = "SELECT * FROM people WHERE bornMonth = ?;"
 464
 465         out = ''
 466         for row in run_query(s,()):
 467                 month = month_numbers(row[0])
 468                 out += "%s:%s%s" %( month,row[1],newLine)
 469
 470                 if row[0]>12:
 471                         u = (row[0],)
 472                         out +=print_query(t,u,newLine)
 473
 474         return out
 475
 476 def count_death_month(newLine):
 477         s = "SELECT diedMonth, count(*)"\
 478                 +" FROM people"\
 479                 +" GROUP BY diedMonth"\
 480                 +" ORDER BY diedMonth;"
 481
 482         t = "SELECT * FROM people WHERE diedMonth = ?;"
 483
 484         out = ''
 485         for row in run_query(s,()):
 486                 month = month_numbers(row[0])
 487                 out += '%s:%s%s'%(month,row[1], newLine)
 488
 489                 if row[0]>12:
 490                         u = (row[0],)
 491                         out +=print_query(t,u,newLine)
 492
 493         return out
 494
 495 def count_age_at_child(newLine):
 496
 497         s = "select p1.bornYear - p2.bornYear as age, count(*)"\
 498                 +" FROM"\
 499                 +" parents INNER JOIN people p1"\
 500                 +" ON parents.ID = p1.ID"\
 501                 +" INNER JOIN people p2"\
 502                 +" ON parents.parentID = p2.ID"\
 503                 +" WHERE p1.bornYear <> 0 and p2.bornYear<>0"\
 504                 +" GROUP BY age;"
 505
 506         out = ''
 507         for row in run_query(s,()):
 508                 out +=print_age_child_count(row,newLine)
 509
 510         return out
 511
 512 def people_had_child_at_age(age,newLine):
 513
 514         s = "select p1.bornYear - p2.bornYear as age, p1.name, p1.ID"\
 515                 +",p2.name,p2.ID FROM"\
 516                 +" parents INNER JOIN people p1"\
 517                 +" ON parents.ID = p1.ID"\
 518                 +" INNER JOIN people p2"\
 519                 +" ON parents.parentID = p2.ID"\
 520                 +" WHERE age = ? AND p1.bornYear<>0 AND p2.bornYear<>0"
 521
 522         t = (int(age),)
 523
 524         out = ''
 525         out = 'At age ' + str(age) + ' :'
 526         for row in run_query(s,t):
 527                 parent = name_html([row[3],row[4]],newLine)
 528                 child = name_html([row[1],row[2]],newLine)
 529                 out += "%s%s had %s" % (newLine,parent,child)
 530         return out
 531
 532 def count_age_at_death(newLine):
 533         s = "select diedYear-bornYear as age,count(*)"\
 534                 +" FROM people"\
 535                 +" WHERE diedYear<>0 AND bornYear<>0"\
 536                 +" GROUP BY age;"
 537         out=''
 538         for row in run_query(s,()):
 539                 out += print_age_death_count(row,newLine)
 540
 541         return out
 542
 543 def people_died_at_age(age,newLine):
 544         s = "SELECT diedYear-bornYear as age, name,ID"\
 545                 +" FROM people"\
 546                 +" WHERE age = ? AND bornYear<>0 AND diedYear<>0;"
 547         t = (int(age),)
 548         out =''
 549         out = 'These people died at age ' +str(age) + ' :'
 550         for row in run_query(s,t):
 551                 out += newLine+ name_html([row[1],row[2]],newLine)
 552         return out
 553
 554 def all_ancestors(personID,newLine):
 555         #find parents
 556
 557         ancestors = [personID]
 558         allAncestors = [personID]
 559         trackLevel = [0]
 560         level = 0
 561
 562         t = "SELECT name,id FROM people WHERE id=?"
 563         id = (personID,)
 564
 565         out = "Ancestors of "
 566         for row in run_query(t,id):
 567                 out += name_html(row,newLine)+newLine
 568
 569         while len(ancestors)>0:
 570                 level = level+1
 571                 newA =[]
 572                 thisout = "%s %s:%s" \
 573                 % (newLine,parent_level(level,'parent'),newLine)
 574
 575                 for ancestor in ancestors:
 576                         [parents, parentIDs,parentNames] \
 577                                 = find_parents(ancestor)
 578                         for i in range(len(parents)):
 579                                 r = [parentNames[i],parentIDs[i]]
 580                                 thisout +=name_html(r,newLine)+newLine
 581
 582                                 if r[1] not in allAncestors\
 583                                 and r[1]!=0:
 584                                         newA.append(r[1])
 585                                         allAncestors.append(r[1])
 586                                         trackLevel.append(level)
 587
 588                 ancestors = newA
 589                 out  +=thisout
 590
 591
 592         image = "<img src = ancestorGraph.py?id=%s>" %personID
 593         out +=newLine + image+newLine
 594         return [out, allAncestors,trackLevel]
 595
 596
 597 def common_ancestors(IDA, IDB,newLine):
 598         out = 'Common ancestors of:' + newLine
 599
 600         s = "SELECT name,id FROM people WHERE id==?"
 601
 602
 603         names=[]
 604         for id in (IDA,IDB):
 605                 t = (id,)
 606                 for row in run_query(s,t):
 607                         out += name_html(row,newLine)+newLine
 608                         names.append(row[0])
 609                 if id==IDA:
 610                         out += 'and'
 611                 out = out + newLine
 612
 613         if len(names)!=2:
 614                 related = 'No details held on one party'
 615                 out += related
 616                 return [out,[],related]
 617
 618
 619         a = all_ancestors(IDA,newLine)
 620         b = all_ancestors(IDB,newLine)
 621
 622         ancestorsB = set(b[1])
 623         ancestorsA = set(a[1])
 624
 625         common = ancestorsA.intersection(ancestorsB)
 626         common = list(common)
 627
 628
 629         aLevels=[]
 630         bLevels=[]
 631         for c in common:
 632                 i = a[1].index(c)
 633                 aLevels.append(a[2][i])
 634                 i = b[1].index(c)
 635                 bLevels.append(b[2][i])
 636
 637         s = "SELECT Name, ID, bornyear"\
 638         +" FROM people"\
 639         +" WHERE ID IN ("
 640         for i in range(len(common)):
 641                 s += "?,"
 642         if len(common)>0:
 643                 s = s[:-1]
 644         s = s+") ORDER BY bornyear;"
 645
 646
 647         if len(common)==0:
 648                 related = '%s and %s are not related' %(names[0], names[1])
 649                 out = out + newLine + related
 650                 return [out, common,related]
 651
 652
 653         out += print_tagged_query('',s,common,newLine)
 654
 655         indexA=[]
 656         indexB=[]
 657
 658         for i in range(len(common)):
 659                 if aLevels[i] == min(aLevels):
 660                         indexA.append(i)
 661                 if bLevels[i] == min(bLevels):
 662                         indexB.append(i)
 663
 664
 665
 666         s = "SELECT name, id"\
 667         +" FROM people"\
 668         +" WHERE id=?"
 669
 670         out  += '%sMost Recent Common Ancestors:%s' %(newLine,newLine)
 671         mrca = []
 672         for a in indexA:
 673                 t = (common[a],)
 674                 mrca.append(common[a])
 675                 out += print_tagged_query('',s,t,newLine)
 676                 if a!=indexA[-1]:
 677                         out += 'and' + newLine
 678
 679         out += parent_level(aLevels[indexA[0]],'parent')
 680         if len(indexA) >1:
 681                 out += 's'
 682
 683         out +=' of %s%s'%( name_html([names[0],IDA],newLine),newLine)
 684
 685         out += parent_level(bLevels[indexB[0]],'parent')
 686         if len(indexB)>1:
 687                 out += 's'
 688         out += ' of %s%s' %(name_html([names[1],IDB],newLine),newLine)
 689
 690
 691         al = aLevels[indexA[0]]
 692         bl = bLevels[indexB[0]]
 693
 694         related = relationship(al,bl,names)
 695         out +=newLine + related
 696
 697
 698         image = "<img src = jointAncestorGraph.py?id="+str(IDA)\
 699                 +"&id2="+str(IDB) + "&LA=" + str(min(aLevels)) \
 700                 +"&LB=" + str(min(bLevels))+">"
 701
 702         image = "<img src = jointAncestorGraph.py?id=%s&id2=%s&LA=%d&LB=%d>"\
 703                 %(IDA,IDB,min(aLevels),min(bLevels))
 704
 705
 706         out +=newLine + image+newLine
 707
 708         return [out,common,related]
 709
 710 def relationship(level1, level2,names):
 711
 712         if level1==0 and level2==0:
 713                 return "%s is %s" %(names[0],names[1])
 714         if level1==0:
 715                 return "%s is %s's %s" \
 716                         %(names[0],names[1],parent_level(level2,'parent'))
 717
 718         if level2==0:
 719                 return "%s is %s's %s" \
 720                         %(names[1],names[0],parent_level(level1,'parent'))
 721
 722
 723         if level1>=level2:
 724                 remove = level1-level2
 725                 cousinNum = level2-1
 726         else:
 727                 remove = level2-level1
 728                 cousinNum = level1-1
 729
 730         if cousinNum==0:
 731                 uaLevel =  parent_level(remove,'uncle or aunt')
 732                 if level1<= level2:
 733                         return "%s is %s's %s" % (names[0],names[1],uaLevel)
 734
 735                 if level2<level1:
 736                         return "%s is %s's %s" % (names[1],names[0],uaLevel)
 737
 738         c=ordinal_numbers(cousinNum)
 739         if remove == 1:
 740                 rem = 'once'
 741         elif remove ==2:
 742                 rem = 'twice'
 743         else:
 744                 rem = str(remove) + ' times'
 745
 746         r = "%s and %s are %s cousins" % (names[0],names[1],c)
 747         if remove !=0:
 748                 r += ' %s removed' % rem
 749
 750         return r
 751
 752 def parent_level(level,type):
 753         if level == 0:
 754                 if type=='parent':
 755                         return 'self'
 756                 else:
 757                         return 'sibling'
 758         out = type
 759         if level ==1:
 760                 return out
 761         if type =='parent':
 762                 out = 'grand ' + out
 763         else:
 764                 out = 'great '+out
 765         if level ==2:
 766                 return out
 767         for i in range(2,level):
 768                 out = 'great '+out
 769         return out
 770
 771 def rulers_of(aTerritory,newLine):
 772
 773         tq = "SELECT name, people.ID, startyear,stopyear,territory"\
 774                 +" FROM territories INNER JOIN people"\
 775                 +" ON people.ID = territories.ID"\
 776                 +" WHERE territory LIKE ?"\
 777                 +" ORDER BY territory,startyear,stopyear;"
 778
 779
 780
 781         thisT  = ''
 782         last = ''
 783         out = ''
 784         for row in run_query(tq,(aTerritory+'%',)):
 785                 if row[4]!=last and last!='':
 786                         out += 'Rulers of %s:%s%s%s'\
 787                         %(terr_html(last,newLine,0,0),newLine,thisT,newLine)
 788                         thisT = ''
 789
 790                 thisT += "%s from %s to %s%s" \
 791                 % (name_html(row,newLine),row[2],row[3],newLine)
 792                 last = row[4]
 793
 794         out += 'Rulers of %s:%s%s' \
 795         % (terr_html(row[4],newLine,0,0),newLine,thisT)
 796
 797         return out
 798
 799 def find_person(ID):
 800         s = "SELECT name || ','||ID, name, ID FROM people WHERE ID=?"
 801         t = (ID,)
 802
 803         for row in run_query(s,t):
 804                 Self = row[0]
 805                 selfName = row[1]
 806                 selfID = row[2]
 807                 return [Self, selfID,selfName]
 808 def isKing(ID):
 809         ID = int(ID.split(',')[-1])
 810         s=  "SELECT style FROM styles WHERE ID=?"
 811         t = (ID,)
 812
 813         k = 0
 814         spellingsOfKing = ['King','Queen','king','queen']
 815         for row in run_query(s,t):
 816                 for s in spellingsOfKing:
 817                         if re.match('.*'+s,row[0]) != None:
 818                                 k = 1
 819
 820         return k
 821
 822 def find_parents(ID):
 823         s = "SELECT name, parentID"\
 824                 +" FROM parents LEFT JOIN people"\
 825                 +" ON people.ID = parentID"\
 826                 +" WHERE parents.ID = ?;"
 827         t = (ID,)
 828
 829         parents = []
 830         parentIDs =[]
 831         parentNames=[]
 832
 833         for row in run_query(s,t):
 834                 if row[0]!=None:
 835                         p = row[0] + ',' + str(row[1])
 836                         pID = row[1]
 837                         pN = row[0]
 838                 else:
 839                         p = row[1] + ',p' + str(ID)
 840                         pID = 0
 841                         pN = row[1]
 842                 parents.append(p)
 843                 parentIDs.append(pID)
 844                 parentNames.append(pN)
 845
 846         if parents[1]==parents[0]:
 847                 parents[1] = parents[1] + ' 2'
 848
 849         return [parents,parentIDs,parentNames]
 850
 851 def find_spouses(ID):
 852         t = (ID,)
 853
 854         order = [["IDb","IDa"],["IDa","IDb"]]
 855
 856         spouses = []
 857         spousesID=[]
 858         spousesNames=[]
 859         for o in order:
 860                 s = "SELECT name, marriages." + o[0]\
 861                 +" FROM marriages LEFT JOIN people"\
 862                 +" ON marriages." +o[0]+" = people.ID"\
 863                 +" WHERE marriages."+o[1]+" = ?;"
 864
 865
 866                 for row in run_query(s,t):
 867                         if row[0]!=None:
 868                                 s = row[0] + "," +str(row[1])
 869                                 sID = row[1]
 870                                 sN = row[0]
 871                         elif row[1] !='':
 872                                 s=row[1] + ",s" +str(ID)
 873                                 sID = 0
 874                                 sN = row[1]
 875                         if row[1] !='':
 876                                 spouses.append(s)
 877                                 spousesID.append(sID)
 878                                 spousesNames.append(sN)
 879
 880         return [spouses,spousesID,spousesNames]
 881
 882
 883 def find_children(ID):
 884         s = "SELECT p1.name, p1.ID,p3.parentID,p4.name,p1.bornYear"\
 885                 +" FROM people p1"\
 886                 +" INNER JOIN parents p2"\
 887                 +" ON p1.ID = p2.ID"\
 888                 +" INNER JOIN parents p3"\
 889                 +" ON p1.ID = p3.ID"\
 890                 +" LEFT JOIN people"\
 891                 +" p4 ON p3.parentID = p4.ID"\
 892                 +" WHERE p2.parentID = ?"\
 893                 +" AND p3.parentID<>?"\
 894                 +" ORDER BY p1.bornYear;"
 895
 896         t = (ID,ID)
 897
 898         childrenBorn=[]
 899         nodes=[]
 900         IDs=[]
 901         names=[]
 902
 903         for row in run_query(s,t):
 904                 c = row[0] + ',' + str(row[1])
 905                 cID = row[1]
 906                 cName = row[0]
 907                 born = row[4]
 908                 childrenBorn.append(born)
 909                 if row[3]!=None:
 910                         op = row[3] + ',' + str(row[2])
 911                         opID = row[2]
 912                         opN = row[3]
 913                 else:
 914                         op = row[2] + ',s' + str(ID)
 915                         opID = 0
 916                         opN = row[2]
 917
 918                 nodes.append([c,op])
 919                 IDs.append([cID,opID])
 920                 names.append([cName,opN])
 921
 922         return [nodes,IDs,names,childrenBorn]
 923
 924 def person_info(personID,newLine):
 925         t = (personID,)
 926
 927         if newLine=='<br>':
 928                 startP = '<p>'
 929                 endP = '</p>'
 930         else:
 931                 startP = ''
 932                 endP = newLine
 933
 934         mainDiv = ''
 935         #Id, Name, Dates, Style, Style-Dates
 936         s = "SELECT * FROM people WHERE ID = ?"
 937         for row in run_query(s,t):
 938                 mainDiv += startP
 939                 mainDiv += 'ID: %s%s' %(row[0] ,newLine)
 940                 mainDiv += print_tagged_name('Name',[row[1], row[0]]\
 941                         ,newLine)
 942                 mainDiv +=endP
 943                 name = row[1]
 944                 url = row[9]
 945                 picture = row[10]
 946
 947                 mainDiv += startP
 948                 mainDiv += '%sBorn:%s%s '% (newLine,row[3],newLine)
 949                 bornYear = row[4]
 950                 mainDiv +='Died: %s' % row[5]
 951
 952                 if row[6] != 0 and row[4] !=0:
 953                         mainDiv += ", aged %s" %(row[6]-row[4])
 954                 mainDiv +=endP
 955
 956
 957         s = "SELECT * FROM styles WHERE ID = ?"
 958         for row in run_query(s,t):
 959                 mainDiv += startP
 960                 mainDiv +='%sStyle: %s%s'%(newLine,row[1],newLine)
 961
 962                 mainDiv += 'Territories:%s' % newLine
 963
 964                 u = "SELECT * FROM territories"\
 965                 +"  WHERE ID =? AND startYear =? AND stopYear=?"
 966                 v=(personID,row[3],row[5])
 967
 968                 any = 0
 969                 for r in run_query(u,v):
 970                         mainDiv += terr_html(r[1],newLine,r[3],r[5]) +','
 971                         any = 1
 972                 if any ==1:
 973                         mainDiv = mainDiv[:-1] + newLine
 974
 975                 mainDiv +=  'From: '+row[2] + newLine
 976                 mainDiv +=  'To: '+row[4]
 977
 978                 mainDiv += endP
 979
 980
 981
 982
 983         mainDiv += startP
 984         s = "SELECT people.Name,consort "\
 985                 +"FROM consorts LEFT JOIN people"\
 986                 +" ON people.ID = consorts.consort"\
 987                 +" WHERE consorts.ID = ?"
 988         for row in run_query(s,t):
 989                 mainDiv += print_tagged_name\
 990                 ('Consort of',row,newLine)
 991         mainDiv += endP
 992
 993         #find parents
 994
 995         [parents,parentIDs,parentNames] = find_parents(personID)
 996         mainDiv += startP
 997         for i in range(len(parents)):
 998                 r = [parentNames[i],parentIDs[i]]
 999                 mainDiv += print_tagged_name('Parent',r,newLine)
1000         mainDiv += endP
1001
1002         #find spouses
1003
1004         [spouses,spousesID,spousesNames] = find_spouses(personID)
1005
1006         mainDiv += startP
1007
1008         for i in range(len(spouses)):
1009                 r = [spousesNames[i],spousesID[i]]
1010                 mainDiv += print_tagged_name('Spouse',r,newLine)
1011                 mainDiv += \
1012                 relationship_html(personID,r[1],newLine)
1013
1014         mainDiv  = mainDiv + endP
1015
1016         #find children
1017         [nodes,IDs,names,childrenBorn] = \
1018                 find_children(personID)
1019
1020         top = ''
1021         for i in range(len(nodes)):
1022                 cr = [names[i][0],IDs[i][0]]
1023                 thisChild = print_tagged_name('Child',cr,newLine)
1024
1025                 opr=[names[i][1],IDs[i][1]]
1026                 top = names[i][1]
1027                 if i==0 or  top != names[i-1][1]:
1028                         mainDiv +=endP
1029                         mainDiv += startP
1030                         mainDiv += print_tagged_name\
1031                         ('With',opr, newLine)
1032
1033
1034                 #age when child born
1035                 cb = childrenBorn[i]
1036                 if  cb!=0 and  bornYear != 0:
1037                         age = cb-bornYear
1038                         thisChild = thisChild[:-4] + \
1039                                 " at the age of "+str(age) + newLine
1040                 mainDiv += thisChild
1041
1042         mainDiv += endP
1043
1044
1045         if newLine == '<br>':
1046                 output = '<div id = "main" style = " float:left">';
1047                 output += mainDiv +  "</div>"
1048
1049                 output += "<div id = 'image' "\
1050                         +"style = 'float:left; margin-left:20px'>"
1051
1052                 imageDiv = ''
1053                 if picture!='.':
1054                         imageDiv += "<a href=" + url+">"\
1055                         +"<img src=" + picture +" alt = 'wiki link'"\
1056                         +" title = 'wiki link'></a>"\
1057                         + newLine
1058
1059                 elif url!='.' and url!='. ':
1060                         imageDiv += "<a href=" + url +">"\
1061                         +name + " (wiki link)</a>"+newLine
1062
1063                 output += imageDiv + "</div>"
1064
1065
1066                 url = 'http://www.chiark.greenend.org.uk/ucgi/~naath/'\
1067                         +'smallGraph.py'
1068
1069                 form = ''
1070                 form += "<form id ='controlForm'"\
1071                 +" action ="+ url +" method = 'get'>"
1072
1073                 form +=\
1074                         "<input type = 'hidden' name = 'ID' value = "\
1075                         +personID + "><br>"
1076
1077                 form +=\
1078                 "Generations of Parents: "\
1079                 +"<input type = 'text' name = 'pl' value='1'>"
1080                 form += newLine
1081                 form += \
1082                 "Generations of Children: "\
1083                 +" <input type = 'text' name = 'cl' value = '1'>"
1084                 form += newLine
1085                 form += \
1086                 "Show siblings: <select name = 's'>"+\
1087                 "<option value = '0'>No</option>"+\
1088                 "<option value = '1'>Yes</option>"+\
1089                 "</select>"
1090                 form += newLine
1091                 form += \
1092                 "Show spouse's other spouses: <select name = 'os'>"+\
1093                 "<option value = '0'>No</option>"+\
1094                 "<option value = '1'>Yes</option>"+\
1095                 "</select>"
1096                 form += newLine
1097                 form += \
1098                 "Show parents' other spouses: <select name = 'pos'>"+\
1099                 "<option value = '0'>No</option>"+\
1100                 "<option value = '1'>Yes</option>"+\
1101                 "</select>"
1102                 form += newLine
1103                 form += \
1104                 "Fount size: "+\
1105                 "<input type = 'text' name = 'fs' value='8'>"
1106                 form += newLine
1107                 form += "</form>"
1108
1109                 graph =  "smallGraph.py?ID="+str(personID)+"&fs=8"
1110
1111                 graph = "<img src ="+ graph + '>'
1112
1113                 output += "<div id = 'graph' style = 'clear:both'>"
1114                 output += "<p id = 'agraph'>"+graph+"</p>"
1115                 output += "Draw this graph with more relatives:"
1116                 output += newLine + form
1117
1118                 output += "<button onclick='myFunction()'>"+\
1119                         "Go</button>"
1120
1121                 output += "</div>"
1122
1123                 output +=\
1124                 '<script>'+\
1125                 'function myFunction()'+\
1126                 '{'+\
1127                 'var x = document.getElementById("controlForm");'+\
1128                 'var txt = "<img src = " + x.action + "?";'+\
1129                 'for (var i=0;i<x.length;i++)'+\
1130                 '{'+\
1131                 'var n=x.elements[i].name;'+\
1132                 'var v=x.elements[i].value;'+\
1133                 'txt = txt + "&"+n+"="+v;'+\
1134                 '}'+\
1135                 'txt = txt + ">";'+\
1136                 'document.getElementById("agraph").innerHTML=txt;'+\
1137                 '}'+\
1138                 '</script>'
1139
1140         else:
1141                 output = mainDiv
1142
1143         return output
1144
1145 def connect():
1146         global conn
1147         conn = sqlite3.connect\
1148                 ('/home/naath/familyTreeProject/familyTree/tree.db')
1149         return conn
1150
1151 def make_cursor():
1152         return conn.cursor()
1153
1154 def close(conn):
1155         conn.close
1156