<commit_before>#!/usr/bin/env python
# -*- coding: utf-8 -*-

import datetime

from sqlalchemy import create_engine
from flask.ext.babel import lazy_gettext
from flask.globals import current_app
from sqlalchemy.exc import OperationalError

from werkzeug.local import LocalProxy

from model.constants import WEEKDAYS

from sipa.utils import timetag_from_timestamp, timestamp_from_timetag
from sipa.utils.exceptions import DBQueryEmpty
from .ldap_utils import get_current_uid

import logging
logger = logging.getLogger(__name__)


def init_db(app):
    app.extensions['db_atlantis'] = create_engine(
        'mysql+pymysql://{0}:{1}@{2}:3306/netusers'.format(
            app.config['DB_ATLANTIS_USER'],
            app.config['DB_ATLANTIS_PASSWORD'],
            app.config['DB_ATLANTIS_HOST']),
        echo=False, connect_args={'connect_timeout': app.config['SQL_TIMEOUT']}
    )
    app.extensions['db_helios'] = create_engine(
        'mysql+pymysql://{0}:{1}@{2}:{3}/'.format(
            app.config['DB_HELIOS_USER'],
            app.config['DB_HELIOS_PASSWORD'],
            app.config['DB_HELIOS_HOST'],
            int(app.config['DB_HELIOS_PORT'])),
        echo=False, connect_args={'connect_timeout': app.config['SQL_TIMEOUT']}
    )


db_atlantis = LocalProxy(lambda: current_app.extensions['db_atlantis'])
db_helios = LocalProxy(lambda: current_app.extensions['db_helios'])

DORMITORIES = [
    'Wundstraße 5',
    'Wundstraße 7',
    'Wundstraße 9',
    'Wundstraße 11',
    'Wundstraße 1',
    'Wundstraße 3',
    'Zellescher Weg 41',
    'Zellescher Weg 41A',
    'Zellescher Weg 41B',
    'Zellescher Weg 41C',
    'Zellescher Weg 41D',
    'Borsbergstraße 34',
]

STATUS = {
    # todo vervollständigen oder mindestens fehlerresistent machen!
    # (Hat ein Nutzer einen unten nicht enthaltenen Status, gibts einen Fehler)
    1: lazy_gettext('Bezahlt, verbunden'),
    2: lazy_gettext('Nicht bezahlt, Netzanschluss gesperrt'),
    7: lazy_gettext('Verstoß gegen Netzordnung, Netzanschluss gesperrt'),
    9: lazy_gettext('Exaktiv'),
    12: lazy_gettext('Trafficlimit überschritten, Netzanschluss gesperrt')
}


def sql_query(query, args=(), database=db_atlantis):
    """Prepare and execute a raw sql query.
    'args' is a tuple needed for string replacement.
    """
    conn = database.connect()
    result = conn.execute(query, args)
    conn.close()
    return result


def status_string_from_id(status_id):
    return STATUS.get(status_id, lazy_gettext("Unbekannt"))


def user_id_from_uid(uid=None):
    """Fetch user.id (MySQL) from user.uid (LDAP)

    :param uid: The uid of the LDAP user object
    :return: The user id of the MySQL user
    """
    if uid is None:
        uid = get_current_uid()

    return sql_query("SELECT nutzer_id FROM nutzer WHERE unix_account = %s",
                     (uid,)).fetchone()['nutzer_id']


def ip_from_user_id(user_id):
    result = (sql_query(
        "SELECT c_ip FROM computer WHERE nutzer_id = %s",
        (user_id,)
    ).fetchone())
    if not result:
        raise DBQueryEmpty('Nutzer hat keine IP')
    return result['c_ip']


def user_id_from_ip(ip):
    """Returns the MySQL user.id corresponding to the ip or 0 if foreign to the db
    :param ip: A valid IP address
    :return: A user id
    """

    result = sql_query("SELECT nutzer_id FROM computer WHERE c_ip = %s",
                       (ip,)).fetchone()
    if result is None:
        return 0

    return result['nutzer_id']


