from __future__ import print_function
import requests
import json
import re
import time
import mysql.connector
import linecache
import itertools
from time import gmtime, strftime

db = mysql.connector.connect(user='root', password ='Kielce83', database='indeedProject')
cursor = db.cursor()

start = time.clock()
getDate = strftime("%a, %d %b %Y", gmtime())

#Format &q='exact+phrase'

api_url = 'http://api.indeed.com/ads/apisearch?publisher=6311497045529956&v=2&limit=100000&format=json'

with open("jobs_full.txt", "r") as f:
    SearchTerms = f.read().splitlines()

Regions = set(['Northeast', 'Southeast', 'Midwest', 'Southwest', 'West'])

print (SearchTerms)


keyList = ["First Line Supervisor of Agricultural Crop and Horticultural Workers", "First Line Supervisor of Animal Husbandry and Animal Care Workers", "First Line Supervisor of Landscaping Lawn Service and Groundskeeping Workers", "Food and Tobacco Roaster Baker and Drying Machine Operator and Tender", "First Line Supervisor of Construction Trades and Extraction Workers", "Helper Brickmasons Blockmasons Stonemasons and Tile Marble Setters", "First Line Supervisor of Office and Administrative Support Workers", "Secretary and Administrative Assistant Except Legal Medical and Executive", "Adult Basic and Secondary Education and Literacy Teacher and Instructor", "Middle School Teacher Except Special and Career Technical Education", "Secondary School Teacher Except Special and Career Technical Education", "Postal Service Mail Sorters Processors and Processing Machine Operator", "Low Vision Therapist Orientation and Mobility Specialist and Vision Rehabilitation Therapist", "Separating Filtering Clarifying Precipitating and Still Machine Setters Operator and Tender", "Lifeguard Ski Patrol and Other Recreational Protective Service Worker", "Computer Numerically Controlled Machine Tool Programmer Metal and Plastic", "Crushing Grinding and Polishing Machine Setter Operator and Tender", "Cutting Punching and Press Machine Setter Operator and Tender Metal and Plastic", "Drilling and Boring Machine Tool Setter Operator and Tender Metal and Plastic", "Electrical and Electronics Repairer Commercial and Industrial Equipment", "Extruding and Drawing Machine Setter Operator and Tender Metal and Plastic", "Extruding and Forming Machine Setters Operator and Tender Synthetic and Glass Fibers", "Extruding Forming Pressing and Compacting Machine Setter Operator and Tenders", "Grinding Lapping Polishing and Buffing Machine Tool Setter Operator and Tender Metal and Plastic", "Heat Treating Equipment Setter Operator and Tender Metal and Plastic", "Lathe and Turning Machine Tool Setter Operator and Tender Metal and Plastic", "Milling and Planing Machine Setter Operator and Tender Metal and Plastic", "Molding Coremaking and Casting Machine Setter Operator and Tender Metal and Plastic", "Multiple Machine Tool Setter Operator and Tender Metal and Plastic", "Plating and Coating Machine Setter Operator and Tender Metal and Plastic", "Telecommunications Equipment Installer and Repairer Except Line Installer", "Textile Winding Twisting and Drawing Out Machine Setter Operator and Tender", "Door To Door Sales Worker News and Street Vendor and Related Worker", "Sales Representative Wholesale and Manufacturing Except Technical and Scientific Products", "Sales Representative Wholesale and Manufacturing Technical and Scientific Products", "Health and Safety Engineer Except Mining Safety Engineer and Inspector", "Ambulance Drivers and Attendant Except Emergency Medical Technician", "Electrical and Electronics Installer and Repairer Transportation Equipment", "First Line Supervisor of Helpers Laborers and Material Movers Hand", "First Line Supervisor of Transportation and Material Moving Machine and Vehicle Operators", "Transportation Vehicle Equipment and Systems Inspector Except Aviation", "Atmospheric Earth Marine and Space Sciences Teacher Postsecondary", "Education Administrator Preschool and Childcare Center or Program", "Cleaning Washing and Metal Pickling Equipment Operator and Tender"]

