import json
import glob, os, string
from openpyxl import Workbook, load_workbook
import pygeoj
import collections
import datetime
from time import strftime

path=os.path.dirname(os.path.realpath(__file__))
os.chdir(path)

FirmLicenseWorkbookfile = glob.glob("../Firm Licenses.xlsx")[0]
FirmLicenseWorkbook = load_workbook(FirmLicenseWorkbookfile)
FirmLicenseSheet = FirmLicenseWorkbook["Sheet1"]
data = FirmLicenseSheet['A:H']
StateInitials = FirmLicenseSheet['B'][1:]

inmemorydata = [];listtuple = []
for state in FirmLicenseSheet.rows:
    listtuple = []
    for j in state:
        listtuple.append(j.value)
    inmemorydata.append(listtuple)

        
	

		
states_url = 'M:/Grow People/Licenses/ZZZ-Scripts for Map/states20m.json'

states = pygeoj.load(filepath=states_url)
attributes = states.all_attributes

outfile = pygeoj.new()
outfile.define_crs(type="link", link="http://spatialreference.org/ref/epsg/4326/esriwkt/", link_type="esriwkt")

for state in states:
    geometry = state.geometry
    for item in state.properties.iteritems():
        if item[0] == u'NAME':
            StateName = item[1]
        if item[0] == u'STUSPS':
            StateInitials = item[1]
            for FirmLicenseState in inmemorydata:
                if FirmLicenseState[1] == StateInitials:
                    try:
                        to = strftime('%m/%d/%Y',FirmLicenseState[4])
                        tr = strftime('%m/%d/%Y',FirmLicenseState[5])
                    except TypeError:
                        to = ""
                        tr = ""
                    outfile.add_feature(properties={"State":StateName,"License #":FirmLicenseState[3],
                    "Original Issue Date":to,"Renewal":tr,"Completed By":FirmLicenseState[6],
                    "Notes":FirmLicenseState[7]},geometry = geometry)

outfile.add_all_bboxes()
outfile.update_bbox()
outfile.add_unique_id()
outfile.save("joinedData.geojson")

        
