import urllib.parse
from openpyxl import load_workbook, Workbook
import requests
from math import radians, cos, sin, asin, sqrt
import xlwt

"""load datafile"""
workbook = load_workbook('CompaniesF.xlsm')
Companysheet = workbook.get_sheet_by_name("Companysheet")
Networksheet = workbook.get_sheet_by_name("Networksheet")
networkco = []
compco = []


"""search for column with latitude/longitude - customers"""
numberlatC = -1
i = 0
for col in Companysheet.iter_cols():
	if col[0].value == "LAT" :
		numberlatC = i
	i+=1

numberlongC = -1
j = 0
for col in Companysheet.iter_cols():
	if col[0].value == "LNG" :
		numberlongC = j
	j+=1

numberlatN = -1
k = 0
for col in Networksheet.iter_cols():
	if col[0].value == "LAT" :
		numberlatN = k
	k+=1

numberlongN = -1
l = 0
for col in Networksheet.iter_cols():
	if col[0].value == "LNG" :
		numberlongN = l
	l+=1

latC = [row[numberlatC].value for row in Companysheet.iter_rows(min_row=3, max_row=73000) ]
#print(latC)
latCFloat = [float(i) for i in latC]

longC = [row[numberlongC].value for row in Companysheet.iter_rows(min_row=3, max_row=73000)]
longCFloat = [float(i) for i in longC]
compco = [list(x) for x in zip(latCFloat, longCFloat)]

#networkco = [[latN, lotN]]
latN = [row[numberlatN].value for row in Networksheet.iter_rows(min_row=3, max_row=103)]
latNFloat = [float(i) for i in latN]

longN = [row[numberlongN].value for row in Networksheet.iter_rows(min_row=3, max_row=103)]
longNFloat = [float(i) for i in longN]
networkco = [list(x) for x in zip(latNFloat, longNFloat)]


def CoordinatesDistance(A, B):
	# haversine formula
    x1,y1 = A
    x2,y2 = B
    dlon = y2 - y1
    dlat = x2 - x1
    a = sin(dlat/2)**2 + cos(x1) * cos(x2) * sin(dlon/2)**2
    c = 2 * asin(sqrt(a))
    r = 6371 # Radius of earth in kilometers. Use 3956 for miles
    distance = c*r
    distances = []
    distances.append([distance])
    return distance


def shortest_distance_pair(Comp, Net):
    pairs = []
    shortestfinal = []
    for P in Comp:
        shortest_distance = 999999
        for Y in Net:
            distance = CoordinatesDistance(P,Y)
            #customer_distance.append(distance)
            if distance < shortest_distance:
                shortest_distance = distance
                sdp = [(P,Y)]
            pairs.append(sdp)
            shortestfinal.append(shortest_distance)
    return shortestfinal

output = shortest_distance_pair(compco, networkco)


wb = Workbook(write_only = True)
ws = wb.create_sheet()

ws.append(output)
wb.save("output.xlsx")