Beispiel #1
0
def run_draft(match_id, team_id):
    c = Cursor("emporium_draft")
    items = c.execute(ITEMS_FOR_DRAFT_QUERY, (match_id, team_id)).fetchall()
    for i, item in enumerate(items):
        if not i % 100:
            print "  processing item #%s" % i
        draft_item(c, match_id, team_id, item)
Beispiel #2
0
def send_scheduled_mails():
    """ Send scheduled mails
    """
    with Cursor() as cursor:
        cursor.prepare("""
            SELECT * FROM scheduled_mails
            WHERE active is TRUE
            AND (schedule_day = (EXTRACT(DOW FROM NOW()) + 1) OR schedule_day = 0)
            """)

        cursor.exec_()
        now = QtCore.QDate.currentDate()
        while cursor.next():
            unit = cursor.value('schedule_unit')
            interval = cursor.value('schedule_interval')
            mail_data = {}

            # Exlude mail when it's too soon to send them
            if cursor.value('last_sent').isValid():
                if unit not in UNIT_TO_FUNC:
                    continue
                send_date = UNIT_TO_FUNC[unit](cursor.value('last_sent'),
                                               interval)
                if send_date > now:
                    continue

            # Ok send mail
            recipients = api.mail.get_recipients(
                cursor.value('filter'),
                cursor.value('filter_value'),
            )
            for recipient in recipients:
                if recipient['hidden']:
                    continue
                subject = api.mail.format_message(cursor.value('subject'),
                                                  recipient)
                message = api.mail.format_message(cursor.value('message'),
                                                  recipient)
                api.mail.send_mail(recipient['mail'], subject, message,
                                   cursor.value('sender'))

            # Update database.
            with Cursor() as update_cursor:
                update_cursor.prepare("""
                    UPDATE scheduled_mails SET last_sent=DATE(NOW())
                    WHERE name=:name
                    """)
                update_cursor.bindValue(":name", cursor.value('name'))
                update_cursor.exec_()
                if update_cursor.lastError().isValid():
                    print(update_cursor.lastError().text())
Beispiel #3
0
def get_possible_filter_values(col, filters):
    """ Get all the possible values for a filter given all other applied filters

    :param str col: db column name
    :param dict filters: A dict giving corresponding value to filters
    """
    if not filters:
        sqlfilters = []
    else:
        sqlfilters = [c + "=:" + c for c in filters if c != col]

    if col == 'product' and filters.get('category', '') != "Note":
        sqlfilters.append("category != 'Note'")

    with Cursor() as cursor:
        cursor.prepare("""
            SELECT DISTINCT {c} FROM transactions
            {w} {filters}
            ORDER BY {c}
            """.format(c=col,
                       w="WHERE" * bool(sqlfilters),
                       filters=' AND '.join(sqlfilters)))
        for key, value in filters.items():
            cursor.bindValue(":{}".format(key), value)

        cursor.exec_()

        if col not in FILTER_FIELDS_CACHE:
            FILTER_FIELDS_CACHE[col] = cursor.indexOf(col)

        while cursor.next():
            yield cursor.value(FILTER_FIELDS_CACHE[col])
def remove_notes(note_names, category_name):
    """ Remove notes from a category

    :param list note_names: A list of nicknames of notes to remove from this category
    :param str category_name: The category name

    :return bool: True if the notes have been removed, False otherwise
    """
    with Cursor() as cursor:
        cursor.prepare("""
            DELETE FROM note_categories_assoc WHERE
                note=(SELECT id FROM notes WHERE nickname=:note) AND
                category=(SELECT id FROM note_categories WHERE name=:category)
            """)

        cursor.bindValue(":note", note_names)
        cursor.bindValue(":category", [
            category_name,
        ] * len(note_names))

        ret = cursor.execBatch()
        if ret:
            for note in note_names:
                api.notes.rebuild_note_cache(note)
            api.redis.send_message("enibar-notes", note_names)
        return ret
