import fiona
import requests
import utm
from geopy.geocoders import Nominatim
import time
import json
import pandas as pd
from fastkml import kml
import xlrd
import xlwt
from xlutils.copy import copy
from statistics import median
import math

kmlfarms = 'Downloads/data_mur.xlsx'
amrofilepath = 'Downloads/emp.xlsx'

def try_lower(temp_str):
    try:
        temp_str = temp_str.lower()
    except:
        wew = 1
    return temp_str


workbook_kmlfarms = xlrd.open_workbook(kmlfarms)
new_book = copy(workbook_kmlfarms)
sheet_kmlfarms_new = new_book.get_sheet(0)
sheet_kmlfarms = workbook_kmlfarms.sheet_by_index(0)
workbook_amrofarms = xlrd.open_workbook(amrofilepath)
sheet_amrofarms = workbook_amrofarms.sheet_by_index(0)
amro_obj = {}

for i in range(1,sheet_amrofarms.nrows):
    store = sheet_amrofarms.cell_value(i,5)
    amro = sheet_amrofarms.cell_value(i,6)
    zone = sheet_amrofarms.cell_value(i,7)
    emp_phone_number = sheet_amrofarms.cell_value(i,4)
    single_farmer_obj = {}
    store = try_lower(store)
    amro = try_lower(amro)
    zone = try_lower(zone)
    single_farmer_obj["zone"] = zone
    single_farmer_obj["amro"] = amro
    single_farmer_obj["store"] = store
    
    amro_obj[emp_phone_number] = single_farmer_obj

#print(amro_obj)

for i in range(1, sheet_kmlfarms.nrows):
    single_farmer_obj = {}
    addedby = sheet_kmlfarms.cell_value(i,1)
    addedby = addedby.replace("+91","")
    addedby = str(addedby)
    for (p,q) in amro_obj.items():
        p = str(p)
        if p in addedby:
            print(q["store"])
            sheet_kmlfarms_new.write(i,2,q["zone"].title())
            sheet_kmlfarms_new.write(i,3,q["amro"].title())
            sheet_kmlfarms_new.write(i,4,q["store"].title())
new_book.save('Farmonaut_new_sheet.xlsx')