GtDataStore/app/cleansing/models/source/mgj.py

#!coding:utf-8

import psycopg2

'''
select id from public.dataitem where name='Group_N_Reflux_Tank_Level';


select val,htime from his.itemdata_20230925 where itemid='25';
'''

class MGJ():
    conn = None
    item_id_map = {}

    def get_item_id(self, devid, name):
        key = "{}_{}".format(devid, name)
        if key in self.item_id_map:
            return self.item_id_map[key]
        query = "select id from public.dataitem where devid={} and name='{}' limit 1".format(devid, name)
        print(query)
        cur = self.conn.cursor()

        cur.execute(query)

        row = cur.fetchone()

        cur.close()

        if row is not None:
            self.item_id_map[key] = row[0]
            return row[0]

        return '-'
    
    def get(self, day, devid, name, et):
        itemid = self.get_item_id(devid, name)
        if itemid == 0:
            return None

        query = "select val,htime from his.itemdata_{} where itemid='{}' and createtime <= '{}' order by createtime desc limit 1".format(day, itemid, et)
        print(query)
        cur = self.conn.cursor()
        cur.execute(query)
        row = cur.fetchone()

        if row is not None:
            return row[0]
        
        return '-'

    def find(self, ymd, devid, name, start=0, limit=1000):
        itemid = self.get_item_id(devid, name)
        if itemid == 0:
            return None

        query = "select val,htime from his.itemdata_{} where itemid='{}' order by createtime desc offset {} limit {}".format(ymd, itemid, start, limit)
        print(query)
        cur = self.conn.cursor()
        cur.execute(query)
        rows = cur.fetchall()
        cur.close()

        return rows

    def __init__(self, mgj_db):
        self.conn = psycopg2.connect(database=mgj_db['dbname'], user=mgj_db['user'], password=mgj_db['password'], host=mgj_db['host'], port=mgj_db['port'])