def receiver_plot(receiver_name):
    import matplotlib.pyplot as plt
    from io import BytesIO

    receiver_query = session.query(func.floor(AircraftBeacon.phi),
                                   func.max(AircraftBeacon.radius/1000)) \
        .filter(AircraftBeacon.receiver_name == receiver_name) \
        .filter(AircraftBeacon.radius is not None) \
        .group_by(func.floor(AircraftBeacon.phi)) \
        .order_by(func.floor(AircraftBeacon.phi))

    rows = receiver_query.all()
    xs, ys = zip(*rows)
    xs = [x / 180 * 3.141592653 for x in xs]

    ax = plt.axes(polar=True)
    ax.set_theta_zero_location('N')
    ax.set_theta_direction('clockwise')

    plt.title('Maximum range')
    plt.plot(xs, ys, 'r')
    plt.ylim(0, 100)

    img = BytesIO()
    plt.savefig(img)
    plt.close()
    img.seek(0)
    return send_file(img, mimetype='image/png')
    def alchemy_expression(self):
        dt = self.product
        grid_spec = self.product.grid_spec

        doc = _jsonb_doc_expression(dt.metadata_type)
        projection_offset = _projection_doc_offset(dt.metadata_type)

        # Calculate tile refs
        geo_ref_points_offset = projection_offset + ['geo_ref_points']
        center_point = func.ST_Centroid(
            func.ST_Collect(
                _gis_point(doc, geo_ref_points_offset + ['ll']),
                _gis_point(doc, geo_ref_points_offset + ['ur']),
            ))

        # todo: look at grid_spec crs. Use it for defaults, conversion.
        size_x, size_y = (grid_spec.tile_size or (1000.0, 1000.0))
        origin_x, origin_y = grid_spec.origin
        return func.concat(
            func.floor(
                (func.ST_X(center_point) - origin_x) / size_x).cast(String),
            '_',
            func.floor(
                (func.ST_Y(center_point) - origin_y) / size_y).cast(String),
        )
 def postgres_aggregates(self, resolution):
     if isinstance(resolution, basestring):
         try:
             resolution = float(resolution)
         except ValueError:
             resolution = self.resolution
     return [
         label('cell_x', func.floor(ST_X(Column('cell')) / resolution) * resolution),
         label('cell_y', func.floor(ST_Y(Column('cell')) / resolution) * resolution)]
Exemple #4
0
def get_ten_minute_core_fv_series_in_period(db, start_time, end_time):
    """
    Get the ten-minute open-price series in this period from our core fv datums.
    Like most of our periods, this one is inclusive on the left but not the right. So
    getting this series for a full day [Nov 1st, Nov 2nd) will give you 24*6 == 144
    datapoints, but getting this series for a day and one minute will likely give you
    25.
    """

    fundamental_value_series = []

    fv_series = db.query(
            Datum.time_created,
            Datum.numeric_value,
        )\
        .filter(Datum.time_created >= start_time)\
        .filter(Datum.time_created < end_time)\
        .filter(Datum.datum_type.like('%_CORE_FUNDAMENTAL_VALUE'))\
        .group_by(
            func.date(Datum.time_created),
            func.hour(Datum.time_created),
            func.floor((func.minute(Datum.time_created) / 10))*10
        )\
        .all()

    # Do a teeny bit of sanity checking.
    delta = end_time - start_time
    ten_minutes = math.floor((delta.days * 86400 + delta.seconds) / 600)
    assert (len(fv_series) == ten_minutes)

    fundamental_value_series = convert_datum_series_to_time_series(fv_series)

    return fundamental_value_series
Exemple #5
0
 def stalenessRating(self):
     """ Return the Queryable staleness rating of the mastery """
     return func.coalesce(
         cast(
             func.floor(
                 func.extract('epoch',
                              func.now() - self.lastCorrectAnswer) / 86400),
             db.Integer) / StalenessPeriod.days, 0)
def device_plot2(address):
    import matplotlib.pyplot as plt
    import numpy as np
    from matplotlib.mlab import griddata
    from io import BytesIO

    sq = session.query(func.floor(case([(AircraftBeacon.theta-AircraftBeacon.track+180 > 0,
                                         AircraftBeacon.theta-AircraftBeacon.track+180),
                                        (AircraftBeacon.theta-AircraftBeacon.track+180 < 0,
                                         AircraftBeacon.theta-AircraftBeacon.track+180+360)])).label('direction'),
                       AircraftBeacon.radius,
                       AircraftBeacon.signal_strength) \
                .filter(AircraftBeacon.receiver_name == 'Koenigsdf') \
                .filter(AircraftBeacon.address == address) \
                .filter(AircraftBeacon.radius is not None).subquery()

    device_query = session.query(func.floor(sq.c.direction/15)*15,
                                 func.floor(sq.c.radius/1000)*1000,
                                 func.avg(sq.c.signal_strength)) \
                          .group_by(func.floor(sq.c.direction/15)*15,
                                    func.floor(sq.c.radius/1000)*1000)

    rows = device_query.all()
    xs, ys, zs = zip(*rows)
    xs = [x / 180 * 3.141592653 for x in xs]

    xi = np.linspace(0, 2*np.pi, 360)
    yi = np.linspace(0, 10000, 5)
    zi = griddata(xs, ys, zs, xi, yi, interp='linear')

    ax = plt.axes(polar=True)
    ax.set_theta_zero_location('N')
    ax.set_theta_direction('clockwise')

    plt.title('Maximum range')
    c = plt.contourf(xi, yi, zi, 5)
    cb = plt.colorbar(c)
    cb.set_label("Signal strength [db]")

    img = BytesIO()
    plt.savefig(img)
    plt.close()
    img.seek(0)
    return send_file(img, mimetype='image/png')
