コード例 #1
0
    def getSubscriptions(self, **kwargs):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = "  SELECT id, user_id, plan_id, subscription_id, " \
                      "         group_id, sub_timestamp, exp_timestamp, " \
                      "         status, type, count, used, tier, recurring " \
                      "    FROM subscriptions "

                if len(kwargs) != 0:
                    where = "WHERE"
                    for key, value in kwargs.items():
                        if type(value) == int:
                            condition = (" {field} = {_data} ").format(
                                field=key, _data=value)
                        else:
                            condition = (" {field} = \"{_data}\" ").format(
                                field=key, _data=value)
                        if where == "WHERE":
                            where += (" {cond} ").format(cond=condition)
                        else:
                            where += (" AND {cond} ").format(cond=condition)
                    sql += " "
                    sql += where

                cursor.execute(sql)
                subscriptions_data = cursor.fetchall()
                return subscriptions_data

            except Exception as e:
                logger.debug(e)
コード例 #2
0
    def deleteData(self, **kwargs):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn) as cursor:
            try:
                sql = "DELETE FROM %s" % (self.tableName)
                if len(kwargs) != 0:
                    where = "WHERE"
                    for key, value in kwargs.items():
                        if type(value) == int:
                            condition = (" {field} = {_data} ").format(
                                field=key, _data=value)
                        else:
                            condition = (" {field} = \"{_data}\" ").format(
                                field=key, _data=value)
                        if where == "WHERE":
                            where += (" {cond} ").format(cond=condition)
                        else:
                            where += (" AND {cond} ").format(cond=condition)
                    sql += " "
                    sql += where

                cursor.execute(sql)
                devconn.commit()

            except Exception as e:
                logger.debug(e)
コード例 #3
0
    def getActivationToken(self, **kwargs):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = "  SELECT * " \
                      "    FROM activation_tokens "

                if len(kwargs) != 0:
                    where = "WHERE"
                    for key, value in kwargs.items():
                        if type(value) == int:
                            condition = (" {field} = {_data} ").format(
                                field=key, _data=value)
                        else:
                            condition = (" {field} = \"{_data}\" ").format(
                                field=key, _data=value)
                        if where == "WHERE":
                            where += (" {cond} ").format(cond=condition)
                        else:
                            where += (" AND {cond} ").format(cond=condition)
                    sql += " "
                    sql += where

                cursor.execute(sql)
                actvtn_tkn = cursor.fetchall()
                return actvtn_tkn

            except Exception as e:
                logger.debug(e)
コード例 #4
0
    def getLicenses(self, **kwargs):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = "  SELECT id, uuid, group_id, token, " \
                      "         system_serial, manufacturer, model, computer_name " \
                      "    FROM licenses "

                if len(kwargs) != 0:
                    where = "WHERE"
                    for key, value in kwargs.items():
                        if type(value) == int:
                            condition = (" {field} = {_data} ").format(
                                field=key, _data=value)
                        else:
                            condition = (" {field} = \"{_data}\" ").format(
                                field=key, _data=value)
                        if where == "WHERE":
                            where += (" {cond} ").format(cond=condition)
                        else:
                            where += (" AND {cond} ").format(cond=condition)
                    sql += " "
                    sql += where

                sql += "ORDER BY id "

                cursor.execute(sql)
                licenses_data = cursor.fetchall()
                return licenses_data

            except Exception as e:
                logger.debug(e)
コード例 #5
0
    def getUserInfo(self, **kwargs):

        methodName = sys._getframe().f_code.co_name
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = "SELECT id, group_id, username, uuid, stripe_cust_id " \
                      "  FROM users "

                if len(kwargs) != 0:
                    where = "WHERE"
                    for key, value in kwargs.items():
                        if type(value) == int:
                            condition = (" {field} = {_data} ").format(
                                field=key, _data=value)
                        else:
                            condition = (" {field} = \"{_data}\" ").format(
                                field=key, _data=value)
                        if where == "WHERE":
                            where += (" {cond} ").format(cond=condition)
                        else:
                            where += (" AND {cond} ").format(cond=condition)
                    sql += " "
                    sql += where

                cursor.execute(sql)
                userInfo = cursor.fetchall()

                return (userInfo)

            except Exception as e:
                logger.debug(e)
                self.response = make_response(constants.USER_INFO_FAIL,
                                              methodName,
                                              constants.BAD_REQUEST_400)
コード例 #6
0
    def getGroupInfo(self, **kwargs):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = "  SELECT id, name, parent_id, uuid, grplvl " \
                      "    FROM groups "

                if len(kwargs) != 0:
                    where = "WHERE"
                    for key, value in kwargs.items():
                        if type(value) == int:
                            condition = (" {field} = {_data} ").format(
                                field=key, _data=value)
                        else:
                            condition = (" {field} = \"{_data}\" ").format(
                                field=key, _data=value)
                        if where == "WHERE":
                            where += (" {cond} ").format(cond=condition)
                        else:
                            where += (" AND {cond} ").format(cond=condition)
                    sql += " "
                    sql += where

                cursor.execute(sql)
                groups_data = cursor.fetchall()  #cursor.fetchone()
                return groups_data

            except Exception as e:
                logger.debug(e)
