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