Exemple #7
0
 def get(self):
     '''방문시 연령대(10세 단위)별 방문 수'''
     Json = dict()
     q = db.session.query(
         func.floor(
             extract('year',
                     func.age(v.visit_start_datetime, p.birth_datetime)) /
             10) * 10, func.count()
     ).join(p, p.person_id == v.person_id).group_by(
         func.floor(
             extract('year',
                     func.age(v.visit_start_datetime, p.birth_datetime)) /
             10) *
         10).order_by(
             func.floor(
                 extract('year',
                         func.age(v.visit_start_datetime, p.birth_datetime))
                 / 10) * 10)
     for name, num in q:
         Json[str(int(name)) + "대"] = num
     return Json
Exemple #8
0
 def count_visit_by_age_group():
     # select age_group, person_id
     ages = db.session.query(
         (func.floor(extract('year', (func.age(Person.birth_datetime))) / 10) * 10).label('age_group'),
         Person.person_id.label('person_id')
     ).subquery()
     # group by age_group
     result = db.session \
         .query(ages.c.age_group, func.count(ages.c.age_group)) \
         .filter(VisitOccurrence.person_id == ages.c.person_id) \
         .group_by(ages.c.age_group) \
         .all()
     return dict(result)
    def coindays_destroyed(self, since=None, interval=None):
        if interval is not None:
            query = self.session.query(
                sqlfunc.min(CoinDaysDestroyed.timestamp),
                sqlfunc.max(CoinDaysDestroyed.timestamp),
                sqlfunc.sum(CoinDaysDestroyed.coindays))
            if since is not None:
                query = query.filter(CoinDaysDestroyed.timestamp >= since)
            query = query.group_by(
                sqlfunc.floor(
                    sqlfunc.to_seconds(CoinDaysDestroyed.timestamp) /
                    interval)).order_by(CoinDaysDestroyed.timestamp.asc())
            return [
                dict(zip(('start', 'end', 'coindaysdestroyed'), period))
                for period in query.all()
            ]

        query = self.session.query(sqlfunc.sum(CoinDaysDestroyed.coindays))
        if since is not None:
            query = query.filter(CoinDaysDestroyed.timestamp >= since)

        destroyed = query.first()[0]
        return round(float(destroyed), 5) if destroyed != None else 0.0
Exemple #10
0
def get_custom_channel_stats(db: Connection, channel_id: int, period_start: datetime, period_end: datetime,
                             average_interval: int = 60) -> List:
    """
    Get channel's stats for specified period.
    """
    query = select([ENTRIES.c.timestamp, func.avg(ENTRIES.c.value)]) \
        .select_from(ENTRIES) \
        .where(and_(ENTRIES.c.channel_id == channel_id,
                    between(ENTRIES.c.timestamp, period_start, period_end))) \
        .group_by(func.date(ENTRIES.c.timestamp),
                  func.hour(ENTRIES.c.timestamp),
                  func.floor(func.minute(ENTRIES.c.timestamp) / average_interval)) \
        .order_by(ENTRIES.c.timestamp.asc())

    result = db.execute(query)

    datetimes = datetimes_between(period_start, period_end, average_interval * 60)
    average_by_datetime = OrderedDict.fromkeys(datetimes)
    for time, value in result:
        dt = time.replace(minute=0, second=0)
        average_by_datetime[dt] = value

    return average_by_datetime.items()
Exemple #11
0
    async def rand(self, ctx: commands.Context, *, args: PlaybackArgumentConverter() = _DEFAULT_PLAYBACK_ARGUMENTS):
        """
        Play a random sound.

        :param args: The volume/speed of playback, in format v[XX%] s[SS%]. e.g. v50 s100 for 50% sound, 100% speed.
        """

        sound = await self.bot.db.fetch_one(
                select([sound_names.c.sound_id, sound_names.c.name])
                    .where(
                        and_(
                                sound_names.c.guild_id == ctx.guild.id,
                                ~sound_names.c.is_alias
                        ))
                    .offset(
                        func.floor(
                                func.random() *
                                select([func.count()])
                                .select_from(sound_names)
                                .where(sound_names.c.guild_id == ctx.guild.id)))
                    .limit(1)
        )
        log.debug(f'Playing random sound {sound[sound_names.c.name]}.')
        await ctx.invoke(self.play, sound, args=args)
Exemple #12
0
def get_peaks_query_group_by_time_step(target_type, target_id, step,
                                       metric_type, time_from, time_until):
    time_until = round_time(time_from, time_until, step=step)
    shift = get_shift(time_from, step)
    group_time = (func.floor(
        (func.extract("epoch", BatchStoryPeaks.time) - shift) / step)) * step
    query_creator = MetricPeakGroupFilter(session=db.session)
    (query_creator.filter_by_metric_type(metric_type).filter_by_time_period(
        time_from=time_from, time_until=time_until).set_query_entities(
            func.max(MetricPeak.value).label("peaks"),
            group_time.label("step"),
            func.min(func.extract("epoch",
                                  BatchStoryPeaks.time)).label("time"),
            MetricPeak.type.label("type"),
        ).group_by("step", "type").order_by("time"))
    if target_type == "cluster":
        query_creator.filter_by_cluster_id(target_id)
    elif target_type == "server":
        query_creator.filter_by_server_id(target_id)
    elif target_type == "services_group":
        query_creator.filter_by_service_group_id(target_id)
    elif target_type == "service":
        query_creator.filter_by_service_id(target_id)
    return query_creator
