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