Beispiel #5
0
    def perform_query(self):
        args = request.args

        parameter = 'pm10'  # parameter will be used as part of an fstring in the query, but don't worry
                            # as we check it against a whitelist
        if 'parameter' in args:
            parameter = args['parameter']
            if parameter not in MyWebService.allowed_parameters:
                raise ValueError("Invalid parameter passed")

        retVal = dict()
        with Cursor(Database()) as c:
            for row in c.execute(
                f"""select longitude, latitude, avg(value) as val, "average" as type, date
                from air_quality
                where date > strftime('%s', datetime('now') - 3*60*60)
                and parameter = '{parameter}'
                group by 1, 2

                union

                select longitude, latitude, value as val, "last_data" as type, date
                from air_quality a1
                where date=(select max(date) from air_quality a2 where a1.longitude=a2.longitude and a1.latitude=a2.latitude and a1.parameter = a2.parameter)
                and parameter = '{parameter}'
                order by date asc"""
            ):  # date is necessary to be in the query because of the "order by" clause
                cur = MyWebService.GeoPoint(lng=row[0], lat=row[1])
                if row[3] == 'average' or cur not in retVal:
                    retVal[cur] = row[2]

#        return jsonify(list({'lng': k.lng, 'lat': k.lat, 'val': v} for k, v in retVal.items()))
        return jsonify(list({'lng': k.lng, 'lat': k.lat, 'val': MyWebService._get_val(v, parameter)} for k, v in retVal.items()))
    def test_get(self):
        """ Testing get price function """
        self.assertEqual(list(api.prices.get()), [])
        price_desc_id = api.prices.add_descriptor("Unité", self.cat_drink, 100)
        pid = api.products.add("Lapin", category_id=self.cat_drink)

        price_id = None
        with Cursor() as cursor:
            cursor.prepare("SELECT id FROM prices WHERE product=?")
            cursor.addBindValue(pid)
            self.assertTrue(cursor.exec_())
            if cursor.next():
                price_id = cursor.record().value('id')
        self.assertIsNotNone(price_id)

        self.assertEqual(list(api.prices.get()), [{
            'alcoholic': False,
            'id': price_id,
            'label': "Unité",
            'value': 0,
            'product': pid,
            'category': self.cat_drink,
        }])
        api.prices.add(None, price_desc_id, 0)
        self.assertTrue(self.count_prices(), 2)
        self.assertEqual(list(api.prices.get(id=price_id)),
                         [{
                             'alcoholic': False,
                             'id': price_id,
                             'label': "Unité",
                             'value': 0,
                             'product': pid,
                             'category': self.cat_drink,
                         }])
Beispiel #7
0
def get_notes_stats():
    """ Yield dicts representing stats.

        {'nickname', 'product', 'price_name', 'price', 'category', 'quantity'}
    """
    global STATS_FIELDS_CACHE
    with Cursor() as cursor:
        cursor.prepare("SELECT notes.nickname AS nickname,\
                        transactions.product AS product,\
                        transactions.price_name AS price_name,\
                        transactions.price/transactions.quantity AS price,\
                        transactions.category AS category,\
                        SUM(transactions.quantity) AS quantity \
                        FROM transactions JOIN notes ON \
                        ((notes.lastname = transactions.lastname AND \
                        notes.firstname = transactions.firstname) OR \
                        notes.nickname = transactions.note) AND \
                        notes.stats_inscription = TRUE \
                        GROUP BY notes.nickname, transactions.product,\
                        transactions.price_name, transactions.price, \
                        transactions.quantity, transactions.category")
        cursor.exec_()
        while cursor.next():
            if not STATS_FIELDS_CACHE:
                STATS_FIELDS_CACHE = {field: cursor.indexOf(field) for field in STATS_FIELDS}

            yield {field: cursor.value(STATS_FIELDS_CACHE[field]) for field in STATS_FIELDS}
Beispiel #8
0
def delete_user(user_id):
    try:
        with Cursor() as cur:
            cur.execute('DELETE FROM users WHERE id = %s', (user_id, ))
            return server.ok(message='successfully deleted user')
    except:
        return server.error('unable to delete user')