def device_plot(address):
    import matplotlib.pyplot as plt
    from io import BytesIO

    receiver_name = 'Koenigsdf'

    device_query = session.query(func.floor(AircraftBeacon.radius/1000),
                                 func.avg(AircraftBeacon.signal_strength)) \
        .filter(AircraftBeacon.address == address) \
        .filter(AircraftBeacon.receiver_name == receiver_name) \
        .group_by(func.floor(AircraftBeacon.radius/1000)) \
        .order_by(func.floor(AircraftBeacon.radius/1000))

    rows = device_query.all()
    xs, ys = zip(*rows)

    plt.plot(xs, ys, 'b', marker='o', label=address)

    receiver_query = session.query(func.floor(AircraftBeacon.radius/1000),
                                   func.avg(AircraftBeacon.signal_strength)) \
        .filter(AircraftBeacon.receiver_name == receiver_name) \
        .group_by(func.floor(AircraftBeacon.radius/1000)) \
        .order_by(func.floor(AircraftBeacon.radius/1000))

    rows = receiver_query.all()
    xs, ys = zip(*rows)

    plt.plot(xs, ys, 'g--', label=receiver_name)

    plt.title('Signal strength over distance')
    plt.xlim(0, 40)
    plt.xlabel('Distance [km]')
    plt.ylabel('Signal strength [db]')
    plt.legend(loc='upper right')
    plt.grid(True, color='0.75')

    img = BytesIO()
    plt.savefig(img)
    img.seek(0)
    return send_file(img, mimetype='image/png')
Exemple #14
0
    def execute(self, message, user, params):
        
        alliance=Alliance()
        race=None
        size_mod=None
        size=None
        value_mod=None
        value=None
        bash=False
        attacker=user.planet
        cluster=None

        params=params.group(1).split()

        for p in params:
            m=self.bashre.match(p)
            if m and not bash:
                bash=True
                continue
            m=self.clusterre.match(p)
            if m and not cluster:
                cluster=int(m.group(1))
            m=self.racere.match(p)
            if m and not race:
                race=m.group(1)
                continue
            m=self.rangere.match(p)
            if m and not size and int(m.group(2)) < 32768:
                size_mod=m.group(1) or '>'
                size=m.group(2)
                continue
            m=self.rangere.match(p)
            if m and not value:
                value_mod=m.group(1) or '<'
                value=m.group(2)
                continue
            m=self.alliancere.match(p)
            if m and not alliance.name and not self.clusterre.match(p):
                alliance = Alliance(name="Unknown") if m.group(1).lower() == "unknown" else Alliance.load(m.group(1))
                if alliance is None:
                    message.reply("No alliance matching '%s' found" % (m.group(1),))
                    return
                continue

        maxcap = PA.getfloat("roids","maxcap")
        mincap = PA.getfloat("roids","mincap")
        modifier = (cast(Planet.value,Float).op("/")(float(attacker.value))).op("^")(0.5)
        caprate = func.float8larger(mincap,func.float8smaller(modifier.op("*")(maxcap),maxcap))
        maxcap = cast(func.floor(cast(Planet.size,Float).op("*")(caprate)),Integer)
        
        bravery = (func.float8larger(0.0,( func.float8smaller(2.0, cast(Planet.value,Float).op("/")(float(attacker.value)))-0.1) * (func.float8smaller(2.0, cast(Planet.score,Float).op("/")(float(attacker.score)))-0.2))).op("*")(10.0)
        xp_gain = cast(func.floor(maxcap.op("*")(bravery)),Integer)
        
        Q = session.query(Planet, Intel, xp_gain.label("xp_gain"))
        if alliance.id:
            Q = Q.join(Planet.intel)
            Q = Q.filter(Intel.alliance == alliance)
        else:
            Q = Q.outerjoin(Planet.intel)
            if alliance.name:
                Q = Q.filter(Intel.alliance == None)
        Q = Q.filter(Planet.active == True)
        if race:
            Q = Q.filter(Planet.race.ilike(race))
        if size:
            Q = Q.filter(Planet.size.op(size_mod)(size))
        if value:
            Q = Q.filter(Planet.value.op(value_mod)(value))
        if bash:
            Q = Q.filter(or_(Planet.value.op(">")(attacker.value*PA.getfloat("bash","value")),
                             Planet.score.op(">")(attacker.score*PA.getfloat("bash","score"))))
        if cluster:
            Q = Q.filter(Planet.x == cluster)
        Q = Q.order_by(desc("xp_gain"))
        Q = Q.order_by(desc(Planet.idle))
        Q = Q.order_by(desc(Planet.value))
        result = Q[:6]
        
        if len(result) < 1:
            reply="No"
            if race:
                reply+=" %s"%(race,)
            reply+=" planets"
            if alliance.name:
                reply+=" in intel matching Alliance: %s"%(alliance.name,)
            else:
                reply+=" matching"
            if size:
                reply+=" Size %s %s" % (size_mod,size)
            if value:
                reply+=" Value %s %s" % (value_mod,value)
            message.reply(reply)
            return
        
        replies = []
        for planet, intel, xp_gain in result[:5]:
            reply="%s:%s:%s (%s)" % (planet.x,planet.y,planet.z,planet.race)
            reply+=" Value: %s Size: %s Scoregain: %d" % (planet.value,planet.size, xp_gain*60)
            if intel:
                if intel.nick:
                    reply+=" Nick: %s" % (intel.nick,)
                if not alliance.name and intel.alliance:
                    reply+=" Alliance: %s" % (intel.alliance.name,)
            replies.append(reply)
        if len(result) > 5:
            replies[-1]+=" (Too many results to list, please refine your search)"
        message.reply("\n".join(replies))