def query_current_credit(uid=None, ip=None):
    """Returns the current credit in MiB
    :param uid: The id of the user
    :param ip: The ip of the user
    :return: The current amount of credit or False if foreign IP
    """
    if uid is None:
        if ip is None:
            raise AttributeError('Either ip or user_id must be specified!')
        user_id = user_id_from_ip(ip)
        if user_id is 0:
            return False  # IP doesn't correspond to any user
    else:
        user_id = user_id_from_uid(uid)
        ip = ip_from_user_id(user_id)

    try:
        result = sql_query(
            "SELECT amount - input - output as current "
            "FROM traffic.tuext AS t "
            "LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag "
            "WHERE ip = %(ip)s AND c.user_id = %(user_id)s "
            "AND t.timetag = %(today)s",
            {'today': timetag_from_timestamp(), 'ip': ip, 'user_id': user_id}
        ).fetchone()
    except OperationalError as e:
        logger.critical('Unable to connect to MySQL server',
                        extra={'data': {'exception_args': e.args}})
        raise
    else:
        return round(result['current'] / 1024, 2)


def query_trafficdata(ip, user_id):
    """Query traffic input/output for IP

    :param ip: a valid ip
    :param user_id: an id of a mysql user tuple
    :return: a dict containing the traffic data in the form of
    {'history': [('weekday', in, out, credit), …], 'credit': credit}
    """
    trafficdata = sql_query(
        "SELECT t.timetag - %(today)s AS day, input, output, amount "
        "FROM traffic.tuext AS t "
        "LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag "
        "WHERE ip = %(ip)s AND c.user_id = %(uid)s "
        "AND t.timetag BETWEEN %(weekago)s AND %(today)s "
        "ORDER BY 'day' DESC ",
        {'today': timetag_from_timestamp(),
         'weekago': timetag_from_timestamp() - 6,
         'ip': ip,
         'uid': user_id}
    ).fetchall()

    if not trafficdata:
        raise DBQueryEmpty('No trafficdata retrieved for user {}@{}'
                           .format(user_id, ip))<commit_msg>Check if credit query result exists<commit_after>#!/usr/bin/env python
# -*- coding: utf-8 -*-

import datetime

from sqlalchemy import create_engine
from flask.ext.babel import lazy_gettext
from flask.globals import current_app
from sqlalchemy.exc import OperationalError

from werkzeug.local import LocalProxy

from model.constants import WEEKDAYS

from sipa.utils import timetag_from_timestamp, timestamp_from_timetag
from sipa.utils.exceptions import DBQueryEmpty
from .ldap_utils import get_current_uid

import logging
logger = logging.getLogger(__name__)


def init_db(app):
    app.extensions['db_atlantis'] = create_engine(
        'mysql+pymysql://{0}:{1}@{2}:3306/netusers'.format(
            app.config['DB_ATLANTIS_USER'],
            app.config['DB_ATLANTIS_PASSWORD'],
            app.config['DB_ATLANTIS_HOST']),
        echo=False, connect_args={'connect_timeout': app.config['SQL_TIMEOUT']}
    )
    app.extensions['db_helios'] = create_engine(
        'mysql+pymysql://{0}:{1}@{2}:{3}/'.format(
            app.config['DB_HELIOS_USER'],
            app.config['DB_HELIOS_PASSWORD'],
            app.config['DB_HELIOS_HOST'],
            int(app.config['DB_HELIOS_PORT'])),
        echo=False, connect_args={'connect_timeout': app.config['SQL_TIMEOUT']}
    )


db_atlantis = LocalProxy(lambda: current_app.extensions['db_atlantis'])
db_helios = LocalProxy(lambda: current_app.extensions['db_helios'])

DORMITORIES = [
    'Wundstraße 5',
    'Wundstraße 7',
    'Wundstraße 9',
    'Wundstraße 11',
    'Wundstraße 1',
    'Wundstraße 3',
    'Zellescher Weg 41',
    'Zellescher Weg 41A',
    'Zellescher Weg 41B',
    'Zellescher Weg 41C',
    'Zellescher Weg 41D',
    'Borsbergstraße 34',
]

STATUS = {
    # todo vervollständigen oder mindestens fehlerresistent machen!
    # (Hat ein Nutzer einen unten nicht enthaltenen Status, gibts einen Fehler)
    1: lazy_gettext('Bezahlt, verbunden'),
    2: lazy_gettext('Nicht bezahlt, Netzanschluss gesperrt'),
    7: lazy_gettext('Verstoß gegen Netzordnung, Netzanschluss gesperrt'),
    9: lazy_gettext('Exaktiv'),
    12: lazy_gettext('Trafficlimit überschritten, Netzanschluss gesperrt')
}


