# 导入pymysql
import pymysql as psql
from retry import retry
import message_pb2 as message
import google.protobuf.text_format as tf
import threading
import datetime
import uuid
import math
class CommandInfo:
    def __init__(self,type,table):
        self.type=type
        self.table=table

class DBCommand():
    def __init__(self,ip,port,database,user,password):
        self.ip=ip
        self.port=port
        self.database=database
        self.user=user
        self.password=password
        self.lock=threading.Lock()
        self.conn=psql.connect(host=ip,
                               port=port,
                               database=database,
                               charset="utf8",
                               user=user,
                               passwd=password)

    def push_command(self,cmd_table):
        #判断指令类型
        if self.table_type(cmd_table)=="park":
            ret_table=self.enqueue_parkcmd(cmd_table)
        elif self.table_type(cmd_table)=="pick":
            ret_table=self.enqueue_pickcmd(cmd_table)
        else:
            print(" 入队指令格式错误")
            return None
        return ret_table

    def enqueue_parkcmd(self,table):
        with self.lock:
            cmd=message.park_table()
            tf.Parse(table,cmd)
            self.conn.ping()
            self.conn.begin()
            cursor = self.conn.cursor()
            #检查车辆是否在库内
            SQL="select * from vehicle where car_number='%s'"%(cmd.car_number)
            exist_vehicle=cursor.execute(SQL)
            SQL="select * from command_queue where car_number='%s'"%(cmd.car_number)
            exist_command=cursor.execute(SQL)
            if exist_command>0 or exist_vehicle>0:
                ts=message.table_statu()
                ts.execute_statu=message.eError
                ts.statu_description="车辆 :%s 已在库内!"%(cmd.car_number)
                cmd.statu.CopyFrom(ts)
                self.conn.commit()
                cursor.close()
                return tf.MessageToString(cmd,as_utf8=True)

            #检查车位是否够
            elif cmd.entrance_measure_info.height<1.55:
                #小轿车, 总车位数-队列等待数>0即可
                SQL="select * from space where car_number IS NULL and unit=%d and statu=0"%(cmd.unit_id)
                idle_space_count=cursor.execute(SQL)
                SQL="select * from command_queue where unit=%d and statu=0 and type=1"%(cmd.unit_id)
                wait_park_count=cursor.execute(SQL)
                if not idle_space_count-wait_park_count>0:
                    ts=message.table_statu()
                    ts.execute_statu=message.eError
                    ts.statu_description="没有空余车位,车牌号:%s 高度:%.3fm!"%(cmd.car_number,cmd.entrance_measure_info.height)
                    cmd.statu.CopyFrom(ts)
                    self.conn.commit()
                    cursor.close()
                    return tf.MessageToString(cmd,as_utf8=True)
            elif cmd.entrance_measure_info.height<1.75:
                #suv ,空闲suv车位-队列suv    在(队列空闲轿车-空闲轿车位）>0时，需要减去这个
                SQL="select * from space where car_number IS NULL and unit=%d and height>1.55 and height<=1.75 and statu=0"%(cmd.unit_id)
                suv_idl_count=cursor.execute(SQL) #空闲suv车位数

                SQL="select * from space where car_number IS NULL and unit=%d and height<=1.55 and statu=0"%(cmd.unit_id)
                mini_idle_count=cursor.execute(SQL) #轿车位数

                SQL="select * from command_queue where unit=%d and statu=0 and type=1"%(cmd.unit_id)
                cursor.execute(SQL)
                queue_cmd=cursor.fetchall() #所有等待指令
                queue_car_count=0   #队列轿车指令计数
                for qc in queue_cmd:
                    info=message.measure_info()
                    tf.Parse(qc[7],info)
                    if info.height<=1.55:    #队列轿车停车指令
                        queue_car_count=queue_car_count+1
                queue_suv_count=len(queue_cmd)-queue_car_count
                space_scarcity=0
                if mini_idle_count<queue_car_count:
                    space_scarcity=queue_car_count-mini_idle_count
                if not (suv_idl_count-queue_suv_count-space_scarcity)>0:
                    ts=message.table_statu()
                    ts.execute_statu=message.eError
                    ts.statu_description="没有SUV车位,车牌号:%s,高度:%.3fm!"%(cmd.car_number,cmd.entrance_measure_info.height)
                    cmd.statu.CopyFrom(ts)
                    self.conn.commit()
                    cursor.close()
                    return tf.MessageToString(cmd,as_utf8=True)
            elif cmd.entrance_measure_info.height<2.0:
                SQL = "select * from space where car_number IS NULL and unit=%d and height>1.75 and height<=2.0 and statu=0" % (cmd.unit_id)
                big_idl_count = cursor.execute(SQL)  # 空闲 big 车位数
                SQL = "select * from space where car_number IS NULL and unit=%d and height>1.55 and height<=1.75 and statu=0" % (cmd.unit_id)
                suv_idle_count = cursor.execute(SQL)  # suv位数
                SQL = "select * from space where car_number IS NULL and unit=%d and height<=1.55 and statu=0" % (cmd.unit_id)
                small_idle_count = cursor.execute(SQL)  # 轿车位数
                if big_idl_count>=1:
                    cmd_small_count=0
                    cmd_suv_count=0
                    cmd_big_count=0
                    SQL = "select * from command_queue where unit=%d and statu=0 and type=1" % (cmd.unit_id)
                    cursor.execute(SQL)
                    queue_cmd = cursor.fetchall()  # 所有等待指令
                    for qc in queue_cmd:
                        info = message.measure_info()
                        tf.Parse(qc[7], info)
                        if info.height <1.55:  # 队列轿车停车指令
                            cmd_small_count = cmd_small_count + 1
                        elif info.height<1.75:
                            cmd_suv_count=cmd_suv_count+1
                        elif info.height<2.0:
                            cmd_big_count=cmd_big_count+1
                        else:
                            pass

                    suv_space_scarcity = 0
                    if small_idle_count < cmd_small_count:
                        suv_space_scarcity = cmd_small_count - small_idle_count
                    suv_idle_count=suv_idle_count-suv_space_scarcity
                    if cmd_suv_count-suv_idle_count>0:
                        big_idl_count=big_idl_count-(cmd_suv_count-suv_idle_count)
                    if big_idl_count<0:
                        ts = message.table_statu()
                        ts.execute_statu = message.eError
                        ts.statu_description = "没有SUV车位,车牌号:%s,高度:%.3fm!" % (
                        cmd.car_number, cmd.entrance_measure_info.height)
                        cmd.statu.CopyFrom(ts)
                        self.conn.commit()
                        cursor.close()
                        return tf.MessageToString(cmd, as_utf8=True)
                else:
                    ts = message.table_statu()
                    ts.execute_statu = message.eError
                    ts.statu_description = "没有SUV车位,车牌号:%s,高度:%.3fm!" % (
                    cmd.car_number, cmd.entrance_measure_info.height)
                    cmd.statu.CopyFrom(ts)
                    self.conn.commit()
                    cursor.close()
                    return tf.MessageToString(cmd, as_utf8=True)
            else:
                ts = message.table_statu()
                ts.execute_statu = message.eError
                ts.statu_description = "车辆超高,车牌号:%s,高度:%.3fm!" % (
                cmd.car_number, cmd.entrance_measure_info.height)
                cmd.statu.CopyFrom(ts)
                self.conn.commit()
                cursor.close()
                return tf.MessageToString(cmd, as_utf8=True)

            #指令允许入队，填写唯一码
            if not cmd.statu.execute_statu==message.eError:
                uid=str(uuid.uuid1())
                cmd.primary_key=uid
                #查询队列指令以及最后一次指令编号及时间
                cursor.execute("select * from command_queue,queue_number_data FOR UPDATE")  #锁表
                SQL="select * from command_queue where unit=%d and statu=0"%(cmd.unit_id)
                cursor.execute(SQL)
                cmd_queue=cursor.fetchall()
                queue_id=0
                #计算当前指令编号
                if len(cmd_queue)>0:
                    SQL="select * from queue_number_data where unit=%d"%cmd.unit_id
                    cursor.execute(SQL)
                    record=cursor.fetchall()
                    last_num=0
                    last_time=datetime.datetime.now()
                    if len(record)==1:
                        last_num=record[0][1]
                        last_time=record[0][2]
                    elif len(record)==0:
                        SQL="insert into queue_number_data values(%d,0,NULL)"%(cmd.unit_id)
                        cursor.execute(SQL)
                    diff=datetime.datetime.now()-last_time
                    time_mns=diff.total_seconds()/60
                    queue_id=int(last_num+1+ time_mns*1)

                #停车入队前，按照库内轴距更新测量轴距值 --------------------------------------------
                database_wheelbase=self.get_wheelbase(cursor,cmd.car_number)
                new_wheelbase=cmd.entrance_measure_info.wheelbase
                if database_wheelbase is not None:
                    [wheelbase,delta]=database_wheelbase
                    entrance_wheelbase=cmd.entrance_measure_info.wheelbase
                    if abs(entrance_wheelbase-wheelbase)>0.1:  #与入口测量偏差过大，不更新
                        print("车辆：%s 测量轴距与数据库相差太大(测量:%f 数据库:%f)，不做融合，以测量为准"%(cmd.car_number,entrance_wheelbase,wheelbase))
                    else:
                        sum=(delta*delta)+0.05*0.05
                        new_wheelbase=entrance_wheelbase*delta*delta/sum+wheelbase*(0.05*0.05)/sum
                        print("车辆：%s 停车更新轴距：%f ，测量：%f,数据库:%f"%(cmd.car_number,new_wheelbase,entrance_wheelbase,wheelbase))
                        new_measure_info=message.measure_info()
                        new_measure_info.CopyFrom(cmd.entrance_measure_info)
                        new_measure_info.wheelbase=new_wheelbase
                        cmd.entrance_measure_info.CopyFrom(new_measure_info)

                else:
                    print("车辆:%s 不存在数据库轴距，使用测量轴距：%f"%(cmd.car_number,cmd.entrance_measure_info.wheelbase))
                #-----------------------------------

                SQL="insert into command_queue values('%s','%s',%d,%d,1,0,NULL,'%s', %d , NULL)"%(
                    cmd.car_number,cmd.primary_key,cmd.unit_id,queue_id
                    ,tf.MessageToString(cmd.entrance_measure_info,as_utf8=True), cmd.terminal_id )
                cursor.execute(SQL)
                #更新指令时间表，
                dt=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                SQL="update queue_number_data set last_number=%d,input_time=str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%S') where unit=%d"%(queue_id,dt,cmd.unit_id)
                cursor.execute(SQL)


            self.conn.commit()
            cursor.close()
            return tf.MessageToString(cmd,as_utf8=True)


    def enqueue_pickcmd(self,table):
        with self.lock:
            cmd=message.pick_table()
            tf.Parse(table,cmd)
            #检查车辆是否在车位表，查找对应车位，找到即入队
            self.conn.ping()
            self.conn.begin()
            cursor = self.conn.cursor()
            SQL="select * from vehicle where primary_key='%s'"%(cmd.primary_key)
            cursor.execute(SQL)
            car_info=cursor.fetchall()
            if not len(car_info)>=1:
                ts=message.table_statu()
                ts.execute_statu=message.eError
                ts.statu_description="车辆信息不存在!"
                cmd.statu.CopyFrom(ts)
            else:
                SQL="select * from command_queue where primary_key='%s'"%(cmd.primary_key)
                num=cursor.execute(SQL)
                if num>0:   #取车指令在对哦中，不能执行
                    ts=message.table_statu()
                    ts.execute_statu=message.eError
                    ts.statu_description="指令正在执行中，请稍等！"
                    cmd.statu.CopyFrom(ts)
                else:
                    car_number=car_info[0][0]
                    cmd.car_number=car_number
                    #查询车位
                    SQL="select * from space where car_number='%s'"%(car_number)
                    cursor.execute(SQL)
                    space=cursor.fetchall()
                    #找不到，将指令表单状态修改为error
                    if not len(space)==1:
                        ts=message.table_statu()
                        ts.execute_statu=message.eError
                        ts.statu_description="没有找到车辆信息，请联系管理员!"
                        cmd.statu.CopyFrom(ts)
                    else:
                    #查询到单元号,给指令表单单元号赋值
                        unit_id=space[0][5]
                        cmd.unit_id=unit_id
                        #给指令表单车位信息赋值
                        space_info=message.parkspace_info()
                        space_info.id=space[0][0]
                        space_info.unit_id=unit_id
                        space_info.floor=space[0][1]
                        space_info.room_id=space[0][2]
                        cmd.actually_space_info.CopyFrom(space_info)
                        #赋值真实测量信息
                        measure_info=message.measure_info()
                        tf.Parse(car_info[0][2],measure_info)
                        cmd.actually_measure_info.CopyFrom(measure_info)
                        #指令入库
                        cursor.execute("select * from command_queue,queue_number_data FOR UPDATE")  #锁表
                        SQL="select * from command_queue where unit=%d and statu=0"%(unit_id)
                        cursor.execute(SQL)
                        cmd_queue=cursor.fetchall()
                        queue_id=0
                        if len(cmd_queue)>0:
                            SQL="select * from queue_number_data where unit=%d"%unit_id
                            cursor.execute(SQL)
                            record=cursor.fetchall()
                            last_num=0
                            last_time=datetime.datetime.now()
                            if len(record)==1:
                                last_num=record[0][1]
                                last_time=record[0][2]
                            elif len(record)==0:
                                SQL="insert into queue_number_data values(%d,0,NULL)"%(cmd.unit_id)
                                cursor.execute(SQL)
                            diff=datetime.datetime.now()-last_time
                            time_mns=diff.total_seconds()/60
                            queue_id=int(last_num+1+ time_mns*1)

                        SQL="insert into command_queue values('%s','%s',%d,%d,2,0,'%s','%s',NULL, NULL)"%(
                            cmd.car_number,cmd.primary_key,unit_id,queue_id,tf.MessageToString(cmd.actually_space_info,as_utf8=True)
                            ,tf.MessageToString(cmd.actually_measure_info,as_utf8=True))
                        cursor.execute(SQL)
                        #更新指令时间表，
                        dt=datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        SQL="update queue_number_data set last_number=%d,input_time=str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%S') where unit=%d"%(queue_id,dt,unit_id)
                        cursor.execute(SQL)

                        #取车指令入队完成，记录轴距信息
                        self.update_wheelbase(cursor,cmd.car_number)

            self.conn.commit()
            cursor.close()
            return tf.MessageToString(cmd,as_utf8=True)
    def update_wheelbase(self,cursor,car_number):
        #取车指令入队成功，此处将车辆夹持杆轴距偏差按照 delta=0.03 更新  ----------------------------
        #获取车辆表中的轴距信息
        SQL="select actually_measure_info,plc_measure_info from vehicle where car_number='%s'"%car_number
        cursor.execute(SQL)
        records=cursor.fetchall()
        act_info=message.measure_info() #入口测量数据
        plc_info=message.measure_info() #plc反馈数据（夹持杆雷达）
        clamp_wheelbase=None
        if len(records)==1: #找到记录
            if records[0][0] is not None and records[0][1] is not None:
                tf.Parse(records[0][0],act_info)
                tf.Parse(records[0][1],plc_info)
                if abs(act_info.wheelbase-plc_info.wheelbase)<0.05: #入口数据与夹持数据相近，可靠，
                    clamp_wheelbase=plc_info.wheelbase

        #夹持数据存在，且可靠数据，存库，
        if(clamp_wheelbase is not None):
            SQL="select * from car_parameter where car_number='%s'"%car_number
            cursor.execute(SQL)
            records=cursor.fetchall()

            update_wheelbase=clamp_wheelbase
            update_delta=0.03
            SQL="insert into car_parameter values('%s',%f,%f,%f,0.03)"%(car_number,update_wheelbase,update_delta,plc_info.width)
            if len(records)==1:#有历史数据
                wheel_base=float(records[0][1])
                wheel_base_delta=float(records[0][2])
                if abs(clamp_wheelbase-wheel_base)>0.05: #可能存在改装或者换牌，重置delta
                    print("车辆：%s 可能存在换牌或者改装，重置轴距信息"%(car_number))
                else:   #计算新值
                    dcdc=update_delta*update_delta
                    dd=wheel_base_delta*wheel_base_delta
                    update_wheelbase=clamp_wheelbase*dd/(dcdc+dd)+wheel_base*dcdc/(dd+dcdc)
                    update_delta=math.sqrt(dcdc*dd/(dcdc+dd))
                    print("车辆:%s 更新数据库轴距:%.5f,标准差:%.5f"%(car_number,update_wheelbase,update_delta))
                SQL="update car_parameter set wheelbase=%f,var_wheelbase=%f where car_number='%s'"%(update_wheelbase,update_delta,car_number)
            else:
                print("新车:%s 第一次光临，存入轴距:%f"%(car_number,update_wheelbase))
            cursor.execute(SQL)
    def get_wheelbase(self,cursor,car_number):
        SQL="select * from car_parameter where car_number='%s'"%(car_number)
        cursor.execute(SQL)
        records=cursor.fetchall()
        if len(records)==1:
            wheelbase=float(records[0][1])
            delta=float(records[0][2])
            return [wheelbase,delta]
        else:
            return None

    @staticmethod
    def table_type(table):
        if table.find("entrance_measure_info")>=0:
            return "park"
        elif table.find("primary_key")>=0:
            return "pick"