Beispiel #9
0
def filtered_getter(table, filter_, reverse=False, max_=None, order_by="id"):
    """ This creates a request in the table table with the filter filter_ and
        returns the cursor of this request for future use.
    """
    with Cursor() as cursor:
        filters = []
        for key in copy.copy(filter_):
            if key.endswith('__gt'):
                filter_[key[:-4]] = filter_[key]
                del filter_[key]
                filters.append("{key}>:{key}".format(key=key[:-4]))
            else:
                filters.append("{key}=:{key}".format(key=key))

        cursor.prepare("SELECT * FROM {} {} {} {} {} {}".format(
            table,
            "WHERE" * bool(len(filters)),
            " AND ".join(filters),
            "ORDER BY {} DESC".format(order_by) * reverse,
            "ORDER BY {} ASC".format(order_by) * (not reverse),
            "LIMIT {}".format(max_) * bool(max_),
        ))
        for key, arg in filter_.items():
            cursor.bindValue(":{}".format(key), arg)
        if cursor.exec_():
            return cursor
Beispiel #10
0
def save_model(name, subject, message, filter_, filter_value):
    """ Save mail model

    :param str name: Model name
    :param str subject: Model subject
    :param str message: Model message
    :param int filter_: Filter used to match notes when sending
    :param str filter_value: Filter additioal value
    :return bool: Operation succes or not
    """
    try:
        subject.format(**COMPLETION_FIELD)
        message.format(**COMPLETION_FIELD)
    except KeyError:
        return False

    with Cursor() as cursor:
        cursor.prepare("""
            INSERT INTO mail_models(name, subject, message, filter, filter_value)
            VALUES (:name, :subject, :message, :filter, :filter_value)
            ON CONFLICT (name) DO UPDATE SET subject=:subject, message=:message,
            filter=:filter, filter_value=:filter_value
            """)
        cursor.bindValue(':name', name)
        cursor.bindValue(':subject', subject)
        cursor.bindValue(':message', message)
        cursor.bindValue(':filter', filter_)
        cursor.bindValue(':filter_value', filter_value)
        cursor.exec_()
        return not cursor.lastError().isValid()
Beispiel #11
0
def add_notes(note_names, category_name):
    """ Add notes to the note category

    :param list note_names: A list of nicknames of notes to add to this category
    :param str category_name: The category name

    :return bool: True if the notes have been added, False otherwise
    """
    with Cursor() as cursor:
        cursor.prepare("""
            INSERT INTO note_categories_assoc (note, category)
            VALUES((SELECT id FROM notes WHERE nickname=:note),
                   (SELECT id FROM note_categories WHERE name=:category))
            """)

        cursor.bindValue(':note', note_names)
        cursor.bindValue(':category', [
            category_name,
        ] * len(note_names))

        ret = cursor.execBatch()
        if ret:
            for note in note_names:
                api.notes.rebuild_note_cache(note)
            api.redis.send_message("enibar-notes", note_names)
        return ret
Beispiel #12
0
def get_descriptor(**kwargs):
    """ Get price descriptor

    :param \*\*kwargs: filters to apply
    """
    global PRICE_DESCRIPTOR_FIELDS_CACHE
    with Cursor() as cursor:
        request_filters = []
        for key in kwargs:
            request_filters.append("{key}=:{key}".format(key=key))
        request = "SELECT * FROM price_description WHERE {}".format(
            " AND ".join(request_filters))
        cursor.prepare(request)
        for key, arg in kwargs.items():
            cursor.bindValue(":{}".format(key), arg)
        cursor.exec_()
        while cursor.next():
            if PRICE_DESCRIPTOR_FIELDS_CACHE == {}:
                PRICE_DESCRIPTOR_FIELDS_CACHE = {
                    f: cursor.indexOf(f)
                    for f in PRICE_DESCRIPTOR_FIELDS
                }
            yield {
                field: cursor.value(PRICE_DESCRIPTOR_FIELDS_CACHE[field])
                for field in PRICE_DESCRIPTOR_FIELDS
            }
