import os
import sys
import json
import time
import requests
import easygui as EG
from tqdm import tqdm
from openpyxl import load_workbook

KEY = '287d9b15ac3a90f9ea8de5ab53c6eb71'
URL = 'https://restapi.amap.com/v3/geocode/geo?'
# https://lbs.amap.com/api/webservice/guide/api/georegeo

def Location(city,address):
    parameters = 'city=' + city + '&address=' + address + '&output=json&key=' + KEY + '&callback=showLocation'
    api_url = URL + parameters
    r = requests.get(api_url)
    r = r.text
    r = r.strip('showLocation(')
    r = r.strip(')')
    jsonData = json.loads(r)
    status = jsonData['status']
    if (status == '1'):
        _address = jsonData['geocodes'][0]['formatted_address']
        location = jsonData['geocodes'][0]['location']
    else:
        _address = jsonData['info']
        location = '0,0'
    return (_address,location)

def Excel(xlsx):
    wb = load_workbook(xlsx)
    ws = wb.active
    max_row = ws.max_row + 1
    pbar = tqdm(total=max_row-2,desc='xzhou') 
    for i in range(2,max_row):
        pbar.update(1)
        US = str(ws.cell(row=i, column=1).value)
        CT = str(ws.cell(row=i, column=2).value)
        JD = str(ws.cell(row=i, column=4).value)
        WD = str(ws.cell(row=i, column=5).value)
        DZ = str(ws.cell(row=i, column=6).value)
        if JD == 'None' or WD == 'None' or JD == '0' or WD == '0':
            if CT == 'None':
                CT = ''
            uTime = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
            if DZ == 'None':
                if US != 'None':
                    DZ = US
                else:
                    continue
            _DZ,_JW = Location(CT,DZ)
            _JW = _JW.split(',')
            ws.cell(row=i, column=4).value = _JW[0]
            ws.cell(row=i, column=5).value = _JW[1]
            ws.cell(row=i, column=6).value = _DZ
            ws.cell(row=i, column=7).value = uTime
        else:
            continue
    wb.save(xlsx)
    wb.close()
    os.system("explorer.exe %s" % xlsx)
    sys.exit()

if __name__ == '__main__':
    TT =  'units city abbreviation longitude latitude address time mark'
    xlsx = EG.fileopenbox(title=TT,default='UnitsMap.xlsx')
    Excel(xlsx)