def find_day2scrobbles(user, artist):
    day2scrobbles = OrderedDict([(day, 0)
                                 for day in range(int(db.session.query(func.coalesce(func.min(Scrobble.uts), 0)).\
                                                                 filter(Scrobble.user == user,
                                                                        Scrobble.artist == artist).\
                                                                 scalar() / 86400),
                                                  int(db.session.query(func.coalesce(func.max(Scrobble.uts), 0)).\
                                                                 filter(Scrobble.user == user,
                                                                        Scrobble.artist == artist).\
                                                                 scalar() / 86400) + 1)])
    for uts, in db.session.query(Scrobble.uts).\
                           filter(Scrobble.user == user,
                                  Scrobble.artist == artist):
        day2scrobbles[int(uts / 86400)] += 1

    for day in day2scrobbles:
        if day2scrobbles[day] < 4:
            day2scrobbles[day] = 0

    for day in day2scrobbles:
        if day2scrobbles[day] != 0:
            break
        del day2scrobbles[day]
    for day in reversed(day2scrobbles):
        if day2scrobbles[day] != 0:
            break
        del day2scrobbles[day]

    return day2scrobbles

def fetch_legs(db, where):
    legs = db.metadata.tables["leg_modes"]
    legends0 = db.metadata.tables["leg_ends"]
    legends1 = legends0.alias("legends1")
    places0 = db.metadata.tables["places"]
    places1 = places0.alias("places1")

    s = select(
        [   legs.c.id,
            legs.c.time_start,
            legs.c.time_end,
            legs.c.activity,
            legs.c.line_type,
            legs.c.line_name,
            legs.c.km,
            legs.c.trip,
            func.coalesce(places0.c.label, cast(
                places0.c.id, String)).label("startplace"),
            func.coalesce(places1.c.label, cast(
                places1.c.id, String)).label("endplace")],
        where,
        legs.outerjoin(legends0, legs.c.cluster_start == legends0.c.id) \
            .outerjoin(legends1, legs.c.cluster_end == legends1.c.id) \
            .outerjoin(places0, legends0.c.place == places0.c.id) \
            .outerjoin(places1, legends1.c.place == places1.c.id),
        order_by=legs.c.time_start)

    return db.engine.execute(s)

 def accumulator(self, column_name, new_row, agg_row, old_row=None):
     new_count = new_row.count
     new_total = new_row.c[column_name] * new_row.count
     if old_row is not None:
         new_count = new_count - old_row.count
         new_total = (new_total -
                     (old_row.c[column_name] * old_row.count))
     agg_count = func.coalesce(agg_row.count, 0)
     agg_value = func.coalesce(agg_row.c[column_name]) * agg_count
     total_count = new_count + agg_count
     return case([(total_count == 0, 0)],
                 else_=(agg_value + new_total) / total_count)

    def get(self):
        # pylint: disable=singleton-comparison
        # Cannot use `is` in SQLAlchemy filters

        key = "address:%s" % ["public", "all"][self.deep_visible()]

        value = self.cache.get(key)
        if value:
            self.write(value)
            return

        address_list = self.orm.query(
            Address.address_id,
            func.coalesce(Address.latitude, Address.manual_latitude),
            func.coalesce(Address.longitude, Address.manual_longitude),
        ).filter(func.coalesce(
            Address.latitude, Address.manual_latitude,
            Address.longitude, Address.manual_longitude
        ) != None)

        org_list = address_list \
            .join((org_address,
                   Address.address_id == org_address.c.address_id)) \
            .join((Org, Org.org_id == org_address.c.org_id)) \
            .add_columns(Org.org_id, Org.name, literal("org"))

        event_list = address_list \
            .join((event_address,
                   Address.address_id == event_address.c.address_id)) \
            .join((Event, Event.event_id == event_address.c.event_id)) \
            .add_columns(Event.event_id, Event.name, literal("event"))

        today = datetime.datetime.now().date()
        event_list = event_list.filter(Event.start_date >= today)

        if not (self.moderator and self.deep_visible()):
            org_list = org_list.filter(Org.public == True)
            event_list = event_list.filter(Event.public == True)

        address_list = org_list.union(event_list)

        obj_list = []
        for result in address_list.all():
            obj_list.append(dict(list(zip([
                "address_id", "latitude", "longitude",
                "entity_id", "name", "entity"
            ], result))))

        value = self.dump_json(obj_list)
        self.cache.set(key, value)

        self.write(value)

 def set_order_to_last(self, context):
     """Set order of the line to maximum rank + 1."""
     self._set_order(
         self.query.session.query(
             func.coalesce(func.max(self._order_column), 0))
         .filter(context)
         .as_scalar() + 1)

