familyTree/askQuestion.py

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