def stockcheck(request, info, session):
    buylist = []
    depts = session.query(Department).order_by(Department.id).all()
    if request.method == "POST":
        form = StockCheckForm(depts, request.POST)
        if form.is_valid():
            cd = form.cleaned_data
            ahead = datetime.timedelta(days=cd["weeks_ahead"] * 7)
            behind = datetime.timedelta(days=cd["months_behind"] * 30.4)
            min_sale = cd["minimum_sold"]
            dept = int(cd["department"])
            q = (
                session.query(StockType, func.sum(StockOut.qty) / behind.days)
                .join(StockItem)
                .join(StockOut)
                .options(lazyload(StockType.department))
                .options(lazyload(StockType.unit))
                .options(undefer(StockType.instock))
                .filter(StockOut.removecode_id == "sold")
                .filter((func.now() - StockOut.time) < behind)
                .filter(StockType.dept_id == dept)
                .having(func.sum(StockOut.qty) / behind.days > min_sale)
                .group_by(StockType)
            )
            r = q.all()
            buylist = [(st, "{:0.1f}".format(sold), "{:0.1f}".format(sold * ahead.days - st.instock)) for st, sold in r]
            buylist.sort(key=lambda l: float(l[2]), reverse=True)
    else:
        form = StockCheckForm(depts)
    return ("stockcheck.html", {"form": form, "buylist": buylist})

def overview():
    uquery = lambda: session.session.query(User)

    entries = [{"title": "Nutzer in Datenbank",
                "href": None,
                "number": uquery().count()},
               {"title": "Mitglieder",
                "href": None,
                "number": uquery().join(Membership).filter(
                                Membership.group == config.member_group,
                                Membership.active())
                           .count()},
               {"title": "Nicht bezahlt",
                "href": None,
                "number": uquery().join(User.account)
                           .join(Split)
                           .group_by(User.id)
                           .having(func.sum(Split.amount) > 0)
                           .count()},
               {"title": "Nicht bezahlt (Mitglieder)",
                "href": "#",
                "number": uquery().join(Membership).filter(
                                Membership.group == config.member_group,
                                Membership.active())
                           .join(User.account)
                           .join(Split)
                           .group_by(User.id)
                           .having(func.sum(Split.amount) > 0)
                           .count()}]
    return render_template("user/user_overview.html", entries=entries)

def accounts():
    month_year = request.args.get('month_year')
    month_years = (db.session.query(func.strftime("%m-%Y", Transaction.date))
                   .select_from(Transaction)
                   .distinct()
                   .order_by(func.strftime("%Y-%m", Transaction.date))
                   .all()
                   )
    month_years = [each[0] for each in month_years]

    accounts_query = (db.session.query(
        func.sum(Transaction.withdrawal),
        func.sum(Transaction.deposit),
        Account.full_title)
                .select_from(Account)
                .outerjoin(Transaction)
                .group_by(Account.id)
                .order_by(Account.full_title)
                )

    if month_year:
        accounts_query = accounts_query.filter(func.strftime("%m-%Y", Transaction.date) == month_year)

    accounts = accounts_query.all()
                

    def relabel(account):
        return {"withdrawal": account[0],
                "deposit": account[1],
                "full_title": account[2]}
                
    accounts = [relabel(account) for account in accounts]
    return render_template('accounts.html', accounts=accounts, title="Accounts", month_year=month_year, month_years=month_years)

def has_exceeded_traffic(user):
    """
    The function calculates the balance of the users traffic.
    :param user: The user object which has to be checked.
    :return: True if the user has more traffic than allowed and false if he
    did not exceed the limit.
    """
    result = session.session.query(
        User.id,
        (func.max(TrafficGroup.traffic_limit) * 1.10) < func.sum(TrafficVolume.size).label("has_exceeded_traffic")
    ).join(
        User.active_traffic_groups
    ).join(
        User.user_hosts
    ).join(
        Host.ips
    ).join(
        Ip.traffic_volumes
    ).filter(
        User.id == user.id
    ).group_by(
        User.id
    ).first()

    if result is not None:
        return result.has_exceeded_traffic
    else:
        return False

 def amount(self, account):
     query = (
         self.session
         .query(func.sum(Ledger.amount))
         .filter(Ledger.account == account)
     )
     return query.scalar() or 0

def group_hot_value(group):
    hot = db.session.query(func.sum(Hot.number))\
        .filter(Group.active == True,
                Post.group_id == group.id,
                Post.id == Hot.post_id) \
        .group_by(Group.id)\
        .first()[0]
    return hot

    def get_done(self):
        total = (
            DBSession.query(func.sum(ST_Area(Task.geometry)))
            .filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state != TaskState.state_removed))
            .scalar()
        )

        done = (
            DBSession.query(func.sum(ST_Area(Task.geometry)))
            .filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state == TaskState.state_done))
            .scalar()
        )

        if not done:
            done = 0

        return round(done * 100 / total) if total != 0 else 0

