# coding=utf-8
import os

import pandas as pd


def get_sim_card_set():
    SIM_CARD_PATH = u"C:/CARD_POOL.xlsx"

    simCardData = pd.read_excel(SIM_CARD_PATH)
    simCardPool = simCardData.ICCID.tolist()
    newSimCardPool = [_item.upper() for _item in simCardPool]
    return set(newSimCardPool)


def check_device(path, simCardPool):
    flag = False
    deviceData = pd.read_excel(path)
    devList = deviceData.to_dict('records')
    simCards = set([str(_item.get(u"流量卡")).upper() for _item in devList])
    badSimCards = simCards & simCardPool

    for _dev in devList:
        if str(_dev.get(u"流量卡")).upper() in badSimCards:
            flag = True
            _dev[u"换卡"] = u"是"
        else:
            _dev[u"换卡"] = u"否"

    return devList, flag


def main():
    path = raw_input(u"请输入文件夹/文件路径：").decode("utf-8")
    if os.path.isdir(path):
        files = [os.path.join(path, _file) for _file in os.listdir(path) if _file.endswith(".xlsx")]
    else:
        files = [path]

    for _file in files:
        df = pd.read_excel(_file).to_dict("record")
        newList = list()
        for _dev in df:
            print _dev[u"离线时间"], type(_dev[u"离线时间"])
            if _dev[u"换卡"] == u"是" and _dev[u"离线时间"] in [u"2020-12-29", u"2020-12-30", u"2020-12-31"]:
                newList.append(_dev)

        with pd.ExcelWriter(_file, mode='a', engine='openpyxl') as writer:
            pd.DataFrame(newList).to_excel(writer, "check")



    

if __name__ == '__main__':
    main()