Exemple #15
0
    def execute(self, message, user, params):

        alliance = Alliance()
        race = None
        size_mod = None
        size = None
        value_mod = None
        value = None
        bash = False
        attacker = user.planet
        cluster = None

        params = params.group(1).split()

        for p in params:
            m = self.bashre.match(p)
            if m and not bash:
                bash = True
                continue
            m = self.clusterre.match(p)
            if m and not cluster:
                cluster = int(m.group(1))
            m = self.racere.match(p)
            if m and not race:
                race = m.group(1)
                continue
            m = self.rangere.match(p)
            if m and not size and int(m.group(2)) < 32768:
                size_mod = m.group(1) or '>'
                size = m.group(2)
                continue
            m = self.rangere.match(p)
            if m and not value:
                value_mod = m.group(1) or '<'
                value = m.group(2)
                continue
            m = self.alliancere.match(p)
            if m and not alliance.name and not self.clusterre.match(p):
                alliance = Alliance(name="Unknown") if m.group(
                    1).lower() == "unknown" else Alliance.load(m.group(1))
                if alliance is None:
                    message.reply("No alliance matching '%s' found" %
                                  (m.group(1), ))
                    return
                continue

        maxcap = PA.getfloat("roids", "maxcap")
        mincap = PA.getfloat("roids", "mincap")
        modifier = (cast(Planet.value,
                         Float).op("/")(float(attacker.value))).op("^")(0.5)
        caprate = func.float8larger(
            mincap, func.float8smaller(modifier.op("*")(maxcap), maxcap))
        maxcap = cast(func.floor(cast(Planet.size, Float).op("*")(caprate)),
                      Integer)

        bravery = (func.float8larger(
            0.0,
            (func.float8smaller(
                2.0,
                cast(Planet.value, Float).op("/")(float(attacker.value))) -
             0.1) * (func.float8smaller(
                 2.0,
                 cast(Planet.score, Float).op("/")(float(attacker.score))) -
                     0.2))).op("*")(10.0)
        xp_gain = cast(func.floor(maxcap.op("*")(bravery)), Integer)

        Q = session.query(Planet, Intel, xp_gain.label("xp_gain"))
        if alliance.id:
            Q = Q.join(Planet.intel)
            Q = Q.filter(Intel.alliance == alliance)
        else:
            Q = Q.outerjoin(Planet.intel)
            if alliance.name:
                Q = Q.filter(Intel.alliance == None)
        Q = Q.filter(Planet.active == True)
        if race:
            Q = Q.filter(Planet.race.ilike(race))
        if size:
            Q = Q.filter(Planet.size.op(size_mod)(size))
        if value:
            Q = Q.filter(Planet.value.op(value_mod)(value))
        if bash:
            Q = Q.filter(
                or_(
                    Planet.value.op(">")(attacker.value *
                                         PA.getfloat("bash", "value")),
                    Planet.score.op(">")(attacker.score *
                                         PA.getfloat("bash", "score"))))
        if cluster:
            Q = Q.filter(Planet.x == cluster)
        Q = Q.order_by(desc("xp_gain"))
        Q = Q.order_by(desc(Planet.idle))
        Q = Q.order_by(desc(Planet.value))
        result = Q[:6]

        if len(result) < 1:
            reply = "No"
            if race:
                reply += " %s" % (race, )
            reply += " planets"
            if alliance.name:
                reply += " in intel matching Alliance: %s" % (alliance.name, )
            else:
                reply += " matching"
            if size:
                reply += " Size %s %s" % (size_mod, size)
            if value:
                reply += " Value %s %s" % (value_mod, value)
            message.reply(reply)
            return

        replies = []
        for planet, intel, xp_gain in result[:5]:
            reply = "%s:%s:%s (%s)" % (planet.x, planet.y, planet.z,
                                       planet.race)
            reply += " Value: %s Size: %s Scoregain: %d" % (
                planet.value, planet.size,
                xp_gain * PA.getint("numbers", "xp_value"))
            if intel:
                if intel.nick:
                    reply += " Nick: %s" % (intel.nick, )
                if not alliance.name and intel.alliance:
                    reply += " Alliance: %s" % (intel.alliance.name, )
            replies.append(reply)
        if len(result) > 5:
            replies[
                -1] += " (Too many results to list, please refine your search)"
        message.reply("\n".join(replies))