def hottest_users():
    users_and_ids = db.session.query(func.sum(Hot.number), User.id).filter(User.id == Post.user_id, Post.id == Hot.post_id).group_by(User.id).order_by(func.sum(Hot.number)).limit(3).all()
    users = []
    for user_and_id in users_and_ids:
        user = user_from_id(user_and_id[1])
        user.__setattr__('hot', user_and_id[0])
        users.append(user)
    return users

    def get_validated(self):
        total = (
            DBSession.query(func.sum(ST_Area(Task.geometry)))
            .filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state != TaskState.state_removed))
            .scalar()
        )

        validated = (
            DBSession.query(func.sum(ST_Area(Task.geometry)))
            .filter(Task.project_id == self.id, Task.cur_state.has(TaskState.state == TaskState.state_validated))
            .scalar()
        )

        if not validated:
            validated = 0

        return round(validated * 100 / total) if total != 0 else 0

    def refund(self, invoice, amount):
        """Refund the invoice

        """
        Transaction = tables.Transaction
        tx_model = self.factory.create_transaction_model()
        transactions = []

        self.get(invoice.guid, with_lockmode='update')

        if invoice.status != self.statuses.SETTLED:
            raise InvalidOperationError('You can only refund a settled invoice')

        refunded_amount = (
            self.session.query(
                func.coalesce(func.sum(Transaction.amount), 0)
            )
            .filter(
                Transaction.invoice == invoice,
                Transaction.transaction_type == TransactionModel.types.REFUND,
                Transaction.submit_status.in_([
                    TransactionModel.submit_statuses.STAGED,
                    TransactionModel.submit_statuses.RETRYING,
                    TransactionModel.submit_statuses.DONE,
                ])
            )
        ).scalar()
        # Make sure do not allow refund more than effective amount
        if refunded_amount + amount > invoice.effective_amount:
            raise InvalidOperationError(
                'Refund total amount {} + {} will exceed invoice effective amount {}'
                .format(
                    refunded_amount,
                    amount,
                    invoice.effective_amount,
                )
            )

        # the settled transaction
        settled_transaction = (
            self.session.query(Transaction)
            .filter(
                Transaction.invoice == invoice,
                Transaction.transaction_type == TransactionModel.types.DEBIT,
                Transaction.submit_status == TransactionModel.submit_statuses.DONE,
            )
        ).one()

        # create the refund transaction
        transaction = tx_model.create(
            invoice=invoice,
            transaction_type=TransactionModel.types.REFUND,
            amount=amount,
            reference_to=settled_transaction,
        )
        transactions.append(transaction)
        return transactions

def hottest_groups(user):
    groups = db.session.query(Group)\
            .outerjoin((Post, Post.group_id == Group.id),
                        (Hot, Post.id == Hot.post_id)) \
        .filter(Group.active == True)\
        .group_by(Group.id)\
        .order_by(func.sum(Hot.number))\
        .limit(3)\
        .all()
    return groups

 def get_stats(self, name, limit=100, total=False):
     q = (db.session.query(Stats.date, func.sum(Stats.value).label("value"))
         .filter(
             place_parents.c.parent_id==self.id,
             place_parents.c.child_id==Stats.place_id,
             Stats.name==name)
         .group_by(Stats.date)
         .order_by(Stats.date)
         .limit(limit))
     return q.all()

 def download_size(self):
     from ututi.model import File, FileDownload
     download_size = meta.Session.query(func.sum(File.filesize))\
         .filter(FileDownload.file_id==File.id)\
         .filter(FileDownload.user==self)\
         .filter(FileDownload.range_start==None)\
         .filter(FileDownload.range_end==None)\
         .scalar()
     if not download_size:
         return 0
     return int(download_size)

    def total_adjustment_amount(self):
        """Sum of total adjustment amount

        """
        from sqlalchemy import func
        session = object_session(self)
        return (
            session.query(func.coalesce(func.sum(Adjustment.amount), 0))
            .filter(Adjustment.invoice_guid == self.guid)
            .scalar()
        )

