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