Beispiel #13
0
 def add_hidden_notes_category(self, name):
     category = api.note_categories.add(name)
     with Cursor() as cursor:
         cursor.prepare(
             "UPDATE note_categories SET hidden=TRUE WHERE name=:name")
         cursor.bindValue(':name', name)
         cursor.exec_()
     return category
Beispiel #14
0
def remove(price_id):
    """ Remove price

    :param int price_id: Price id
    """
    with Cursor() as cursor:
        cursor.prepare("DELETE FROM prices WHERE id=:id")
        cursor.bindValue(':id', price_id)
        return cursor.exec_()
Beispiel #15
0
def create_user(steamid, group=UserGroup.NORMAL):
    """
    Attempts to create a user based on their steamid, and optionally a group.
    This expectes to fail if the steamid already exists, and will raise `psycopg2.IntegrityError`
    when it does.
    """
    with Cursor() as c:
        now = datetime.utcnow()
        return c.execute(CREATE_USER_QUERY, (steamid, True, now, now, group, Cursor.json(DEFAULT_SETTINGS))).fetchone().id
Beispiel #16
0
def remove_descriptor(descriptor_id):
    """ Remove price from category

    :param int descriptor_id: Price descriptor id
    :return bool: True if operation succeed
    """
    with Cursor() as cursor:
        cursor.prepare("DELETE FROM price_description WHERE id=:id")
        cursor.bindValue(":id", descriptor_id)
        return cursor.exec_()
Beispiel #17
0
def delete_model(name):
    """ Delete model with given name

    :param str name: Model name
    """
    with Cursor() as cursor:
        cursor.prepare("DELETE FROM mail_models WHERE name=:name")
        cursor.bindValue(':name', name)
        cursor.exec_()
        return not cursor.lastError().isValid()
Beispiel #18
0
def delete_scheduled_mail(name):
    """ Delete scheduled mail

    :param str name: Name of the mail you want to delete
    :return bool: Operation success
    """
    with Cursor() as cursor:
        cursor.prepare("DELETE FROM scheduled_mails WHERE name=:name")
        cursor.bindValue(':name', name)
        cursor.exec_()
        return not cursor.lastError().isValid()
Beispiel #19
0
def save_scheduled_mails(name, active, sched_int, sched_unit, sched_day,
                         filter_, filter_val, subject, sender, message,
                         last_sent):
    """ Save scheduled mail

    :param str name: Scheduled mail name
    :param bool active: Enable mail sending if True
    :param int sched_int: Schedule interval
    :param str sched_unit: One of the following: day, week, month
    :param int sched_day: Day the mail should be sent on
    :param int filter_: Filter used to match notes to send to
    ;parma str filter_val: Filter value
    :param str subject: Mail subject
    :param str sender: Mail sender
    :param str message: Mail content
    :param QDateTime last_sent: Last date message was sent
    :return bool: Operation success
    """
    try:
        subject.format(**COMPLETION_FIELD)
        message.format(**COMPLETION_FIELD)
    except KeyError:
        return False

    with Cursor() as cursor:
        cursor.prepare("""
            INSERT INTO scheduled_mails(name, active, schedule_interval,
                schedule_day, filter, filter_value, subject, sender, message,
                schedule_unit, last_sent)
            VALUES(:name, :active, :sched_interval, :sched_day, :filter,
                :filter_val, :subject, :sender, :message, :schedule_unit,
                :last_sent)
            ON CONFLICT (name) DO UPDATE SET name=:name, active=:active,
                schedule_interval=:sched_interval, schedule_day=:sched_day,
                filter=:filter, filter_value=:filter_val, subject=:subject,
                message=:message, sender=:sender, schedule_unit=:schedule_unit,
                last_sent=:last_sent
            """)

        cursor.bindValue(':name', name)
        cursor.bindValue(':active', bool(active))
        cursor.bindValue(':sched_interval', sched_int)
        cursor.bindValue(':sched_day', sched_day)
        cursor.bindValue(':filter', filter_)
        cursor.bindValue(':filter_val', filter_val)
        cursor.bindValue(':subject', subject)
        cursor.bindValue(':sender', sender)
        cursor.bindValue(':message', message)
        cursor.bindValue(':schedule_unit', sched_unit)
        cursor.bindValue(':last_sent', last_sent)

        cursor.exec_()
        return not cursor.lastError().isValid()
