#!/usr/bin/env python
# coding: utf-8

# # Quick Conversations With the Companies House API
# 
# This notebook describes a basic converational UI for working with the UK Companies House API.
# 
# To work with the API, you will need to get an API key: 
# 
# 
# 

# In[1]:


CH_API_TOKEN='YOUR_TOKEN'


# In[7]:


import urllib2, base64, json
from urllib import urlencode

#Inspired by http://stackoverflow.com/a/2955687/454773
def ch_request(CH_API_TOKEN,url,args=None):
    if args is not None:
        url='{}?{}'.format(url,urlencode(args))
    request = urllib2.Request(url)
    # You need the replace to handle encodestring adding a trailing newline 
    # (https://docs.python.org/2/library/base64.html#base64.encodestring)
    base64string = base64.encodestring('%s:' % (CH_API_TOKEN)).replace('\n', '')
    request.add_header("Authorization", "Basic %s" % base64string)   
    result = urllib2.urlopen(request)

    return json.loads(result.read())


# In[168]:


def ch_searchCompanies(q,n=50,start_index='',typ=None):
    #typ: exact, contains, None,
    url= 'https://api.companieshouse.gov.uk/search/companies'
    properties={'q':q,'items_per_page':n,'start_index':''}
    c=ch_request(CH_API_TOKEN,url,properties)
    if typ=='contains':
        c['items']=[i for i in c['items'] if q.lower() in i['title'].lower()]
    elif typ=='exact':
        c['items']=[i for i in c['items'] if q.lower() == i['title'].lower()]
    return c


# In[169]:


ch_searchCompanies('FutureLearn',5,typ='contains')


# In[101]:


def ch_getCompany(cn):
    url="https://api.companieshouse.gov.uk/company/{}".format(cn)
    return ch_request(CH_API_TOKEN,url)


# In[103]:


ch_getCompany('08324083')


# In[95]:


def ch_getCompanyOfficers(cn,typ='all'):
    #typ: all, current, previous
    url="https://api.companieshouse.gov.uk/company/{}/officers".format(cn)
    co=ch_request(CH_API_TOKEN,url)
    if typ=='current':
        co['items']=[i for i in co['items'] if 'resigned_on' not in i]
        #should possibly check here that len(co['items'])==co['active_count'] ?
    elif typ=='previous':
        co['items']=[i for i in co['items'] if 'resigned_on' in i]
    return co


# In[99]:


ch_getCompanyOfficers('08324083',typ='current')


# In[188]:


def ch_getAppointments(slug,location=None):
    if len(slug.split('/'))==1:
        slug='/officers/{}/appointments'.format(slug)
    url= 'https://api.companieshouse.gov.uk{}'.format(slug)
    a=ch_request(CH_API_TOKEN,url)
    if location is not None:
        a['items']=[i for i in a['items'] if location.lower() in i['address']['locality'].lower()]
    return a


# In[177]:


ch_getAppointments('/officers/Tyk3KEbbpwXQ0RJ-h7fRJCf08B4/appointments')


# In[159]:


def ch_searchOfficers(q,n=50,start_index='',company=''):
    url= 'https://api.companieshouse.gov.uk/search/officers'
    properties={'q':q,'items_per_page':n,'start_index':start_index} 
    o=ch_request(CH_API_TOKEN,url,properties)
    if company != '':
        o['items'] = [j for j in o['items'] for i in ch_getAppointments(j['links']['self'])['items'] if company.lower() in i['appointed_to']['company_name'].lower()]
    return o


# In[160]:


ch_searchOfficers('Peter Horrocks',n=5,company='Futurelearn')


# The Companies House API doesn't provide a very powerful search facility, but we can finesse our own by loading the scattergun results from the Companies House API into an in memory database and then querying again.

# In[57]:


import sqlite3

db = sqlite3.connect(":memory:")
c = db.cursor()
c.execute('''create table directors
         (id text primary key,
          dob integer,
          addr text,
          title text,
          cnt integer,
          descr text)''')


# In[59]:


x=[{'id':p['links']['self'],
  'dob':p['date_of_birth']['year'] if 'date_of_birth' in p else None,
  'addr':p['snippet'],
  'title':p['title'],
  'cnt':p['appointment_count'],
  'descr':p['description']} for p in o['items']]


# In[60]:


c.executemany('INSERT INTO directors (id, dob,addr,title,cnt,descr) '
                 'VALUES (:id,:dob,:addr,:title,:cnt,:descr)', x)


# In[61]:


for r in c.execute('select * from directors where title="Peter Horrocks" COLLATE NOCASE order by cnt desc'):
    print(r)


# In[55]:


for r in c.execute("select * from directors where addr LIKE '%Manchester%' order by cnt desc"):
    print(r)


# In[192]:


from datetime import date

def dirCompanies(a):
    if not len(a['items']): return
    print('{}, {}, has the following appointments:'.format(a['name'],
                                                       date.today().year-a['date_of_birth']['year'] -(date.today().month <a['date_of_birth']['month'] )))
    
    for aa in a['items']:
        print('\t- {}, {} ({}): {} ({})'.format(aa['appointed_to']['company_name'],
                                        aa['address']['locality'],
                                    aa['appointed_to']['company_number'],
                                    aa['officer_role'],
                                    aa['appointed_on'] if 'appointed_on' in aa else aa['appointed_before']))


# In[193]:


import re

regexp=re.compile(r'.*companies ?(?:is|are) (.*) a director of?')

m=re.match(regexp,'What companies is Peter Horrocks a director of?')
for c in ch_searchOfficers(m.group(1),n=5)['items']:
    dirCompanies(ch_getAppointments(c['links']['self']))


# In[185]:


regexp=re.compile(r'Which (.*) is a director of (.*?)\??$')

m=re.match(regexp,'Which Peter Horrocks is a director of Futurelearn?')
for c in ch_searchOfficers(m.group(1),n=5,company=m.group(2))['items']:
    dirCompanies(ch_getAppointments(c['links']['self']))


# In[194]:


regexp=re.compile(r'.*companies in (.*?) (?:is|are) (.*) a director of\?')

m=re.match(regexp,'What companies in Bolton is Peter Horrocks a director of?')

for c in ch_searchOfficers(m.group(2),n=5)['items']:
    dirCompanies(ch_getAppointments(c['links']['self'],location=m.group(1)))


# In[175]:


regexp=re.compile(r'Who were the previous directors of (.*?)[\?]?$')

m=re.match(regexp,'Who were the previous directors of Futurelearn?')
for c in ch_searchCompanies(m.group(1),5,typ='contains')['items']:
    print(c['title'])
    for o in ch_getCompanyOfficers(c['company_number'],typ='previous')['items']:
        print('{} (resigned on {})'.format(o['name'],o['resigned_on']))
    print('----')


# In[170]:





# In[ ]:


def exact_match(items, txt):
    ''' Prioritise the list so that exact matches are first... '''
    pass