familyTree/askQuestion.py

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