def get_stats(project):
    """
    the changes to create a chart with
    """

    total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
        .filter(
            Task.cur_state.has(TaskState.state != TaskState.state_removed),
            Task.project_id == project.id
        ) \
        .scalar()

    subquery = DBSession.query(
        TaskState.state,
        TaskState.date,
        ST_Area(Task.geometry).label('area'),
        func.lag(TaskState.state).over(
            partition_by=(
                TaskState.task_id,
                TaskState.project_id
            ),
            order_by=TaskState.date
        ).label('prev_state')
    ).join(Task).filter(
        TaskState.project_id == project.id,
        TaskState.state != TaskState.state_ready) \
     .order_by(TaskState.date)

    tasks = subquery.all()
    log.debug('Number of tiles: %s', len(tasks))
    stats = [[project.created.isoformat(), 0, 0]]
    done = 0
    validated = 0

    # for every day count number of changes and aggregate changed tiles
    for task in tasks:
        if task.state == TaskState.state_done:
            done += task.area
        if task.state == TaskState.state_invalidated:
            if task.prev_state == TaskState.state_done:
                done -= task.area
            elif task.prev_state == TaskState.state_validated:
                validated -= task.area
        if task.state == TaskState.state_validated:
            validated += task.area
            done -= task.area

        # append a day to the stats and add total number of 'done' tiles and a
        # copy of a current tile_changes list
        stats.append([task.date.isoformat(), done, validated])

    return {"total": total, "stats": stats}

 def amount(self, account):
     cache = self.session.query(AccountAmount).get(account.guid)
     query = (
         self.session
         .query(func.sum(Ledger.amount))
         .filter(Ledger.account == account)
     )
     if cache is None:
         last_amount = 0
     else:
         last_amount = cache.amount
         query = query.filter(Ledger.created_at > cache.updated_at)
     return (query.scalar() or 0) + last_amount

    def get_space_usage(self, user_id):
        """Get space usage of user

        """
        from sqlalchemy.sql.expression import func
        # 100 MB basic space
        basic = 100 * 1024 * 1024
        # TODO: sum space of user here
        used = self.session \
            .query(func.ifnull(func.sum(tables.Audio.size), 0)) \
            .filter_by(user_id=user_id) \
            .scalar()
        return dict(total=basic, used=used)

def recalculate_account_totals(accounts):
    """Recalculate the account totals based on the transaction log.
    """
    amounts = [] # Set of tuples (Account, old total, new total)
    for account in accounts:
        deposit_sum = session.query(func.sum(Transaction.amount).\
                                    label('deposit_sum')).\
                            filter(Transaction.account == account).\
                            filter(Transaction.status == True).\
                            filter(Transaction.action == Transaction.DEPOSIT).\
                            one().deposit_sum
        withdraw_sum = session.query(func.sum(Transaction.amount).\
                                     label('withdrawal_sum')).\
                         filter(Transaction.account == account).\
                         filter(Transaction.status == True).\
                         filter(Transaction.action == Transaction.WITHDRAWAL).\
                         one().withdrawal_sum
        if debug:
            print('Deposits: %s; Withdrawals: %s' % (deposit_sum, withdraw_sum))
        new_total = ((deposit_sum if deposit_sum is not None else 0) -
                     (withdraw_sum if withdraw_sum is not None else 0))
        amounts.append((account, account.total, _format_out_amount(new_total)))
    return amounts

def update_score(connection, update_all=True):
    """
    Update the score of all teams. If ``update_all`` is set, the points
    for all challenges are updated beforehand as well.

    This is your one-shot function to create up-to-date points for everything.
    """
    from fluxscoreboard.models import dynamic_challenges
    if update_all:
        update_challenge_points(connection, update_team_count=True)
    bonus_col = func.sum(Challenge._points - Challenge.base_points)
    bonus_score = (select([func.coalesce(bonus_col, 0)]).
                   where(Challenge.id == Submission.challenge_id).
                   where(Team.id == Submission.team_id).
                   where(~Challenge.dynamic).
                   where(Challenge.published).
                   correlate(Team))

    # base score
    challenge_sum = func.coalesce(func.sum(Challenge.base_points), 0)
    # first blood
    fb_sum = func.coalesce(func.sum(Submission.additional_pts), 0)

    points_col = challenge_sum + fb_sum
    for module in dynamic_challenges.registry.values():
        points_col += module.get_points_query(Team)
    base_score = (select([points_col]).
                  where(Challenge.id == Submission.challenge_id).
                  where(Team.id == Submission.team_id).
                  where(~Challenge.dynamic).
                  where(Challenge.published).
                  correlate(Team))
    query = (Team.__table__.update().
             where(Team.active).
             values(base_score=base_score,
                    bonus_score=bonus_score))
    connection.execute(query)

    def get_balance(self):
        s = select([
        AccountTransaction.patientAccount_id,
        func.sum( AccountTransaction.amount * AccountTransactionType.multiplier ).label('balance')
        ]).where(
        and_(
            AccountTransaction.transactionType_id == AccountTransactionType.id,
            AccountTransaction.patientAccount_id == self.id
            )
        ).group_by(AccountTransaction.patientAccount_id)

        result = db.session.execute(s).fetchone()
        balance = result[1] if result else 0

        return balance + self.initialBalance