
# python imports
import json
import xlsxwriter 
import pandas as pd
from numpy import nan
import openpyxl as op
from time import sleep
from bs4 import BeautifulSoup
from selenium import webdriver 


MAIN_PAGE = 'https://sellercentral.amazon.in/gspn/?ref=spn_rc&sellFrom=IN&sellIn=IN&ref_=sc_gspn_na_hp'

CATEGORY_LIST = { 
    'Taxes' : 'https://sellercentral.amazon.in/gspn/searchpage/Taxes?ref_=sc_gspn_clst_talst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Account Management' : 'https://sellercentral.amazon.in/gspn/searchpage/Account%20Management?ref_=sc_gspn_hp_blst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Imaging' : 'https://sellercentral.amazon.in/gspn/searchpage/Imaging?ref_=sc_gspn_hp_ilst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Cataloging' : 'https://sellercentral.amazon.in/gspn/searchpage/Cataloguing?ref_=sc_gspn_hp_clst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Advertising Optimization' : 'https://sellercentral.amazon.in/gspn/searchpage/Advertising%20Optimization?ref_=sc_gspn_hp_alst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Domestic Shipping' : 'https://sellercentral.amazon.in/gspn/searchpage/Domestic%20Shipping?ref_=sc_gspn_alst_dslst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Training' : 'https://sellercentral.amazon.in/gspn/searchpage/Training?ref_=sc_gspn_dslst_trlst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Seller Reinstatement' : 'https://sellercentral.amazon.in/gspn/searchpage/Seller%20Reinstatement?ref_=sc_gspn_trlst_srelst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Accounting' : 'https://sellercentral.amazon.in/gspn/searchpage/Accounting?ref_=sc_gspn_srelst_acclst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Enhanced Brand Content' : 'https://sellercentral.amazon.in/gspn/searchpage/Enhanced%20Brand%20Content?ref_=sc_gspn_acclst_ebclst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'IP Accelerator' : 'https://sellercentral.amazon.in/gspn/searchpage/IP%20Accelerator?ref_=sc_gspn_ebclst_iplst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Excess Inventory' : 'https://sellercentral.amazon.in/gspn/searchpage/Excess%20Inventory?ref_=sc_gspn_iplst_eilst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Compliance' : 'https://sellercentral.amazon.in/gspn/searchpage/Compliance?ref_=sc_gspn_eilst_colst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'FBA Preparation' : 'https://sellercentral.amazon.in/gspn/searchpage/FBA%20Preparation?ref_=sc_gspn_colst_fplst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'International Returns' : 'https://sellercentral.amazon.in/gspn/searchpage/International%20Returns?ref_=sc_gspn_fplst_irlst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'International Shipping' : 'https://sellercentral.amazon.in/gspn/searchpage/International%20Shipping?ref_=sc_gspn_irlst_islst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Storage' : 'https://sellercentral.amazon.in/gspn/searchpage/Storage?ref_=sc_gspn_islst_slst&sellFrom=IN&sellIn=IN&localeSelection=en_US',
    'Translation' : 'https://sellercentral.amazon.in/gspn/searchpage/Translation?ref_=sc_gspn_slst_tralst&sellFrom=IN&sellIn=IN&localeSelection=en_US'
}


