WHJ工赛NUMBER1

**一 设备状态**
class device_status(object):
    #在绑定任务时，是用位置传参，故 red,yellow,green
    def execute(self,red,yellow,green):
        if  green ==1 or green ==True:
            return '正常运行中'
        elif red ==1 or red ==True:
            return '故障中'
        elif yellow ==1 or yellow ==True:
            return '待机中'
        else:
            return '未知'

**二 达成率**
class da(object):
	#注意jh，sj的顺序
    def execute(self,jh,sj):
        if sj ==0:
            return 0
        else:
            da=round(sj/jh)*100
            da=str(da)+'%'
            print(da)
            return da


**三 稼动率**
import pytz
import psycopg2
import datetime
from tzlocal import get_localzone

global psycopg2
global pytz
global datetime
global tzlocal
global get_localzone

#时间稼动率=（负荷时间-停机时间）/负荷时间

class jia(object):
    def execute(self):
        print(datetime.datetime.now())
        conn = psycopg2.connect(database="postgres", user="postgres", pass###word="1", host="192.168.46.5",
                                port="5432")
#pass 中间#要删除掉 host= 的ip 换为192.168.100.5
        no_time=[]
        cur = conn.cursor()
        table_name='ol_001_23' #根据实际表名来更换 绿灯存的表
        #cur.execute("truncate table time_count1")
        cur.execute("create table if not exists time_count1(count_time time)")
        cur.execute("SELECT value,time,id FROM %s order by time desc limit 1")%table_name  #根据实际表名来更换
        result_new = cur.fetchall()
        if result_new and result_new[0][0] ==False :
            #print(result_new[0][2])
            where_id5=result_new[0][2]-1  ##为什么要用ID了，用ID不严谨，但这个编辑器不支持输出type,所以这么用。应该使用时间
            sql5="SELECT value,time,id FROM %s where id=%s"%(table_name,where_id5)
            cur.execute(sql5)
            res5=cur.fetchall()
            utc_tz = pytz.timezone('Asia/Shanghai')
            now_time2=datetime.datetime.now(tz=utc_tz)-res5[0][1]
            #print(datetime.now(tz=utc_tz)-res5[0][1])
            #now_time2 = datetime.now().strftime("%H:%M:%S") - res5[0][1]
            #cur.execute("INSERT INTO time_count1(count_time) VALUES ('%s')" %now_time2)
            print("接近当下的停机时间 %s"%now_time2)
            no_time.append(now_time2)
            print(no_time)
        cur.execute("SELECT value,time,id FROM %s order by time desc")%table_name  #根据实际表名来更换
        result = cur.fetchall()
        ##最后一条，如果返回false,说明灯没亮
        for i in result:
            if i[0]== True:
                where_id=i[2]-1  ##为什么要用ID了，用ID不严谨，但这个编辑器不支持输出type,所以这么用。应该使用时间
                sql1="SELECT value,time,id FROM %s where id=%s"%(table_name,where_id)
                cur.execute(sql1)
                res1=cur.fetchall()
                if len(res1) ==0:
                    continue
                else:
                    pass
                #print(res1[0][1])
                time2=i[1]-res1[0][1]
                #print(time2)
                #cur.execute("INSERT INTO time_count1(count_time) VALUES ('%s')" %time2)
                no_time.append(time2)
            else:
                pass
        now_time=datetime.datetime.now()
        now_time1=datetime.datetime.now()
        #print(no_time)
        for n in no_time:
            now_time=now_time+n
        print(now_time-now_time1)
        guzhang_total=now_time-now_time1
        cur.execute("SELECT time FROM %s order by time asc limit 1")%table_name
        res6= result = cur.fetchall()
        utc_tz = pytz.timezone('Asia/Shanghai')
        ##机器运行总时长，用第一次写库的时间和now time对比
        start_time=datetime.datetime.now(tz=utc_tz)-res6[0][0]
        ##运行时间 
        run_time=start_time - guzhang_total
        print(start_time)
        print('机器总时长%s:'%start_time)
        print('机器停机时间%s:'%guzhang_total)
        print('机器工作时间%s'%run_time)
        jia=rount(run_time/start_time*100)
        #round
        print(jia)
        cur.close()
        conn.commit()
        conn.close()
        return jia

**四 减速机温度的sql语句**
 
 select * from ol_001_19 order by time desc limit 20 
 # 表名更换 减速机温度变量所在表


**五 故障分布的sql语句**

 # 表名更换 报警的故障 所在的同一张表  序号ID更换故障类型的
select t2.cou as old_value,round(t2.cou / (select sum(cou) from (SELECT count(*)as cou, var_id FROM "ol_001_49" where value='true' group by var_id) as t1)*100) as rate, t2.var_id,CASE t2.var_id

WHEN '48' THEN '主轴过载'

WHEN '49' THEN '伺服温度高'

WHEN '51' THEN '机器温度高'
WHEN '51' THEN '机器抖动'

ELSE '未知' END as chinese_name

 from (SELECT count(*)as cou, var_id FROM "ol_001_49" where value='true' group by var_id) as t2

**六  其他**

#1.MQTT的连接设置
#IP:192.168.100.5
#Port:1883
#用户名： admin
#密码：public
#服务质量：Qos 2 

#2.BI看板的设置数据源
#数据库连接名 postgresql连接
#数据库名称：postgres
#主机：192.168.100.5
#端口：5432
#用户名：postgres
#密码：postgres

#3.远程存储
#数据库类型 postgresql 
#端口：5432
#密码：postgres
#数据库主机ip：192.168.100.5
#账号：postgres
#数据库名称：dashboards
#存储天数：10

 #4.辅助变量表sql取不同值
 # SELECT value from ol_001_58 where var_id='62' order by time desc limit 1
 # select regexp_replace(value,'%','') from ol_001_58 where var_id='63' order by time desc limit 1  去掉%才能传给BI看板产能达成率