valuesList = ["First Line Supervisor of Agricultural Crop and Horticultural Wor", "First Line Supervisor of Animal Husbandry and Animal Care Worker", "First Line Supervisor of Landscaping Lawn Service and Groundskee", "Food and Tobacco Roaster Baker and Drying Machine Operator and T", "First Line Supervisor of Construction Trades and Extraction Work", "Helper Brickmasons Blockmasons Stonemasons and Tile Marble Sette", "First Line Supervisor of Office and Administrative Support Worke", "Secretary and Administrative Assistant Except Legal Medical and", "Adult Basic and Secondary Education and Literacy Teacher and Ins", "Middle School Teacher Except Special and Career Technical Educat", "Secondary School Teacher Except Special and Career Technical Edu", "Postal Service Mail Sorters Processors and Processing Machine Op", "Low Vision Therapist Orientation and Mobility Specialist and Vis", "Separating Filtering Clarifying Precipitating and Still Machine", "Lifeguard Ski Patrol and Other Recreational Protective Service W", "Computer Numerically Controlled Machine Tool Programmer Metal an", "Crushing Grinding and Polishing Machine Setter Operator and Tend", "Cutting Punching and Press Machine Setter Operator and Tender Me", "Drilling and Boring Machine Tool Setter Operator and Tender Meta", "Electrical and Electronics Repairer Commercial and Industrial Eq", "Extruding and Drawing Machine Setter Operator and Tender Metal a", "Extruding and Forming Machine Setters Operator and Tender Synthe", "Extruding Forming Pressing and Compacting Machine Setter Operato", "Grinding Lapping Polishing and Buffing Machine Tool Setter Opera", "Heat Treating Equipment Setter Operator and Tender Metal and Pla", "Lathe and Turning Machine Tool Setter Operator and Tender Metal", "Milling and Planing Machine Setter Operator and Tender Metal an","Molding Coremaking and Casting Machine Setter Operator and Tend", "Multiple Machine Tool Setter Operator and Tender Metal and Plast", "Plating and Coating Machine Setter Operator and Tender Metal an","Telecommunications Equipment Installer and Repairer Except Lin","Textile Winding Twisting and Drawing Out Machine Setter Operato","Door To Door Sales Worker News and Street Vendor and Related Wor", "Sales Representative Wholesale and Manufacturing Except Technica", "Sales Representative Wholesale and Manufacturing Technical and S", "Health and Safety Engineer Except Mining Safety Engineer and Ins", "Ambulance Drivers and Attendant Except Emergency Medical Technic", "Electrical and Electronics Installer and Repairer Transportatio","First Line Supervisor of Helpers Laborers and Material Movers Ha", "First Line Supervisor of Transportation and Material Moving Mach", "Transportation Vehicle Equipment and Systems Inspector Except Av", "Atmospheric Earth Marine and Space Sciences Teacher Postsecondar", "Education Administrator Preschool and Childcare Center or Progra", "Cleaning Washing and Metal Pickling Equipment Operator and Tende"]