Beispiel #20
0
def set_value(id_, value):
    """ Set price value
    If you have to change value of multiple prices use set_multiple_values
    instead.

    :param int id_: price id
    :param float value: new price value
    """
    with Cursor() as cursor:
        cursor.prepare("UPDATE prices SET value=:value WHERE id=:id")
        cursor.bindValue(":id", id_)
        cursor.bindValue(":value", value)
        return cursor.exec_()
Beispiel #21
0
def rollback_transaction(id_, full=False):
    """ Rollback transaction
    And refill note with money

    :param int id_: Transaction id:
    :param bool full: Rollback the full transaction ?
    """
    trans = get_unique(id=id_)
    if not trans:
        return False

    try:

        def filter_(x):
            return x['firstname'] == trans['firstname'] and \
                x['lastname'] == trans['lastname']

        note = api.notes.get(filter_)[0]
    except IndexError:
        return False

    quantity = int(trans['quantity'])

    with Cursor() as cursor:
        if quantity > 1 and not full:
            cursor.prepare("UPDATE transactions SET quantity=quantity - 1,\
                    price=:price WHERE id=:id AND deletable=TRUE")
            price = trans['price'] / quantity
            cursor.bindValue(':price', trans['price'] - price)
            cursor.bindValue(':id', trans['id'])
            task = api.sde.send_history_lines([{
                "id": trans["id"],
                "price": trans["price"] - price,
                "quantity": quantity - 1
            }])
        else:
            task = api.sde.send_history_deletion([trans['id']])
            cursor.prepare("DELETE FROM transactions WHERE id=:id "
                           "AND deletable=TRUE")
            cursor.bindValue(':id', trans['id'])
            price = trans['price']

        cursor.exec_()
        if not cursor.lastError().isValid() and cursor.numRowsAffected() > 0:
            asyncio.ensure_future(task)
            api.redis.send_message("enibar-notes", [note['nickname']])
            return True
        task.close()
    return False
Beispiel #22
0
 def test_protected_categories(self):
     note_categories.add("test")
     with Cursor() as cursor:
         cursor.prepare(
             "UPDATE note_categories SET protected=TRUE WHERE name='test'")
         cursor.exec_()
     note_categories.delete(["test"])
     note_categories.set_hidden(["test"], True)
     note_categories.rename("test", "test2")
     self.assertEqual(list(note_categories.get()), [{
         'name': 'test',
         'hidden': False,
         'id': 1,
         'protected': 1
     }])
Beispiel #23
0
def change_descriptor(id_, name, quantity):
    """ Change price descriptor name and quantity

    :param int id_: Price descriptor id
    :param int quantity: The quantity (in mL) of the product
    :return bool: True if operation succeed
    """
    with Cursor() as cursor:
        cursor.prepare(
            "UPDATE price_description SET label=:label, quantity=:quantity WHERE id=:id"
        )
        cursor.bindValue(":id", id_)
        cursor.bindValue(":label", name)
        cursor.bindValue(":quantity", quantity)
        return cursor.exec_()
Beispiel #24
0
def rename(old_name, new_name):
    """ Rename a note category

    :param str old_name: Old category name
    :param str new_name: New category name

    """
    with Cursor() as cursor:
        cursor.prepare(
            "UPDATE note_categories SET name=:new_name WHERE name=:old_name AND protected=FALSE"
        )

        cursor.bindValue(":new_name", new_name)
        cursor.bindValue(":old_name", old_name)

        cursor.exec_()
