import pymssql
import logging
from DataCleaning.library.functions.getFileNames import *
from DataCleaning.library.functions.readXlsxFile import *
from DataCleaning.library.functions.getTwoSheets import *
from DataCleaning.library.functions.floatFormatter import *
from DataCleaning.library.functions.addPath import *

addPath("config")
from config import *


def generateCitySearcherDic(cursor):
    countrySearcherFile = getFileNamesInExternal()
    bkTemp = readXlsxFileInExternal(countrySearcherFile)
    sh1, sh2 = getTwoSheets(bkTemp)
    citySearcherDic = {}

    getMap = 'SELECT * FROM ' + countryNameMapping
    cursor.execute(getMap)
    rs = cursor.fetchall()
    for i in rs:
        citySearcherDic[i[2].upper()] = [i[3], i[2], i[4]]

    # from excel mapping
    for i in range(1, sh1.nrows):
        if sh1.cell(i, 3).value != "":
            citySearcherDic[str(sh1.cell(i, 3).value).upper()] = \
                [sh1.cell(i, 8).value, sh1.cell(i, 10).value, sh1.cell(i, 11).value]
        if sh1.cell(i, 5).value != "":
            citySearcherDic[str(sh1.cell(i, 5).value).upper()] = \
                [sh1.cell(i, 8).value, sh1.cell(i, 10).value, sh1.cell(i, 11).value]
        if sh1.cell(i, 6).value != "":
            citySearcherDic[floatFormatter(sh1.cell(i, 6).value)] = \
                [sh1.cell(i, 8).value, sh1.cell(i, 10).value, sh1.cell(i, 11).value]


    invalidFSL = []
    cityNameDic = {"EMEA HUB": "EMEA HUB", "CDC HUB": "CDC HUB"}
    for i in range(1, sh2.nrows):
        if len(sh2.cell(i, 8).value) > 6 and "but" not in sh2.cell(i, 8).value:
            invalidFSL.append(sh2.cell(i, 3).value)
        if sh2.cell(i, 4).value != "":
            cityNameDic[sh2.cell(i, 3).value] = sh2.cell(i, 4).value
    return citySearcherDic, invalidFSL, cityNameDic