writeResults = open("totalResults.txt", "a")
totSum = 0
j = 0
supersum = 0
for Term in SearchTerms:

    # check for long table name and substitute shorter name
    while j < len(keyList):
        if (Term == keyList[j]):
            Term = valuesList[j]
            j = j+1


    # write sum after each category
    if (Term == '-'):
        writeResults.write(repr(totSum) + '\n')
        supersum = totSum + supersum
        totSum = 0

    else:

        urlfirst = api_url + '&co=us' + '&q=' + Term + '&latlong=1' + '&start=0'  + '&fromage=1'

        underscoreTerm = Term.replace(" ", "_")

        grabforNum = requests.get(urlfirst)
        json_content = json.loads(grabforNum.content)
        print(json_content["totalResults"])
        results = json_content["totalResults"]

    # must match the actual number of job results to the lower of the 25 increment or the last page will repeat over and over


        writeResults.write(repr(results) + '\n')

    #add results to new total sum for category
        totSum = totSum + results



        if (results > 1025):

            for Region in Regions:

                urlfirst = api_url + '&co=us' + '&q=' + Term + '&latlong=1' + '&start=0'  + '&fromage=1' + '&l=' + Region

                grabforNum = requests.get(urlfirst)
                json_content = json.loads(grabforNum.content)
                regionResults = json_content["totalResults"]

                for number in range(-25, regionResults - 25, 25):

                    url = api_url + '&co=us' + '&q=' + Term + '&latlong=1' + '&start=' + str(number + 25) + '&fromage=1' + '&l=' + Region

                    response = requests.get(url)

                    jsonResponse=json.loads(response.content)
                    jsonData = jsonResponse["results"]

                    for item in jsonData:
                    #to grab non strings use str(item.get("totalResults")
                    #f.write (str(numresults))

                        date = item.get("date").encode("utf-8")
                        postDate = date[5:16]
                        jobtitle = item.get("jobtitle").encode("utf-8")
                        company = item.get("company").encode("utf-8")
                        formattedLocationFull = item.get("formattedLocationFull").encode("utf-8")
                        url = item.get("url").encode("utf-8")
                        latitude = str(item.get("latitude"))
                        longitude = str(item.get("longitude"))
                    #region = item.get("l")

                        cursor.execute("insert into " + underscoreTerm + " (postDate, jobtitle, company, formattedLocationFull, url, latitude, longitude)" "values (%s, %s, %s, %s, %s, %s, %s)" , (postDate, jobtitle, company, formattedLocationFull, url, latitude, longitude))
                    #cursor.execute("insert into totalResults (postDate, jobtitle" + str(results) + ")" "values (%s, %s)" , (postDate, jobtitle, results))




                        db.commit()

                        print (results, 'left---Completed' , url)
                        results-=1
        else:

            for number in range(-25, results - 25, 25):

                url = api_url + '&co=us' + '&q=' + Term + '&latlong=1' + '&start=' + str(number + 25) + '&fromage=1'

                response = requests.get(url)

                jsonResponse=json.loads(response.content)
                jsonData = jsonResponse["results"]

                for item in jsonData:
                #to grab non strings use str(item.get("totalResults")
                #f.write (str(numresults))

                    date = item.get("date").encode("utf-8")
                    postDate = date[5:16]
                    jobtitle = item.get("jobtitle").encode("utf-8")
                    company = item.get("company").encode("utf-8")
                    formattedLocationFull = item.get("formattedLocationFull").encode("utf-8")
                    url = item.get("url").encode("utf-8")
                    latitude = str(item.get("latitude"))
                    longitude = str(item.get("longitude"))
                #region = item.get("l")

                    cursor.execute("insert into " + underscoreTerm + " (postDate, jobtitle, company, formattedLocationFull, url, latitude, longitude)" "values (%s, %s, %s, %s, %s, %s, %s)" , (postDate, jobtitle, company, formattedLocationFull, url, latitude, longitude))
                    table_name = "results"
                #cursor.execute("insert into " + table_name + " (postDate, jobtitle" + str(results) + ")" "values (%s, %s)" , (postDate, jobtitle, results))
                    db.commit()

                    print (results, 'left---Completed' , url)
                    results-=1




writeResults.close()


with open("totalResults.txt", "r") as h:
    getTotals = h.read().splitlines()



for total in getTotals:
    termcat = " "
    termcat = termcat + "," + total


cursor.execute ("insert into totalResults values (" + getDate + termcat + ")")
db.commit()


cursor.close()
db.close()
f.close()
writeResults.close()


elapsed = (time.clock() - start)
print (elapsed)