Beispiel #25
0
def rename_scheduled_mail(name, newname):
    """ Rename scheduled mail

    :param str name: Mail name
    :param str newname: New mail name
    :return bool: Operation success
    """
    with Cursor() as cursor:
        cursor.prepare("""
            UPDATE scheduled_mails
            SET name=:newname
            WHERE name=:name
            """)
        cursor.bindValue(':name', name)
        cursor.bindValue(':newname', newname)
        cursor.exec_()
        return not cursor.lastError().isValid()
Beispiel #26
0
def admin_edit_game():
    game = request.values.get("game")

    g.cursor.execute("SELECT id FROM games WHERE id=%s", (game, ))
    if not g.cursor.fetchone():
        raise APIError("Invalid Game")

    query = {k: v for (k, v) in request.values.iteritems() if k in GAME_EDITABLE_FIELDS}
    if not len(query):
        raise APIError("Nothing to change!")

    sql = "UPDATE games SET {} WHERE id=%(id)s".format(Cursor.map_values(query))

    query['id'] = game
    g.cursor.execute(sql, query)

    return APIResponse()
Beispiel #27
0
def admin_user_edit():
    user = request.values.get("user")
    print user

    g.cursor.execute("SELECT id FROM users WHERE id=%s", (user, ))
    if not g.cursor.fetchone():
        raise APIError("Invalid User")

    query = {k: v for (k, v) in request.values.iteritems() if k in USER_EDITABLE_FIELDS}
    if not len(query):
        raise APIError("Nothing to change!")

    sql = "UPDATE users SET {} WHERE id=%(id)s".format(Cursor.map_values(query))

    query['id'] = user
    g.cursor.execute(sql, query)

    return APIResponse()
Beispiel #28
0
def change_ecocups(nick, diff, do_not=False):
    """ Change the number of ecocups taken on a note.

        :param str nick: The nickname og the note
        :param int diff: The number of ecocups to add.
    """
    with Cursor() as cursor:
        cursor.prepare("UPDATE notes SET ecocups=ecocups+:diff WHERE\
                        nickname=:nick")
        cursor.bindValue(":diff", diff)
        cursor.bindValue(":nick", nick)
        value = cursor.exec_()
        note = NOTES_CACHE[nick]
        note['ecocups'] = note['ecocups'] + diff
    api.redis.send_message("enibar-notes", [
        nick,
    ])
    return value
Beispiel #29
0
def create_match(user, game, teams, meta, lock_date, match_date, public_date, view_perm=UserGroup.NORMAL):
    validate_match_team_data(teams)

    with Cursor() as c:
        c.execute(CREATE_MATCH_SQL, {
            "game": game,
            "teams": teams,
            "meta": meta,
            "results": Cursor.json({}),
            "lock_date": lock_date,
            "match_date": match_date,
            "public_date": public_date,
            "view_perm": view_perm,
            "active": False,
            "created_at": datetime.utcnow(),
            "created_by": user
        })

        return c.fetchone().id
Beispiel #30
0
def process_item(name, image, data):
    with Cursor() as c:
        pdata = c.execute("SELECT id, price FROM items WHERE name=%s", (name, )).fetchone()

        if pdata:
            return (pdata.id, float(pdata.price))

        try:
            i_vol, i_low, i_med = steam.market(730).get_item_price(name)
        except SteamAPIError:
            log.exception("Failed to get price for item '%s'" % name)

        new = c.execute("INSERT INTO items (name, price, meta) VALUES (%s, %s, %s) RETURNING id", (
            name, i_low, Cursor.json({
                "image": image,
                "descriptions": data
            })
        )).fetchone()
        return (new.id, i_low)