class SPN:
    data = {}
    count = 1
    limit = 0
    clicked = 0
    source = None
    driver = None
    browser = None
    sheetname = ''
    filename = 'companies_data.xlsx'
    headers = ['Company Name', 'Web-Link', 'Provider Description', 'Requests', 'Reviews', 'Product Category', 'Lab Location', 'Provider Location', 'Shipping Mode', 'Ship from location',
                'Ship to location', 'Return from location', 'Return to location', 'Provider Language', 'Source language', 'Target language', 'Value Added Services', 'Service Type']

    def __init__(self):
        self.browser = webdriver.Chrome('chromedriver.exe')
        self.browser.set_window_position(680, 30)
        self.browser.set_window_size(700, 700)
        self.create_excel_file()

    def start_data_collection(self):
        for cat in CATEGORY_LIST:
            self.browser.get(CATEGORY_LIST[cat])
            self.browser.execute_script("window.scrollTo(0,document.body.scrollHeight)")
            self.add_excel_sheet(cat)
            self.reset_variables()
            print(f'----------------------------')
            print(f'{cat}  [{self.check_limit()}]')
            print('----------------------------')
            self.get_companies()
            self.write_to_excel()
        self.close_browser()

    def reset_variables(self):
        self.data = {}
        self.count = 1
        self.clicked = 0

    def check_limit(self):
        lis = self.browser.find_elements_by_class_name("pagination-pageNumber")
        self.limit = len(lis)
        return self.limit

    def dump_json(self):
        print('\n ----- Dumping to JSON ----- ')
        with open ('data.json', 'w+') as my_data:
            json.dump(self.data, my_data, indent=4)

    def get_companies(self):
        if self.clicked >= self.limit:
            # print(f'\nClicked = {self.clicked}\n----------------------------\n')
            return
        try:    
            li = self.browser.find_element_by_class_name("a-last")
        except: 
            # print('\n --- END OF PAGES --- \n')
            pass
        
        soup = BeautifulSoup(self.browser.page_source, features='lxml')
        main_div = soup.find('div', attrs={'class':'a-column a-span12 a-ws-span9 searchResultsRightDiv a-span-last'})
        div = main_div.find('div')
        a_tags = div.find_all('a')
        for a in a_tags:
            self.get_company_data(a['href'])
            self.count += 1
        # print(self.count, end=" ")
        try:
            li.click()
            self.browser.execute_script("window.scrollTo(0,document.body.scrollHeight)")
            self.clicked += 1
            self.get_companies()
        except:
            if self.clicked < self.limit: 
                self.get_companies()
            else:
                print('\n\n -------- Completed ------- \n\n')
                sleep(10)
      
    def get_driver(self):
        if self.driver: return self.driver
        options = webdriver.ChromeOptions()
        options.add_argument("--incognito")
        self.driver = webdriver.Chrome(options=options)
        # self.driver = webdriver.Chrome('chromedriver.exe')
        self.driver.set_window_position(680, 30)
        self.driver.set_window_size(700, 700)
        return self.driver

    def get_company_data(self, link):
        company = {
            'Web-Link':'',
            'Reviews':'N/A',
            'Requests':'N/A',
            'Company Name': '',
            'Service Type': ['N/A'],
            'Lab Location': ['N/A'],
            'Source language' : ['N/A'],
            'Target language' : ['N/A'],
            'Product Category': ['N/A'],
            'Provider Location': ['N/A'],
            'Provider Language': ['N/A'],
            'Provider Description': 'N/A',
            'Return to location' : ['N/A'],
            'Value Added Services': ['N/A'],
            'Return from location' : ['N/A'],
        }

        browser = self.get_driver()
        company_name = ''
        browser.get(link)
        soup = BeautifulSoup(browser.page_source, features='lxml')
        try:
            first_div = soup.find('div', attrs={'class':'a-column a-span12 a-spacing-base a-ws-span12 a-ws-spacing-none a-span-last a-ws-span-last'})
            span = first_div.find('span', attrs={'class':'a-size-medium a-color-base providerNamePadding'})
            print('\n-------------------------------------------------------------------------------------')
            company_name = str(span.get_text())
            company['Company Name'] = company_name
            print(self.count, ' Company = ', company_name)
        except: pass
        company['Web-Link'] = link
        print('Web-Link: ', link)
        try:
            span = first_div.find('span', attrs={'id':'service-request-completed-count'})
            requests = str(span.get_text()).replace('More than','')
            requests = requests.replace('Less than','')
            company['Requests'] = requests
            print('Requests = ', requests)
        except: pass
        try:
            div = first_div.find('div', attrs={'class':'view-reviews center-align-metrics'})
            span = div.find('span', attrs={'class':'a-color-tertiary'})
            reviews = str(span.get_text()).replace('Reviews','')
            company['Reviews'] = reviews
            print('Reviews = ', reviews)
        except: pass
        try:
            second_div = soup.find('div', attrs={'class':'a-row category-specific-data'})
            div = second_div.find('div', attrs={'class':'a-column a-span12 a-text-left a-spacing-large'})
            divs = div.find_all('div', attrs={'class':'a-row'})
        except: pass
        try:
            desc = divs[2].find('div', attrs={'class':'a-column a-span12 a-spacing-base sectionDescription'})
            spans = desc.find_all('span')
            company['Provider Description'] = str( spans[1].get_text() )
            print('Provider Description: ', company['Provider Description'])
        except: pass
        # Service Type 
        try:
            services = []
            span = divs[3].find('span', attrs={'class':'a-size-medium'})
            first = str(span.get_text())
            first = first.lower()
            print(first, end=" = ")
            div = divs[3].find('div', attrs={'class':'a-column a-span9 a-text-left'})
            spans = div.find_all('span', attrs={'class':'sectionDescription'})
            for span in spans:  
                service = str(span.get_text())
                services.append(service)
                print(service, end="  ")
            if first.__contains__('service type'):              company['Service Type']             = services
            elif first.__contains__('value added services'):    company['Value Added Services']     = services
            elif first.__contains__('ship to location'):       company['Ship to location']          = services
            elif first.__contains__('return to location'):     company['Return to location']        = services
            elif first.__contains__('ship from location'):     company['Ship from location']        = services
            elif first.__contains__('return from location'):   company['Return from location']      = services
        except: 
            # print('\nno service type')
            pass
        # value added services
        try:
            added_services = []
            span = divs[4].find('span', attrs={'class':'a-size-medium'})
            second = str(span.get_text())
            second = second.lower()
            print()
            print(second, end=" = ")
            div = divs[4].find('div', attrs={'class':'a-column a-span9 a-text-left'})
            spans = div.find_all('span', attrs={'class':'sectionDescription'})
            for span in spans:  
                added_service = str(span.get_text())
                added_services.append(added_service)
                print(added_service, end="  ")
            if second.__contains__('value added services'):     company['Value Added Services']     = added_services
            elif second.__contains__('provider location'):      company['Provider Location']        = added_services
            elif second.__contains__('product category'):       company['Product Category']         = added_services
            elif second.__contains__('source language'):        company['Source Category']          = added_services
            elif second.__contains__('target language'):        company['Target Category']          = added_services
            elif second.__contains__('ship to location'):       company['Ship to location']         = added_services
            elif second.__contains__('return to location'):     company['Return to location']       = added_services
            elif second.__contains__('ship from location'):     company['Ship from location']       = added_services
            elif second.__contains__('return from location'):   company['Return from location']     = added_services
        except: 
            # print('\nno value added service')
            pass
        # Provider Location
        try:
            locations = []
            span = divs[5].find('span', attrs={'class':'a-size-medium'})
            third = str(span.get_text())
            third = third.lower()
            print()
            print(third, end=" = ")
            div = divs[5].find('div', attrs={'class':'a-column a-span9 a-text-left'})
            spans = div.find_all('span', attrs={'class':'sectionDescription'})
            for span in spans:  
                location = str(span.get_text())
                locations.append(location)
                print(location, end="  ")
            if third.__contains__('provider location'):         company['Provider Location']        = locations
            elif third.__contains__('lab location'):            company['Lab Location']             = locations
            elif third.__contains__('provider language'):       company['Provider Language']        = locations
            elif third.__contains__('source language'):         company['Source Language']          = locations
            elif third.__contains__('target language'):         company['Target Language']          = locations
            elif third.__contains__('ship to location'):        company['Ship to location']         = locations
            elif third.__contains__('return to location'):      company['Return to location']       = locations
            elif third.__contains__('ship from location'):      company['Ship from location']       = locations
            elif third.__contains__('return from location'):    company['Return from location']     = locations
        except: 
            # print('\nno location')
            pass
        # Provider Language
        try:
            languages = []
            span = divs[6].find('span', attrs={'class':'a-size-medium'})
            fourth = str(span.get_text())
            fourth = fourth.lower()
            print()
            print(fourth, end=" = ")
            div = divs[6].find('div', attrs={'class':'a-column a-span9 a-text-left'})
            spans = div.find_all('span', attrs={'class':'sectionDescription'})
            for span in spans:  
                language = str(span.get_text())
                languages.append(language)
                print(language, end="  ")
            if fourth.__contains__('provider location'):        company['Provider Location']        = languages
            elif fourth.__contains__('provider language'):      company['Provider Language']        = languages
            elif fourth.__contains__('source language'):        company['Source language']          = languages
            elif fourth.__contains__('target language'):        company['Target language']          = languages
            elif fourth.__contains__('ship to location'):       company['Ship to location']         = languages
            elif fourth.__contains__('return to location'):     company['Return to location']       = languages
            elif fourth.__contains__('ship from location'):     company['Ship from location']       = languages
            elif fourth.__contains__('return from location'):   company['Return from location']     = languages
            elif fourth.__contains__('shipping mode'):           company['Shipping Mode']            = languages
        except: 
            # print('\nNo language')
            pass
        for index in range(7,13):         
            try:
                values = []
                span = divs[index].find('span', attrs={'class':'a-size-medium'})
                text = str(span.get_text())
                text = text.lower()
                print()
                print(text, end=" = ")
                div = divs[index].find('div', attrs={'class':'a-column a-span9 a-text-left'})
                spans = div.find_all('span', attrs={'class':'sectionDescription'})
                for span in spans:  
                    value = str(span.get_text())
                    values.append(value)
                    print(value, end="  ")
                # print('\n\n Values = ', values)
                if text.__contains__('provider location'):     company['Provider Location']    = values
                elif text.__contains__('provider language'):   company['Provider Language']    = values
                elif text.__contains__('shipping mode'):       company['Shipping Mode']        = values
            except: pass
        
        print('\n')
        self.data[company_name] = company
    
    def create_excel_file(self):
        # creating new excle file
        workbook = xlsxwriter.Workbook(self.filename)
        workbook.add_worksheet('Categories')
        workbook.close()
        workbook = op.load_workbook(self.filename, False)
        worksheet = workbook['Categories']
        worksheet.append(['Categories', 'Web-Links'])
        for cat in CATEGORY_LIST:   
            cats = []
            cats.append(cat)
            cats.append(CATEGORY_LIST[cat])
            worksheet.append(cats)
        workbook.save(self.filename)
        workbook.close()
    
    def add_excel_sheet(self, sheetname):
        self.sheetname = sheetname
        workbook = op.load_workbook(self.filename, False)
        workbook.create_sheet(sheetname)
        worksheet = workbook[sheetname]
        worksheet.append(self.headers)
        workbook.save(self.filename)
        workbook.close()

    def write_to_excel(self):
        workbook = op.load_workbook(self.filename, False)
        worksheet = workbook[self.sheetname]
        for company in self.data:
            saving_data = []
            company = self.data[company]
            for data_field in self.headers:
                try:
                    if data_field == 'Service Type':
                        for v in company[data_field]: saving_data.append(v)
                    else:  
                        if isinstance(company[data_field], list):
                            new_list = ''
                            for v in company[data_field]:
                                if len(company[data_field]) > 1:    new_list += v + ' , '
                                else: new_list += v
                            saving_data.append(new_list)
                        else: saving_data.append(company[data_field])
                except: saving_data.append('N/A')
            worksheet.append(saving_data)
        workbook.save(self.filename)
        workbook.close()

    def close_browser(self):
        self.browser.quit()
        self.driver.quit()

if __name__ == '__main__':
    spn = SPN()
    spn.start_data_collection()
    sleep(1)
        
""" TODO

1. separate headers for each category
2. headers = {}
3. headers['category']

"""