コード例 #7
0
    def deleteTableData(self):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn) as cursor:
            try:
                sql = "DELETE FROM %s " % (self.tableName)
                cursor.execute(sql)
                devconn.commit()

            except Exception as e:
                logger.debug(e)
コード例 #8
0
    def deleteActivationTokens(cls):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn) as cursor:
            try:
                sql = "DELETE FROM activation_tokens"
                cursor.execute(sql)
                devconn.commit()

            except Exception as e:
                logger.debug(e)
コード例 #9
0
    def getTableData(self, **kwargs):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                col_sql = "SELECT COLUMN_NAME FROM information_schema.columns " \
                          " WHERE table_schema = \"%s\" AND " \
                          "       table_name = \"%s\" " % (common.mdb, self.tableName)
                cursor.execute(col_sql)
                field_names = cursor.fetchall()
                # field_names is a list of dicts with keys 'COLUMN_NAME' and field names as values.
                # convert to field names list
                field_names_list = []
                for field_dict in field_names:
                    field_names_list.append(list(field_dict.values()))

                sql = "  SELECT * " \
                      "    FROM %s" % (self.tableName)

                if len(kwargs) != 0:
                    where = "WHERE"
                    stmt_line = ""
                    for key, value in kwargs.items():
                        for list_item in field_names_list:
                            if key in list_item:
                                if type(value) == int:
                                    condition = (" {field} = {_data} ").format(
                                        field=key, _data=value)
                                else:
                                    condition = (
                                        " {field} = \"{_data}\" ").format(
                                            field=key, _data=value)
                                if where == "WHERE":
                                    where += (" {cond} ").format(
                                        cond=condition)
                                else:
                                    where += (" AND {cond} ").format(
                                        cond=condition)
                        if key == "statement":  # statements after WHERE should follow correct SQL style
                            stmt_line += " "
                            stmt_line += value
                    sql += " "
                    if where != "WHERE":
                        sql += where
                    sql += stmt_line

                cursor.execute(sql)
                tbl_dat = cursor.fetchall()
                return tbl_dat

            except Exception as e:
                logger.debug(e)
コード例 #10
0
    def getAssetsInGroup(self):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = (
                    "    SELECT S.id, S.deviceid, S.name, S.group_id, S.uuid  "
                    "      FROM sentinels S  "
                    "INNER JOIN groups    G  "
                    "     WHERE G.id = {grp_id} "
                    "       AND G.id = S.group_id "
                    "  ORDER BY S.id").format(grp_id=self.dvc_grp_dict["id"])
                cursor.execute(sql)
                asset_dict_list = cursor.fetchall()
                return asset_dict_list

            except Exception as e:
                logger.debug(e)
コード例 #11
0
    def getUsersInGroup(self):
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn, dictionary=True) as cursor:
            try:
                sql = (
                    "    SELECT U.id, U.fname, U.group_id, U.lname, U.username, U.uuid  "
                    "      FROM users U  "
                    "INNER JOIN groups G  "
                    "     WHERE G.id = {grp_id} "
                    "       AND G.id = U.group_id "
                    "  ORDER BY U.id").format(grp_id=self.dvc_grp_dict["id"])
                cursor.execute(sql)
                user_dict_list = cursor.fetchall()
                return user_dict_list

            except Exception as e:
                logger.debug(e)
コード例 #12
0
    def getUgrInfo(self):

        methodName = sys._getframe().f_code.co_name
        #logger.debug("\n\n Inside %s" % methodName)  #CTO
        with get_db_connection(database=common.mdb) as devconn, get_db_cursor(
                devconn) as cursor:
            #logger.debug("AFT get_db_connection()")  #CTO
            try:
                sql = "SELECT UGR.id, UGR.group_id, UGR.role_tag, " \
                      "       UGR.uuid, G.name, G.parent_id, U.id, U.uuid " \
                      "  FROM groups G, user_group_roles UGR, users U" \
                      " WHERE UGR.user_id = U.id " \
                      "   AND G.id = UGR.group_id " \
                      "   AND U.username = \"%s\" " \
                   " ORDER BY UGR.id " % (self.username)
                cursor.execute(sql)
                rows = cursor.fetchall()
                ugrInfo = []
                for row in rows:
                    self.user_id = row[6]
                    self.user_uuid = row[7]
                    self.group_id = row[1]
                    self.groupname = row[4]
                    self.parent_id = row[5]
                    self.ugr_id = row[0]
                    self.role_tag = row[2]
                    self.ugr_uuid = row[3]

                    ugrTmp = {}
                    ugrTmp = {
                        "user_id": self.user_id,
                        "user_uuid": self.user_uuid,
                        "username": self.username,
                        "group_id": self.group_id,
                        "groupname": self.groupname,
                        "parent_id": self.parent_id,
                        "ugr_id": self.ugr_id,
                        "role_tag": self.role_tag,
                        "ugr_uuid": self.ugr_uuid
                    }
                    ugrInfo.append(ugrTmp)

                return (ugrInfo)

            except Exception as e:
                logger.debug(e)