Exemple #16
0
    def get(self,
            variable,
            group_by,
            start_date=None,
            end_date=None,
            only_loc=None,
            use_ids=None,
            date_variable=None,
            additional_variables=None,
            group_by_variables=None):

        variable = str(variable)
        if not only_loc:
            if "only_loc" in request.args:
                only_loc = request.args["only_loc"]
            else:
                only_loc = g.allowed_location
        if not is_allowed_location(only_loc, g.allowed_location):
            return {}

        start_date, end_date = fix_dates(start_date, end_date)
        if "use_ids" in request.args.keys() or use_ids:
            use_ids = True
        else:
            use_ids = False

        if date_variable:
            date_conditions = [
                func.to_date(Data.variables[date_variable].astext,
                             "YYYY-MM-DDTHH-MI-SS") >= start_date,
                func.to_date(Data.variables[date_variable].astext,
                             "YYYY-MM-DDTHH-MI-SS") < end_date
            ]
        else:
            date_conditions = [Data.date >= start_date, Data.date < end_date]

        if "location" in variable:
            location_id = variable.split(":")[1]
            conditions = date_conditions + [
                or_(loc == location_id
                    for loc in (Data.country, Data.zone, Data.region,
                                Data.district, Data.clinic))
            ]
        else:
            conditions = [Data.variables.has_key(variable)] + date_conditions
            if additional_variables:
                # add additional variable filters if there are and
                for i in additional_variables:
                    conditions.append(Data.variables.has_key(i))

            if only_loc:
                conditions += [
                    or_(loc == only_loc
                        for loc in (Data.country, Data.zone, Data.region,
                                    Data.district, Data.clinic))
                ]
        epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date(
            start_date)
        # Determine which columns we want to extract from the Data table
        columns_to_extract = [func.count(Data.id).label('value')]
        if date_variable:
            columns_to_extract.append(
                func.floor(
                    extract(
                        'days',
                        func.to_date(Data.variables[date_variable].astext,
                                     "YYYY-MM-DDTHH-MI-SS") - epi_year_start) /
                    7 + 1).label("week"))
        else:
            columns_to_extract.append(
                func.floor(
                    extract('days', Data.date - epi_year_start) / 7 +
                    1).label("week"))
        # We want to add the columns to extract based on the group_by value
        # in addition we create a names dict that translates ids to names

        if "locations" in group_by:
            # If we have locations in group_by we also specify the level at
            #  which we want to group the locations, clinic, district or region
            if ":" in group_by:
                level = group_by.split(":")[1]
            else:
                level = "clinic"

            locations = abacus_util.get_locations(db.session)
            ids = locations.keys()
            names = get_locations_by_level(level, only_loc)

            columns_to_extract += [getattr(Data, level, None)]
            group_by_query = level
        else:
            if not group_by_variables:
                names = get_variables(group_by)
            else:
                names = group_by_variables
            if len(names) == 0:
                return {}
            ids = names.keys()
            for i in ids:
                columns_to_extract.append(
                    Data.variables.has_key(str(i)).label("id" + str(i)))
            group_by_query = ",".join(["id" + str(i) for i in ids])
        if use_ids:
            names = {vid: vid for vid in names.keys()}
        start_epi_week = abacus_util.epi_week.epi_week_for_date(start_date)[1]
        end_epi_week = abacus_util.epi_week.epi_week_for_date(end_date)[1]

        # How we deal with start and end dates in different years
        if start_date.year != end_date.year:
            end_epi_week += 53 * (end_date.year - start_date.year)

        # DB Query
        results = db.session.query(*tuple(columns_to_extract)).filter(
            *conditions).group_by("week," + group_by_query)
        # Assemble return dict
        ret = {}
        for n in names.values():
            ret[n] = {
                "total": 0,
                "weeks":
                {i: 0
                 for i in range(start_epi_week, end_epi_week + 1)}
            }

        for r in results:
            # r = (number, week, other_columns_to_extract
            if "locations" in group_by:
                # r[2] = location
                if r[2]:
                    ret[names[r[2]]]["total"] += r[0]
                    ret[names[r[2]]]["weeks"][int(r[1])] = int(r[0])
            else:
                # r[2:] are the ids that the record has
                for i, i_d in enumerate(ids):
                    if r[i + 2]:
                        ret[names[i_d]]["total"] += r[0]
                        ret[names[i_d]]["weeks"][int(r[1])] = int(r[0])
        return ret
Exemple #17
0
 def stalenessRating(self):
     """ Return the Queryable staleness rating of the mastery """
     return func.coalesce(cast(func.floor(func.extract('epoch', func.now()-self.lastCorrectAnswer)/86400), db.Integer)/StalenessPeriod.days, 0)
Exemple #18
0
def stat_hourly(page, rows, offset, sidx, sord, date_filter, queues_filter):
   # Hourly distribution (30 min sections)
   if db_engine=='oracle':
      xh = func.floor((sql.cast(func.to_char(Queue_log.timestamp, 'HH24'), types.INT) *60 + \
         sql.cast(func.to_char(Queue_log.timestamp, 'MI'), types.INT) ) / 30)
   else: # PostgreSql
      xh = func.floor((extract('hour', Queue_log.timestamp) * 60 + \
         extract('min', Queue_log.timestamp) ) / 30)
   xh = xh.label('xhour')

