import datetime
import math
import traceback
import psycopg2

from unitls.settings import DBServer, flightDB4, databasefileDB, FilePath

dataListLabel = ['序号', '类型', '时间', '数据1', '数据2', '唯一序列', '备1', '备2', '备3', '备4', '备5', '备6']

dbhost, dbport, dbuser, dbpassword,online_host, online_port, online_user, online_password = DBServer()
host=dbhost
port=dbport
user1=dbuser
password=dbpassword
flightDatedb = flightDB4()
databasefileDB = databasefileDB()

class flightDB():

    def __init__(self, host, port, user, password, database):

        self.conn = psycopg2.connect(
            host=host,
            port=port,
            user=user,
            password=password,
            database=database
        )
        self.c = self.conn.cursor()

    def initTable(self, tableName: str, primarykey: str, primarykeyStr: str, keyDict: dict):
        try:
            keyStr = ''
            num = 1
            for key in keyDict:
                if num != len(keyDict):
                    keyStr += '{} {},\n'.format(key, keyDict[key])
                else:
                    keyStr += '{} {}'.format(key, keyDict[key])
                num += 1

            curStr = """
                            create table if not exists {} (
                            {} {} ,
                            {}
                            )
                            """.format(tableName, primarykey, primarykeyStr, keyStr)
            # print(curStr)
            self.c.execute(curStr)
            self.conn.commit()
        except Exception:
            print(traceback.format_exc())
            #print(curStr)

    def insertData(self, tableName: str, data: dict, *args):
        try:
            # curStr1 = 'insert into {} '.format(tableName)
            num = 1
            curStr2 = ''
            for key in data:
                if num != len(data):
                    curStr2 += '{},'.format(key)
                else:
                    curStr2 += '{}'.format(key)
                num += 1
            curStr3 = ''
            num = 1
            for key in data:
                if num != len(data):
                    curStr3 += "'{}',".format(data[key])
                else:
                    curStr3 += "'{}'".format(data[key])
                num += 1
            curStr = """
                    insert into {} ({})
                    values ({})
                    """.format(tableName, curStr2, curStr3)
            #print(curStr)
            self.c.execute(curStr)

            if args == ():
                self.conn.commit()
        except Exception:
            print(traceback.format_exc())
    def lazyInsertData(self, tableName: str, data: dict):
        self.insertData(tableName, data, 'lazy')

    def FunctionCommit(self):
        try:
            self.conn.commit()
        except Exception:
            print(traceback.format_exc())


    def FunctionRollback(self):
        self.conn.rollback()

    def deleteTable(self, tablename: str, *condition: str):
        try:
            # print(condition)
            if condition != ():
                curStr = """
                           delete from {} where {}
                           """.format(tablename, condition[0])
            else:
                curStr = """
                           delete from {}
                           """.format(tablename)
            # print(curStr)
            self.c.execute(curStr)
            self.conn.commit()
        except Exception:
            print(traceback.format_exc())
    def lazydeleteTable(self, tablename: str, *condition: str):
        try:
            # print(condition)
            if condition != ():
                curStr = """
                           delete from {} where {}
                           """.format(tablename, condition[0])
            else:
                curStr = """
                           delete from {}
                           """.format(tablename)
            # print(curStr)
            self.c.execute(curStr)
        except Exception:
            print(traceback.format_exc())

    def deleteTable2(self, tablename: str):
        try:
            # print(condition)

            curStr = """
                    DROP TABLE IF EXISTS {};
                    """.format(tablename)
            # print(curStr)
            self.c.execute(curStr)
            self.conn.commit()
        except Exception:
            print(traceback.format_exc())

    def sort_queryTable(self, findkey: str, tablename: str, condition: str, tableKey: str, fn):
        try:
            curStr = """
                    select {} from {} where {} order by {} {}
                    """.format(findkey, tablename, condition, tableKey, fn)
            # print(curStr)
            self.c.execute(curStr)
            return self.c.fetchall()
        except Exception:
            print(traceback.format_exc())

    def sortTable(self, tablename: str, tableKey: str, fn):
        try:
            curStr = """
                    select * from {} order by {} {}
                    """.format(tablename, tableKey, fn)
            # print(curStr)
            self.c.execute(curStr)
            return self.c.fetchall()
        except Exception:
            print(traceback.format_exc())

    def sort_queryTable2(self, findkey: str, tablename: str, condition: str, tableKey: str, fn, tableKey1: str, fn1):
        try:
            curStr = """
                    select {} from {} where {} order by {} {},{} {}
                    """.format(findkey, tablename, condition, tableKey, fn, tableKey1, fn1)
            # print(curStr)
            self.c.execute(curStr)
            return self.c.fetchall()
        except Exception:
            print(traceback.format_exc())

    def getAlldata(self, tablename: str):
        try:
            curStr = """select * from {}""".format(tablename)
            self.c.execute(curStr)
            return self.c.fetchall()
        except Exception:
            print(traceback.format_exc())

    def queryTabel(self, tablename: str, key: str, condition: str):
        # print(tablename,key,condition)
        try:
            curStr1 = """
                    SELECT EXISTS (
                      SELECT * FROM pg_catalog.pg_tables
                      WHERE tablename = '{}' AND schemaname = 'public'
                    );
            """.format(tablename.lower())
            self.c.execute(curStr1)
            result = self.c.fetchall()[0][0]
            if result:
                curStr = """
                        select {} from {} where {}
                        """.format(key, 'public.' + tablename, condition)
                # print(key,tablename,condition)
                # print(curStr)
                self.c.execute(curStr)

                return self.c.fetchall()
            else:
                # print('{} 不存在'.format(tablename))
                return None
        except Exception:
            # print(curStr)
            print(traceback.format_exc())

    def upDateItem(self, tablename: str, dateDic: dict, condition: str, *args):
        try:
            setStr = ''
            for key in dateDic:
                setStr += "{}={},".format(key, dateDic[key])
            setStr = setStr[:-1]
            curStr = """
                    update {} set {} where {}
                    """.format(tablename, setStr, condition)
            print(curStr)
            if setStr != "":
                self.c.execute(curStr)
            if args == ():
                self.conn.commit()
        except Exception:
            print(traceback.format_exc())

    def lazyUpdateItem(self, tablename: str, dateDic: dict, condition: str):
        try:
            self.upDateItem(tablename, dateDic, condition, 'lazy')
        except Exception:
            print(traceback.format_exc())
            pass

    def getSingledata(self, findkey: str, tablename: str):
        try:
            curStr = """
                    select {} from {}
                    """.format(findkey, tablename)
            # print(curStr)
            result = self.c.execute(curStr)
            return result.fetchall()
        except Exception:
            print(traceback.format_exc())

    def deleteSingledata(self, tablename: str, findkey: str):
        try:
            curStr = """
                    delete from {} where {}
                    """.format(tablename, findkey)
            self.c.execute(curStr)
            # print(curStr)
            self.conn.commit()
        except Exception:
            print(traceback.format_exc())

    def funcExecute(self, string):
        self.c.execute(string)
        return self.c.fetchall()

    def close(self):
        try:
            self.conn.close()
        except Exception:
            print(traceback.format_exc())

