familyTree/askQuestion.py

   1 #!/usr/bin/python
   2
   3 import sqlite3
   4 import findYear
   5 from string import Template
   6 import cgi
   7 import re
   8 import pickle
   9 import pygraph.algorithms.accessibility as access
  10 import englishUtils as eU
  11 import printUtils as pU
  12 import graphQuestions as gQ
  13 import urllib2
  14
  15 global presentYear
  16 presentYear= 1000000
  17 global ageTable
  18 ageTable = "(SELECT diedYear-bornYear as age, name,ID,diedYear,bornYear"\
  19         +" FROM people"\
  20         +" WHERE bornYear<>0 AND diedYear<>0 AND (diedMonth>bornMonth"\
  21         +" OR (diedMonth==0)"\
  22         +" OR (diedMonth = bornMonth AND (diedDay>=bornDay OR diedDay =0)))"\
  23         +" UNION"\
  24         +" SELECT diedYear-bornYear-1 as age,name,ID,diedYear,bornYear"\
  25         +" FROM people"\
  26         +" WHERE bornYear<>0 AND diedYear<>0 AND diedMonth<>0"\
  27         +" AND (diedMonth<bornMonth"\
  28         +" OR (diedMonth = bornMonth AND (diedDay<bornDay AND diedDay <>0)))"\
  29         +" )"
  30
  31 global ageNowTable
  32 ageNowTable = "(SELECT strftime('%Y',date('now'))-bornYear as age,"\
  33         +"  name,ID,diedYear,bornYear"\
  34         +" FROM people"\
  35         +" WHERE bornYear<>0  AND (strftime('%m',date('now'))>bornMonth"\
  36         +" OR (strftime('%m',date('now')) = bornMonth AND"\
  37         +" (strftime('%d',date('now'))>=bornDay)))"\
  38         +" UNION"\
  39         +" SELECT strftime('%Y',date('now'))-bornYear-1 as age,"\
  40         +" name,ID,strftime('%Y',date('now')),bornYear"\
  41         +" FROM people"\
  42         +" WHERE bornYear<>0  AND (strftime('%m',date('now'))<bornMonth"\
  43         +" OR (strftime('%m',date('now')) = bornMonth"\
  44         +" AND (strftime('%d',date('now'))<bornDay)))"\
  45         +" )"
  46
  47 global ageChildTable
  48 ageChildTable = "(SELECT c.bornYear - p.bornYear as age,"\
  49         +" c.name as cName,c.ID as cID,"\
  50         +" p.name as pname,p.ID as pID,"\
  51         +" c.bornYear as bornYear"\
  52         +" FROM"\
  53         +" parents INNER JOIN people c"\
  54         +" ON parents.ID = c.ID"\
  55         +" INNER JOIN people p"\
  56         +" ON parents.parentID = p.ID"\
  57         +" WHERE p.bornYear <>0 AND c.bornYear<>0"\
  58         +" AND (c.bornMonth>p.bornMonth"\
  59         +" OR (c.bornMonth = p.bornMonth AND c.bornDay>=p.bornDay))"\
  60         +" UNION"\
  61         +" SELECT c.bornYear - p.bornYear-1 as age,"\
  62         +" c.name as cName,c.ID as cID,"\
  63         +" p.name as pName,p.ID as pID,"\
  64         +" c.bornYear as bornYear"\
  65         +" FROM"\
  66         +" parents INNER JOIN people c"\
  67         +" ON parents.ID = c.ID"\
  68         +" INNER JOIN people p"\
  69         +" ON parents.parentID = p.ID"\
  70         +" WHERE p.bornYear <>0 AND c.bornYear<>0"\
  71         +" AND (c.bornMonth<p.bornMonth"\
  72         +" OR (c.bornMonth = p.bornMonth AND c.bornDay<p.bornDay))"\
  73         +" )"
  74
  75
  76
  77 def run_query(s,t):
  78         c = make_cursor()
  79         return c.execute(s,t)
  80
  81 def make_insert(table,fields):
  82
  83         t = tuple(fields)
  84         values = '('
  85         for i in range(len(fields)):
  86                 values = values+'?,'
  87         values = values[:-1]+')'
  88
  89         s = 'INSERT INTO '+table+' VALUES'\
  90                 +values
  91         run_query(s,t)
  92
  93 def number_people():
  94         s = 'SELECT max(ID) FROM people;'
  95         for r in run_query(s,()):
  96                 return int(r[0])
  97
  98 def calendar(newLine):
  99         out = ''
 100
 101         mLength = [31, 29, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31]
 102         b = 'born'
 103         x = 'died'
 104         fcstart = '<FONT COLOR = "'
 105         fcmid = '">'
 106         fcend = '</FONT>'
 107         dcol = 'red'
 108         dcstart = fcstart+dcol+fcmid
 109
 110         for m in range(12):
 111
 112                 n = count_born_on(m+1,0,b)
 113                 o = count_born_on(m+1,0,x)
 114                 p = count_born_in(m+1,b)
 115                 q = count_born_in(m+1,x)
 116
 117                 script = 'birthday.py?date=%d-0' %(m+1)
 118                 d = 0
 119                 url = pU.link_Template.substitute\
 120                                 (script = script,title = eU.month_numbers(m+1),\
 121                                 text = eU.month_numbers(m+1))
 122                 out += "%s %d %s%d%s %d %s%d%s%s" \
 123                         %(url,n,dcstart,o,fcend,p,dcstart,q,fcend,newLine)
 124
 125
 126                 out+="<table>"
 127                 out+="<tr>"
 128                 for d in range(mLength[m]):
 129                         script = 'birthday.py?date=%d-%d' % (m+1,d+1)
 130                         url = pU.link_Template.substitute\
 131                                 (script = script,title = str(d+1),\
 132                                 text = str(d+1))
 133
 134                         n = count_born_on(m+1,d+1,b)
 135                         o = count_born_on(m+1,d+1,x)
 136
 137                         s = "<td>"
 138                         e = "</td>"
 139                         out+="%s%s%s %s%d%s %s%s%s%s%s " %(s,url,e,\
 140                                 s,n,e,s,dcstart,o,fcend,e)
 141                         if (d+1)%7 ==0 or (d+1)==mLength[m]:
 142                                 out+="</tr>"
 143                                 out+="<tr>"
 144                 out+="</table>"
 145                 out+=newLine
 146
 147         script = 'birthday.py?date=0-0'
 148         url = pU.link_Template.substitute\
 149                 (script = script,title = 'unknown',\
 150                 text = 'unkown')
 151
 152         n = count_born_on(0,0,b)
 153         o = count_born_on(0,0,x)
 154         out+="%s %d %s%d%s%s" %(url,n,dcstart,o,fcend,newLine)
 155
 156
 157
 158         script = 'birthday.py?date=20-0'
 159         url = pU.link_Template.substitute\
 160                 (script = script,title = 'on their birthday',\
 161                 text = 'on their birthday')
 162         n = count_born_on(20,0,b)
 163         out +="%d people died %s%s" %(n,url,newLine)
 164
 165
 166         s = "select diedYear,count(*)"\
 167                 +" FROM people"\
 168                 +" WHERE diedYear==?"\
 169                 +" GROUP BY diedYear;"
 170         for row in run_query(s,(presentYear,)):
 171                 out += pU.print_age_death_count(row,newLine)
 172
 173         for bd in ('born','died'):
 174                 out+=newLine
 175                 s = "SELECT "+bd+", count(*) AS n"\
 176                         +" FROM (SELECT "+bd + "," + bd + "Year"\
 177                         +" FROM people"\
 178                         +" WHERE "+bd+"Month<>0 AND "+bd+"Day<>0)"\
 179                         +" GROUP BY "+bd\
 180                         +" HAVING n>1"\
 181                         +" ORDER BY "+bd+"Year;"
 182
 183                 t = "SELECT name,id"\
 184                         +" FROM people"\
 185                         +" WHERE "+bd+" = ?;"
 186
 187
 188                 out += "On the following days more than one person %s: %s" \
 189                         %(bd,newLine)
 190
 191                 for row in run_query (s,()):
 192                         out+=row[0]+":"+newLine
 193                         for r in run_query(t,(str(row[0]),)):
 194                                 out+=pU.name_html(r,newLine)+newLine
 195
 196
 197
 198         return out
 199
 200 def count_born_on(month,day,bd):
 201         if month==20:
 202                 s = "SELECT count(*) FROM people"\
 203                         +" WHERE bornMonth==diedMonth"\
 204                         +" AND bornDay==diedDay"\
 205                         +" AND bornMonth<>0"\
 206                         +" AND diedDay<>0;"
 207                 t = ()
 208
 209         else:
 210                 s = "SELECT count(*) FROM people"\
 211                         +" WHERE "+bd+"Month==?"\
 212                         +" AND "+bd+"Day==?"\
 213                         +" AND "+bd+"Year<>?;"
 214
 215                 t = (month,day,presentYear)
 216
 217         for row in run_query(s,t):
 218                 return row[0]
 219
 220 def count_born_in(month,bd):
 221
 222         s = "SELECT count(*) FROM people"\
 223                 +" WHERE "+bd+"Month==?"\
 224                 +" AND "+bd+"Year<>?;"
 225         t = (month,presentYear)
 226
 227         for row in run_query(s,t):
 228                 return row[0]
 229
 230 def born_on(date,newLine):
 231         month = int(date.split('-')[0])
 232         day = int(date.split('-')[1])
 233
 234         fcstart = '<FONT COLOR = "'
 235         fcmid = '">'
 236         fcend = '</FONT>'
 237         dcol = 'red'
 238         dcstart = fcstart+dcol+fcmid
 239
 240
 241         out = ''
 242
 243
 244         if month==20:
 245                 n = count_born_on(month,day,'')
 246
 247                 out+="%d %s died on their birthday: %s"\
 248                         %(n, eU.print_people(n),newLine)
 249
 250                 s =  "SELECT name,id,bornYear,diedYear,bornMonth,bornDay"\
 251                         +" FROM people"\
 252                         +" WHERE bornMonth==diedMonth"\
 253                         +" AND bornDay==diedDay"\
 254                         +" AND bornMonth<>0"\
 255                         +" AND diedDay<>0"\
 256                         +" ORDER BY diedYear-bornYear;"
 257                 t=()
 258                 out+="Died on the day they were born:%s"%(newLine)
 259                 out+=pU.table_header(['link','born and died'],newLine)
 260                 header=0
 261                 for row in run_query(s,t):
 262                         if row[2]!=row[3] and header==0:
 263                                 header = 1
 264                                 out+=pU.table_foot(newLine)
 265                                 out+="%sDied on a later birthday:%s"\
 266                                         %(newLine,newLine)
 267                                 out+=pU.table_header(['link','born','died','month','day'],\
 268                                         newLine)
 269                         link = pU.name_html(row,newLine)
 270                         born = pU.print_year(row[2])
 271                         died = pU.print_year(row[3])
 272                         month = eU.month_numbers(row[4])
 273                         day = str(row[5])
 274                         if row[2]==row[3]:
 275                                 out+=pU.table_row([link,born],newLine)
 276                         else:
 277                                 out+=pU.table_row([link,born,died,month,day],newLine)
 278                 out+=pU.table_foot(newLine)
 279                 return out
 280
 281         if day!= 0:
 282                 out += '%s %s %s' \
 283                 %(eU.month_numbers(month),eU.ordinal_numbers(day),newLine)
 284         else:
 285                 out +='%s %s'\
 286                 %(eU.month_numbers(month),newLine)
 287
 288
 289         for bd in ['born','died']:
 290                 s = "SELECT name,id,"+bd+"Year FROM people"\
 291                         +" WHERE "+bd+"Month==?"\
 292                         +" AND "+bd+"Day==?"\
 293                         +" AND "+bd+"Year<>?"\
 294                         +" ORDER BY "+bd+"Year;"
 295
 296                 t = (month,day,presentYear)
 297                 n = count_born_on(month,day,bd)
 298
 299                 if bd=='died':
 300                         out+=dcstart
 301                 if day!=0:
 302                         out+="%s %s %s on this day %s" \
 303                         %(n,eU.print_people(n),bd, newLine)
 304                 elif month!=0:
 305                         out+="%s %s %s in this month on unknown day %s"\
 306                         %(n,eU.print_people(n),bd,newLine)
 307                 else:
 308                         out+="%s %s %s in unknown month on unknown day %s"\
 309                         %(n,eU.print_people(n),bd,newLine)
 310                 if bd=='died':
 311                         out+=fcend
 312                 out+=pU.table_header(['id','link',bd],newLine)
 313                 for row in run_query(s,t):
 314                         y = pU.print_year(row[2])
 315
 316                         link = pU.name_html(row,newLine)
 317                         out+=pU.table_row([row[1],link,y],newLine)
 318
 319                 out+=pU.table_foot(newLine)
 320                 out+=newLine
 321
 322         if day==0 and month!=0:
 323                 for bd in ['born','died']:
 324                         s = "SELECT name,id,"+bd+"Year FROM people"\
 325                         +" WHERE "+bd+"Month==?"\
 326                         +" AND "+bd+"Year<>?"\
 327                         +" ORDER BY "+bd+"Year;"
 328
 329                         t = (month,presentYear)
 330                         n = count_born_in(month,bd)
 331
 332                         if bd=='died':
 333                                 out+=dcstart
 334                         out+="%s %s %s in this month %s" \
 335                                 %(n,eU.print_people(n),bd, newLine)
 336                         if bd=='died':
 337                                 out+=fcend
 338
 339                         out+=pU.table_header(['link',bd],newLine)
 340                         for row in run_query(s,t):
 341                                 link = pU.name_html(row,newLine)
 342                                 y = pU.print_year(row[2])
 343
 344                                 out+=pU.table_row([link,y],newLine)
 345                         out+=pU.table_foot(newLine)
 346                         out+=newLine
 347
 348         return out
 349
 350
 351 def alive_on(day,month,year,newLine):
 352
 353         bornBefore = "(bornMonth<? OR"\
 354                         +" (bornMonth==? AND bornDay<=?))"
 355
 356         diedAfter = "(diedMonth>? OR diedMonth==0"\
 357                         +" OR (diedMonth==? AND (diedDay>=? OR diedDay==0)))"
 358
 359         bornOrder = "bornyear,bornmonth,bornday"
 360         diedOrder = "diedyear,diedmonth,diedday"
 361         s = "SELECT name,ID,bornYear,diedYear FROM people"\
 362                 +" WHERE"\
 363                 +" bornYear<>0 AND diedYear<>0 AND("\
 364                 +" (bornYear<? AND diedYear>?)"\
 365                 +" OR"\
 366                 +" (bornYear==? AND diedYear>? AND "+bornBefore+")"\
 367                 +" OR"\
 368                 +" (bornYear<? AND diedYear==? AND "+diedAfter+")"\
 369                 +" OR"\
 370                 +" (bornYear==? and diedYear==? AND "\
 371                 +bornBefore+" AND "+diedAfter+")"\
 372                 +")"\
 373                 +"ORDER BY "+bornOrder+"," + diedOrder+";"
 374
 375         yeart = (year,year)
 376         bbt=(month,month,day)
 377         dat = (month,month,day)
 378         t = yeart+yeart+bbt+yeart+dat+yeart+bbt+dat
 379         out=pU.table_header(['link','born','died'],newLine)
 380         for row in run_query(s,t):
 381                 link=pU.name_html(row,newLine)
 382                 born = pU.print_year(row[2])
 383                 died = pU.print_year(row[3])
 384                 out+=pU.table_row([link,born,died],newLine)
 385
 386         out+=pU.table_foot(newLine)
 387
 388         return out
 389
 390 def list_people_parents():
 391         s = "SELECT name,id"\
 392                 +" FROM people"\
 393                 +" ORDER BY id;"
 394
 395         output = []
 396         for row in run_query(s,()):
 397
 398                 ID = row[1]
 399                 [parents, parentIDs,parentNames] = find_parents(ID)
 400                 [spouses,spousesID,spousesNames,sD] = find_spouses(ID)
 401
 402                 [self,myID,myName] = find_person(ID)
 403                 output.append([self,parents,spouses])
 404         return output
 405
 406
 407 def list_people(newLine):
 408         s = "SELECT name,id,bornyear,fullname,diedYear,url"\
 409         +" FROM people"\
 410         +" ORDER BY bornyear,diedYear;"
 411
 412         out = pU.table_header(['ID','link','wiki','name','born','died'],\
 413                 newLine)
 414         year = 0
 415         out+=pU.table_row(['','',\
 416                 '<b>Unknown Year</b>',0,0,''],newLine)
 417         for row in run_query(s,()):
 418                 if row[2]!=0 and row[2]/100==0:
 419                         out+=pU.table_row(['','','',\
 420                         '<b>1st century</b>'\
 421                         ,1,100],newLine)
 422                 if row[2]/100!=year/100:
 423                         century = row[2]/100 + 1
 424                         start = century * 100 -99
 425                         end = century * 100
 426                         out+=pU.table_row(['','','',\
 427                         '<b>'+eU.ordinal_numbers(century)+' century</b>',\
 428                                 start,end],newLine)
 429
 430
 431                 ID = row[1]
 432                 link = pU.name_html(row,newLine)
 433 #               if row[5] != '.':
 434 #                       struct = fetch_page(row[5])
 435 #                       if struct.has_key('query'):
 436 #                               pName = struct['query']['pages'].keys()[0]
 437 #                               wikiName = struct['query']['pages'][pName]['title']
 438 #                       else:
 439 #                               wikiName = row[5]
 440 #               else:
 441 #                       wikiName = ''
 442                 wikiName = row[5]
 443                 wikiLink = "<a href='" + wikiName + "'>"+wikiName+"</a>"
 444
 445                 a = re.search('#',wikiName)
 446                 if a:
 447                         wikiName = '#'
 448                         wikiLink = wikiName
 449                 if row[5]=='.':
 450                         wikiName = 'None'
 451                         wikiLink = wikiName
 452
 453
 454                 name = row[3]
 455                 born = pU.print_year(row[2])
 456                 died = pU.print_year(row[4])
 457
 458                 out+=pU.table_row([ID,link,wikiLink,name,born,died],newLine)
 459                 year = row[2]
 460         out+=pU.table_foot(newLine)
 461         return out
 462
 463 def list_page(newLine):
 464         s = "SELECT name,ID,url FROM people ORDER BY ID;"
 465         t = ()
 466         out = ''
 467
 468         check=[]
 469         for row in run_query(s,t):
 470                 if row[2]!='.':
 471                         topLink = "%d   <a href='%s'>%s</a>" %(row[1],row[2],row[0])
 472                         print row[1]
 473                         myLink = pU.name_html(row,newLine)
 474                         topLink = topLink+' '+myLink
 475                         url = row[2]
 476                         id = row[1]
 477
 478                         [fam,count,checkMe] = find_fam(id,url,newLine)
 479                         if checkMe==0:
 480                                 out+=topLink+newLine+fam+newLine
 481                         else:
 482                                 check.append([id,topLink,url,fam,count])
 483
 484         outDone = out
 485         out = 'MATCHED'+newLine
 486         hold = 'CHECK COUNT'+newLine
 487         checkAgain=[]
 488         for p in check:
 489                 id = p[0]
 490                 topLink=p[1]
 491                 url=p[2]
 492                 fam=p[3]
 493                 countF = p[4]
 494                 print id
 495
 496                 links,countL = find_links(url)
 497                 if links:
 498                         if links[0]!=-1:
 499                                 this=topLink+newLine+fam+newLine
 500
 501                                 this+='<ul>'
 502                                 for l in links:
 503                                         this+='<li>'+l+newLine+'\n'
 504                                 this+='</ul>'
 505                                 good=1
 506                                 for i in range(3):
 507                                         if countL[i]<countF[i]:
 508                                                 good = 0;
 509                                 if good==1:
 510                                         out+=this
 511                                 else:
 512                                         hold+=\
 513                         this+newLine+str(countF)+str(countL)+newLine
 514
 515                         else:
 516                                 checkAgain.append(p)
 517                 else:
 518                                 checkAgain.append(p)
 519         outMatched = out
 520         outUnMatched = hold
 521
 522         out=newLine + 'CHECK NO INFO'+newLine
 523         for p in checkAgain:
 524                 id = p[0]
 525                 topLink=p[1]
 526                 url=p[2]
 527                 fam=p[3]
 528                 print id
 529
 530                 if re.search('#',url):
 531                         continue
 532                 out+=topLink+newLine+fam
 533
 534         return [outDone,outMatched,outUnMatched,out]
 535
 536 def find_fam(id,link,newLine):
 537         out = ''
 538
 539         [parents, parentIDs,parentNames] = find_parents(id)
 540         [spouses,spousesID,spousesNames,sD] = find_spouses(id)
 541         [nodes,IDs,names,childrenBorn] = \
 542                 find_children(id)
 543
 544         relIDs = parentIDs+spousesID
 545         for ID in IDs:
 546                 relIDs.append(ID[0])
 547
 548         out+='<ul>'
 549         findUrl = 'SELECT url,name FROM people WHERE ID=?'
 550         findName = 'SELECT name,ID FROM people WHERE ID=?'
 551         anyCheck = 0;
 552         count = [0, 0, 0]
 553         for ID in relIDs:
 554                 t = (ID,)
 555                 if ID in parentIDs:
 556                         type = 'parent'
 557                         count[0]+=1
 558                 elif ID in spousesID:
 559                         type = 'spouse'
 560                         count[1]+=1
 561                 else:
 562                         type = 'child'
 563                         count[2]+=1
 564
 565                 for row in run_query(findUrl,t):
 566                         url = row[0]
 567
 568                         if url!='.':
 569                                 url = row[0]
 570                                 title = row[1]
 571                                 out+='<li>'+type+" <a href="+url\
 572                                 +">"+title+"</a>"+newLine
 573                         else:
 574                                 for row in run_query(findName,t):
 575 #                                       out+='<li>'+pU.name_html(row,newLine)\
 576 #                                       +'CHECK'+newLine
 577                                         name=row[0]
 578                                         id = str(row[1])
 579                                         out+='<li>'+type+' '+name + ' '+id+\
 580                                                 ' CHECK'+newLine
 581                                         anyCheck = 1
 582
 583         out+='</ul>'
 584         return [out,count, anyCheck]
 585
 586 def fetch_page(url):
 587         import json
 588
 589         title = url.split('/')[-1]
 590         url = 'http://en.wikipedia.org/w/api.php?'\
 591                 +'format=json&action=query'\
 592                 +'&titles='+title\
 593                 +'&prop=revisions&rvprop=content&redirects'
 594
 595         r = urllib2.urlopen(url)
 596         t = r.read()
 597         jd = json.JSONDecoder()
 598         struct = jd.decode(t)
 599
 600
 601         return struct
 602
 603 def find_infoBox(url):
 604         struct = fetch_page(url)
 605         try:
 606                 pages = struct['query']['pages'].keys()
 607         except:
 608                 return
 609
 610         startPatt = re.compile('{{',re.DOTALL)
 611         endPatt = re.compile('}}',re.DOTALL)
 612         infoboxPatt = re.compile('{{( )*[Ii]nfobox',re.DOTALL)
 613
 614         isIBOX = 0
 615         for p in pages:
 616                 try:
 617                         page = struct['query']['pages'][p]['revisions'][0]['*']
 618                 except:
 619                         return
 620
 621                 title = struct['query']['pages'][p]['title']
 622                 iBox = re.search(infoboxPatt,page)
 623                 starts = re.finditer(startPatt,page)
 624                 ends = re.finditer(endPatt,page)
 625
 626                 if iBox==None:
 627                         continue
 628
 629                 myStart = iBox.start()
 630                 isIBOX=1
 631
 632                 countMe = 0
 633                 start = -1
 634                 while start<myStart:
 635                         start = starts.next().start()
 636                         end = -1
 637                 while end<myStart:
 638                         end = ends.next().start()
 639                 while 1==1:
 640                         if start<end:
 641                                 countMe+=1
 642                                 start = starts.next().start()
 643                         elif end<start:
 644                                 countMe-=1
 645                                 myEnd = end
 646                                 end = ends.next().start()
 647                         if countMe==0:
 648                                 break
 649                 info = page[myStart+2:myEnd]
 650
 651         if isIBOX==1:
 652                 return info
 653         else:
 654                 return
 655
 656 def find_links(url):
 657
 658         info = find_infoBox(url)
 659
 660         if info:
 661                 l,c = wikilink_box(info)
 662                 return l,c
 663         else:
 664                 return [-1],[]
 665
 666 def wikilink_box(text):
 667         linkPatt = "([^=]*?)\[\[(.*?)\]\](.*?)"
 668         #linkPatt = "\|(.*?)\[\[(.*?)\]\](.*?)\n"
 669         #linkPatt = "(.*?)\[\[(.*?)\]\]([^<]*?)[\\n|<br.*?>|,]"
 670         links = []
 671
 672         commentPatt = "<!--.*?>"
 673         sizePatt = "<[/]*small>"
 674         text=re.sub(commentPatt,'',text)
 675         text = re.sub(sizePatt,'',text)
 676
 677         pscPatt = "father|mother|parent|spouse|issue|child"
 678         typePatt = "(.*?)="
 679         lines = re.split('\\n',text)
 680         count = [0, 0,0]
 681         for line in lines:
 682                 if re.search(pscPatt,line):
 683                         t = re.search(typePatt,line)
 684                         if t is None:
 685                                 continue
 686                         type = t.group(1)
 687                         if re.search('father|mother|parent',type):
 688                                 c=0
 689                         elif re.search('spouse',type):
 690                                 c=1
 691                         else:
 692                                 c=2
 693                         people = re.split('<br.*?>',line)
 694                         for p in people:
 695                                 count[c]+=1
 696                                 if re.search(linkPatt,p):
 697                                         l = re.search(linkPatt,p)
 698                                         url = wiki_to_url(l.group(2))
 699                                         t = l.group(2).split('|')[-1]
 700
 701                                         myLink = '<a href="%s">%s</a>' \
 702                                         %(url,t)
 703                                         link = l.group(1)+myLink+l.group(3)
 704                                         add=type+link
 705                                 else:
 706                                         add = type+' '+p
 707                                 links.append(add)
 708         return links,count
 709
 710
 711 def wiki_to_url(wiki):
 712         base = 'https://en.wikipedia.org/wiki/'
 713         u = wiki.split('|')[0]
 714         u = re.sub(' ','_',u)
 715
 716         return base+u
 717
 718 def check_true(newLine):
 719         s = "SELECT name,id,url,born,died FROM people WHERE id>? and id<? ORDER BY id;"
 720         t = (0,5000)
 721
 722         out='<table border = "1" style = "width:100%">'
 723         out+='<tr>'
 724         out+='<th style="width:20%">Name</th>'
 725         out+='<th style="width:40%">Toy</th>'
 726         out+='<th style="width:40%">Wiki</th>'
 727         out+='</tr>'
 728
 729         for row in run_query(s,t):
 730                 if row[2]=='.':
 731                         continue
 732                 struct = fetch_page(row[2])
 733                 try:
 734                         p = struct['query']['pages'].keys()[0]
 735                 except:
 736                         continue
 737
 738                 title = struct['query']['pages'][p]['title']
 739
 740                 topLink = "%d   <a href='%s'>%s</a>" %(row[1],row[2],title)
 741
 742                 myLink = pU.name_html(row,newLine)
 743                 topLink = topLink+newLine+myLink
 744                 url = row[2]
 745                 id = row[1]
 746
 747                 [fam,count,checkMe] = find_fam(id,url,newLine)
 748                 infobox = find_infoBox(url)
 749
 750
 751                 if checkMe!=0:
 752                         continue
 753                 out+='<tr>'
 754                 out+='<td>'+topLink+'</td>'
 755
 756                 if infobox is None:
 757                         out+='</tr>'
 758                         continue
 759
 760
 761
 762                 out+='<td>'+fam+newLine+'born:'+row[3]+newLine\
 763                         +'died:'+row[4]+'</td>'
 764
 765
 766                 wikiLink='\\[\\[([^\\|\\]]*)\\|*(.*?)\\]\\]'
 767                 htmlLink=r'<a href="https://en.wikipedia.org/wiki/\1">\1</a>'
 768
 769                 htmlBox = re.sub(wikiLink,htmlLink,infobox)
 770
 771                 place = 0
 772                 starts = re.finditer('{{',htmlBox)
 773                 stops = re.finditer('}}',htmlBox)
 774                 last = len(htmlBox)
 775
 776                 try:
 777                         start = starts.next().start()
 778                         stop = stops.next().start()
 779                 except:
 780                         start = last
 781                         stop = last
 782
 783                 lines=[]
 784                 inList= 0
 785
 786                 for i in re.finditer('\|',htmlBox):
 787                         if i.start()>start and start<stop:
 788                                 inList+=1
 789                                 try:
 790                                         start= starts.next().start()
 791                                 except:
 792                                         start = last
 793                         elif i.start()>stop and stop<start:
 794                                 inList-=1
 795                                 try:
 796                                         stop = stops.next().start()
 797                                 except:
 798                                         stop = last
 799
 800                         if inList==0:
 801                                 lines.append(htmlBox[place:i.start()])
 802                                 place = i.start()
 803
 804
 805                 of_interest = 'father|mother|parent|child|issue'\
 806                         +'|spouse|birth_date|death_date'
 807
 808                 out+='<td>'
 809
 810                 parents = []
 811                 children=[]
 812                 spouse=[]
 813                 dates=[]
 814
 815                 for l in lines:
 816                         if re.search('father|mother|parent',l):
 817                                 parents.append(l)
 818                         if re.search('child|issue',l):
 819                                 children.append(l)
 820                         if re.search('spouse',l):
 821                                 spouse.append(l)
 822                         if re.search('birth_date|death_date',l):
 823                                 dates.append(l)
 824
 825                 for p in parents:
 826                         out+=p+newLine
 827                 for s in spouse:
 828                         out+=s+newLine
 829                 for c in children:
 830                         out+=c+newLine
 831                 for d in dates:
 832                         out+=d+newLine
 833
 834                 out+='</td></tr>'
 835
 836         out+='</table>'
 837         return out
 838
 839
 840
 841
 842 def search_name(name,newLine):
 843         s = "SELECT name, people.ID,fullname,BornYear,DiedYear"\
 844         +" FROM people"\
 845         +" WHERE fullname LIKE ? or name LIKE ?"\
 846         +" ORDER BY BornYear;"
 847
 848         s2 = "SELECT family FROM families where ID=?"
 849
 850         IDs=[]
 851         names=[]
 852
 853         out = 'Names starting with %s:%s' % (name,newLine)
 854         t = (name + '%',name+'%')
 855         fullIDs=[]
 856         out+=pU.table_header(['link','name','born','died','family'],newLine)
 857         for row in run_query(s,t):
 858                 b = pU.print_year(row[3])
 859                 d = pU.print_year(row[4])
 860
 861                 id = row[1]
 862                 fams=''
 863                 for r in run_query(s2,(id,)):
 864                         fams+=r[0]+' '
 865
 866                 out+=pU.table_row([pU.name_html(row,newLine),row[2],b,d,fams],\
 867                         newLine)
 868
 869                 fullIDs.append(row[1])
 870                 names.append(row[0])
 871                 IDs.append(row[1])
 872         out+=pU.table_foot(newLine)
 873
 874         t = ('%' + name + '%','%'+name+'%')
 875         out += '%sNames containing %s:%s' %(newLine,name,newLine)
 876         out+=pU.table_header(['link','name','born','died','family'],newLine)
 877         for row in run_query(s,t):
 878                 if row[1] not in fullIDs:
 879                         b = pU.print_year(row[3])
 880                         d = pU.print_year(row[4])
 881
 882                         id = row[1]
 883                         fams=''
 884                         for r in run_query(s2,(id,)):
 885                                 fams+=r[0]+' '
 886
 887                         out+=pU.table_row([pU.name_html(row,newLine),\
 888                                 row[2],b,d,fams],newLine)
 889                         names.append(row[0]+','+row[2])
 890                         IDs.append(row[1])
 891         out+=pU.table_foot(newLine)
 892
 893         s = '''SELECT name,people.ID,style,fullname,bornYear, diedYear,
 894         startYear,stopYear,family
 895         FROM people INNER JOIN styles
 896         ON styles.id = people.id
 897         LEFT JOIN families ON people.ID = families.ID
 898         WHERE style LIKE ?
 899         ORDER BY bornYear;'''
 900
 901         out += '%sStyles containing %s:%s' %(newLine,name,newLine)
 902         out+=pU.table_header(['link','name','born',\
 903                 'died','style','from','to','family'],newLine)
 904         t = (t[0],)
 905         for row in run_query(s,t):
 906                 b = pU.print_year(row[4])
 907                 d = pU.print_year(row[5])
 908                 start = pU.print_year(row[6])
 909                 stop = pU.print_year(row[7])
 910                 out+=pU.table_row([pU.name_html(row,newLine),\
 911                         row[3],b,d,row[2],start,stop,row[8]],newLine)
 912
 913         out+=pU.table_foot(newLine)
 914         return [out,names,IDs]
 915
 916 def count_names(newLine):
 917         s="SELECT names.name, count(*),"\
 918         +" min(nullif(bornYear,0)),max(nullif(bornYear,0))"\
 919         +" FROM names INNER JOIN people on people.ID = names.id"\
 920         +" group by names.name"\
 921
 922
 923         t = "SELECT count(*) FROM people WHERE firstName = ?"
 924
 925         out=pU.table_header(\
 926                 ['name','count','earliest born',\
 927                 'latest born','range','count as first name'],\
 928                 newLine)
 929         for row in run_query(s,()):
 930                 for r in run_query(t,(row[0],)):
 931                         c =r[0]
 932                 if row[3] is not None and row[2] is not None:
 933                         range = row[3]-row[2]
 934                 else:
 935                         range = None
 936                 out+=pU.print_name_count(row,range,c,newLine)
 937         out+=pU.table_foot(newLine)
 938
 939         return out
 940
 941 def people_with_name(name,newLine):
 942         s = "SELECT name, ID,fullname,bornYear,diedYear"\
 943         +" FROM people"\
 944         +" WHERE firstname = ?"\
 945         +" ORDER BY bornYear;"
 946
 947         t = (name,)
 948
 949         out='As a first name'+newLine
 950         out += pU.table_header(['ID','name','link','born','died'],newLine)
 951         for row in run_query(s,t):
 952                 myName = pU.name_html(row,newLine)
 953                 by = pU.print_year(row[3])
 954                 dy = pU.print_year(row[4])
 955
 956                 out+=pU.table_row([row[1],row[2],myName,by,dy],newLine)
 957
 958         out+=pU.table_foot(newLine)
 959
 960         s = "SELECT people.name, people.ID,fullname,bornYear,diedYear"\
 961         +" FROM people"\
 962         +" LEFT JOIN names ON people.ID = names.ID"\
 963         +" WHERE names.name=? AND firstName<>?"\
 964         +" ORDER BY bornYear;"
 965
 966         t = (name,name)
 967         out+=newLine+'As a middle name'+newLine
 968         out +=pU.table_header(['ID','name','link','born','died'],newLine)
 969         for row in run_query(s,t):
 970                 myName = pU.name_html(row,newLine)
 971                 by = pU.print_year(row[3])
 972                 dy = pU.print_year(row[4])
 973
 974                 out+=pU.table_row([row[1],row[2],myName,by,dy],newLine)
 975
 976         out+=pU.table_foot(newLine)
 977
 978
 979
 980         return out
 981
 982
 983 def count_children(newLine):
 984
 985         s = "SELECT count(*),nc"\
 986         +" FROM ("\
 987         +" SELECT count(*) AS nc"\
 988         +" FROM parents INNER JOIN people ON parentID = people.ID"\
 989         +" GROUP BY parentID"\
 990         +" HAVING parentID <>'?'"\
 991         +" AND parentID <> '0')"\
 992         +" GROUP BY nc;"
 993
 994         out = ''
 995         for row in run_query(s,()):
 996                 out += pU.print_children_count(row,newLine)
 997         return out
 998
 999 def parents_with_children(nChildren,newLine):
