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