familyTree/askQuestion.py

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