1000         s = "SELECT name,parentID"\
1001         + " FROM parents"\
1002         + " INNER JOIN people"\
1003         + " ON parentID = people.ID"\
1004         + " GROUP BY parentID"\
1005         + " HAVING count(*) = ?"\
1006         + " AND parentID <> 0"\
1007         + " ORDER BY bornYear;"
1008
1009
1010         u = "SELECT count(*)"\
1011         +" FROM  parents INNER JOIN people"\
1012         +" ON parents.ID = people.ID"\
1013         +" WHERE parentID = ?"\
1014         +" AND (diedyear-bornyear>? OR died='present');"
1015
1016         out = "People who had %s %s:%s" \
1017                 %(nChildren,eU.print_children(nChildren),newLine)
1018
1019         out+=pU.table_header(['link','survived infancy','survived to adulthood'],newLine)
1020         for row in run_query(s,(nChildren,)):
1021                 link = pU.name_html(row,newLine)
1022                 for r in run_query(u,(row[1],1)):
1023                         infant = r[0]
1024                 for r in run_query(u,(row[1],18)):
1025                         adult = r[0]
1026                 out +=pU.table_row([link,infant,adult],newLine)
1027         out+=pU.table_foot(newLine)
1028         return out
1029
1030
1031 def count_age_at_child(newLine):
1032
1033         s = "SELECT age,count(*)"\
1034                 +" FROM "+ageChildTable\
1035                 +" GROUP BY age ORDER BY age;"
1036
1037         out = ''
1038         for row in run_query(s,()):
1039                 out +=pU.print_age_child_count(row,newLine)
1040
1041         return out
1042
1043 def people_had_child_at_age(age,newLine):
1044
1045
1046         s = "SELECT age,cname,cID,pname,pID,bornYear"\
1047                 +" FROM "+ageChildTable\
1048                 +" WHERE age = ?;"
1049
1050         t = (int(age),)
1051         out = 'At age ' + str(age) + ' :'
1052         out+=pU.table_header(['parent','child','year'],newLine)
1053         for row in run_query(s,t):
1054                 parent = pU.name_html([row[3],row[4]],newLine)
1055                 child = pU.name_html([row[1],row[2]],newLine)
1056                 year = pU.print_year(row[5])
1057                 out+=pU.table_row([parent,child,year],newLine)
1058 #               out += "%s%s had %s" % (newLine,parent,child)
1059         out+=pU.table_foot(newLine)
1060         return out
1061
1062 def count_age_at_death(newLine):
1063
1064
1065         s = "SELECT age,count(*)"\
1066                 +" FROM"\
1067                 + ageTable\
1068                 +" WHERE diedYear<>?"\
1069                 +" GROUP BY age;"
1070
1071         out=''
1072         for row in run_query(s,(presentYear,)):
1073                 out += pU.print_age_death_count(row,newLine)
1074
1075         s = "select diedYear,count(*)"\
1076                 +" FROM people"\
1077                 +" WHERE diedYear==?"\
1078                 +" GROUP BY diedYear;"
1079         for row in run_query(s,(presentYear,)):
1080                 out += pU.print_age_death_count(row,newLine)
1081
1082         return out
1083
1084 def people_died_at_age(age,newLine):
1085
1086
1087         if age != str(presentYear):
1088
1089                 s = "SELECT age,name,ID,diedYear,bornYear"\
1090                 +" FROM "\
1091                 + ageTable\
1092                 +" WHERE age = ?"\
1093                 +" ORDER BY diedYear;"
1094
1095
1096                 t = (int(age),)
1097                 out = 'Died at age ' +str(age)+newLine
1098                 out+=pU.table_header(['link','in'],newLine)
1099                 for row in run_query(s,t):
1100                         link = pU.name_html([row[1],row[2]],newLine)
1101                         year = pU.print_year(row[3])
1102                         out+=pU.table_row([link,year],newLine)
1103         else:
1104                 s = "SELECT diedYear, name,ID,bornYear,url"\
1105                         +" FROM people"\
1106                         +" WHERE diedYear = ?;"
1107                 out = 'Still alive'+newLine
1108                 out+=pU.table_header(['link','born in','wikipedia'],newLine)
1109                 for row in run_query(s,(presentYear,)):
1110                         link = pU.name_html([row[1],row[2]],newLine)
1111                         born = pU.print_year(row[3])
1112                         if row[4]=='.':
1113                                 wlink = ''
1114                         else:
1115                                 wlink = '<a href="'+row[4]+'">wikipedia</a>'
1116                         out+=pU.table_row([link,born,wlink],newLine)
1117         out+=pU.table_foot(newLine)
1118         return out
1119
1120
1121 def all_ancestors(personID,newLine):
1122
1123
1124         s = "SELECT name,id FROM people WHERE id=?"
1125         t = (personID,)
1126         out=pU.print_tagged_query("Ancestors of ",s,t,newLine)
1127
1128         names = ['me']
1129
1130         allAncestors,levelDict = gQ.ancestors(int(personID))
1131
1132         for level in levelDict.keys():
1133
1134                 out += eU.parent_level(level,'parent')+':'+newLine
1135
1136                 for a in levelDict[level]:
1137                         if eU.is_number(a):
1138                                 for r in run_query(s,(a,)):
1139                                         out+=pU.name_html(r,newLine)+newLine
1140                         else:
1141                                 out+=a+newLine
1142
1143         image = "<img src = ancestorGraph.py?id=%s>" %personID
1144         out +=newLine + image+newLine
1145
1146         return [out, allAncestors,levelDict]
1147 def all_descendants(personID,newLine):
1148         s = "SELECT name,id,bornYear,diedYear FROM people WHERE id=?"
1149         t = (personID,)
1150         out = pU.print_tagged_query("Descendants of ",s,t,newLine)
1151         names = ['me']
1152         allDescendants,levelDict = gQ.descendants(int(personID))
1153         for level in levelDict.keys():
1154                 out+=eU.parent_level(level,'child')+':'+newLine
1155
1156
1157                 out+=pU.table_header(['Name','Born','Died'],newLine)
1158                 for a in levelDict[level]:
1159                         if eU.is_number(a):
1160                                 for r in run_query(s,(a,)):
1161                                         n=pU.name_html(r,newLine)
1162                                         b = r[2]
1163                                         d = r[3]
1164                         else:
1165                                 n=a
1166                                 b=0
1167                                 d=0
1168                         if b==0:
1169                                 b = '?'
1170                         if d==0:
1171                                 d = '?'
1172                         out+=pU.table_row([n,b,d],newLine)
1173
1174
1175                 out+=pU.table_foot(newLine)
1176
1177         return [out, allDescendants,levelDict]
1178 def check_relationship(a,b):
1179         s = 'SELECT related FROM marriages'\
1180                 +' WHERE ida =? AND idb=? AND related<>0'
1181         t = (min(a,b),max(a,b))
1182         for r in run_query(s,t):
1183                 return r[0]
1184
1185 def update_relationship(a,b,r,commit):
1186         s = 'SELECT related FROM marriages'\
1187                 +' WHERE ida =? AND idb=?'
1188         t = (min(a,b),max(a,b))
1189
1190         for row in run_query(s,t):
1191                 u = "UPDATE marriages SET Related = ?"\
1192                 +" WHERE ida = ? AND idb=?;"
1193                 v = (r,)+t
1194                 try:
1195                         run_query(u,v)
1196                         if commit==1:
1197                                 commit_changes()
1198                         return
1199                 except:
1200                         return
1201
1202
1203 def find_relationship(IDA,IDB,commit):
1204         nameList=[]
1205         s  = "SELECT name,id FROM people WHERE id ==?"
1206         for id in (IDA,IDB):
1207                 t = (id,)
1208                 for row in run_query(s,t):
1209                         nameList.append(pU.name_html(row,'<br>'))
1210
1211
1212         mrca,orca,aL,bL = gQ.relationship(IDA,IDB)
1213         related = eU.relationship(aL,bL,nameList)
1214         update_relationship(IDA,IDB,related,commit)
1215         return related
1216
1217 def relation_text(IDA,IDB,newLine):
1218
1219         IDA = int(IDA)
1220         IDB = int(IDB)
1221         related = check_relationship(IDA,IDB)
1222
1223         if related is None:
1224                 related = find_relationship(IDA,IDB,1)
1225
1226
1227         return related
1228
1229 def common_ancestors(IDA,IDB,newLine):
1230         related = relation_text(IDA,IDB,newLine)
1231         out = related+newLine
1232
1233         if related[-11:]!='not related':
1234                 mrca,orca,aL,bL = gQ.relationship(int(IDA),int(IDB))
1235                 s = 'SELECT name,id FROM people WHERE id=?'
1236                 cText = 'Most recent common ancestors:'+newLine
1237                 for c in mrca:
1238                         for row in run_query(s,(c,)):
1239                                 cText+=pU.name_html(row,newLine)+newLine
1240                 cText+=newLine
1241                 findName = re.compile('(<a.*?</a>) and (<a.*?</a>).*')
1242                 findNameP = re.compile('(<a.*?</a>) is (<a.*?</a>).*')
1243                 found = findName.search(related)
1244                 if found is None:
1245                         found = findNameP.search(related)
1246                 nameA = found.group(1)
1247                 nameB = found.group(2)
1248                 cText+=nameA+"'s "+eU.parent_level(aL,'parent')\
1249                         +newLine\
1250                         +nameB+"'s "\
1251                         +eU.parent_level(bL,'parent')
1252
1253
1254                 out += newLine+cText
1255
1256                 image = "<img src = jointAncestorGraph.py?id=%s&id2=%s&mL=%s>"\
1257                         %(IDA,IDB,max(aL,bL))
1258
1259                 out +=newLine + image
1260         out+=newLine
1261
1262         return out
1263
1264 def list_territories(newLine):
1265         s = "SELECT DISTINCT short"\
1266         +" FROM styleDecomp"\
1267         +" ORDER BY short;"
1268
1269         out = ''
1270         terrs = []
1271         for row in run_query(s,()):
1272                 match = 0
1273
1274                 for i in range(len(eU.maleStyles)):
1275                         m = eU.maleStyles[i]+' '
1276                         if re.search(m,row[0]):
1277                                 match = 1
1278                                 break
1279                 if match==1:
1280                         t = row[0]
1281                 else:
1282                         t = eU.swap_gender(row[0])
1283
1284                 if t not in terrs:
1285                         terrs.append(t)
1286
1287         for i in range(len(terrs)):
1288                 terrs[i] = eU.make_plural(terrs[i])
1289
1290         for terr in terrs:
1291                 out += pU.terr_html(terr,newLine,0,0) +newLine
1292
1293         return out
1294
1295
1296 def list_families(newLine):
1297         s = "SELECT family, count(*)"\
1298         +" FROM families GROUP BY family ORDER BY family;"
1299         out = pU.table_header(['family','members'],newLine)
1300
1301         for row in run_query(s,()):
1302                 link=pU.fam_html(row[0],newLine)
1303                 count = row[1]
1304                 out+=pU.table_row([link,count],newLine)
1305
1306
1307         out+=pU.table_foot(newLine)
1308         return out
1309
1310 def combine_states(aTerritory):
1311         p = aTerritory
1312         places = []
1313
1314         predeccessorStates = eU.predeccessorStates
1315         successorStates = eU.successorStates
1316
1317         ap = eU.make_male(aTerritory)
1318         while predeccessorStates.has_key(ap):
1319                 ap = predeccessorStates[ap][0]
1320                 places.append(ap)
1321
1322
1323         ap = eU.make_male(aTerritory)
1324         while successorStates.has_key(ap):
1325                 ap = successorStates[ap]
1326                 places.append(ap)
1327
1328         return places
1329
1330 def people_in(fam,newLine):
1331         s = 'SELECT name,people.id,bornYear,diedYear FROM'\
1332         +' people INNER JOIN families'\
1333         +' ON people.id = families.id'\
1334         +' WHERE family = ? ORDER BY bornYear;'
1335         t = (fam,)
1336
1337         out='Family: %s' %fam
1338         out+=newLine+newLine
1339
1340         out += pU.table_header(['id','name','born','died'],newLine)
1341         ids=''
1342         for row in run_query(s,t):
1343                 name=pU.name_html(row,newLine)
1344
1345                 b = row[2]
1346                 d = row[3]
1347                 if b==0:
1348                         b='?'
1349                 if d==0:
1350                         d = '?'
1351                 if d==presentYear:
1352                         d='present'
1353                 out+=pU.table_row([row[1],name,b,d],newLine)
1354                 ids+='%d,' % row[1]
1355         ids=ids[:-1]
1356         out+=pU.table_foot(newLine)
1357
1358         image = "<img src = rulersGraph.py?names=%s>"\
1359                 % ids
1360         out+=image
1361         return out
1362
1363
1364 def rulers_of(aTerritory,newLine):
1365
1366         out = pU.terr_html(eU.make_male(aTerritory),newLine,0,0)+newLine
1367
1368         otherStates = combine_states(aTerritory)
1369         if len(otherStates)>0:
1370                 out+="Otherwise:"+newLine
1371         for s in otherStates:
1372                 out+=pU.terr_html(s,newLine,0,0)+newLine
1373
1374         out += find_rulers(aTerritory,newLine)[0]
1375
1376
1377         image = "<img src = rulersGraph.py?terr=%s>" \
1378                 %(re.sub(' ','%20',aTerritory))
1379         out+=image
1380         return out
1381
1382 def find_rulers(aTerritory,newLine):
1383         places = [eU.make_singular(aTerritory)]
1384         places+=combine_states(aTerritory)
1385
1386         out = ''
1387         fullTerr = []
1388
1389         for p in places:
1390                 s = "SELECT DISTINCT short"\
1391                         +" FROM styleDecomp"\
1392                         +" WHERE short LIKE ?"\
1393                         +" OR short LIKE ?"\
1394                         +" OR short LIKE?;"
1395                 t = ('%'+p+'%','%'+eU.swap_gender(p)+'%',\
1396                 '%'+eU.make_plural(p)+'%')
1397
1398
1399                 for row in run_query(s,t):
1400                         fullTerr.append(row[0])
1401
1402
1403         if len(fullTerr)==0:
1404                 return out,[]
1405
1406         tq = "SELECT styleDecomp.style,name,people.ID,"\
1407                 +" startyear,stopyear,short"\
1408                 +" FROM styleDecomp INNER JOIN styles"\
1409                 +" ON styleDecomp.style = styles.style"\
1410                 +" INNER JOIN people"\
1411                 +" ON people.ID = styles.ID"\
1412                 +" WHERE short IN ("
1413
1414         for i in range(len(fullTerr)):
1415                 tq+='?,'
1416         tq = tq[:-1]
1417         tq+=" )ORDER BY startyear,stopyear;"
1418         s = ''
1419
1420         t = tuple(fullTerr)
1421         rulers = []
1422         out+=pU.table_header(['link','style','from', 'to','house'],newLine)
1423         for row in run_query(tq,t):
1424                 #if row[0]!=s:
1425                 #       out +=newLine+row[0]+newLine
1426                 #       s = row[0]
1427                 fy = pU.print_year(row[3])
1428                 ft = pU.print_year(row[4])
1429
1430
1431                 id = row[2];
1432                 u = "SELECT family FROM families WHERE id = ?"
1433                 v = (id,)
1434                 house = ''
1435                 for r in run_query(u,v):
1436                         house+=pU.fam_html(r[0],newLine)
1437
1438                 out+=pU.table_row([pU.name_html(row[1:],newLine),\
1439                         row[0],fy,ft,house],newLine)
1440
1441                 if row[2] not in rulers:
1442 #                       rulers.append(row[2])
1443                         rulers = [row[2]]+rulers
1444         out+=pU.table_foot(newLine)
1445         return [out,rulers]
1446
1447 def find_adjacent(terr,start,stop,id):
1448         s = "SELECT name,people.id,stopyear"\
1449         +" FROM people INNER JOIN styles"\
1450         +" ON people.id = styles.id"\
1451         +" INNER JOIN styleDecomp"\
1452         +" ON styles.style = styleDecomp.style"\
1453         +" WHERE short = ? AND stopyear<=? AND stopyear <>0 AND people.id<>?"\
1454         +" ORDER BY stopyear DESC, startyear DESC;"
1455
1456
1457         t = (terr,start,id)
1458         myPrevious=[]
1459         y=0
1460         for row in run_query(s,t):
1461                 myPrevious = row[0:2]
1462                 y = row[2]
1463                 break
1464
1465         t = (eU.swap_gender(terr),start,id)
1466         for row in run_query(s,t):
1467                 if row[2]>y:
1468                         myPrevious = row[0:2]
1469                 break
1470
1471         u = "SELECT name,people.id,startyear"\
1472         +" FROM people INNER JOIN styles"\
1473         +" ON people.id = styles.id"\
1474         +" INNER JOIN styleDecomp"\
1475         +" ON styles.style = styleDecomp.style"\
1476         +" WHERE short = ? AND startyear>=? AND startyear <>0 AND people.id<>?"\
1477         +" ORDER BY startyear;"
1478
1479         v = (terr,stop,id)
1480         myNext=[]
1481         y=float('inf')
1482         for r in run_query(u,v):
1483                 myNext = r[0:2]
1484                 y = r[2]
1485                 break
1486
1487
1488         v = (eU.swap_gender(terr),stop,id)
1489         for r in run_query(u,v):
1490                 if r[2]<y:
1491                         myNext = r[0:2]
1492                 break
1493
1494         return [myPrevious, myNext]
1495
1496
1497 def find_person(ID):
1498         s = "SELECT name||','||ID, name, ID FROM people WHERE ID=?"
1499         t = (ID,)
1500
1501         for row in run_query(s,t):
1502                 Self = row[0]
1503                 selfName = row[1]
1504                 selfID = row[2]
1505                 return [Self, selfID,selfName]
1506
1507
1508 def find_parents(ID):
1509         s = "SELECT name, parentID"\
1510                 +" FROM parents LEFT JOIN people"\
1511                 +" ON people.ID = parentID"\
1512                 +" WHERE parents.ID = ?;"
1513         t = (ID,)
1514
1515         parents = []
1516         parentIDs =[]
1517         parentNames=[]
1518
1519         for row in run_query(s,t):
1520                 if row[0]!=None:
1521                         p = row[0] + ',' + str(row[1])
1522                         pID = int(row[1])
1523                         pN = row[0]
1524                 else:
1525                         p = row[1] + ',p' + str(ID)
1526                         pID = 0
1527                         pN = row[1]
1528                 parents.append(p)
1529                 parentIDs.append(pID)
1530                 parentNames.append(pN)
1531
1532         if len(parents)>1 and parents[1]==parents[0]:
1533                 parents[1] = parents[1] + ' 2'
1534
1535         return [parents,parentIDs,parentNames]
1536
1537
1538
1539 def is_married(IDa, IDb):
1540
1541         s = "SELECT idb FROM marriages WHERE ida =?;"
1542         t = (min(IDa,IDb),)
1543
1544
1545         for row in run_query(s,t):
1546                 if int(row[0])==max(IDa,IDb):
1547                         return 1
1548         return 0
1549
1550 def find_spouses(ID):
1551         t = (ID,)
1552
1553
1554         spouses = []
1555         spousesID=[]
1556         spousesNames=[]
1557         spouseDates = []
1558
1559         s = "SELECT ida,a.name,idb,b.name,marriage,end,"\
1560         +" a.bornYear,a.diedYear,b.bornYear,b.diedYear,marriageYear,"\
1561         +" a.diedMonth,a.diedDay,b.diedMonth,b.diedDay"\
1562         +" FROM marriages"\
1563         +" LEFT JOIN people AS a"\
1564         +" ON ida = a.ID"\
1565         +" LEFT JOIN people AS b"\
1566         +" ON idb = b.ID"\
1567         +" WHERE ida = ? OR idb = ?"\
1568         +" ORDER BY marriageYear;"\
1569
1570         t = (ID,ID)
1571         for row in run_query(s,t):
1572                 sID = ''
1573                 if row[0]!=int(ID): #spouse is ida
1574                         if row[1]!=None:
1575                                 s = row[1]+","+str(row[0])
1576                                 sID = row[0]
1577                                 sN = row[1]
1578                         elif row[0]!='':
1579                                 s = row[0]+",s"+str(ID)
1580                                 sID = 0
1581                                 sN = row[0]
1582                         myDates = [row[1],row[0],row[4],row[5],row[6],\
1583                         row[7],row[11],row[12]]
1584                 else: #spouse is idb
1585                         if row[3]!=None:
1586                                 s = row[3]+","+str(row[2])
1587                                 sID = row[2]
1588                                 sN = row[3]
1589                         elif row[2]!='':
1590                                 s = row[2]+",s"+str(ID)
1591                                 sID = 0
1592                                 sN=  row[2]
1593                         myDates = [row[3],row[2],row[4],row[5],row[8],row[9],\
1594                                 row[13],row[14]]
1595                         for i in range(len(myDates)):
1596                                 if myDates[i] is None:
1597                                         myDates[i]=0
1598                 if sID!='':
1599                         spouses.append(s)
1600                         spousesID.append(sID)
1601                         spousesNames.append(sN)
1602                         spouseDates.append(myDates)
1603
1604         return [spouses,spousesID,spousesNames,spouseDates]
1605
1606
1607 def find_children(ID):
1608         s = "SELECT p1.name, p1.ID,p3.parentID,p4.name,p1.bornYear"\
1609                 +" FROM people p1"\
1610                 +" INNER JOIN parents p2"\
1611                 +" ON p1.ID = p2.ID"\
1612                 +" INNER JOIN parents p3"\
1613                 +" ON p1.ID = p3.ID"\
1614                 +" LEFT JOIN people p4"\
1615                 +" ON p3.parentID = p4.ID"\
1616                 +" WHERE p2.parentID = ?"\
1617                 +" AND p3.parentID<>?"\
1618                 +" ORDER BY p1.bornYear,p1.ID;"
1619
1620         t = (ID,ID)
1621
1622         childrenBorn=[]
1623         nodes=[]
1624         IDs=[]
1625         names=[]
1626
1627         for row in run_query(s,t):
1628                 c = row[0] + ',' + str(row[1])
1629                 cID = row[1]
1630                 cName = row[0]
1631                 born = row[4]
1632                 childrenBorn.append(born)
1633                 if row[3]!=None:
1634                         op = row[3] + ',' + str(row[2])
1635                         opID = row[2]
1636                         opN = row[3]
1637                 else:
1638                         op = row[2] + ',s' + str(ID)
1639                         opID = 0
1640                         opN = row[2]
1641
1642                 nodes.append([c,op])
1643                 IDs.append([cID,opID])
1644                 names.append([cName,opN])
1645
1646         return [nodes,IDs,names,childrenBorn]
1647
1648 def person_info(personID,newLine):
1649         t = (personID,)
1650
1651         if newLine=='<br>':
1652                 startP = '<p>'
1653                 endP = '</p>'
1654         else:
1655                 startP = ''
1656                 endP = newLine
1657
1658         mainDiv = ''
1659         #Id, Name, Dates, Style, Style-Dates
1660         s = "SELECT id,name,fullname,url,picture,Born,Died,"\
1661                 +" bornYear,diedYear,diedMonth,diedDay"\
1662                 +" FROM people WHERE ID = ?"
1663         rows = 0
1664         for row in run_query(s,t):
1665                 rows = 1
1666                 mainDiv += startP
1667                 preURL = pU.name_html(['previous',row[0]-1],newLine)
1668                 postURL =  pU.name_html(['next',row[0]+1],newLine)
1669                 mainDiv += 'ID: %s(%s %s)%s' %(row[0],preURL,postURL ,newLine)
1670                 mainDiv += pU.print_tagged_name('Name',[row[1], row[0]]\
1671                         ,newLine)
1672                 mainDiv+='Full Name: '+row[2]+newLine
1673                 mainDiv +=endP
1674
1675                 name = row[1]
1676                 fullname = row[2]
1677                 url = row[3]
1678                 picture = row[4]
1679                 born = row[5]
1680                 died = row[6]
1681                 bornYear = row[7]
1682                 diedYear = row[8]
1683                 diedMonth = row[9]
1684                 diedDay = row[10]
1685
1686                 mainDiv += startP
1687                 mainDiv += '%sBorn:%s%s '% (newLine,born,newLine)
1688
1689
1690
1691                 if died!='present':
1692                         mainDiv +='Died: %s' % died
1693
1694                         if diedYear != 0 and bornYear !=0:
1695
1696                                 u = "SELECT age FROM"+ageTable\
1697                                         +" WHERE id = ?"
1698
1699                                 for row in run_query(u,t):
1700                                         mainDiv += ", aged %s" %(row[0])
1701                 else:
1702                         u = "SELECT age FROM" + ageNowTable\
1703                                 +" WHERE id = ?"
1704
1705                         for row in run_query(u,t):
1706                                 mainDiv +='Still Alive, aged %s' %(row[0])
1707
1708
1709
1710                 mainDiv +=endP
1711
1712         s = 'SELECT family FROM families WHERE ID = ?'
1713
1714         for row in run_query(s,t):
1715                 mainDiv+='Family: %s' % pU.fam_html(row[0],newLine)
1716
1717         if rows==0:
1718                 return ''
1719
1720         s = "SELECT * FROM styles WHERE ID = ?"
1721         for row in run_query(s,t):
1722                 mainDiv += startP
1723                 mainDiv +='%sStyle: %s%s'%(newLine,row[1],newLine)
1724
1725                 mainDiv += 'Territories:%s' % newLine
1726
1727                 w = "SELECT short FROM styleDecomp"\
1728                 +" WHERE style = ?"
1729
1730                 for r in run_query(w,(row[1],)):
1731                         [p,n]=find_adjacent(r[0],row[3],row[5],personID)
1732                         if len(p)>0:
1733                                 mainDiv +='%s|'%(pU.name_html(p,newLine))
1734
1735                         mainDiv+=pU.terr_html(r[0],newLine,row[3],row[5])
1736
1737                         if len(n)>0:
1738                                 mainDiv+='|%s'%(pU.name_html(n,newLine))
1739                         mainDiv+=newLine
1740
1741                 mainDiv +=  'From: '+row[2] + newLine
1742                 mainDiv +=  'To: '+row[4]
1743
1744                 mainDiv += endP
1745
1746
1747
1748
1749         mainDiv += startP
1750         mainDiv += endP
1751
1752         #find parents
1753
1754         [parents,parentIDs,parentNames] = find_parents(personID)
1755         mainDiv += startP
1756         for i in range(len(parents)):
1757                 r = [parentNames[i],parentIDs[i]]
1758                 mainDiv += pU.print_tagged_name('Parent',r,newLine)
1759         mainDiv += endP
1760
1761         #find spouses
1762
1763         [spouses,spousesID,spousesNames,spouseDates] = find_spouses(personID)
1764
1765         mainDiv += startP
1766
1767         for i in range(len(spouses)):
1768                 r = [spousesNames[i],spousesID[i]]
1769                 d = spouseDates[i][2:8]
1770
1771                 mainDiv += pU.print_tagged_name('Spouse',r,newLine)
1772                 if d[0]!='.':
1773                         mainDiv+='married: '+d[0]+newLine
1774                 else:
1775                         mainDiv+='marriage dates not yet recorded'\
1776                         + newLine
1777                 if d[1]!='.':
1778                         mainDiv+='marriage ended: '+d[1]+newLine
1779                 elif d[0]!='.':
1780                         dPrint = 1
1781                         if d[3]<diedYear:
1782                                 y = pU.print_year(d[3])
1783
1784                                 ot = 'their'
1785                         elif d[3]==diedYear and diedYear!=presentYear:
1786                                 y = d[3]
1787
1788                                 if d[4]<diedMonth:
1789                                         ot = 'their'
1790                                 elif d[4]>diedMonth:
1791                                         ot = 'own'
1792                                 else:
1793                                         if d[5]<diedDay:
1794                                                 ot = 'their'
1795                                         elif d[5]>diedDay:
1796                                                 ot = 'own'
1797                                         else:
1798                                                 dPrint = 0
1799                                                 mainDiv+='until both of their deaths in %s%s'\
1800                                                         %(str(y),newLine)
1801
1802                         else:
1803                                 if diedYear==presentYear:
1804                                         dPrint = 0
1805                                         mainDiv+='still married%s'%(newLine)
1806                                 else:
1807                                         y = str(pU.print_year(diedYear))
1808
1809                                         ot='own'
1810                         if dPrint==1:
1811                                 mainDiv+='until %s death in %s%s' %(ot,y,newLine)
1812
1813                 if r[1]!=0:
1814                         mainDiv +=''
1815                         #mainDiv += \
1816                         #       pU.relationship_html(personID,r[1],newLine)\
1817                         #       +newLine
1818                 else:
1819                         mainDiv+=newLine
1820
1821                 marriageStyles = ''
1822                 mStart = int(findYear.find_year(d[0]))
1823                 mStop = int(findYear.find_year(d[1]))
1824                 if mStop == 0:
1825                         mStop = diedYear
1826
1827                 if mStart==0:
1828                         continue
1829
1830
1831                 ss = "SELECT styles.style, short,startyear,"\
1832                 +" stopyear"\
1833                 +" FROM styles INNER JOIN styledecomp"\
1834                 +" ON styles.style = styledecomp.style"\
1835                 +" WHERE  id = ?;"
1836                 st = (spousesID[i],)
1837
1838                 for sr in run_query(ss,st):
1839                         starty = int(sr[2])
1840                         stopy = int(sr[3])
1841
1842                         if mStart>stopy:
1843                                 continue
1844                         if mStop<starty and mStop != 0:
1845                                 continue
1846
1847
1848                         if mStart>starty:
1849                                 fromy = mStart
1850                         else:
1851                                 fromy = starty
1852
1853                         if mStop<stopy and mStop !=0:
1854                                 to = str(mStop)
1855                         elif diedYear<=stopy:
1856                                 to = str(pU.print_year(diedYear))
1857
1858                         else:
1859                                 to = str(sr[3])
1860
1861
1862                         marriageStyles+='%d to %s: %s%s' \
1863                         %(fromy, to,eU.swap_gender(sr[1]),newLine)
1864
1865                 if len(marriageStyles)>0:
1866                         mainDiv+="Through this marriage:"+newLine
1867                         mainDiv+=marriageStyles+newLine
1868
1869         mainDiv  = mainDiv + newLine+endP
1870
1871         #find children
1872         [nodes,IDs,names,childrenBorn] = \
1873                 find_children(personID)
1874
1875         top = ''
1876         for i in range(len(nodes)):
1877                 cr = [names[i][0],IDs[i][0]]
1878                 thisChild = pU.print_tagged_name('Child',cr,newLine)
1879
1880                 opr=[names[i][1],IDs[i][1]]
1881                 top = names[i][1]
1882                 if i==0 or  top != names[i-1][1]:
1883                         mainDiv +=endP
1884                         mainDiv += startP
1885                         mainDiv += pU.print_tagged_name\
1886                         ('With',opr, newLine)
1887
1888
1889                 #age when child born
1890                 cb = childrenBorn[i]
1891                 if  cb!=0 and  bornYear != 0:
1892
1893                         cs = "SELECT age,pID,cID FROM "+ageChildTable\
1894                                 +"WHERE pID=? AND cID =?"
1895                         ct = (personID,IDs[i][0])
1896
1897                         for row in run_query(cs,ct):
1898                                 thisChild = thisChild[:-4]+\
1899                                 " at the age of "+str(row[0]) +newLine
1900                 mainDiv += thisChild
1901
1902         mainDiv += endP
1903
1904
1905         if newLine == '<br>':
1906                 output = '<div id = "main" style = " float:left;width:75%">';
1907                 output += mainDiv +  "</div>"
1908
1909                 output += "<div id = 'image' "\
1910                         +"style = 'float:left; margin-left:20px'>"
1911
1912                 imageDiv = ''
1913                 if picture!='.':
1914                         imageDiv += "<a href=" + url+">"\
1915                         +"<img src=" + picture +" alt = 'wiki link'"\
1916                         +" title = 'wiki link'></a>"\
1917                         + newLine
1918
1919                 elif url!='.' and url!='. ':
1920                         imageDiv += "<a href=" + url +">"\
1921                         +name + " (wiki link)</a>"+newLine
1922
1923                 output += imageDiv + "</div>"
1924
1925
1926                 url = 'http://www.chiark.greenend.org.uk/ucgi/~naath/'\
1927                         +'smallGraph.py'
1928
1929                 form = ''
1930                 form += "<form id ='controlForm'"\
1931                 +" action ="+ url +" method = 'get'>"
1932
1933                 form +=\
1934                         "<input type = 'hidden' name = 'ID' value = "\
1935                         +personID + "><br>"
1936
1937                 form +=\
1938                 "Generations of Parents: "\
1939                 +"<input type = 'text' name = 'pl' value='1'>"
1940                 form += newLine
1941                 form += \
1942                 "Generations of Children: "\
1943                 +" <input type = 'text' name = 'cl' value = '1'>"
1944                 form += newLine
1945                 form += \
1946                 "Show siblings: <select name = 's'>"+\
1947                 "<option value = '0'>No</option>"+\
1948                 "<option value = '1'>Yes</option>"+\
1949                 "</select>"
1950                 form += newLine
1951                 form += \
1952                 "Show spouse's other spouses: <select name = 'os'>"+\
1953                 "<option value = '0'>No</option>"+\
1954                 "<option value = '1'>Yes</option>"+\
1955                 "</select>"
1956                 form += newLine
1957                 form += \
1958                 "Show parents' other spouses: <select name = 'pos'>"+\
1959                 "<option value = '0'>No</option>"+\
1960                 "<option value = '1'>Yes</option>"+\
1961                 "</select>"
1962                 form += newLine
1963                 form += \
1964                 "Fount size: "+\
1965                 "<input type = 'text' name = 'fs' value='8'>"
1966                 form += newLine
1967                 form += "</form>"
1968
1969                 graph =  "smallGraph.py?ID="+str(personID)+"&fs=8"
1970
1971                 graph = "<img src ="+ graph + '>'
1972
1973                 output += "<div id = 'graph' style = 'clear:both'>"
1974                 output += "<p id = 'agraph'>"+graph+"</p>"
1975                 output += "Draw this graph with more relatives:"
1976                 output += newLine + form
1977
1978                 output += "<button onclick='myFunction()'>"+\
1979                         "Go</button>"
1980
1981                 output += "</div>"
1982
1983                 output +=\
1984                 '<script>'+\
1985                 'function myFunction()'+\
1986                 '{'+\
1987                 'var x = document.getElementById("controlForm");'+\
1988                 'var txt = "<img src = " + x.action + "?";'+\
1989                 'for (var i=0;i<x.length;i++)'+\
1990                 '{'+\
1991                 'var n=x.elements[i].name;'+\
1992                 'var v=x.elements[i].value;'+\
1993                 'txt = txt + "&"+n+"="+v;'+\
1994                 '}'+\
1995                 'txt = txt + ">";'+\
1996                 'document.getElementById("agraph").innerHTML=txt;'+\
1997                 '}'+\
1998                 '</script>'
1999
2000         else:
2001                 output = mainDiv
2002
2003         return output
2004
2005 def connect():
2006         global conn
2007         conn = sqlite3.connect\
2008                 ('/home/naath/familyTreeProject/familyTree/tree.db')
2009         conn.cursor().execute('PRAGMA journal_mode = WAL')
2010
2011 def make_cursor():
2012         return conn.cursor()
2013
2014 def commit_changes():
2015         conn.commit()
2016
2017 def close():
2018         conn.close
2019