import pandas as pd
from zipfile import ZipFile
import glob
import os
from datetime import date
import requests
import urllib.request
import time
from bs4 import BeautifulSoup
import matplotlib.pyplot as plt

def check_latest_online():
    PageName = "http://download.companieshouse.gov.uk/en_output.html"
    RootPageName = "http://download.companieshouse.gov.uk/"
    page = requests.get(PageName)
    soup = BeautifulSoup(page.content, 'html.parser')
    for one_a_tag in soup.findAll('a'):  #'a' tags are for links
        link = one_a_tag['href']
        if "AsOneFile" in link:
            latest_online=link
            time.sleep(1) #pause the code for a sec
            break
        else:
            next         
    return latest_online

# Make a request
def download_companyFile():
    PageName = "http://download.companieshouse.gov.uk/en_output.html"
    RootPageName = "http://download.companieshouse.gov.uk/"
    page = requests.get(PageName)
    
    soup = BeautifulSoup(page.content, 'html.parser')

    for one_a_tag in soup.findAll('a'):  #'a' tags are for links
        link = one_a_tag['href']
        if "AsOneFile" in link:
            urllib.request.urlretrieve(RootPageName+link, './data/'+link) #this might be out of date?
            print(RootPageName+link)            
            time.sleep(1) #pause the code for a sec
            break
        else:
            next     

def extract_latest_zip():
    list_of_ZIP = glob.glob(path_to_ZIP) 
    latest_ZIP = max(list_of_ZIP, key=os.path.getctime)
    with ZipFile(latest_ZIP, 'r') as zipObj:
        # Extract all the contents of zip file in current directory
        zipObj.extractall(path='./data/')
    list_of_CSV = glob.glob(path_to_CSV)
    latest_CSV = max(list_of_CSV, key=os.path.getctime)
    return latest_CSV

def latest_zip_csv ():
    list_of_ZIP = glob.glob(path_to_ZIP) 
    latest_ZIP = max(list_of_ZIP, key=os.path.getctime)
    list_of_CSV = glob.glob(path_to_CSV)
    latest_CSV = max(list_of_CSV, key=os.path.getctime)
    return latest_ZIP, latest_CSV

path_to_ZIP='./data/*.zip' # * means all if need specific format then *.csv
path_to_CSV='./data/*.csv'
path_to_data_folder='./data'
latest_ZIP, latest_CSV = latest_zip_csv ()
#latest_online_zip = check_latest_online()
#Offline work.
latest_online_zip = os.path.basename(latest_ZIP)
# Rear word replace in String using split() + join()
new_extension= ".csv"
latest_online_csv =  "".join(latest_online_zip.split('.')[:-1] + [new_extension])

if latest_online_csv == os.path.basename(latest_CSV):
    print (latest_online_csv)
    print (latest_CSV)
    print ("We have up to date CSV")
    print ("It is now business time: the latest csv is "+ latest_CSV)    
elif latest_online_zip == os.path.basename(latest_ZIP):
    print (latest_online_zip)
    print (latest_ZIP)
    print ("Latest zip downloaded but we did not extract!, will do so now.")
    extract_latest_zip()
    latest_ZIP, latest_CSV = latest_zip_csv ()
    print ("It is now business time: the latest csv is "+ latest_CSV)
else:
    print ("all fail")
    print (latest_online_zip)
    print (latest_ZIP) 
    print (latest_online_csv)
    print (latest_CSV)
    print ("So we download the newest version, please wait for 0.5 GB file to download")
    download_companyFile()
    latest_CSV = extract_latest_zip()
    print ("It is now business time: the latest csv is "+ latest_CSV)

companies_df = pd.read_csv(latest_CSV,# index_col=0, header= TRUE ,# row.names =1, 
                        usecols=["CompanyName","RegAddress.AddressLine1",
                                 "RegAddress.PostTown",
                                 "RegAddress.PostCode",
                                 "CompanyCategory" ,"CompanyStatus", 
                                 "SICCode.SicText_1", "SICCode.SicText_2",
                                "SICCode.SicText_3", "SICCode.SicText_4"])
