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("<a href = http://www.chiark.greenend.org.uk/ucgi/~naath/$script>$text</a>")
   9
  10 def run_query(s,t):
  11         c = make_cursor()
  12         return c.execute(s,t)
  13
  14 def print_row(row,newLine):
  15         out = ''
  16         for item in row:
  17                 out = out + str(item)+'|'
  18         return out[:-1] + newLine
  19
  20 def print_query(s,t,newLine):
  21         printMe = ''
  22         for row in run_query(s,t):
  23                 printMe = printMe + print_row(row,newLine)
  24         return printMe
  25
  26 def is_number(s):
  27     try:
  28         float(s)
  29         return 1
  30     except ValueError:
  31         return 0
  32
  33 def print_tagged_query(relationship,s,t,newLine):
  34         mine = ''
  35         for row in run_query(s,t):
  36                 mine = mine + print_tagged_name(relationship,row,newLine)
  37         return mine
  38
  39
  40 def relationship_html(ID,ID2,newLine):
  41         if newLine=='<br>':
  42                 relationship = common_ancestors(ID,ID2,newLine)[2]
  43                 script = "ancestors.py?ID="+str(ID)+"&ID2="+str(ID2)
  44                 url = link_Template.substitute(script = script,text = "Common ancestors")
  45                 return relationship + ' '+url + newLine
  46         else:
  47                 return ''
  48
  49 def terr_html(terr,newLine):
  50         if newLine=='<br>':
  51                 return link_Template.substitute(script = "territory.py?terr="+terr, text=terr)
  52         else:
  53                 return terr
  54 def name_html(row,html):
  55         if html=='<br>':
  56                 html=1
  57         elif html=='\n':
  58                 html=0
  59
  60         if row[0] == None:
  61                 return row[1]
  62         else:
  63                 if html==1:
  64                         script = "person.py?ID=" + str(row[1])
  65                         name = row[0]
  66                         return link_Template.substitute(script = script\
  67                                 , text = name)
  68                 else:
  69                         return row[0] + "," +str(row[1])
  70
  71 def print_age_count(row,newLine):
  72         if newLine == '<br>':
  73                 script = "age.py?age="+str(row[0])
  74                 link = link_Template.substitute(script = \
  75                         script, text = row[0])
  76                 return str(row[1]) + ' people had children at age '\
  77                          + link + newLine
  78         else:
  79                 return print_row(row,newLine)
  80
  81
  82 def print_tagged_name(relationship,row,newLine):
  83         if row[0]==None:
  84                 out = relationship + " not yet entered: " + row[1]
  85         else:
  86                 if newLine == '<br>':
  87                         html = 1
  88                 else:
  89                         html=0
  90                 if relationship =='':
  91                         out = name_html(row,html) + '   '
  92                 else:
  93                         out = relationship + ": " + name_html(row,html)
  94         return out + newLine
  95
  96 def month_numbers(monthN):
  97         if monthN == 0:
  98                 month ='unknown month'
  99         elif monthN == 1:
 100                 month ='January'
 101         elif monthN==2:
 102                 month ='February'
 103         elif monthN==3:
 104                 month ='March'
 105         elif monthN==4:
 106                 month ='April'
 107         elif monthN==5:
 108                 month ='May'
 109         elif monthN==6:
 110                 month ='June'
 111         elif monthN==7:
 112                 month ='July'
 113         elif monthN==8:
 114                 month ='August'
 115         elif monthN==9:
 116                 month ='September'
 117         elif monthN==10:
 118                 month ='October'
 119         elif monthN==11:
 120                 month ='November'
 121         elif monthN==12:
 122                 month ='December'
 123         else:
 124                 month = 'Incorrectly entered month ' + str(monthN)
 125         return month
 126
 127 def ordinal_numbers(number):
 128         number = int(number)
 129         if number % 10==1 and number/10 % 10 !=1:
 130                 out = str(number) +'st'
 131         elif number % 10==2 and number/10 % 10 !=1:
 132                 out = str(number) +'nd'
 133         elif number % 10==3 and number/10 % 10 !=1:
 134                 out = str(number) +'rd'
 135         else:
 136                 out = str(number) +'th'
 137         return out
 138
 139 def list_territories(newLine):
 140         s = "SELECT DISTINCT territory"\
 141         +" FROM territories"\
 142         +" ORDER BY territory;"
 143
 144         out = ''
 145         for row in run_query(s,()):
 146                 out =out + terr_html(row[0],newLine) +newLine
 147         return out
 148
 149
 150 def list_people(newLine):
 151         s = "SELECT name,id,bornyear"\
 152         +" FROM people"\
 153         +" ORDER BY bornyear;"
 154
 155         out = ''
 156         year = 0
 157         out = out + 'born in unknown year:' +newLine
 158         for row in run_query(s,()):
 159                 if row[2]!=0 and row[2]/100==0:
 160                         out = out +newLine+ 'born in 1st century:' +newLine
 161
 162                 if row[2]/100!=year/100:
 163                         century = row[2]/100 + 1
 164                         out = out +newLine+ 'born in '
 165
 166                         out = out +ordinal_numbers(century) \
 167                                 + ' century:' + newLine
 168
 169                 out = out + name_html(row,newLine) +newLine
 170
 171                 if row[2] == 0: #unknown year
 172
 173                         t = (row[1],) #person ID
 174
 175
 176                         #died
 177                         u = "SELECT diedyear FROM people WHERE ID = ?;"
 178
 179                         bornAfter = 0
 180                         for r in run_query(u,t):
 181                                 if r[0] !=0:
 182                                         out = out + "died: "\
 183                                          + str(r[0]) + newLine
 184                                         bornAfter = r[0] -100
 185
 186                         #find children
 187                         u = "Select people.bornYear from"\
 188                                 +" people INNER JOIN parents"\
 189                                 +" ON people.ID = parents.ID"\
 190                                 +" WHERE parents.parentID = ?"\
 191                                 + " ORDER BY people.bornYear;"
 192
 193                         hadChild=[]
 194
 195                         for r in run_query(u,t):
 196                                 if r[0] != 0:
 197                                         hadChild.append(r[0])
 198
 199                         bornBefore = 0
 200                         if len(hadChild)!=0:
 201                                 out = out + "had children in: "
 202                                 for c in hadChild:
 203                                         out = out + str(c) + ','
 204                                 out = out[:-1] + newLine
 205
 206                                 bornBefore = hadChild[0]-12
 207                                 if bornAfter==0:
 208                                         bornAfter = hadChild[0]-100
 209
 210                         if bornAfter!=0:
 211                                 if bornBefore == 0:
 212                                         out = out + "probably born "\
 213                                                 +"after " + str(bornAfter)
 214                                 else:
 215                                         out = out + "probably born "\
 216                                                 +"betwen " + str(bornAfter)\
 217                                                 +" and " + str(bornBefore)
 218                                 out = out + newLine
 219
 220
 221                 year = row[2]
 222         return out
 223
 224 def count_names(newLine):
 225         s = "SELECT firstName, count(*)"\
 226         +" FROM people"\
 227         +" GROUP BY firstName"\
 228         +" ORDER BY count(*) DESC;"
 229
 230         out = print_query(s,(),newLine)
 231
 232
 233         return out
 234
 235 def count_birth_month(newLine):
 236         s = "SELECT bornMonth, count(*)"\
 237                 +" FROM people"\
 238                 +" GROUP BY bornMonth"\
 239                 +" ORDER BY bornMonth;"
 240
 241         t = "SELECT * FROM people WHERE bornMonth = ?;"
 242
 243         out = ''
 244         for row in run_query(s,()):
 245                 month = month_numbers(row[0])
 246                 out = out + month + ': ' + str(row[1]) + newLine
 247
 248                 if row[0]>12:
 249                         u = (row[0],)
 250                         out =  out +print_query(t,u,newLine)
 251
 252         return out
 253
 254 def count_death_month(newLine):
 255         s = "SELECT diedMonth, count(*)"\
 256                 +" FROM people"\
 257                 +" GROUP BY diedMonth"\
 258                 +" ORDER BY diedMonth;"
 259
 260         t = "SELECT * FROM people WHERE diedMonth = ?;"
 261
 262         out = ''
 263         for row in run_query(s,()):
 264                 month = month_numbers(row[0])
 265                 out = out + month + ': ' + str(row[1]) + newLine
 266
 267                 if row[0]>12:
 268                         u = (row[0],)
 269                         out =  out +print_query(t,u,newLine)
 270
 271         return out
 272
 273 def count_age_at_child(newLine):
 274
 275         s = "select p1.bornYear - p2.bornYear as age, count(*)"\
 276                 +" FROM"\
 277                 +" parents INNER JOIN people p1"\
 278                 +" ON parents.ID = p1.ID"\
 279                 +" INNER JOIN people p2"\
 280                 +" ON parents.parentID = p2.ID"\
 281                 +" WHERE p1.bornYear <> 0 and p2.bornYear<>0"\
 282                 +" GROUP BY age;"
 283
 284         out = ''
 285         for row in run_query(s,()):
 286                 out = out + print_age_count(row,newLine)
 287
 288         return out
 289
 290 def people_had_child_at_age(age,newLine):
 291
 292         s = "select p1.bornYear - p2.bornYear as age, p1.name, p1.ID"\
 293                 +",p2.name,p2.ID FROM"\
 294                 +" parents INNER JOIN people p1"\
 295                 +" ON parents.ID = p1.ID"\
 296                 +" INNER JOIN people p2"\
 297                 +" ON parents.parentID = p2.ID"\
 298                 +" WHERE age = ? AND p1.bornYear<>0 AND p2.bornYear<>0"
 299
 300         t = (int(age),)
 301
 302         out = ''
 303         out = 'At age ' + str(age) + ' :'
 304         for row in run_query(s,t):
 305                 out = out + newLine
 306                 out =out + name_html([row[3],row[4]],newLine) + ' had '\
 307                         +name_html([row[1],row[2]],newLine)
 308
 309         return out
 310
 311
 312 def all_ancestors(personID,newLine):
 313         #find parents
 314         s = "SELECT people.Name,parents.parentID FROM"\
 315                 +" parents LEFT JOIN people"\
 316                 +" ON parents.parentID = people.ID"\
 317                 +" WHERE parents.ID = ?"\
 318                 +" AND parents.parentID <> '.';"
 319
 320
 321         ancestors = [personID]
 322         allAncestors = [personID]
 323         trackLevel = [0]
 324         level = 0
 325
 326         t = "SELECT name,id FROM people WHERE id==?"
 327         id = (personID,)
 328
 329         out = "Ancestors of "
 330         for row in run_query(t,id):
 331                 out = out + name_html(row,newLine)+newLine
 332
 333         while len(ancestors)>0:
 334                 level = level+1
 335                 newA =[]
 336                 thisout = newLine + parent_level(level,'parent') +':' + newLine
 337                 for ancestor in ancestors:
 338                         id = (ancestor,)
 339                         for row in run_query(s,id):
 340                                 thisout = thisout + name_html(row,newLine)+newLine
 341                                 if row[1] not in allAncestors and is_number(row[1])!=0:
 342                                         newA.append(row[1])
 343                                         allAncestors.append(row[1])
 344                                         trackLevel.append(level)
 345                 ancestors = newA
 346                 if len(ancestors)>0:
 347                         out  = out+thisout
 348
 349
 350         return [out, allAncestors,trackLevel]
 351
 352
 353 def common_ancestors(IDA, IDB,newLine):
 354         out = 'Common ancestors of:' + newLine
 355
 356         s = "SELECT name,id FROM people WHERE id==?"
 357
 358
 359         names=[]
 360         for id in (IDA,IDB):
 361                 t = (id,)
 362                 for row in run_query(s,t):
 363                         out = out + name_html(row,newLine)+newLine
 364                         names.append(row[0])
 365                 if id==IDA:
 366                         out = out + 'and'
 367                 out = out + newLine
 368
 369         if len(names)!=2:
 370                 related = 'No details held on one party'
 371                 out = out + related
 372                 return [out,[],related]
 373
 374
 375         a = all_ancestors(IDA,newLine)
 376         b = all_ancestors(IDB,newLine)
 377
 378         ancestorsB = set(b[1])
 379         ancestorsA = set(a[1])
 380
 381         common = ancestorsA.intersection(ancestorsB)
 382         common = list(common)
 383
 384
 385         aLevels=[]
 386         bLevels=[]
 387         for c in common:
 388                 i = a[1].index(c)
 389                 aLevels.append(a[2][i])
 390                 i = b[1].index(c)
 391                 bLevels.append(b[2][i])
 392
 393         s = "SELECT Name, ID, bornyear"\
 394         +" FROM people"\
 395         +" WHERE ID IN ("
 396         for i in range(len(common)):
 397                 s = s+ "?,"
 398         if len(common)>0:
 399                 s = s[:-1]
 400
 401
 402         s = s+") ORDER BY bornyear;"
 403
 404
 405         if len(common)==0:
 406                 related = names[0]+' and '+names[1]+' are not related'
 407                 out = out + newLine + related
 408                 return [out, common,related]
 409
 410
 411         out = out + print_tagged_query('',s,common,newLine)
 412
 413         indexA=[]
 414         indexB=[]
 415
 416         for i in range(len(common)):
 417                 if aLevels[i] == min(aLevels):
 418                         indexA.append(i)
 419                 if bLevels[i] == min(bLevels):
 420                         indexB.append(i)
 421
 422
 423
 424         s = "SELECT name, id"\
 425         +" FROM people"\
 426         +" WHERE id=?"
 427
 428         out  = out + newLine + 'Most Recent Common Ancestors:' + newLine
 429         for a in indexA:
 430                 t = (common[a],)
 431                 out = out + print_tagged_query('',s,t,newLine)
 432                 if a!=indexA[-1]:
 433                         out = out + 'and' + newLine
 434
 435         out = out + parent_level(aLevels[indexA[0]],'parent')
 436         if len(indexA) >1:
 437                 out = out + 's'
 438
 439         out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
 440
 441         out = out + newLine
 442         for b in indexB:
 443                 t = (common[b],)
 444                 out = out + print_tagged_query('',s,t,newLine)
 445                 if b!=indexB[-1]:
 446                         out = out + 'and' + newLine
 447
 448         out = out + parent_level(bLevels[indexB[0]],'parent')
 449         if len(indexB)>1:
 450                 out = out + 's'
 451         out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
 452
 453
 454         al = aLevels[indexA[0]]
 455         bl = bLevels[indexB[0]]
 456
 457         related = relationship(al,bl,names)
 458         out = out+newLine + related
 459
 460         return [out,common,related]
 461
 462 def relationship(level1, level2,names):
 463
 464         if level1==0 and level2==0:
 465                 return names[0] + ' is ' +names[1]
 466         if level1==0:
 467                 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
 468         if level2==0:
 469                 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
 470
 471
 472         if level1>=level2:
 473                 remove = level1-level2
 474                 cousinNum = level2-1
 475         else:
 476                 remove = level2-level1
 477                 cousinNum = level1-1
 478
 479         if cousinNum==0:
 480                 uaLevel =  parent_level(remove,'uncle or aunt')
 481                 if level1<= level2:
 482                         return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
 483
 484                 if level2<level1:
 485                         return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
 486
 487         c=ordinal_numbers(cousinNum)
 488         if remove == 1:
 489                 rem = 'once'
 490         elif remove ==2:
 491                 rem = 'twice'
 492         else:
 493                 rem = str(remove) + ' times'
 494
 495         r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
 496         if remove !=0:
 497                 r = r+ rem + ' removed'
 498
 499         return r
 500
 501 def parent_level(level,type):
 502         if level == 0:
 503                 if type=='parent':
 504                         return 'self'
 505                 else:
 506                         return 'sibling'
 507         out = type
 508         if level ==1:
 509                 return out
 510         if type =='parent':
 511                 out = 'grand '+out
 512         else:
 513                 out = 'great '+out
 514         if level ==2:
 515                 return out
 516         for i in range(2,level):
 517                 out = 'great '+out
 518         return out
 519
 520 def rulers_of(aTerritory,newLine):
 521
 522         tq = "SELECT name, people.ID, startyear,stopyear,territory"\
 523                 +" FROM territories INNER JOIN people"\
 524                 +" ON people.ID = territories.ID"\
 525                 +" WHERE territory LIKE ?"\
 526                 +" ORDER BY territory,startyear,stopyear;"
 527
 528
 529
 530         thisT  = ''
 531         last = ''
 532         out = ''
 533         for row in run_query(tq,(aTerritory+'%',)):
 534                 if row[4]!=last and last!='':
 535                         out  = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
 536                         thisT = ''
 537
 538                 thisT = thisT +name_html(row,newLine)
 539                 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
 540                 last = row[4]
 541
 542         out  = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
 543
 544         return out
 545
 546 def person_info(personID,newLine):
 547         t = (personID,)
 548
 549         output = '';
 550
 551         #Id, Name, Dates, Style, Style-Dates
 552         s = "SELECT * FROM people WHERE ID = ?"
 553         for row in run_query(s,t):
 554                 output = output + 'ID: '+str(row[0]) +newLine
 555                 output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
 556                 output = output + 'Born: '+row[3] + newLine
 557                 bornYear = row[4]
 558                 output = output + 'Died: '+row[5] + newLine
 559
 560         s = "SELECT * FROM styles WHERE ID = ?"
 561         for row in run_query(s,t):
 562                 output = output +newLine+ 'Style: '+row[1] + newLine
 563
 564                 output = output + 'Territories:' + newLine
 565
 566                 u = "SELECT * FROM territories"\
 567                 +"  WHERE ID =? AND startYear =? AND stopYear=?"
 568                 v=(personID,row[3],row[5])
 569
 570                 any = 0
 571                 for r in run_query(u,v):
 572                         output = output + terr_html(r[1],newLine) +','
 573                         any = 1
 574                 if any ==1:
 575                         output = output[:-1] + newLine
 576
 577                 output = output +  'From: '+row[2] + newLine
 578                 output = output +  'To: '+row[4] + newLine
 579
 580         s = "SELECT people.Name,consort "\
 581                 +"FROM consorts LEFT JOIN people"\
 582                 +" ON people.ID = consorts.consort"\
 583                 +" WHERE consorts.ID = ?"
 584         for row in run_query(s,t):
 585                 output = output + print_tagged_name('Consort',row,newLine)
 586
 587         output = output + newLine
 588         #find parents
 589         s = "SELECT people.Name,parents.parentID FROM"\
 590                 +" parents LEFT JOIN people"\
 591                 +" ON parents.parentID = people.ID"\
 592                 +" WHERE parents.ID = ?"
 593         for row in run_query(s,t):
 594                 output = output + print_tagged_name('Parent',row,newLine)
 595
 596         #find spouses
 597         s = "SELECT people.NAME, marriages.IDb from"\
 598                 +" marriages LEFT JOIN people"\
 599                 +" ON people.ID = marriages.IDb"\
 600                 +" WHERE marriages.IDa = ?"
 601         for row in run_query(s,t):
 602                 output = output + newLine
 603                 output = output + print_tagged_name('Spouse',row,newLine)
 604                 output = output + relationship_html(personID,row[1],newLine)
 605
 606         s = "SELECT people.NAME, marriages.IDa from"\
 607                 +" marriages LEFT JOIN people"\
 608                 +" ON people.ID = marriages.IDa"\
 609                 +" WHERE marriages.IDb = ?"
 610         for row in run_query(s,t):
 611                 output = output + newLine
 612                 output = output + print_tagged_name('Spouse',row,newLine)
 613                 output = output + relationship_html(personID,row[1],newLine)
 614
 615         output = output + newLine
 616
 617         #find children
 618         s = "Select people.NAME, people.ID ,people.bornYear"\
 619                 +" FROM people INNER JOIN parents"\
 620                 +" ON people.ID = parents.ID"\
 621                 +" WHERE parents.parentID = ?"\
 622                 +" ORDER BY people.bornYear;"
 623
 624         for row in run_query(s,t):
 625                 output = output  + print_tagged_name('Child',row,newLine)
 626
 627                  #find children's other parent
 628                 u = "Select people.NAME, parents.parentID FROM"\
 629                 +" parents LEFT JOIN people"\
 630                 +" ON people.ID = parents.parentID"\
 631                 +" WHERE parents.ID = ? AND parents.parentID <> ?"
 632
 633                 ids = (row[1],t[0])
 634
 635                 for r in run_query(u,ids):
 636                         output = output + print_tagged_name('With',r,newLine)
 637
 638                 #age when child born
 639                 age = row[2]-bornYear
 640                 output = output[:-4] + " at the age of "+str(age) + newLine
 641
 642         output = output + newLine
 643
 644         return output
 645
 646 def connect():
 647         global conn
 648         conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
 649         return conn
 650
 651 def make_cursor():
 652         return conn.cursor()
 653
 654 def close(conn):
 655         conn.close
 656
 657 #def main():
 658
 659 #       [c, conn] = connect()
 660 #
 661 #       person_info(1,c)
 662 #       person_info(17,c)
 663 #       person_info(38,c)
 664 #       person_info(90,c)
 665 #
 666 #       close(conn)