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