#   h_incoming = DBSession.query(xh, func.count('*').label('incoming')).\
#      filter(Queue_log.queue_event_id==Queue_event.qe_id).\
#      filter(Queue_event.event=='ENTERQUEUE').filter(queues_filter)
#   if date_filter is not None:
#      h_incoming = h_incoming.filter(date_filter)
#   h_incoming = h_incoming.group_by(xh).order_by(xh).subquery()

   h_connect = DBSession.query(
         xh, func.count('*').label('count')).\
      filter(queues_filter). \
      filter(Queue_log.queue_event_id==Queue_event.qe_id).\
      filter(Queue_event.event=='CONNECT').filter(queues_filter)
   if date_filter is not None:
      h_connect = h_connect.filter(date_filter)
   h_connect = h_connect.group_by(xh).subquery()

   h_abandon = DBSession.query(
         xh, func.count('*').label('count')).\
      filter(queues_filter). \
      filter(Queue_log.queue_event_id==Queue_event.qe_id).\
      filter(Queue_event.event=='ABANDON').filter(queues_filter)
   if date_filter is not None:
      h_abandon = h_abandon.filter(date_filter)
   h_abandon = h_abandon.group_by(xh).subquery()

   h_closed = DBSession.query(
         xh, func.count('*').label('count')).\
      filter(queues_filter). \
      filter(Queue_log.queue_event_id==Queue_event.qe_id).\
      filter(Queue_event.event=='CLOSED').filter(queues_filter)
   if date_filter is not None:
      h_closed = h_closed.filter(date_filter)
   h_closed = h_closed.group_by(xh).subquery()

   h_dissuasion = DBSession.query(
         xh, func.count('*').label('count')).\
      filter(queues_filter). \
      filter(Queue_log.queue_event_id==Queue_event.qe_id).\
      filter(Queue_event.event=='DISSUASION').filter(queues_filter)
   if date_filter is not None:
      h_dissuasion = h_dissuasion.filter(date_filter)
   h_dissuasion = h_dissuasion.group_by(xh).subquery()

   q = DBSession.query(xh, func.count('*').label('incoming'),
            h_abandon.c.count.label('abandon'),
            h_connect.c.count.label('connect'), 
            h_dissuasion.c.count.label('dissuasion'), 
            h_closed.c.count.label('closed')).\
      filter(Queue_log.queue_event_id==Queue_event.qe_id). \
      filter(Queue_event.event=='ENTERQUEUE').filter(queues_filter). \
      filter(queues_filter). \
      outerjoin((h_connect, xh==h_connect.c.xhour)). \
      outerjoin((h_abandon, xh==h_abandon.c.xhour)). \
      outerjoin((h_closed, xh==h_closed.c.xhour)). \
      outerjoin((h_dissuasion, xh==h_dissuasion.c.xhour)). \
      group_by(xh,h_abandon.c.count, h_connect.c.count, 
            h_dissuasion.c.count, h_closed.c.count)
   
   if date_filter is not None:
      q = q.filter(date_filter)

   if sidx=='incoming':
      q = q.order_by(desc(func.count('*'))) if sord=='desc' \
            else q.order_by(func.count('*'))

   elif sidx=='connect':
      q = q.order_by(desc(h_connect.c.count)) if sord=='desc' \
            else q.order_by(h_connect.c.count)

   elif sidx=='abandon':
      q = q.order_by(desc(h_abandon.c.count)) if sord=='desc' \
            else q.order_by(h_abandon.c.count)

   elif sidx=='dissuasion':
      q = q.order_by(desc(h_dissuasion.c.count)) if sord=='desc' \
            else q.order_by(h_dissuasion.c.count)

   elif sidx=='closed':
      q = q.order_by(desc(h_closed.c.count)) if sord=='desc' \
            else q.order_by(h_closed.c.count)

   else:
      q = q.order_by(desc(xh)) if sord=='desc' \
            else q.order_by(xh)

   q = q.offset(offset).limit(rows)
   total = q.count()/rows + 1
   data = []
   total_in = 0
   for i, r in enumerate(q.all()):
      total_in += r.incoming
      data.append({ 'id'  : i, 'cell': [
         u'%dh30' % (r.xhour/2) if i%2 \
            else u'%dh' % (r.xhour/2),
         r.incoming, 0, r.closed, 0, r.dissuasion, 0,
         r.abandon, 0, r.connect, 0]
      })

   for x in data:
      x['cell'][2] = '%.1f %%' % (100.0 * x['cell'][1] / total_in) \
            if x['cell'][1] else ''
      x['cell'][4] = '%.1f %%' % (100.0 * x['cell'][3] / total_in) \
            if x['cell'][3] else ''
      x['cell'][6] = '%.1f %%' % (100.0 * x['cell'][5] / total_in) \
            if x['cell'][5] else ''
      x['cell'][8] = '%.1f %%' % (100.0 * x['cell'][7] / total_in) \
            if x['cell'][7] else ''
      x['cell'][10] = '%.1f %%' % (100.0 * x['cell'][9] / total_in) \
            if x['cell'][9] else ''
   log.debug(data)
   return dict(page=page, total=total, rows=data)
Exemple #19
0
 def optimized_random(self, limit):
     return self.query.offset(
         func.floor(
             func.random() *
             db.session.query(func.count(self.id)))).limit(limit).all()
