Example #1
0
class dbHandler():
    def __init__(self):
        self.mysql = MySQL()
        self.influx = InfluxDB()
        self.query = Query()
        cfg = getConfig()
        self.mysql.init_cfg(cfg)
        self.influx.init_cfg(cfg)
        self._con = self.mysql.get_connection()
        self.query.init_db(self._con, self.influx.get_client())

    def getQuery(self):
        return self.query

    def close(self):
        return self.mysql.close()

    def getallMAC(self, table):
        infQuery = self.query.getInfQuery()
        result = infQuery('SELECT distinct("MAC") as MAC FROM "{}";'.format(
            escape(table)))
        return result.raw

    def getallSensor(self, table, mac):
        infQuery = self.query.getInfQuery()
        param = {"mac": mac}
        result = infQuery('SELECT s,topic FROM "{}" WHERE MAC=$mac;'.format(
            escape(table)),
                          bind_params=param)
        return result.raw

    def getallTopic(self, table, mac):
        infQuery = self.query.getInfQuery()
        result = infQuery('SELECT topic,MAC,status FROM "{}"'.format(
            escape(table)))
        for sensor in result.raw["series"][0]["values"]:
            smac = sensor[2]
            topic = sensor[1]
            if (smac == mac):
                return ['topic', topic, 'MAC', smac]

    def getLastCT(self, sid, _type="ct"):
        infQuery = self.query.getInfQuery()
        param = {"s": sid}
        result = infQuery('SELECT a FROM "{}" WHERE s=$s LIMIT 1'.format(
            escape(_type)),
                          bind_params=param)
        return result.raw

    def getLastPIR(self, mac, _type="pir"):
        infQuery = self.query.getInfQuery()
        param = {"mac": mac}
        result = infQuery(
            'SELECT status FROM "{}" WHERE MAC=$mac LIMIT 1'.format(
                escape(_type)),
            bind_params=param)
        return result.raw
Example #2
0
class dbHandler():
    def __init__(self):
        self.mysql = MySQL()
        self.mongo = MongoDB()
        self.query = Query()
        cfg = getConfig()
        self.mysql.init_cfg(cfg)
        self.mongo.init_cfg(cfg)
        self._con = self.mysql.get_connection()
        self._client = self.mongo.get_client()
        self.query.init_db(self._con, self._client)
        self._db = self._client[cfg.MONGODB_COLLECTION]

    def getQuery(self):
        return self.query

    def getallMAC(self, table):  #/
        allmac = self._db[table].find().distinct("message.MAC")
        return allmac

    def getallSensor(self, table, mac):  #/
        result = self._db[table].find(
            {"$and": [{
                "message.MAC": mac
            }, {
                "message.s": {
                    "$exists": True
                }
            }]})
        alltopic = result.distinct("topic")
        allnum = result.distinct("message.s")
        res = []
        for s in allnum:
            for topic in alltopic:
                res.append({"s": s, "topic": topic})
        return res

    def getallTopic(self, table, mac):  #/
        result = self._db[table].find_one(
            {"$or": [{
                "message.MAC": mac
            }, {
                "message.s": mac
            }]})
        return ['topic', result["topic"], 'MAC', result["message"]["MAC"]]

    def getLastVAL(self, sid, mac, _type="ct"):
        createdAt = 0
        res = None
        query = {"message.MAC": mac}
        if (_type.split("_") == "dm"):
            query["message.s"] = sid
        rawCT = self._db[_type].find(query).sort("created_at", -1).limit(1)
        for CT in rawCT:
            if (CT["created_at"] > createdAt):
                res = CT
                createdAt = CT["created_at"]
        if (_type.split("_")[0] == "dm"):
            return (res["message"]["AL1"] + res["message"]["AL2"] +
                    res["message"]["AL3"]) / 3
        return res["message"]["a"]

    def getLastPIR(self, mac, _type="pir"):
        createdAt = 0
        res = {}
        for t in self.getTable({"sensor_type": _type}):
            rawPIR = self._db[t].find({
                "message.MAC": mac
            }).sort("created_at", -1).limit(1)
            for PIR in rawPIR:
                if (PIR["created_at"] > createdAt):
                    res = PIR
                    createdAt = PIR["created_at"]
        return res

    def getTable(self, devFind={}):  #/
        res = []
        for _type in self._db["iot_type"].find(devFind):
            name = _type["sensor_type"]
            if (_type["device_type"] != ''):
                name = name + "_" + _type["device_type"]
            res.append(name)
        return res