def TuplefindInDataList(lists: list, type,data, numb):  # 返回列表
    res_list = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
    res_list.append(data)
    res = ','.join(str(x) for x in res_list)
    if lists and len(lists) !=0:
        try:
            for l in lists:
                if type == l[numb]:
                    lst = [int(x) for x in l[3].split(',')][1:]
                    lst.append(data)
                    res= ','.join(str(x) for x in lst)
                    break
            return res
        except:
            return res
    else:
        return res
def TuplefindInDataList2(lists: list, type,data, numb, type2, numb2):  # 返回列表
    res_list = [0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]
    res_list.append(data)
    res = ','.join(str(x) for x in res_list)
    if len(lists) !=0:
        try:
            for l in lists:
                if type == l[numb] and type2 == l[numb2]:
                    lst = [int(x) for x in l[3].split(',')][1:]
                    lst.append(data)
                    res= ','.join(str(x) for x in lst)
                    break
            return res
        except:
            return res
    else:
        return res
def mytask():
    finishdeTask=""
    nowDay = datetime.datetime.now().strftime("%Y%m%d").replace("-","")
    nowDay_1 = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d").replace("-","")
    nowDay_2 = (datetime.date.today() - datetime.timedelta(days=2)).strftime("%Y%m%d").replace("-", "")
    initFlightDatabase1(nowDay)
    initFlightDatabase1(nowDay_1)
    initFlightDatabase1(nowDay_2)

    alldata=DataDBUtilsgetData("sortFlight%s" % nowDay, "*", "编号 != ''")
    Yalldata=DataDBUtilsgetData("sortFlight%s" % nowDay_1, "*", "编号 != ''")
    try:
        mytask1 = function1(alldata,Yalldata)
        finishdeTask=finishdeTask+"task1/"
    except:
        mytask1=[]
        pass

    try:
        mytask2 = function2(alldata,Yalldata)
        finishdeTask=finishdeTask+"task2/"
    except:
        mytask2 = []
        pass

    now = datetime.datetime.now()
    startTime = now.replace(hour=0, minute=0, second=0, microsecond=0)
    ystartTime = (now - datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)
    tstartTime = (now + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)

    taskMainData=getData("taskMain", "*", "'%s' < 任务开始时间 and 任务开始时间 <'%s'"%(startTime,tstartTime))
    YtaskMainData = getData("taskMain", "*", "'%s' < 任务开始时间 and 任务开始时间 <'%s'" % (ystartTime, startTime))

    try:
        mytask3 = function4(taskMainData,YtaskMainData)
        finishdeTask=finishdeTask+"task3/"
    except:
        mytask3=[]
        pass

    try:
        mytask4 = function5()
        finishdeTask=finishdeTask+"task4/"
    except:
        pass
    try:
        mytask5 = function7()
        finishdeTask=finishdeTask+"task5/"
    except:
        pass
    try:
        mytask6 = function9()
        finishdeTask=finishdeTask+"task6/"
    except:
        pass

    nowday=datetime.date.today()
    nowday_1 = datetime.date.today() - datetime.timedelta(days=1)
    dy={0:"每日短停",1:"每日航前",2:"每日航后",3:"每日特后前",4:"每日停场",5:"每日短停",6:"每日航前",7:"每日航后",8:"每日特后前",9:"每日停场",10:"每日外委",11:"每日外委",12:"每日总数",13:"每日总数",14:"每日川航",15:"每日川航",16:"已执行",17:"已执行"}
    fdb = flightDB(host=online_host,
                   port=online_port,
                   user=online_user,
                   password=online_password,
                   database=databasefileDB
                   )
    try:
        olddata_1=fdb.getAlldata("dataList{}".format(nowDay_1))
    except:
        olddata_1=[]
    try:
        olddata_2=fdb.getAlldata("dataList{}".format(nowDay_2))
    except:
        olddata_2=[]

    fdb.lazydeleteTable("dataList{}".format(nowDay_1),"1=1")
    fdb.lazydeleteTable("dataList{}".format(nowDay), "1=1")
    try:
        for ii in range(0,len(mytask1)):  ##每日航班分布
            if ii in [5,6,7,8,9,11,13,15,17]:
                res_data_1=TuplefindInDataList(olddata_2,dy[ii],mytask1[ii],1)
                newdic={"类型":dy[ii],"时间":nowday_1,"数据1":res_data_1,"数据2":"",'唯一序列':"",'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
                fdb.lazyInsertData("dataList{}".format(nowDay_1), newdic)
            else:
                res_data=TuplefindInDataList(olddata_1,dy[ii],mytask1[ii],1)
                newdic = {"类型": dy[ii], "时间": nowday, "数据1": res_data, "数据2": "", '唯一序列': "", '备1': "", '备2': "",'备3': "", '备4': "", '备5': "", '备6': ""}
                fdb.lazyInsertData("dataList{}".format(nowDay), newdic)

        for ii in mytask2[0].keys():##每刻航班分布
            for iii in mytask2[0][ii].keys():
                res_data = TuplefindInDataList2(olddata_1, '每刻%s'%iii,mytask2[0][ii][iii],1, ii,2)
                newdic={"类型":'每刻%s'%iii,"时间":ii,"数据1":res_data,"数据2":"",'唯一序列':"",'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
                fdb.lazyInsertData("dataList{}".format(nowDay), newdic)

        for iiii in mytask2[1].keys():
            for iiiii in mytask2[1][iiii].keys():
                res_data = TuplefindInDataList2(olddata_2, '每刻%s'%iiiii, mytask2[1][iiii][iiiii],1, iiii,2)
                newdic={"类型":'每刻%s'%iiiii,"时间":iiii,"数据1":res_data,"数据2":"",'唯一序列':"",'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
                fdb.lazyInsertData("dataList{}".format(nowDay_1), newdic)

         #每日任务分布

        res_data_1 = TuplefindInDataList(olddata_1, '任务分布', mytask3[1], 1)
        res_data_2 = TuplefindInDataList(olddata_1, '任务分布', mytask3[0], 1)
        newdic={"类型":'任务分布',"时间":nowday,"数据1":res_data_1,"数据2":res_data_2,'唯一序列':"",'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
        fdb.lazyInsertData("dataList{}".format(nowDay), newdic)
        res_data_1 = TuplefindInDataList(olddata_2, '任务分布', mytask3[3], 1)
        res_data_2 = TuplefindInDataList(olddata_2, '任务分布', mytask3[2], 1)
        newdic = {"类型": '任务分布', "时间": nowday_1, "数据1": res_data_1, "数据2": res_data_2, '唯一序列': "", '备1': "", '备2': "",'备3': "", '备4': "", '备5': "", '备6': ""}
        fdb.lazyInsertData("dataList{}".format(nowDay_1), newdic)

        #随机任务
        res_data_1 = TuplefindInDataList(olddata_1, '随机任务', mytask4[0], 1)
        res_data_2 = TuplefindInDataList(olddata_1, '随机任务', mytask4[1], 1)
        newdic={"类型":'随机任务',"时间":nowday,"数据1":res_data_1,"数据2":res_data_2,'唯一序列':"",'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
        fdb.lazyInsertData("dataList{}".format(nowDay), newdic)

        res_data_1 = TuplefindInDataList(olddata_2, '随机任务', mytask4[2], 1)
        res_data_2 = TuplefindInDataList(olddata_2, '随机任务', mytask4[3], 1)
        newdic = {"类型": '随机任务', "时间": nowday_1, "数据1":res_data_1,"数据2":res_data_2, '唯一序列': "", '备1': "", '备2': "",'备3': "", '备4': "", '备5': "", '备6': ""}
        fdb.lazyInsertData("dataList{}".format(nowDay_1), newdic)

        #航材工具配送
        for i in mytask5[0].keys():
            newdic={"类型":'%s配送'%mytask5[0][i]["类型"],"时间":mytask5[0][i]['配送发起时间'],"数据1":mytask5[0][i]['机位'],"数据2":mytask5[0][i]['耗时'],'唯一序列':mytask5[0][i]['任务号'],'备1':mytask5[0][i]['任务级别'],'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
            fdb.lazyInsertData("dataList{}".format(nowDay), newdic)
        for i in mytask5[1].keys():
            newdic={"类型":'%s配送'%mytask5[1][i]["类型"],"时间":mytask5[1][i]['配送发起时间'],"数据1":mytask5[1][i]['机位'],"数据2":mytask5[1][i]['耗时'],'唯一序列':mytask5[1][i]['任务号'],'备1':mytask5[1][i]['任务级别'],'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
            fdb.lazyInsertData("dataList{}".format(nowDay_1), newdic)
        #二拖
        for i in mytask6[0].keys():
            newdic={"类型":'二拖任务',"时间":mytask6[0][i]['创建时间'],"数据1":"","数据2":mytask6[0][i]['耗时'],'唯一序列':mytask6[0][i]['任务号'],'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
            fdb.lazyInsertData("dataList{}".format(nowDay), newdic)
        for i in mytask6[1].keys():
            newdic={"类型":'二拖任务',"时间":mytask6[1][i]['创建时间'],"数据1":"","数据2":mytask6[1][i]['耗时'],'唯一序列':mytask6[1][i]['任务号'],'备1':"",'备2':"",'备3':"",'备4':"",'备5':"",'备6':""}
            fdb.lazyInsertData("dataList{}".format(nowDay_1), newdic)
        fdb.FunctionCommit()
        fdb.close()
        #t3 = datetime.datetime.now()
        #print(t3 - t2)
        print(datetime.datetime.now(), "[计算中心]统计分析写入完毕(%s)"%finishdeTask)
        return "ok"
    except Exception as e:
        print(traceback.format_exc())
        fdb.close()
        return "fail"

def TuplefindInList(lists: list, args, numb):  # 返回列表
    res = []
    try:
        for l in lists:
            if args == l[numb]:
                res.append(l)
        return res
    except:
        return res
def CountAllList(lists: list):  # 返回列表
    res = 0
    try:
        for l in lists:
            res+=1
        return res
    except:
        return res

def CountInList(lists: list, args, numb):  # 返回列表
    res = 0
    try:
        for l in lists:
            if args in l[numb]:
                res+=1
        return res
    except:
        return res
def CountInListNot(lists: list, args, numb):  # 返回列表
    res = 0
    try:
        for l in lists:
            if l[numb] != args:
                res+=1
        return res
    except:
        return res

def CountFinshed(lists: list):  # 返回列表
    res = 0
    try:
        for l in lists:
            if l[3] != "停场" and (l[5] == "3" or l[5] == "4"):
                res+=1
        return res
    except:
        return res

def function1(alldata,Yalldata):   #查询每日航班的分布
    resTR=CountInList(alldata, "短停", 3)
    resPEF=CountInList(alldata, "航前", 3)
    resPOF=CountInList(alldata, "航后", 3)
    resTAF=CountInList(alldata, "特后前", 3)
    resST=CountInList(alldata, "停场", 3)
    resAll=resTR+resPEF+resPOF+resTAF
    resfinsh=CountFinshed(alldata)
    YresTR=CountInList(Yalldata, "短停", 3)
    YresPEF=CountInList(Yalldata, "航前", 3)
    YresPOF=CountInList(Yalldata, "航后", 3)
    YresTAF=CountInList(Yalldata, "特后前", 3)
    YresST=CountInList(Yalldata, "停场", 3)
    YreAll=YresTR+YresPEF+YresPOF+YresTAF
    ww=CountInList(alldata, "PB", 0)
    yww=CountInList(Yalldata, "PB", 0)
    ch=resAll-ww
    Ych=YreAll-yww
    Yresfinsh=CountFinshed(Yalldata)

    return (resTR,resPEF,resPOF,resTAF,resST,YresTR,YresPEF,YresPOF,YresTAF,YresST,ww,yww,resAll,YreAll,ch,Ych,resfinsh,Yresfinsh)
def function2(alldata,Yalldata): #实时任务分布
    now=datetime.datetime.now()
    startTime = now.replace(hour=0,minute=0, second=0, microsecond=0)
    ystartTime = (now - datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)
    res=function3(startTime,alldata)
    yres = function3(ystartTime, Yalldata)
    return(res,yres)

def function4(taskMainData,YtaskMainData):#任务分布
    resALL=len(taskMainData) if taskMainData != None else 0
    resFinish=CountInListNot(taskMainData,"",20)
    yresALL=len(YtaskMainData) if YtaskMainData != None else 0
    yresFinish=CountInListNot(YtaskMainData,"",20)
    return(resALL, resFinish,yresALL,yresFinish)


def function3(startTime,res):

    list=[]
    ress={}
    aa=30#准备时间
    bb=25#收尾时间
    if len(res) !=0 and res !=None:
        for i in res:
            #print(i)
            if i[3]== "航前":
                a=("航前",datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=5)- datetime.timedelta(minutes=aa),datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S")+ datetime.timedelta(minutes=90)+ datetime.timedelta(minutes=bb),i[5])
            elif i[3]== "短停接":
                a = ("短停接", datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=5)- datetime.timedelta(minutes=aa), datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") + datetime.timedelta(minutes=15)+ datetime.timedelta(minutes=bb),i[5])
            elif i[3]== "特后前接":
                a = ("特后前接", datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=5)- datetime.timedelta(minutes=aa), datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") + datetime.timedelta(minutes=15)+ datetime.timedelta(minutes=bb),i[5])
            elif i[3]== "短停送":
                a = ("短停送", datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=5)- datetime.timedelta(minutes=aa), datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") + datetime.timedelta(minutes=25)+ datetime.timedelta(minutes=bb),i[5])
            elif i[3]== "特后前送":
                a = ("特后前送", datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=5)- datetime.timedelta(minutes=aa), datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") + datetime.timedelta(minutes=25)+ datetime.timedelta(minutes=bb),i[5])
            else:
                a = ("航后", datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") - datetime.timedelta(minutes=5)- datetime.timedelta(minutes=aa), datetime.datetime.strptime(i[2], "%Y-%m-%d %H:%M:%S") + datetime.timedelta(minutes=120)+ datetime.timedelta(minutes=bb),i[5])
            list.append(a)

    for j in range(0,66): #1980 /间隔
        judgeTime=startTime + datetime.timedelta(minutes=30*j)
        tr=0
        prf=0
        pof=0
        taf=0
        ytr = 0   #预测
        yprf = 0
        ypof = 0
        ytaf = 0
        if list !=[]:
            for ii in list:
                if ii[1] < judgeTime < ii[2] and ii[3] != "2" and judgeTime < datetime.datetime.now():
                    if ii[0]== "航前":
                        prf+=1
                    elif ii[0]== "短停接":
                        tr+=1
                    elif ii[0]== "特后前接":
                        taf+=1
                    elif ii[0]== "短停送":
                        tr+=1
                    elif ii[0]== "特后前送":
                        taf+=1
                    else:
                        pof+=1
                if ii[1] < judgeTime < ii[2]:
                    if ii[0] == "航前":
                        yprf += 1
                    elif ii[0] == "短停接":
                        ytr+=1
                    elif ii[0] == "特后前接":
                        ytaf+=1
                    elif ii[0]== "短停送":
                        ytr+=1
                    elif ii[0]== "特后前送":
                        ytaf+=1
                    else:
                        ypof+=1
        ress["%s"%judgeTime]={"航前":prf,"短停":tr,"特后前":taf,"航后":pof,"合计":prf+tr+taf+pof,"预计航前":yprf,"预计短停":ytr,"预计特后前":ytaf,"预计航后":ypof,"预计合计":yprf+ytr+ytaf+ypof}
    return ress

def function5():#随机任务
    now = datetime.datetime.now()
    nowDay = datetime.date.today().strftime("%Y%m%d")
    nowDay_1 = (datetime.date.today() - datetime.timedelta(days=1)).strftime("%Y%m%d")

    peopleSchedule=DataDBUtilsgetData("peopleSchedule%s" % nowDay, "*", "附加消息 like '%随机离港%'")
    ypeopleSchedule = DataDBUtilsgetData("peopleSchedule%s" % nowDay_1, "*", "附加消息 like '%随机离港%'")
    res=function6(peopleSchedule,nowDay)
    yres = function6(ypeopleSchedule, nowDay_1)
    return(res,len(peopleSchedule),yres,len(ypeopleSchedule))
def function6(res,day):
    a=0
    if res != None and len(res) !=0:
        for i in res:
            check=DataDBUtilsgetData("sortFlight%s" % day, "级别", "编号 ='%s'" % (i[1] + "-2"))
            if check !=None and len(check) != 0 and check[0][0] == "4":
                a+=1
    return a

def function7():#航材工具
    now = datetime.datetime.now()
    startTime = now.replace(hour=0, minute=0, second=0, microsecond=0)
    ystartTime = (now - datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)
    tstartTime = (now + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)
    resALL=getData("sendTaskList",'*',"'%s' < 配送发起时间 and 配送发起时间 <'%s'"%(startTime,tstartTime))
    yresALL = getData("sendTaskList", '*', "'%s' < 配送发起时间 and 配送发起时间 <'%s'" % (ystartTime, startTime))
    res=function8(resALL)
    yres = function8(yresALL)
    return(res,yres)
    #
def function8(res):
    ress={}
    if res !=None and len(res) !=0:
        for i in res:
            if i[23] == "" and i[6] != "":
                costtime= math.ceil((datetime.datetime.strptime(i[23].split(".")[0], "%Y-%m-%d %H:%M:%S")- datetime.datetime.strptime(i[6].split(".")[0], "%Y-%m-%d %H:%M:%S")).total_seconds()/60)
            else:
                costtime=""

            ress[i[16]]={"类型":i[2],"机位":i[17],"配送发起时间":i[4].split(".")[0],"耗时":costtime,"任务级别":i[13],"任务号":i[16]}

    return ress
def function9():#二拖
    now = datetime.datetime.now()
    startTime = now.replace(hour=0, minute=0, second=0, microsecond=0)
    ystartTime = (now - datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)
    tstartTime = (now + datetime.timedelta(days=1)).replace(hour=0, minute=0, second=0, microsecond=0)

    resALL = getData("towbarMain", '*', "'%s' < 创建时间 and 创建时间 <'%s'" % (startTime, tstartTime))
    yresALL = getData("towbarMain", '*', "'%s' < 创建时间 and 创建时间 <'%s'" % (ystartTime, startTime))
    res=function10(resALL)
    yres = function10(yresALL)
    return(res,yres)
def function10(res):
    ress={}
    if res !=None and len(res) !=0:
        for i in res:
            if i[11] !="":
                costtime= math.ceil((datetime.datetime.strptime(i[11].split(".")[0], "%Y-%m-%d %H:%M:%S")- datetime.datetime.strptime(i[9].split(".")[0], "%Y-%m-%d %H:%M:%S")).total_seconds()/60)
            else:
                costtime=""
            ress[i[12]]={"创建时间":i[8].split(".")[0],"耗时":costtime,"任务号":i[12]}
    return ress


def initFlightDatabase1(datatime):
    database = flightDB(host=online_host,
                        port=online_port,
                        user=user1,
                        password=online_password,
                        database=databasefileDB
                        )
    try:
        dataListDic = {}
        primaryKey = 'SERIAL PRIMARY KEY'
        for key8 in list(dataListLabel)[1:]:
            dataListDic[key8] = 'text not null'
        database.initTable('dataList{}'.format(datatime), "序号", primaryKey, dataListDic)
        database.close()
    except Exception:
        database.close()
        print(traceback.format_exc())

def getData(tablename, data, key):
    fdb = flightDB(host=host,
                   port=port,
                   user=user1,
                   password=password,
                   database=databasefileDB
                   )
    try:
        res = fdb.queryTabel(tablename, data, key)
        fdb.close()
        return res
    except Exception:
        fdb.close()
        print(traceback.format_exc())
        return []

def DataDBUtilsgetData(tablename,data,key):
    fdb = flightDB(host=host,
                   port=port,
                   user=user1,
                   password=password,
                   database=flightDatedb,
                   )
    try:
        res = fdb.queryTabel(tablename, data, key)
        fdb.close()
        return res
    except Exception:
        fdb.close()
        print(traceback.format_exc())
        return []