Beispiel #31
0
def create_user(first_name, last_name, email, password, created_by):
    password_hash = bcrypt.hashpw(password.encode('utf8'),
                                  bcrypt.gensalt()).decode('utf8')

    try:
        with Cursor() as cur:
            cur.execute(
                'INSERT INTO users '
                '(first_name, last_name, email, password, created_by) '
                'VALUES '
                '(%s, %s, %s, %s, %s) '
                'RETURNING *',
                (first_name, last_name, email, password_hash, created_by))

            emails.new_user(email, password)

            return server.ok(data=cur.fetchone())
    except:
        return server.error('unable to create user')
Beispiel #32
0
def get_users(page_size, page_index, name):
    query = "SELECT * FROM users WHERE type = 'regular' "

    if name:
        query += "AND levenshtein(first_name, '{0}') <= 2 or levenshtein(last_name, '{0}') <= 2 " \
          .format(name)

    query += "ORDER BY created_on DESC LIMIT {} OFFSET {}".format(
        page_size, page_size * page_index)

    try:
        with Cursor() as cur:
            cur.execute(query)

            if cur.rowcount:
                return server.ok(data=cur.fetchall())
            else:
                return server.not_found('no users found')
    except:
        return server.error('unable to get users')
Beispiel #33
0
def get_notes(category_name):
    """ Yields the nicknames of all the notes in the category

    :param str category_name: The category name
    """
    with Cursor() as cursor:
        cursor.prepare("""
            SELECT filtered_notes.nickname as nick FROM
                (SELECT id, nickname FROM notes WHERE notes.id IN
                    (SELECT note FROM note_categories_assoc WHERE category=
                        (SELECT id FROM note_categories WHERE name=:name)
                    )
                ) filtered_notes
            """)

        cursor.bindValue(':name', category_name)

        if cursor.exec_():
            while cursor.next():
                yield cursor.value("nick")
Beispiel #34
0
def set_hidden(category_names, hidden):
    """ Change the hidden value of one or more categories

    :param str category_name: The name of the category
    :param bool hidden: The new state.
    """
    with Cursor() as cursor:
        cursor.prepare(
            "UPDATE note_categories SET hidden=:hidden WHERE name=:name AND protected=FALSE"
        )

        cursor.bindValue(":name", category_names)
        cursor.bindValue(":hidden", [
            hidden,
        ] * len(category_names))

        cursor.execBatch()
    notes = api.notes.get(lambda x: any(category_name in x["categories"]
                                        for category_name in category_names))
    api.redis.send_message("enibar-notes",
                           [note['nickname'] for note in notes])
Beispiel #35
0
def generateReport():
    """
        Generates a report of answers to the questions
        in the questionsAndQueriesList
    """
    for listItem in questionsAndQueriesList:
        with Cursor() as cursor:
            cursor.execute(listItem['query'].replace('\n', ' '))
            results = cursor.fetchall()
            field_names = [
                str(i[0]).replace('_', ' ').upper() for i in cursor.description
            ]

        print('### ' + listItem['question'], end='\n\n')
        print('| ' + ' | '.join(field_names) + ' |')
        print('| ' + ('--- | ' * len(field_names)))

        for result in results:
            print('| ' + ' | '.join(str(x) for x in result) + ' |')

        print('\r\n', end='')
Beispiel #36
0
def get(**kwargs):
    """ Get prices filtered by given values

    :param \*\*kwargs: filters to apply
    """
    global PRICE_FIELDS_CACHE

    with Cursor() as cursor:
        filters = []
        for key in kwargs:
            filters.append("prices.{key}=:{key}".format(key=key))

        cursor.prepare("SELECT prices.id as id,\
            prices.product as product,\
            prices.value as value,\
            price_description.label as label,\
            price_description.category as category, \
            categories.alcoholic AS alcoholic \
            FROM prices JOIN price_description \
            ON prices.price_description=price_description.id \
            JOIN categories \
            ON categories.id=price_description.category \
            {} {}".format("WHERE" * bool(filters), " AND ".join(filters)))
        for key, arg in kwargs.items():
            cursor.bindValue(":{}".format(key), arg)

        if cursor.exec_():
            while cursor.next():
                if PRICE_FIELDS_CACHE == {}:
                    PRICE_FIELDS_CACHE = {
                        f: cursor.indexOf(f)
                        for f in PRICE_FIELDS
                    }
                line = {
                    field: cursor.value(PRICE_FIELDS_CACHE[field])
                    for field in PRICE_FIELDS
                }
                line[
                    'value'] += settings.ALCOHOL_MAJORATION * line['alcoholic']
                yield line