companies_df[0:4]

companies_df['RegAddress.PostCode'].fillna("no info", inplace=True)
companies_df['RegAddress.PostTown'].fillna("no info", inplace=True)

companies_df=companies_df.astype({"CompanyName": 'str',
                                  "RegAddress.AddressLine1": 'str',
                                 "RegAddress.PostTown": 'str',
                                 "RegAddress.PostCode": 'str',
                                 "CompanyCategory" : 'str' ,
                                  "CompanyStatus" : 'str', 
                                 "SICCode.SicText_1" : 'str', 
                                  "SICCode.SicText_2" : 'str',
                                "SICCode.SicText_3" : 'str', 
                                  "SICCode.SicText_4" : 'str'})

TW10companies_df=companies_df.loc[companies_df['RegAddress.PostCode'].str.contains('TW10')|
                                  companies_df['RegAddress.PostCode'].str.contains('KT2 5')|
                                  #companies_df['RegAddress.PostTown'].str.contains('HAM')| #There is alot of HAM
                                  companies_df['RegAddress.PostTown'].str.contains('PETERSHAM')
                                 ]

TW10_active_companies_df=TW10companies_df.loc[TW10companies_df['CompanyStatus'].str.contains('Active')]
word = "99999"
TW10_active_awake_df=TW10_active_companies_df[TW10_active_companies_df["SICCode.SicText_1"].str.contains(word) == False]
TW10_active_awake_df

SIC_repair_TW10_active_companies_df = TW10_active_awake_df.loc[(TW10_active_awake_df['SICCode.SicText_1']>'95105') & (TW10_active_companies_df['SICCode.SicText_1']<'95290')]
SIC_repair_TW10_active_companies_df

#14190 - Manufacture of other wearing apparel

SIC_manuFabric_TW10_active_companies_df = TW10_active_awake_df.loc[(TW10_active_awake_df['SICCode.SicText_1']>'14000') & (TW10_active_companies_df['SICCode.SicText_1']<'15000')]
SIC_manuFabric_TW10_active_companies_df

SIC_14000_TW10_active_df = TW10_active_awake_df.loc[(TW10_active_awake_df['SICCode.SicText_1']>'14000') &
                                                        (TW10_active_awake_df['SICCode.SicText_1']<'15000')|
                                                       (TW10_active_awake_df['SICCode.SicText_2']>'14000') &
                                                        (TW10_active_awake_df['SICCode.SicText_2']<'15000')|
                                                       (TW10_active_awake_df['SICCode.SicText_3']>'14000') &
                                                        (TW10_active_awake_df['SICCode.SicText_3']<'15000')|
                                                       (TW10_active_awake_df['SICCode.SicText_4']>'14000') &
                                                        (TW10_active_awake_df['SICCode.SicText_4']<'15000')
                                                       ]
SIC_14000_TW10_active_df

Q1=TW10_active_awake_df['SICCode.SicText_1'].value_counts()

Q1.plot.pie()
plt.ylabel('')
plt.title('SIC code 1');

Top_12=TW10_active_awake_df['SICCode.SicText_1'].value_counts().head(12)
Top_12.plot.pie()
#Q1.plot.bar()
plt.ylabel('')
plt.title('SIC code 1');

Top_12

Total=TW10_active_awake_df.shape[0]

Top_12_percent=(Top_12.sum()*100)/(Total)
print ('The top 12 percent represents', Top_12_percent.round(), '% of the total', Total)

TW107_df=TW10_active_awake_df.loc[TW10_active_awake_df['RegAddress.PostCode'].str.contains('TW10 7')]

TW107_df_estate=  TW107_df.loc[TW107_df['SICCode.SicText_1'].str.contains('68209')|
                               TW107_df['SICCode.SicText_1'].str.contains('68100')|
                               TW107_df['SICCode.SicText_1'].str.contains('41100')
                                        ]
TW107_df_estate