Exemple #20
0
def latest_query(db,
                 var_id,
                 identifier_id,
                 start_date,
                 end_date,
                 location,
                 allowed_location=1,
                 level=None,
                 weeks=False,
                 date_variable=None,
                 week_offset=0):
    """
    To query register like data where we want to get the latest value.

    I.e If the value of the number of beds is updated each week and we want the latest number. 
    We take the latest value per clinic.

    Args:
        var_id: Variable id to get last of
        identifier_id: Id to identify which records we should use
        start_date: Start date
        end_date: End date
        location: Location to restrict to
        date_variable: if None we use date from data otherwise we use the variable indicated
        weeks: True if we want a breakdwon by weeks.
    Returns:
       result(dict): Dictionary with results. Always has total key, and if
                     level was given there is a level key with the data
                     breakdown
    """
    if allowed_location == 1:
        if g:
            allowed_location = g.allowed_location
    if not is_allowed_location(location, allowed_location):
        return {}
    location_condtion = [
        or_(loc == location for loc in (Data.country, Data.zone, Data.region,
                                        Data.district, Data.clinic))
    ]
    if date_variable:
        date_conditions = [
            func.to_date(Data.variables[date_variable].astext,
                         "YYYY-MM-DDTHH-MI-SS") >= start_date,
            func.to_date(Data.variables[date_variable].astext,
                         "YYYY-MM-DDTHH-MI-SS") < end_date
        ]
    else:
        date_conditions = [Data.date >= start_date, Data.date < end_date]
    conditions = location_condtion + date_conditions + [
        Data.variables.has_key(identifier_id)
    ]

    if weeks:
        epi_year_start = meerkat_abacus.util.epi_week.epi_year_start_date(
            start_date)
        if date_variable:
            c = func.floor(
                extract(
                    'days',
                    func.to_date(Data.variables[date_variable].astext,
                                 "YYYY-MM-DDTHH-MI-SS") - epi_year_start) / 7 +
                1).label("week")
        else:
            c = func.floor(
                extract('days', Data.date - epi_year_start) / 7 +
                1).label("week")
        # This query selects that latest record for each clinic for each week
        # that has the variable identifier_id
        query = db.session.query(
            Data.clinic, c, Data.date,
            Data.region, Data.district, Data.variables).distinct(
                Data.clinic, c).filter(*conditions).order_by(
                    Data.clinic).order_by(c).order_by(Data.date.desc())
        ret = {
            "total": 0,
            "weeks": {},
            "district": {},
            "clinic": {},
            "region": {}
        }

        for r in query:
            val = r.variables.get(var_id, 0)
            ret["total"] += val
            week = int(r.week) - week_offset
            ret["weeks"].setdefault(week, 0)
            ret["weeks"][week] += val

            ret["clinic"].setdefault(r.clinic, {"total": 0, "weeks": {}})
            ret["clinic"][r.clinic]["total"] += val
            ret["clinic"][r.clinic]["weeks"][week] = val
            ret["district"].setdefault(r.district, {"total": 0, "weeks": {}})
            ret["district"][r.district]["total"] += val
            ret["district"][r.district]["weeks"][week] = +val
            ret["region"].setdefault(r.region, {"total": 0, "weeks": {}})
            ret["region"][r.region]["total"] += val
            ret["region"][r.region]["weeks"][week] = +val
        return ret
    else:
        # This query selects that latest record for each clinic
        # that has the variable identifier_id
        query = db.session.query(
            Data.clinic, Data.date, Data.region, Data.district,
            Data.variables).distinct(Data.clinic).filter(*conditions).order_by(
                Data.clinic).order_by(Data.date.desc())

        ret = {"total": 0, "clinic": {}, "district": {}, "region": {}}
        for r in query:
            val = r.variables.get(var_id, 0)
            ret["total"] += val
            ret["clinic"][r.clinic] = val
            ret["district"].setdefault(r.district, 0)
            ret["district"][r.district] += val
            ret["region"].setdefault(r.region, 0)
            ret["region"][r.region] += val
        return ret
    def get_progression_stats(self,
                              session,
                              id_,
                              time_unit: 'TimeUnit' = TimeUnit.MONTH,
                              start_date: datetime.datetime = None,
                              end_date: datetime.datetime = None,
                              playlist: int = 13):

        if time_unit == TimeUnit.MONTH:
            date = func.to_char(Game.match_date, 'YY-MM')
            time_format = "%y-%m"
        elif time_unit == TimeUnit.DAY:
            date = func.to_char(Game.match_date, 'YY-MM-DD')
            time_format = "%y-%m-%d"
        elif time_unit == TimeUnit.YEAR:
            date = func.to_char(Game.match_date, 'YY')
            time_format = "%y"
        elif time_unit == TimeUnit.QUARTER:
            date = func.concat(
                func.to_char(Game.match_date, 'YY'), '-',
                func.floor(
                    cast(func.extract('quarter', Game.match_date),
                         sqlalchemy.Numeric)))
            time_format = '%y-%m'
        else:
            # Mainly to help the linter know that time_unit is assigned.
            logger.error(
                f'Unknown time unit: {time_unit}. Falling back onto month.')
            date = func.to_char(Game.match_date, 'YY-MM')
            time_format = "%y-%m"

        date = date.label('date')
        mean_query = session.query(date, func.count(
            Game.hash), *self.get_player_stat_query()).join(PlayerGame).filter(
                PlayerGame.time_in_game > 0).filter(
                    PlayerGame.player == id_).group_by('date').order_by('date')
        std_query = session.query(
            date, func.count(Game.hash),
            *self.get_player_stat_std_query()).join(PlayerGame).filter(
                PlayerGame.time_in_game > 0).filter(
                    PlayerGame.player == id_).group_by('date').order_by('date')

        if start_date is not None:
            mean_query = mean_query.filter(Game.match_date > start_date)
            std_query = std_query.filter(Game.match_date > start_date)
        if end_date is not None:
            mean_query = mean_query.filter(Game.match_date < end_date)
            std_query = std_query.filter(Game.match_date < end_date)
        if playlist is not None:
            mean_query = mean_query.filter(Game.playlist == playlist)
            std_query = std_query.filter(Game.playlist == playlist)

        mean_query = mean_query.all()
        std_query = std_query.all()

        mean_query = [list(q) for q in mean_query]
        std_query = [list(q) for q in std_query]
        results = []
        for q, s in zip(mean_query, std_query):
            result = {
                'name':
                datetime.datetime.strptime(q[0], time_format),
                'average':
                self.get_wrapped_stats([self.float_maybe(qn) for qn in q[2:]],
                                       self.player_stats),
                'std_dev':
                self.get_wrapped_stats([self.float_maybe(qn) for qn in s[2:]],
                                       self.player_stats),
                'count':
                q[1]
            }
            if time_unit == 'quarter':
                date = result['name']
                result['name'] = datetime.datetime(date.year,
                                                   (date.month - 1) * 3 + 1, 1)
            result['name'] = result['name'].isoformat()
            results.append(result)
        return results