Beispiel #37
0
def match_to_json(m, user=None):
    """
    Right now this function is a performance clusterfuck. Almost all of the data in
    here can be gathered with a single query and windowed multi-join, but lets wait
    until shit breaks, eh?
    """
    c = Cursor()

    if not isinstance(m, tuple):
        c.execute(MATCH_SELECT_SQL, (m, ))
        m = c.fetchone()

    if not m:
        raise InvalidRequestError("Failed to match_to_json with arg %s" % m)

    match = {}

    match['id'] = m.id
    match['game'] = m.game
    match['when'] = int(m.match_date.strftime("%s"))
    match['teams'] = []
    match['extra'] = {}
    match['stats'] = {}

    # This will most definitily require some f*****g caching at some point
    bet_stats = c.execute("""SELECT
            sum(array_length(items, 1)) as skins_count,
            count(*) as count,
            sum(value) as value,
            team
        FROM bets WHERE match=%s AND state >= 'confirmed' GROUP BY team""", (m.id, )).fetchall(as_list=True)

    match['stats']['players'] = sum(map(lambda i: i.count, bet_stats))
    match['stats']['skins'] = sum(map(lambda i: i.skins_count, bet_stats))
    match['stats']['value'] = sum(map(lambda i: i.value, bet_stats))
    bet_stats = { i.team: i for i in bet_stats}

    # Grab team information, including bets (this should be a join)
    c.execute("SELECT * FROM teams WHERE id IN %s", (tuple(m.teams), ))
    teams = c.fetchall()

    total_bets = sum(map(lambda i: i.count, bet_stats.values())) * 1.0
    total_value = sum(map(lambda i: i.value, bet_stats.values()))

    for index, team in enumerate(teams):
        team_data = {
            "id": team.id,
            "name": team.name,
            "tag": team.tag,
            "logo": team.logo,
            "stats": {
                "players": 0,
                "skins": 0,
                "value": 0,
            },
            "odds": 0
        }

        if index in bet_stats:
            team_data['stats']['players'] = bet_stats[index].count
            team_data['stats']['skins'] = bet_stats[index].skins_count
            team_data['stats']['value'] = bet_stats[index].value
            team_data['odds'] = float("{0:.2f}".format(bet_stats[index].count / total_bets))

        match['teams'].append(team_data)

    if user:
        match['me'] = {}

        mybet = c.execute("""
            SELECT id, team, items::steam_item[], state, value
            FROM bets WHERE match=%s AND better=%s
        """, (m.id, user)).fetchone()

        if mybet:
            match['me']['id'] = mybet.id
            match['me']['team'] = mybet.team
            match['me']['state'] = mybet.state
            match['me']['value'] = mybet.value
            match['me']['state'] = mybet.state

            if total_value > mybet.value:
                my_return = ((total_value * 1.0) / match['teams'][mybet.team]['stats']['value']) * mybet.value
            else:
                my_return = mybet.value

            match['me']['return'] = float("{0:.2f}".format(my_return))

            # Load items in sub query :(
            match['me']['items'] = []

            items = c.execute("SELECT id, name, price, meta FROM items WHERE id IN %s", (tuple(
                map(lambda i: i.item_id, mybet.items)), ))

            for item in items.fetchall():
                match['me']['items'].append({
                    "id": item.id,
                    "name": item.name,
                    "price": float(item.price),
                    "image": item.meta['image']
                })


    for key in ['league', 'type', 'event', 'streams', 'maps', 'note']:
        if key in m.meta:
            match['extra'][key] = m.meta[key]

    match['results'] = m.results
    return match