def user_summary(users, projects=None, resources=None, after=None, before=None):
    s = Session()
    jobs_q = s.query(Job.user_id.label("user_id"), func.count(Job.id).label("job_count"))
    charges_q = s.query(Job.user_id.label("user_id"), func.sum(Charge.amount).label("charge_sum"))
    charges_q = charges_q.join(Charge.job)
    refunds_q = s.query(Job.user_id.label("user_id"), func.sum(Refund.amount).label("refund_sum"))
    refunds_q = refunds_q.join(Refund.charge, Charge.job)

    jobs_q.filter(Job.user_id.in_(user.id for user in users))
    if projects:
        jobs_q = jobs_q.filter(Job.account_id.in_(project.id for project in projects))
        charges_ = Charge.allocation.has(Allocation.project_id.in_(project.id for project in projects))
        charges_q = charges_q.filter(charges_)
        refunds_q = refunds_q.filter(charges_)
    if resources:
        charges_ = Charge.allocation.has(Allocation.resource_id.in_(resource.id for resource in resources))
        jobs_q = jobs_q.filter(Job.charges.any(charges_))
        charges_q = charges_q.filter(charges_)
        refunds_q = refunds_q.filter(charges_)
    if after:
        jobs_q = jobs_q.filter(Job.end > after)
        charges_ = Charge.datetime >= after
        charges_q = charges_q.filter(charges_)
        refunds_q = refunds_q.filter(charges_)
    if before:
        jobs_q = jobs_q.filter(Job.start < before)
        charges_ = Charge.datetime < before
        charges_q = charges_q.filter(charges_)
        refunds_q = refunds_q.filter(charges_)

    jobs_q = jobs_q.group_by(Job.user_id).subquery()
    charges_q = charges_q.group_by(Job.user_id).subquery()
    refunds_q = refunds_q.group_by(Job.user_id).subquery()
    query = s.query(
        Job.user_id,
        func.coalesce(jobs_q.c.job_count, 0),
        (func.coalesce(charges_q.c.charge_sum, 0) - func.coalesce(refunds_q.c.refund_sum, 0)),
    )
    query = query.outerjoin(
        (jobs_q, Job.user_id == jobs_q.c.user_id),
        (charges_q, Job.user_id == charges_q.c.user_id),
        (refunds_q, Job.user_id == refunds_q.c.user_id),
    )
    query = query.filter(Job.user_id.in_(user.id for user in users))
    query = query.distinct().order_by(Job.user_id)
    return query

    def update_after(self, object, result, session):

        join = self.get_parent_primary_keys(object)
        end_date_field = object._table.sa_table.c[self.end]
        setattr(result, self.field_name,
                select([func.max(func.coalesce(end_date_field, self.default_end))],
                       and_(*join))
               )
        session.add_no_validate(result)

def _record_test_duration(step):
    create_or_update(ItemStat, where={
        'item_id': step.id,
        'name': 'test_duration',
    }, values={
        'value': db.session.query(func.coalesce(func.sum(TestCase.duration), 0)).filter(
            TestCase.step_id == step.id,
        ).as_scalar(),
    })

