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         if html == 1:
  60                 script = "person.py?ID=" + str(row[1])
  61                 name = row[0]
  62                 return link_Template.substitute(script = script, text = name)
  63         if row[0] == None:
  64                 return row[1]
  65         else:
  66                 return row[0] + "," +str(row[1])
  67
  68 def print_tagged_name(relationship,row,newLine):
  69         if row[0]==None:
  70                 out = relationship + " not yet entered: " + row[1]
  71         else:
  72                 if newLine == '<br>':
  73                         html = 1
  74                 else:
  75                         html=0
  76                 if relationship =='':
  77                         out = name_html(row,html) + '   '
  78                 else:
  79                         out = relationship + ": " + name_html(row,html)
  80         return out + newLine
  81
  82
  83 def ordinal_numbers(number):
  84         number = int(number)
  85         if number % 10==1 and number/10 % 10 !=1:
  86                 out = str(number) +'st'
  87         elif number % 10==2 and number/10 % 10 !=1:
  88                 out = str(number) +'nd'
  89         elif number % 10==3 and number/10 % 10 !=1:
  90                 out = str(number) +'rd'
  91         else:
  92                 out = str(number) +'th'
  93         return out
  94
  95 def list_territories(newLine):
  96         s = "SELECT DISTINCT territory"\
  97         +" FROM territories"\
  98         +" ORDER BY territory;"
  99
 100         out = ''
 101         for row in run_query(s,()):
 102                 out =out + terr_html(row[0],newLine) +newLine
 103         return out
 104
 105 def list_people(newLine):
 106         s = "SELECT name,id,bornyear"\
 107         +" FROM people"\
 108         +" ORDER BY bornyear;"
 109
 110         out = ''
 111         year = 0
 112         out = out + 'born in unknown year:' +newLine
 113         for row in run_query(s,()):
 114                 if row[2]!=0 and row[2]/100==0:
 115                         out = out +newLine+ 'born in 1st century:' +newLine
 116
 117                 if row[2]/100!=year/100:
 118                         century = row[2]/100 + 1
 119                         out = out +newLine+ 'born in '
 120
 121                         out = out +ordinal_numbers(century) + ' century:' + newLine
 122
 123                 out = out + name_html(row,newLine) +newLine
 124                 year = row[2]
 125         return out
 126
 127 def count_names(newLine):
 128         s = "SELECT firstName, count(*)"\
 129         +" FROM people"\
 130         +" GROUP BY firstName"\
 131         +" ORDER BY count(*) DESC;"
 132
 133         out = print_query(s,(),newLine)
 134
 135
 136         return out
 137
 138 def all_ancestors(personID,newLine):
 139         #find parents
 140         s = "SELECT people.Name,parents.parentID FROM"\
 141                 +" parents LEFT JOIN people"\
 142                 +" ON parents.parentID = people.ID"\
 143                 +" WHERE parents.ID = ?"\
 144                 +" AND parents.parentID <> '.';"
 145
 146
 147         ancestors = [personID]
 148         allAncestors = [personID]
 149         trackLevel = [0]
 150         level = 0
 151
 152         t = "SELECT name,id FROM people WHERE id==?"
 153         id = (personID,)
 154
 155         out = "Ancestors of "
 156         for row in run_query(t,id):
 157                 out = out + name_html(row,newLine)+newLine
 158
 159         while len(ancestors)>0:
 160                 level = level+1
 161                 newA =[]
 162                 thisout = newLine + parent_level(level,'parent') +':' + newLine
 163                 for ancestor in ancestors:
 164                         id = (ancestor,)
 165                         for row in run_query(s,id):
 166                                 thisout = thisout + name_html(row,newLine)+newLine
 167                                 if row[1] not in allAncestors and is_number(row[1])!=0:
 168                                         newA.append(row[1])
 169                                         allAncestors.append(row[1])
 170                                         trackLevel.append(level)
 171                 ancestors = newA
 172                 if len(ancestors)>0:
 173                         out  = out+thisout
 174
 175
 176         return [out, allAncestors,trackLevel]
 177
 178
 179 def common_ancestors(IDA, IDB,newLine):
 180         out = 'Common ancestors of:' + newLine
 181
 182         s = "SELECT name,id FROM people WHERE id==?"
 183
 184
 185         names=[]
 186         for id in (IDA,IDB):
 187                 t = (id,)
 188                 for row in run_query(s,t):
 189                         out = out + name_html(row,newLine)+newLine
 190                         names.append(row[0])
 191                 if id==IDA:
 192                         out = out + 'and'
 193                 out = out + newLine
 194
 195         if len(names)!=2:
 196                 related = 'No details held on one party'
 197                 out = out + related
 198                 return [out,[],related]
 199
 200
 201         a = all_ancestors(IDA,newLine)
 202         b = all_ancestors(IDB,newLine)
 203
 204         ancestorsB = set(b[1])
 205         ancestorsA = set(a[1])
 206
 207         common = ancestorsA.intersection(ancestorsB)
 208         common = list(common)
 209
 210
 211         aLevels=[]
 212         bLevels=[]
 213         for c in common:
 214                 i = a[1].index(c)
 215                 aLevels.append(a[2][i])
 216                 i = b[1].index(c)
 217                 bLevels.append(b[2][i])
 218
 219         s = "SELECT Name, ID, bornyear"\
 220         +" FROM people"\
 221         +" WHERE ID IN ("
 222         for i in range(len(common)):
 223                 s = s+ "?,"
 224         if len(common)>0:
 225                 s = s[:-1]
 226
 227
 228         s = s+") ORDER BY bornyear;"
 229
 230
 231         if len(common)==0:
 232                 related = names[0]+' and '+names[1]+' are not related'
 233                 out = out + newLine + related
 234                 return [out, common,related]
 235
 236
 237         out = out + print_tagged_query('',s,common,newLine)
 238
 239         indexA=[]
 240         indexB=[]
 241
 242         for i in range(len(common)):
 243                 if aLevels[i] == min(aLevels):
 244                         indexA.append(i)
 245                 if bLevels[i] == min(bLevels):
 246                         indexB.append(i)
 247
 248
 249
 250         s = "SELECT name, id"\
 251         +" FROM people"\
 252         +" WHERE id=?"
 253
 254         out  = out + newLine + 'Most Recent Common Ancestors:' + newLine
 255         for a in indexA:
 256                 t = (common[a],)
 257                 out = out + print_tagged_query('',s,t,newLine)
 258                 if a!=indexA[-1]:
 259                         out = out + 'and' + newLine
 260
 261         out = out + parent_level(aLevels[indexA[0]],'parent')
 262         if len(indexA) >1:
 263                 out = out + 's'
 264
 265         out = out + ' of ' + name_html([names[0],IDA],newLine)+newLine
 266
 267         out = out + newLine
 268         for b in indexB:
 269                 t = (common[b],)
 270                 out = out + print_tagged_query('',s,t,newLine)
 271                 if b!=indexB[-1]:
 272                         out = out + 'and' + newLine
 273
 274         out = out + parent_level(bLevels[indexB[0]],'parent')
 275         if len(indexB)>1:
 276                 out = out + 's'
 277         out = out + ' of ' + name_html([names[1],IDB],newLine)+newLine
 278
 279
 280         al = aLevels[indexA[0]]
 281         bl = bLevels[indexB[0]]
 282
 283         related = relationship(al,bl,names)
 284         out = out+newLine + related
 285
 286         return [out,common,related]
 287
 288 def relationship(level1, level2,names):
 289
 290         if level1==0 and level2==0:
 291                 return names[0] + ' is ' +names[1]
 292         if level1==0:
 293                 return names[0] + ' is ' + names[1] + '\'s '+ parent_level(level2,'parent')
 294         if level2==0:
 295                 return names[1] + ' is ' + names[0] + '\'s '+ parent_level(level1,'parent')
 296
 297
 298         if level1>=level2:
 299                 remove = level1-level2
 300                 cousinNum = level2-1
 301         else:
 302                 remove = level2-level1
 303                 cousinNum = level1-1
 304
 305         if cousinNum==0:
 306                 uaLevel =  parent_level(remove,'uncle or aunt')
 307                 if level1<= level2:
 308                         return names[0] + ' is ' + names[1] + '\'s ' + uaLevel
 309
 310                 if level2<level1:
 311                         return names[1] + ' is ' + names[0] + '\'s ' + uaLevel
 312
 313         c=ordinal_numbers(cousinNum)
 314         if remove == 1:
 315                 rem = 'once'
 316         elif remove ==2:
 317                 rem = 'twice'
 318         else:
 319                 rem = str(remove) + ' times'
 320
 321         r = names[0] +' and '+names[1] +' are ' + c + ' cousins '
 322         if remove !=0:
 323                 r = r+ rem + ' removed'
 324
 325         return r
 326
 327 def parent_level(level,type):
 328         if level == 0:
 329                 if type=='parent':
 330                         return 'self'
 331                 else:
 332                         return 'sibling'
 333         out = type
 334         if level ==1:
 335                 return out
 336         if type =='parent':
 337                 out = 'grand '+out
 338         else:
 339                 out = 'great '+out
 340         if level ==2:
 341                 return out
 342         for i in range(2,level):
 343                 out = 'great '+out
 344         return out
 345
 346 def rulers_of(aTerritory,newLine):
 347
 348         tq = "SELECT name, people.ID, startyear,stopyear,territory"\
 349                 +" FROM territories INNER JOIN people"\
 350                 +" ON people.ID = territories.ID"\
 351                 +" WHERE territory LIKE ?"\
 352                 +" ORDER BY territory,startyear,stopyear;"
 353
 354
 355
 356         thisT  = ''
 357         last = ''
 358         out = ''
 359         for row in run_query(tq,(aTerritory+'%',)):
 360                 if row[4]!=last and last!='':
 361                         out  = out + 'Rulers of '+terr_html(last,newLine) +':'+ newLine +thisT +newLine
 362                         thisT = ''
 363
 364                 thisT = thisT +name_html(row,newLine)
 365                 thisT = thisT +' from ' + str(row[2])+' to '+str(row[3]) + newLine
 366                 last = row[4]
 367
 368         out  = out + 'Rulers of '+terr_html(row[4],newLine) +':'+ newLine +thisT
 369
 370         return out
 371
 372 def person_info(personID,newLine):
 373         t = (personID,)
 374
 375         output = '';
 376
 377         #Id, Name, Dates, Style, Style-Dates
 378         s = "SELECT * FROM people WHERE ID = ?"
 379         for row in run_query(s,t):
 380                 output = output + 'ID: '+str(row[0]) +newLine
 381                 output = output + print_tagged_name('Name',[row[1], row[0]],newLine) +newLine
 382                 output = output + 'Born: '+row[3] + newLine
 383                 output = output + 'Died: '+row[5] + newLine
 384
 385         s = "SELECT * FROM styles WHERE ID = ?"
 386         for row in run_query(s,t):
 387                 output = output +newLine+ 'Style: '+row[1] + newLine
 388
 389                 output = output + 'Territories:' + newLine
 390
 391                 u = "SELECT * FROM territories"\
 392                 +"  WHERE ID =? AND startYear =? AND stopYear=?"
 393                 v=(personID,row[3],row[5])
 394
 395                 any = 0
 396                 for r in run_query(u,v):
 397                         output = output + terr_html(r[1],newLine) +','
 398                         any = 1
 399                 if any ==1:
 400                         output = output[:-1] + newLine
 401
 402                 output = output +  'From: '+row[2] + newLine
 403                 output = output +  'To: '+row[4] + newLine
 404
 405         output = output + newLine
 406         #find parents
 407         s = "SELECT people.Name,parents.parentID FROM"\
 408                 +" parents LEFT JOIN people"\
 409                 +" ON parents.parentID = people.ID"\
 410                 +" WHERE parents.ID = ?"
 411         for row in run_query(s,t):
 412                 output = output + print_tagged_name('Parent',row,newLine)
 413
 414         #find spouses
 415         s = "SELECT people.NAME, marriages.IDb from"\
 416                 +" marriages LEFT JOIN people"\
 417                 +" ON people.ID = marriages.IDb"\
 418                 +" WHERE marriages.IDa = ?"
 419         for row in run_query(s,t):
 420                 output = output + newLine
 421                 output = output + print_tagged_name('Spouse',row,newLine)
 422                 output = output + relationship_html(personID,row[1],newLine)
 423
 424         s = "SELECT people.NAME, marriages.IDa from"\
 425                 +" marriages LEFT JOIN people"\
 426                 +" ON people.ID = marriages.IDa"\
 427                 +" WHERE marriages.IDb = ?"
 428         for row in run_query(s,t):
 429                 output = output + newLine
 430                 output = output + print_tagged_name('Spouse',row,newLine)
 431                 output = output + relationship_html(personID,row[1],newLine)
 432
 433         output = output + newLine
 434
 435         #find children
 436         s = "Select people.NAME, people.ID from"\
 437                 +" people INNER JOIN parents"\
 438                 +" ON people.ID = parents.ID"\
 439                 +" WHERE parents.parentID = ?"
 440
 441         for row in run_query(s,t):
 442                 output = output  + print_tagged_name('Child',row,newLine)
 443
 444                  #find children's other parent
 445                 u = "Select people.NAME, parents.parentID FROM"\
 446                 +" parents LEFT JOIN people"\
 447                 +" ON people.ID = parents.parentID"\
 448                 +" WHERE parents.ID = ? AND parents.parentID <> ?"
 449
 450                 ids = (row[1],t[0])
 451
 452                 for row in run_query(u,ids):
 453                         output = output + print_tagged_name('With',row,newLine)
 454
 455         output = output + newLine
 456
 457         return output
 458
 459 def connect():
 460         global conn
 461         conn = sqlite3.connect('/home/naath/familyTreeProject/familyTree/tree.db')
 462         return conn
 463
 464 def make_cursor():
 465         return conn.cursor()
 466
 467 def close(conn):
 468         conn.close
 469
 470 #def main():
 471
 472 #       [c, conn] = connect()
 473 #
 474 #       person_info(1,c)
 475 #       person_info(17,c)
 476 #       person_info(38,c)
 477 #       person_info(90,c)
 478 #
 479 #       close(conn)