def sql_query(query, args=(), database=db_atlantis):
    """Prepare and execute a raw sql query.
    'args' is a tuple needed for string replacement.
    """
    conn = database.connect()
    result = conn.execute(query, args)
    conn.close()
    return result


def status_string_from_id(status_id):
    return STATUS.get(status_id, lazy_gettext("Unbekannt"))


def user_id_from_uid(uid=None):
    """Fetch user.id (MySQL) from user.uid (LDAP)

    :param uid: The uid of the LDAP user object
    :return: The user id of the MySQL user
    """
    if uid is None:
        uid = get_current_uid()

    return sql_query("SELECT nutzer_id FROM nutzer WHERE unix_account = %s",
                     (uid,)).fetchone()['nutzer_id']


def ip_from_user_id(user_id):
    result = (sql_query(
        "SELECT c_ip FROM computer WHERE nutzer_id = %s",
        (user_id,)
    ).fetchone())
    if not result:
        raise DBQueryEmpty('Nutzer hat keine IP')
    return result['c_ip']


def user_id_from_ip(ip):
    """Returns the MySQL user.id corresponding to the ip or 0 if foreign to the db
    :param ip: A valid IP address
    :return: A user id
    """

    result = sql_query("SELECT nutzer_id FROM computer WHERE c_ip = %s",
                       (ip,)).fetchone()
    if result is None:
        return 0

    return result['nutzer_id']


def query_current_credit(uid=None, ip=None):
    """Returns the current credit in MiB
    :param uid: The id of the user
    :param ip: The ip of the user
    :return: The current amount of credit or False if foreign IP
    """
    if uid is None:
        if ip is None:
            raise AttributeError('Either ip or user_id must be specified!')
        user_id = user_id_from_ip(ip)
        if user_id is 0:
            return False  # IP doesn't correspond to any user
    else:
        user_id = user_id_from_uid(uid)
        ip = ip_from_user_id(user_id)

    try:
        result = sql_query(
            "SELECT amount - input - output as current "
            "FROM traffic.tuext AS t "
            "LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag "
            "WHERE ip = %(ip)s AND c.user_id = %(user_id)s "
            "AND t.timetag = %(today)s",
            {'today': timetag_from_timestamp(), 'ip': ip, 'user_id': user_id}
        ).fetchone()
    except OperationalError as e:
        logger.critical('Unable to connect to MySQL server',
                        extra={'data': {'exception_args': e.args}})
        raise
    else:
        if result and 'current' in result.keys():
            return round(result['current'] / 1024, 2)
        else:
            logger.warning("Credit query was empty", extra={'data': {
                'today': timetag_from_timestamp(), 'ip': ip, 'user_id': user_id
            }})


def query_trafficdata(ip, user_id):
    """Query traffic input/output for IP

    :param ip: a valid ip
    :param user_id: an id of a mysql user tuple
    :return: a dict containing the traffic data in the form of
    {'history': [('weekday', in, out, credit), …], 'credit': credit}
    """
    trafficdata = sql_query(
        "SELECT t.timetag - %(today)s AS day, input, output, amount "
        "FROM traffic.tuext AS t "
        "LEFT OUTER JOIN credit AS c ON t.timetag = c.timetag "
        "WHERE ip = %(ip)s AND c.user_id = %(uid)s "
        "AND t.timetag BETWEEN %(weekago)s AND %(today)s "
        "ORDER BY 'day' DESC ",
        {'today': timetag_from_timestamp(),
         'weekago': timetag_from_timestamp() - 6,
         'ip': ip,
         'uid': user_id}
    ).fetchall()

    if not trafficdata:
        raise DBQueryEmpty('No trafficdata retrieved for user {}@{}'
                           .format(user_id, ip))
{'fraction_non_alphanumeric': 0.11525069637883008, 'fraction_numerical': 0.008008356545961003, 'mean_word_length': 2.8144090305444887, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '5418620', 'n_tokens_mistral': 3918, 'n_tokens_neox': 3776, 'n_words': 1040}