def _build_notification_before_days_filter(notification_before_days):
    days_until_occurrence = cast(ReservationOccurrence.start_dt, Date) - cast(func.now(), Date)
    notification_before_days = func.coalesce(Room.notification_before_days, notification_before_days)
    if datetime.now().hour >= settings.get('notification_hour', 6):
        # Notify of today and delayed occurrences (happening in N or less days)
        return days_until_occurrence <= notification_before_days
    else:
        # Notify only of delayed occurrences (happening in less than N days)
        return days_until_occurrence < notification_before_days

def gen_invoice_no(organization, jurisdiction, invoice_dt):
    """
    Generates a sequential invoice number scoped by the given organization for
    the fiscal year of the given invoice datetime
    """
    fy_start_at, fy_end_at = get_fiscal_year(jurisdiction, invoice_dt)
    return select([func.coalesce(func.max(Invoice.invoice_no + 1), 1)]).where(
        Invoice.organization == organization).where(
        Invoice.invoiced_at >= fy_start_at).where(Invoice.invoiced_at < fy_end_at)

def _service_get_all_topic_subquery(context, session, topic, subq, label):
    sort_value = getattr(subq.c, label)
    return model_query(context, models.Service,
                       func.coalesce(sort_value, 0),
                       session=session, read_deleted="no").\
        filter_by(topic=topic).\
        filter_by(disabled=False).\
        outerjoin((subq, models.Service.host == subq.c.host)).\
        order_by(sort_value).\
        all()

 def get_protection_parent_cte(cls):
     cat_alias = db.aliased(cls)
     cte_query = (select([cat_alias.id, db.cast(literal(None), db.Integer).label('protection_parent')])
                  .where(cat_alias.parent_id.is_(None))
                  .cte(recursive=True))
     rec_query = (select([cat_alias.id,
                          db.case({ProtectionMode.inheriting.value: func.coalesce(cte_query.c.protection_parent, 0)},
                                  else_=cat_alias.id, value=cat_alias.protection_mode)])
                  .where(cat_alias.parent_id == cte_query.c.id))
     return cte_query.union_all(rec_query)

 def get_transformations(self, measures):
     transformations = {}
     for name, expr in measures.items():
         measure = measures[name]
         transformations[expr.name] = (measure.agg.accumulator(
             expr.name,
             self.new_row, self.agg_row).label(expr.name))
     # Update the fact count
     transformations[self.agg.fact_count_column.name] = ((
         self.new_row.c[self.agg.fact_count_column.name] +
         func.coalesce(self.agg_row.c[self.agg.fact_count_column.name], 0))
         .label(self.agg.fact_count_column.name))
     return transformations

