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