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