def system_utilisation_counts_by_group(grouping, systems):
    retval = defaultdict(lambda: dict((k, 0) for k in
            ['recipe', 'manual', 'idle_automated', 'idle_manual',
             'idle_broken', 'idle_removed']))
    query = systems.outerjoin(System.open_reservation)\
            .with_entities(grouping,
                func.coalesce(Reservation.type,
                func.concat('idle_', func.lower(System.status))),
                func.count(System.id))\
            .group_by(literal_column("1"), literal_column("2"))
    for group, state, count in query:
        retval[group][state] = count
    return retval

 def get_storage_used(self, user=None):
     """
     Get the storage used by model runs for a user or all users
     :param user: the user
     :return: a tuple of user id, model run status name, sum of storage in mb (if null returns 0)
     """
     with self.readonly_scope() as session:
         query = session\
             .query(ModelRun.user_id, ModelRunStatus.name, func.coalesce(func.sum(ModelRun.storage_in_mb), 0))\
             .join(ModelRunStatus)\
             .group_by(ModelRun.user_id, ModelRunStatus.name)
         if user is not None:
             query = query.filter(ModelRun.user_id == user.id)
         return query.all()

 def on_get(self, req, resp, phrase_id):
     columns = []
     columns.extend(phrase.c)
     columns.append(
         f.coalesce(
             select([f.array_agg(gs_phrase.c.section_id)]).
             where(gs_phrase.c.phrase_id == phrase_id).
             as_scalar(),
             []
         ).
         label("grammar_sections")
     )
     columns.append(
         f.coalesce(
             select([f.array_agg(theme_phrase.c.theme_id)]).
             where(theme_phrase.c.phrase_id == phrase_id).
             as_scalar(),
             []
         ).
         label("themes")
     )
     sel = select(columns).where(phrase.c.id == phrase_id)
     resp.body = phrase_editor_view(self.db.execute(sel).fetchone())

    def get_chart_data(self, project_id, query_date):
        calendar = db.session.query(
            func.generate_series(
                query_date - timedelta(days=CHART_DATA_LIMIT - 1),
                query_date,
                timedelta(days=1)
            ).label('day')
        ).subquery()

        historical_data = db.session.query(
            calendar.c.day,
            func.coalesce(func.sum(FlakyTestStat.flaky_runs), 0),
            func.coalesce(func.sum(FlakyTestStat.double_reruns), 0),
            func.coalesce(func.sum(FlakyTestStat.passing_runs), 0)
        ).outerjoin(
            FlakyTestStat,
            and_(
                calendar.c.day == FlakyTestStat.date,
                FlakyTestStat.project_id == project_id
            )
        ).order_by(
            calendar.c.day.desc()
        ).group_by(
            calendar.c.day
        )

        chart_data = []
        for d, flaky_runs, double_reruns, passing_runs in historical_data:
            chart_data.append({
                'date': str(d.date()),
                'flaky_runs': flaky_runs,
                'double_reruns': double_reruns,
                'passing_runs': passing_runs
            })

        return chart_data

 def run(self, db):
     session = db.session
     today = datetime.date.today()
     skills = session.query(
         BotIdentity.id,
         func.coalesce(func.sum(MatchResult.delta_chips), 0),
         func.coalesce(func.sum(MatchResult.hands), 0),
         func.coalesce(func.count(MatchResult.id), 0),
         ) \
         .outerjoin(MatchResult) \
         .group_by(BotIdentity.id) \
         .all()
     yesterday = today - datetime.timedelta(days=1)
     yesterdays_skills = {b.bot: b.skill for b in
                          BotSkill.query.filter_by(date=yesterday).all()}
     BotSkill.query.filter_by(date=today).delete()
     session.bulk_save_objects(
         [BotSkill(s[0], today,
                   self.calc_winnings_per_hand(s[1], s[2]),
                   yesterdays_skills.get(s[0], 0),
                   s[3])
          for s in skills]
     )
     session.commit()

    def get_aggregated_stats(environment_id):
        unit = db_session.get_session()
        now = timeutils.utcnow_ts()
        query = unit.query(models.Instance.instance_type, func.sum(
            func.coalesce(models.Instance.destroyed, now) -
            models.Instance.created), func.count()).filter(
                models.Instance.environment_id == environment_id)

        res = query.group_by(models.Instance.instance_type).all()

        return [{
                'type': int(record[0]),
                'duration': int(record[1]),
                'count': int(record[2])
                } for record in res]

def system_utilisation_counts(systems):
    """
    Similar to the above except returns counts of systems based on the current 
    state, rather than historical data about particular systems.
    """
    retval = dict((k, 0) for k in
            ['recipe', 'manual', 'idle_automated', 'idle_manual',
             'idle_broken', 'idle_removed'])
    query = systems.outerjoin(System.open_reservation)\
            .with_entities(func.coalesce(Reservation.type,
                func.concat('idle_', func.lower(System.status))),
                func.count(System.id))\
            .group_by(literal_column("1"))
    for state, count in query:
        retval[state] = count
    return retval