CT_project/指令入队节点/PushCommand.py

# 导入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

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)

                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.conn.commit()
            cursor.close()
            return tf.MessageToString(cmd,as_utf8=True)

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