Exemple #22
0
    def execute(self, message, user, params):

        alliance = Alliance()
        race = None
        size_mod = None
        size = None
        value_mod = None
        value = None
        bash = False
        attacker = user.planet
        cluster = None
        limit = 5

        params = params.group(1).split()

        for p in params:
            m = self.bashre.match(p)
            if m and not bash:
                bash = True
                continue
            m = self.clusterre.match(p)
            if m and not cluster:
                cluster = int(m.group(1))
            m = self.racere.match(p)
            if m and not race:
                race = m.group(1)
                continue
            m = self.rangere.match(p)
            if m and not size and int(m.group(2)) < 32768:
                size_mod = m.group(1) or '>'
                size = m.group(2)
                continue
            m = self.rangere.match(p)
            if m and not value:
                value_mod = m.group(1) or '<'
                value = m.group(2)
                continue
            m = self.alliancere.match(p)
            if m and not alliance.name and not self.clusterre.match(p):
                alliance = Alliance(name="Unknown") if m.group(
                    1).lower() == "unknown" else Alliance.load(m.group(1))
                if alliance is None:
                    message.reply("No alliance matching '%s' found" %
                                  (m.group(1), ))
                    return
                continue
            if p[:4] == "lots" and user.is_admin():
                limit = int(p[4:])

        maxcap = PA.getfloat("roids", "maxcap")
        mincap = PA.getfloat("roids", "mincap")
        modifier = (cast(Planet.value,
                         Float).op("/")(float(attacker.value))).op("^")(0.5)
        caprate = func.greatest(mincap,
                                func.least(modifier.op("*")(maxcap), maxcap))
        maxcap = cast(func.floor(cast(Planet.size, Float).op("*")(caprate)),
                      Integer)

        Q = session.query(Planet, Intel, maxcap.label("maxcap"))
        if alliance.id:
            Q = Q.join(Planet.intel)
            Q = Q.filter(Intel.alliance == alliance)
        else:
            Q = Q.outerjoin(Planet.intel)
            if alliance.name:
                Q = Q.filter(Intel.alliance == None)
        Q = Q.filter(Planet.active == True)
        if race:
            Q = Q.filter(Planet.race.ilike(race))
        if size:
            Q = Q.filter(Planet.size.op(size_mod)(size))
        if value:
            Q = Q.filter(Planet.value.op(value_mod)(value))
        if bash:
            Q = Q.filter(
                or_(
                    Planet.value.op(">")(attacker.value *
                                         PA.getfloat("bash", "value")),
                    Planet.score.op(">")(attacker.score *
                                         PA.getfloat("bash", "score"))))
        if cluster:
            Q = Q.filter(Planet.x == cluster)
        Q = Q.order_by(desc("maxcap"))
        Q = Q.order_by(desc(Planet.size))
        Q = Q.order_by(desc(Planet.value))
        result = Q[:(limit + 1)]

        if len(result) < 1:
            reply = "No"
            if race:
                reply += " %s" % (race, )
            reply += " planets"
            if alliance.name:
                reply += " in intel matching Alliance: %s" % (alliance.name, )
            else:
                reply += " matching"
            if size:
                reply += " Size %s %s" % (size_mod, size)
            if value:
                reply += " Value %s %s" % (value_mod, value)
            message.reply(reply)
            return

        replies = []
        for planet, intel, maxcap in result[:limit]:
            reply = "%s:%s:%s (%s)" % (planet.x, planet.y, planet.z,
                                       planet.race)
            reply += " Value: %s Size: %s MaxCap: %s" % (planet.value,
                                                         planet.size, maxcap)
            if intel:
                if intel.nick:
                    reply += " Nick: %s" % (intel.nick, )
                if not alliance.name and intel.alliance:
                    reply += " Alliance: %s" % (intel.alliance.name, )
            replies.append(reply)
            print(len(replies))
            if len(replies) == 5:
                message.reply("\n".join(replies))
                replies = []
                sleep(3)
        if len(result) > limit:
            replies.append(
                "(Too many results to list, please refine your search)")
        message.reply("\n".join(replies))
Exemple #23
0
def usage_data(hall_no, year, month, day):
    # turn date info into a date object
    # find start range by subtracting 30 days
    now = datetime.date(year, month, day)
    start = now - datetime.timedelta(days=30)

    # get the current day of the week for today and tomorrow
    # python dow is monday = 0, while sql dow is sunday = 0
    dow = (now.weekday() + 1) % 7
    tmw = (dow + 1) % 7

    # some commands are different between mysql and sqlite
    is_mysql = sqldb.engine.name == "mysql"

    # get the laundry information for today based on the day
    # of week (if today is tuesday, get all the tuesdays
    # in the past 30 days), group them by time, and include
    # the first 2 hours of the next day
    data = sqldb.session.query(
        LaundrySnapshot.date,
        (func.floor(LaundrySnapshot.time / 60).label("time") if is_mysql else
         cast(LaundrySnapshot.time / 60, Integer).label("time")),
        func.avg(LaundrySnapshot.washers).label("all_washers"),
        func.avg(LaundrySnapshot.dryers).label("all_dryers"),
        func.avg(LaundrySnapshot.total_washers).label("all_total_washers"),
        func.avg(LaundrySnapshot.total_dryers).label("all_total_dryers"),
    ).filter(((LaundrySnapshot.room == hall_no)
             & ((func.dayofweek(LaundrySnapshot.date) == dow + 1 if is_mysql else
                 func.strftime("%w", LaundrySnapshot.date) == str(dow))
             | ((LaundrySnapshot.time <= 180 - 1)
                 & (func.dayofweek(LaundrySnapshot.date) == tmw + 1 if is_mysql else
                    func.strftime("%w", LaundrySnapshot.date) == str(tmw))))
             & (LaundrySnapshot.date >= start))) \
     .group_by(LaundrySnapshot.date, "time") \
     .order_by(LaundrySnapshot.date, "time").all()
    data = [x._asdict() for x in data]
    all_dryers = [int(x["all_total_dryers"]) for x in data]
    all_washers = [int(x["all_total_washers"]) for x in data]
    washer_points = {k: 0 for k in range(27)}
    dryer_points = {k: 0 for k in range(27)}
    washer_total = {k: 0 for k in range(27)}
    dryer_total = {k: 0 for k in range(27)}
    for x in data:
        hour = int(x["time"])
        # if the value is for tomorrow, add 24 hours
        if x["date"].weekday() != now.weekday():
            hour += 24
        washer_points[hour] += int(x["all_washers"])
        dryer_points[hour] += int(x["all_dryers"])
        washer_total[hour] += 1
        dryer_total[hour] += 1
    dates = [x["date"] for x in data]
    if not dates:
        dates = [now]
    return {
        "hall_name":
        laundry.id_to_hall[hall_no],
        "location":
        laundry.id_to_location[hall_no],
        "day_of_week":
        calendar.day_name[now.weekday()],
        "start_date":
        min(dates).strftime("%Y-%m-%d"),
        "end_date":
        max(dates).strftime("%Y-%m-%d"),
        "total_number_of_dryers":
        safe_division(sum(all_dryers), len(all_dryers)),
        "total_number_of_washers":
        safe_division(sum(all_washers), len(all_washers)),
        "washer_data": {
            x: safe_division(washer_points[x], washer_total[x])
            for x in washer_points
        },
        "dryer_data": {
            x: safe_division(dryer_points[x], dryer_total[x])
            for x in dryer_points
        }
    }