Пример #1
0
 def get_errors(self, test_id):
     statement = select([
         (func.sum(aggregate.c.count*aggregate.c.errors/100)*100/func.sum(aggregate.c.count)).label('fail_%'),
         (100 - func.sum(aggregate.c.count*aggregate.c.errors/100)*100/func.sum(aggregate.c.count)).label('success_%')
     ])\
         .where(aggregate.c.test_id == test_id)
     return self.execute_statement(statement, False)
Пример #2
0
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)
Пример #3
0
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'])
            r = session\
                .query(StockType, func.sum(StockOut.qty) / behind.days)\
                .join(StockItem)\
                .join(StockOut)\
                .options(lazyload(StockType.department),
                         lazyload(StockType.unit),
                         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)\
                .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})
Пример #4
0
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)
Пример #5
0
    def get_query_orders_summary(self,
                                 store_id,
                                 start_time,
                                 end_time,
                                 room_id=0):
        OrderModel = get_model('t_order', store_id)
        q = self.slave.query(
            func.count('1').label('count'),
            func.sum(OrderModel.money).label('money'),
            func.sum(OrderModel.real_money).label('real_money'))
        if room_id:
            q = q.filter_by(room_id=room_id)
        q = q.filter(OrderModel.state == ORDER_STATE['finish'])
        q = q.filter(OrderModel.finish_time >= start_time,
                     OrderModel.finish_time <= end_time)
        result = q.all()

        summary = {'count': 0, 'money': 0, 'real_money': 0}

        if not result:
            return summary

        if result[0].count:
            summary['count'] = result[0].count
        if result[0].money:
            summary['money'] = int(result[0].money)
        if result[0].real_money:
            summary['real_money'] = int(result[0].real_money)

        return summary
Пример #6
0
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})
Пример #7
0
 def get_pack_sales(self,
                    store_id,
                    bill_ids,
                    order_by='count',
                    page=1,
                    page_size=10):
     if not bill_ids:
         return []
     offset = (page - 1) * page_size
     BillModel = get_model('t_bill_product', store_id)
     q = self.slave.query(BillModel.pack_id,
                          func.sum(BillModel.count).label('count'),
                          func.sum(BillModel.money).label('money'))
     q = q.filter(BillModel.bill_id.in_(tuple(bill_ids)),
                  BillModel.pack_id > 0)
     q = q.group_by('pack_id').order_by(
         desc(order_by)).offset(offset).limit(page_size)
     result = q.all()
     sales = []
     for ret in result:
         sales.append({
             'pack_id': ret.pack_id,
             'count': int(ret.count) if ret.count else 0,
             'money': int(ret.money) if ret.money else 0
         })
     return sales
def get_average_score(word_type):
    """Average score of last 7 days"""
    other_time = datetime.now() - timedelta(hours=168)
    x = config.db.session.query(
        label('total', func.sum(Words.attempts)),
        label('correct',
              func.sum(Words.correct))).filter_by(word_type=word_type).first()
    return {"attempts": x[0], "correct": x[1]}
Пример #9
0
def generate_metrics(full=False):
    def write_single_stat(name, help, type, value):
        fmt = "# HELP {name} {help}\n# TYPE {name} {type}\n{name} {value}\n\n"

        return fmt.format(name=name, help=help, type=type, value=value)

    def gen_labels(labels):
        pieces = [key + "=" + str(val) for key, val in labels.items()]
        return ",".join(pieces)

    def write_array_stat(name, help, type, data):
        ret = "# HELP {name} {help}\n# TYPE {name} {type}\n" \
         .format(name=name, help=help, type=type)

        for entry in data:
            assert (len(entry) == 2)
            ret += "{name}{{{labels}}} {value}\n" \
             .format(name=name, labels=gen_labels(entry[0]), value=entry[1])

        return ret + "\n"

    downloads_result = db.session.query(func.sum(
        Package.downloads)).one_or_none()
    downloads = 0 if not downloads_result or not downloads_result[
        0] else downloads_result[0]

    packages = Package.query.filter_by(state=PackageState.APPROVED).count()
    users = User.query.filter(User.rank != UserRank.NOT_JOINED).count()

    ret = ""
    ret += write_single_stat("contentdb_packages", "Total packages", "counter",
                             packages)
    ret += write_single_stat("contentdb_users", "Number of registered users",
                             "counter", users)
    ret += write_single_stat("contentdb_downloads", "Total downloads",
                             "counter", downloads)

    if full:
        scores = Package.query.join(User).with_entities(User.username, Package.name, Package.score) \
         .filter(Package.state==PackageState.APPROVED).all()

        ret += write_array_stat("contentdb_package_score", "Package score",
                                "gauge", [({
                                    "author": score[0],
                                    "name": score[1]
                                }, score[2]) for score in scores])
    else:
        score_result = db.session.query(func.sum(Package.score)).one_or_none()
        score = 0 if not score_result or not score_result[0] else score_result[
            0]
        ret += write_single_stat("contentdb_score", "Total package score",
                                 "gauge", score)

    return ret
Пример #10
0
    def get_accepted_ids_query():
        limit = Settings.get_settings().accept_limit

        Application.standardize_scores()

        answer_values = Application.question_answer_matrix_subquery()

        return db.session.query(Application.id, func.sum(answer_values.c.sum_values + Application.standardized_score)) \
        .join(answer_values, answer_values.c.id == Application.id) \
        .filter(Application.score != 0) \
        .group_by(Application.id) \
        .order_by(func.sum(answer_values.c.sum_values + Application.standardized_score).desc().nullslast()) \
        .limit(limit) \
        .subquery()
Пример #11
0
    def get_done(self):
        total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
            .filter(Task.project_id == self.id) \
            .filter(Task.state != Task.state_removed) \
            .scalar()

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

        if not done:
            done = 0

        return round(done * 100 / total) if total != 0 else 0
Пример #12
0
    def get_validated(self):
        total = DBSession.query(func.sum(ST_Area(Task.geometry))) \
            .filter(Task.project_id == self.id) \
            .filter(Task.state != Task.state_removed) \
            .scalar()

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

        if not validated:
            validated = 0

        return round(validated * 100 / total) if total != 0 else 0
Пример #13
0
def get_order_items():
    orderItems = db.session.query(func.date_trunc(
        'year', Order.order_date), func.sum(OrderItem.quantity)).group_by(
            func.date_trunc('year', Order.order_date)).join(Order).all()
    schema = OrderItemSchema(many=True)
    # Renvoi un tab [] vide ??
    return jsonify(orderItems)
Пример #14
0
 def question_answer_matrix_subquery():
     return db.session.query(Application.id, func.sum(Answer.answer_weight * Question.weight).label("sum_values")) \
         .join(Answer) \
         .join(Question) \
         .filter((Question.question_type == QuestionType.demographic) | (Question.question_type == QuestionType.university)) \
         .group_by(Application.id) \
         .subquery()
Пример #15
0
    def rank_participants():
        Application.standardize_scores()

        answer_values = Application.question_answer_matrix_subquery()

        scores = db.session.query(Application.id, func.sum(answer_values.c.sum_values + Application.standardized_score).label('calculated_score')) \
            .join(answer_values, answer_values.c.id == Application.id) \
            .group_by(Application.id) \
            .subquery()

        team_scores = db.session.query(Answer.answer, func.avg(scores.c.calculated_score).label('team_score')) \
            .join(scores, scores.c.id == Answer.application_id) \
            .join(Question, Question.id == Answer.question_id) \
            .filter(Question.question_type == QuestionType.teamEmail) \
            .group_by(Answer.answer) \
            .subquery()

        teamQuestion = aliased(Question)
        teamAnswer = aliased(Answer)

        results_q = db.session.query(Application.id, Application.date_added, Application.feedback, Application.score, Application.standardized_score, team_scores.c.team_score, func.json_object_agg(Question.question, Answer.answer)) \
            .join(Answer) \
            .join(Question) \
            .join(teamAnswer, teamAnswer.application_id == Application.id) \
            .join(teamQuestion, teamQuestion.id == teamAnswer.question_id) \
            .join(team_scores, team_scores.c.answer == teamAnswer.answer) \
            .filter(teamQuestion.question_type == QuestionType.teamEmail) \
            .group_by(Application.id, team_scores.c.team_score) \
            .order_by(team_scores.c.team_score.desc().nullslast()) \

        results = results_q.all()

        return results
Пример #16
0
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
Пример #17
0
 def amount(self, account):
     query = (
         self.session
         .query(func.sum(Ledger.amount))
         .filter(Ledger.account == account)
     )
     return query.scalar() or 0
Пример #18
0
 def disliked_post_count(self):
     from szurubooru.db import session
     return (session
         .query(func.sum(1))
         .filter(PostScore.user_id == self.user_id)
         .filter(PostScore.score == -1)
         .one()[0] or 0)
Пример #19
0
 def __check_user_jobs(self, job):
     if job.user:
         # Check the hard limit first
         if self.app.config.registered_user_job_limit:
             # Cache the job count if necessary
             if not self.user_job_count:
                 query = self.sa_session.execute(select([model.Job.table.c.user_id, func.count(model.Job.table.c.user_id)]) \
                         .where(and_(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING)), (model.Job.table.c.user_id is not None))) \
                         .group_by(model.Job.table.c.user_id))
                 for row in query:
                     self.user_job_count[row[0]] = row[1]
             if self.user_job_count.get(
                     job.user_id,
                     0) >= self.app.config.registered_user_job_limit:
                 return JOB_WAIT
         # If we pass the hard limit, also check the per-runner count
         if job.job_runner_name in self.app.config.job_limits:
             # Cache the job count if necessary
             if job.job_runner_name not in self.user_job_count_per_runner:
                 self.user_job_count_per_runner[job.job_runner_name] = {}
                 query_url, limit = self.app.config.job_limits[
                     job.job_runner_name]
                 base_query = select([model.Job.table.c.user_id, model.Job.table.c.job_runner_name, func.count(model.Job.table.c.user_id).label('job_count')]) \
                             .where(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING))) \
                             .group_by(model.Job.table.c.user_id, model.Job.table.c.job_runner_name)
                 if '%' in query_url or '_' in query_url:
                     subq = base_query.having(
                         model.Job.table.c.job_runner_name.like(
                             query_url)).alias('subq')
                     query = self.sa_session.execute(
                         select([
                             subq.c.user_id,
                             func.sum(subq.c.job_count).label('job_count')
                         ]).group_by(subq.c.user_id))
                 else:
                     query = self.sa_session.execute(
                         base_query.having(model.Job.table.c.job_runner_name
                                           == query_url))
                 for row in query:
                     self.user_job_count_per_runner[job.job_runner_name][
                         row['user_id']] = row['job_count']
             if self.user_job_count_per_runner[job.job_runner_name].get(
                     job.user_id, 0) >= self.app.config.job_limits[
                         job.job_runner_name][1]:
                 return JOB_WAIT
     elif job.galaxy_session:
         # Anonymous users only get the hard limit
         if self.app.config.anonymous_user_job_limit:
             count = self.sa_session.query( model.Job ).enable_eagerloads( False ) \
                         .filter( and_( model.Job.session_id == job.galaxy_session.id,
                                        or_( model.Job.state == model.Job.states.RUNNING,
                                             model.Job.state == model.Job.states.QUEUED ) ) ).count()
             if count >= self.app.config.anonymous_user_job_limit:
                 return JOB_WAIT
     else:
         log.warning(
             'Job %s is not associated with a user or session so job concurrency limit cannot be checked.'
             % job.id)
     return JOB_READY
Пример #20
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
Пример #21
0
    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
Пример #22
0
    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
Пример #23
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
Пример #24
0
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,
        traffic_top_table=TrafficTopTable(
            data_url=url_for("user.json_users_highest_traffic"),
            table_args={
                'data-page-size': 10,
                'data-search': 'false'
            },
        ))
Пример #25
0
 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()
Пример #26
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
Пример #27
0
 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
Пример #28
0
 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()
Пример #29
0
def home_page():
	query   = Package.query.filter_by(approved=True, soft_deleted=False)
	count   = query.count()
	new     = query.order_by(db.desc(Package.created_at)).limit(8).all()
	pop_mod = query.filter_by(type=PackageType.MOD).order_by(db.desc(Package.score)).limit(8).all()
	pop_gam = query.filter_by(type=PackageType.GAME).order_by(db.desc(Package.score)).limit(4).all()
	pop_txp = query.filter_by(type=PackageType.TXP).order_by(db.desc(Package.score)).limit(4).all()
	downloads = db.session.query(func.sum(PackageRelease.downloads)).first()[0]
	return render_template("index.html", count=count, downloads=downloads, \
			new=new, pop_mod=pop_mod, pop_txp=pop_txp, pop_gam=pop_gam)
Пример #30
0
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
Пример #31
0
def test_life_cycle_daily_total(db: me_db.Database):
    """Test that the sum of all measurements for a day is <= 24 hours."""
    with db.Session() as s:
        q = s.query(func.DATE(me_db.Measurement.date).label('date'),
                    (func.sum(me_db.Measurement.value)).label('time')) \
          .filter(me_db.Measurement.source == 'LifeCycle') \
          .group_by(me_db.Measurement.date)
        df = pd.read_sql(q.statement, q.session.bind)

        assert df[df.time > MILLISECONDS_IN_A_DAY].empty
Пример #32
0
    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 math.floor(done * 100 / total) if total != 0 else 0
Пример #33
0
    def set_event_on_days(self, day_event: DayEvent, days_duration: list):
        """ Set an event on several days each time with a specific duration.

        :param day_event:
        :param days_duration: An array of pairs. Each pair is (date, duration). Each date
         must be unique.
        :return:
        """

        day_max = date(1980, 1, 1)
        day_min = date(2050, 12, 31)

        mainlog.debug("set_event_on_days")
        mainlog.debug(days_duration)

        for day, duration in days_duration:
            day_min = min(day_min, day)
            day_max = max(day_max, day)

        db_events = session().query(DayEvent).filter(
            and_(DayEvent.employee_id == day_event.employee_id,
                 DayEvent.event_type == day_event.event_type,
                 DayEvent.date.between(day_min, day_max))).all()

        db_events_dates = dict(zip([e.date for e in db_events], db_events))

        other_db_events = session().query(DayEvent.date,
                                          func.sum(DayEvent.duration).label("duration_sum")).\
            filter( and_( DayEvent.employee_id == day_event.employee_id,
                          DayEvent.event_type != day_event.event_type,
                          DayEvent.date.between(day_min, day_max))).\
            group_by(DayEvent.date).all()

        other_db_events_dates = dict([(e.date, e.duration_sum)
                                      for e in other_db_events])

        for day, duration in days_duration:
            if day in other_db_events_dates and other_db_events_dates[
                    day] + duration > 1:
                raise ServerException(ServerErrors.too_much_off_time_on_a_day,
                                      date_to_dmy(day))

            if day in db_events_dates:
                # Replace the old duration
                db_event = db_events_dates[day]
                db_event.duration = duration
            else:
                nu_event = DayEvent()
                nu_event.date = day
                nu_event.duration = duration
                nu_event.event_type = day_event.event_type
                nu_event.employee_id = day_event.employee_id
                session().add(nu_event)

        session().commit()
Пример #34
0
    def get_compare_avg_cpu_load_data_for_test_ids(self, test_id_1, test_id_2):

        test_1_name = self.execute_statement(
            select([tests.c.display_name]).where(tests.c.id == test_id_1),
            False)[0][0]
        test_2_name = self.execute_statement(
            select([tests.c.display_name]).where(tests.c.id == test_id_2),
            False)[0][0]

        if test_1_name == test_2_name:
            test_1_name += '_1'
            test_2_name += '_2'

        st1 = select([tests_monitoring_data.c.server_name,
            func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_1'),
                      literal(0).label('CPU_LOAD_2'),
                      ])\
            .where(tests_monitoring_data.c.test_id == tests.c.id)\
            .where(tests.c.id == test_id_1).group_by(tests_monitoring_data.c.server_name)

        st2 = select([
                      tests_monitoring_data.c.server_name,
                      literal(0).label('CPU_LOAD_1'),
                      func.avg(tests_monitoring_data.c.CPU_user+tests_monitoring_data.c.CPU_system+tests_monitoring_data.c.CPU_iowait).label('CPU_LOAD_2')]) \
            .where(tests_monitoring_data.c.test_id == tests.c.id) \
            .where(tests.c.id == test_id_2).group_by(tests_monitoring_data.c.server_name)

        #s1 = st1.alias('s1')
        #s2 = st2.alias('s2')

        qt = union_all(st1, st2).alias("united")

        qr = select([
            qt.c.server_name,
            func.sum(qt.c.CPU_LOAD_1).label(test_1_name),
            func.sum(qt.c.CPU_LOAD_2).label(test_2_name)
        ]).group_by(qt.c.server_name)

        #statement = select([s1.c.server_name,s1.c.CPU_LOAD.label(test_1_name),s2.c.CPU_LOAD.label(test_2_name)])\
        #    .where(s1.c.server_name==s2.c.server_name)

        return self.execute_statement(qr, True)
Пример #35
0
 def build_query(self, name_field, id_field, subquery_where_guid=None):
     fao = self.build_sub_query(where_guid=subquery_where_guid).alias()
     query = select([name_field.label(Constants.NAME),
                     id_field.label(Constants.ID),
                     fao.columns[Constants.ASMT_SUBJECT].label(Constants.ASMT_SUBJECT),
                     fao.columns[Constants.LEVEL].label(Constants.LEVEL),
                     func.sum(fao.columns[Constants.TOTAL]).label(Constants.TOTAL)],
                    from_obj=[fao.join(self._dim_inst_hier, self._dim_inst_hier.c.inst_hier_rec_id == fao.c.inst_hier_rec_id)])\
         .group_by(Constants.NAME, Constants.ID, Constants.ASMT_SUBJECT, Constants.LEVEL)\
         .order_by(Constants.NAME)
     return query
Пример #36
0
 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)
Пример #37
0
    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()
        )
Пример #38
0
def compute_statistics():
    n_all = Question.query.count()

    # Partial questions
    n_partial_query = Question.query.filter(
        db.and_(Question.n_answers > 0, Question.n_answers < MAX_ANSWERS))
    n_partial = n_partial_query.count()
    partial_questions_array = [0, 0, 0]
    all_partial = n_partial_query.all()
    for question in all_partial:
        partial_questions_array[question.n_answers - 1] += 1

    n_full_questions = Question.query.filter(
        Question.n_answers == MAX_ANSWERS).count()

    # Partial examples
    n_full_examples, partial_examples = count_full_examples()
    partial_examples_array = [0, 0, 0, 0, 0]
    for example in partial_examples:
        partial_examples_array[5 - example[1]] += 1

    # Non-empty and non-full examples:
    questions_not_empty = Question.query.filter(Question.n_answers > 0).all()
    examples_not_empty = set()
    for q in questions_not_empty:
        examples_not_empty.add(q.example)
    examples_not_empty = list(examples_not_empty)
    examples_n_answers = []
    for example in examples_not_empty:
        sum_answers = Question.query.filter(
            Question.example == example).with_entities(
                func.sum(Question.n_answers)).scalar()
        # Do not add examples that have been fully answered
        if not sum_answers == MAX_ANSWERS * (N_MODELS * (N_MODELS - 1) / 2):
            examples_n_answers += [[example, sum_answers]]

    n_answers = Answer.query.count()
    n_participants = User.query.count()
    non_zero_participants = User.query.join(Answer).filter(
        func.length(User.answers) > 0).all()

    print "Total", n_all
    print "Full examples:", n_full_examples
    print "Partial examples:", len(partial_examples), ', distribution:', zip([
        '1 question:', '2 questions:', '3 questions:', '4 questions:',
        '5 questions:'
    ], partial_examples_array)
    print "Non-empty examples:", len(examples_n_answers)
    print "Full questions: ", n_full_questions
    print "Partial questions:", n_partial, ', distribution:', zip(
        ['1 answer:', '2 answers:', '3 answers:'], partial_questions_array)
    print 'Total answers:', n_answers
    print 'Total participants:', n_participants, "(non-zero:", len(
        non_zero_participants), ")"
Пример #39
0
def home():
    def join(query):
        return query.options(joinedload(Package.license),
                             joinedload(Package.media_license))

    query = Package.query.filter_by(state=PackageState.APPROVED)
    count = query.count()

    featured = query.filter(Package.tags.any(name="featured")).order_by(
        func.random()).limit(6).all()

    new = join(query.order_by(db.desc(Package.approved_at))).limit(4).all()
    pop_mod = join(
        query.filter_by(type=PackageType.MOD).order_by(db.desc(
            Package.score))).limit(8).all()
    pop_gam = join(
        query.filter_by(type=PackageType.GAME).order_by(db.desc(
            Package.score))).limit(8).all()
    pop_txp = join(
        query.filter_by(type=PackageType.TXP).order_by(db.desc(
            Package.score))).limit(8).all()
    high_reviewed = join(query.order_by(db.desc(Package.score - Package.score_downloads))) \
      .filter(Package.reviews.any()).limit(4).all()

    updated = db.session.query(Package).select_from(PackageRelease).join(Package) \
      .filter_by(state=PackageState.APPROVED) \
      .order_by(db.desc(PackageRelease.releaseDate)) \
      .limit(20).all()
    updated = updated[:4]

    reviews = PackageReview.query.filter_by(recommends=True).order_by(
        db.desc(PackageReview.created_at)).limit(5).all()

    downloads_result = db.session.query(func.sum(
        Package.downloads)).one_or_none()
    downloads = 0 if not downloads_result or not downloads_result[
        0] else downloads_result[0]

    tags = db.session.query(func.count(Tags.c.tag_id), Tag) \
     .select_from(Tag).outerjoin(Tags).group_by(Tag.id).order_by(db.asc(Tag.title)).all()

    return render_template("index.html",
                           count=count,
                           downloads=downloads,
                           tags=tags,
                           featured=featured,
                           new=new,
                           updated=updated,
                           pop_mod=pop_mod,
                           pop_txp=pop_txp,
                           pop_gam=pop_gam,
                           high_reviewed=high_reviewed,
                           reviews=reviews)
    def get_total(self, filter_request: FilterRequest) -> float:
        """Gets the total of all transaction matching the provided filters.

        :param filter_request: the filter request
        :return: the total
        """
        query = self.__entity_manager.query(
            label('total', func.sum(TransactionDbo.amount)))
        query = self.__query_builder.build(query, filters=filter_request)
        logging.debug(query)
        total = query.scalar()
        return 0 if total is None else total
Пример #41
0
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}
Пример #42
0
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'])
            r = session\
                .query(StockType, func.sum(StockOut.qty) / behind.days)\
                .join(StockItem)\
                .join(StockOut)\
                .options(lazyload(StockType.department),
                         lazyload(StockType.unit),
                         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)\
                .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', {
        'nav': [("Buying list", info.reverse("tillweb-stockcheck"))],
        'form': form,
        'buylist': buylist,
    })
Пример #43
0
 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
Пример #44
0
    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)
Пример #45
0
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
Пример #46
0
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)
Пример #47
0
 def _query(self, id, site_id, date, date1, date2):
     query = db.session.query(func.sum(RankModel.rank))
     query = query.join(PageModel, RankModel.page == PageModel.id)
     query = query.join(SiteModel, PageModel.site_id == SiteModel.id)
     query = query.join(PersonModel, RankModel.person == PersonModel.id)
     if date1 and date2:
         return query.filter(RankModel.person == id,
                             SiteModel.id == site_id,
                             func.DATE(PageModel.scan) >= date1,
                             func.DATE(PageModel.scan) <= date2)
     if date:
         return query.filter(RankModel.person == id,
                             SiteModel.id == site_id,
                             func.DATE(PageModel.scan) == date)
     return query.filter(RankModel.person == id, SiteModel.id == site_id)
Пример #48
0
    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
Пример #49
0
def _resource_consumption_stats_view():
    time_difference_expr = func.sum(KojiTask.finished - KojiTask.started)
    time_difference = extract('EPOCH', time_difference_expr)
    time_difference_all = select([time_difference]).select_from(KojiTask)
    return (select([
        Package.name,
        KojiTask.arch,
        time_difference_expr.label('time'),
        cast(time_difference / time_difference_all,
             Float).label('time_percentage'),
    ]).select_from(
        join(
            join(Package, Build, Package.id == Build.package_id),
            KojiTask,
        )).group_by(Package.name, KojiTask.arch))
Пример #50
0
def order_update():
    this_week = Week.query.filter(Week.current is True).first()
    the_orders = Order.query.filter(Order.user_id == current_user.id and Order.week == this_week.id).all()
    the_order_ids = [i.id for i in the_orders]
    if request.method == 'GET':
        obj = {'items': [], 'old_orders': []}
        the_items = Item.query.filter(Item.active is True, Item.week_id == this_week.id).order_by(asc(Item.user_id)).all()
        for i in range(len(the_items)):
            gone = 0
            for order in the_items[i].orders:
                gone += order.amount
            obj['items'].append({'name': the_items[i].name, 'description': the_items[i].description, 'price': the_items[i].price, 'units': the_items[i].unit, 'available': the_items[i].max_available - gone, 'farmer': the_items[i].user.username, 'id': the_items[i].id})
        for i in range(len(the_orders)):
            obj['old_orders'].append({'name': the_orders[i].item.name, 'quantity': the_orders[i].amount, 'units': the_orders[i].item.unit, 'price': the_orders[i].item.price, 'id': the_orders[i].id, 'item_id': the_orders[i].item_id})
        return json.dumps(obj)
    elif request.method == 'POST':
        orders = request.get_json(force=True)
        order_ids = []
        for i in orders:
            try:
                order_ids.append(i['id'])
            except:
                pass
        for order in orders:
            gone = db.session.query(func.sum(Order.amount)).filter(Order.week_id == this_week.id, Order.item_id == order['item_id']).first()[0]
            if 'id' in order:
                this_quan = Order.query.get(order['id']).amount
            else:
                this_quan = 0
            if not gone:
                gone = 0
            if not this_quan:
                this_quan = 0
            left = float(Item.query.get(order['item_id']).max_available) - gone + float(this_quan)
            order['quantity'] = min(left, float(order['quantity']))
            new = Order(this_week.id, order['item_id'], order['quantity'], current_user.id)
            try:
                new.id = order['id']
            except:
                pass
            db.session.merge(new)
            db.session.commit()
        for order in the_orders:
            if order.id not in order_ids:
                db.session.delete(order)
                db.session.commit()
        response = {'message': 'Your Order has been placed', 'priority': 'success', 'items': []}
        return json.dumps(response)
Пример #51
0
def get_idea_published_tag(idea_id):
    tag_ids = [elt[0] for elt in
               session.query(TagData.id).outerjoin(TagData.ideas).filter(
                   IdeaData.id == idea_id)]

    result = (session.query(
        TagData.label,
        func.sum(StateData.label.in_(get_workflow().get_published_states())).label(
            'ideas_count')
    ).outerjoin(TagData.ideas))

    result = (result.outerjoin(IdeaData.wf_context)
              .outerjoin(IdeaWFContextData.state)
              .filter(TagData.id.in_(tag_ids))
              .group_by(TagData.label))

    return result
Пример #52
0
def business_totals(session, firstday, lastday):
    # This query is wrong in that it ignores the 'business' field in
    # VatRate objects.  Fixes that don't involve a database round-trip
    # per session are welcome!
    return (
        session.query(Business, func.sum(Transline.items * Transline.amount))
        .join(VatBand)
        .join(Department)
        .join(Transline)
        .join(Transaction)
        .join(Session)
        .filter(Session.date <= lastday)
        .filter(Session.date >= firstday)
        .order_by(Business.id)
        .group_by(Business)
        .all()
    )
Пример #53
0
 def testName(self):
     date1 = datetime.date(2009, 5, 23)
     date2 = datetime.date(2010, 5, 23)
     date3 = datetime.date(2011, 5, 23)
     date4 = datetime.date(2013, 5, 23)
     date5 = datetime.date(2014, 5, 23)
     with self.control._session_context as session:
         deal = model.Deal()
         session.add(deal)
         deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
         deal.cashflows.append(model.Cashflow(amount=Decimal(15), settlement_date = date1))
         deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date1))
         deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date2))
         deal.cashflows.append(model.Cashflow(amount=Decimal(-15), settlement_date = date4))
         deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(25), settlement_date = date4))
         deal.forward_cashflows.append(model.ForwardCashflow(amount=Decimal(75), settlement_date = date5))
         deal.holdings.append(model.Holding(current_value=Decimal(10)))
         deal.holdings.append(model.Holding(current_value=Decimal(-5)))
         session.commit()
         
         s = session.query(func.sum(model.Cashflow.amount), func.avg(model.Cashflow.amount))
         s = s.filter(model.Cashflow.amount == Decimal(15))
         print s
         #s.filter(model.Cashflow.amount == Decimal(15))
         #print s
         r = session.execute(s)
         id = deal.id
         cash_flows = []
         #for row in r:
             #print row
         back = session.query(model.Cashflow.settlement_date.label("settlement_date"),model.Cashflow.amount.label("amount")).\
             filter(model.Cashflow.deal_id==deal.id)
         forward = session.query(model.ForwardCashflow.settlement_date.label("settlement_date"), model.ForwardCashflow.amount.label("amount")).\
             filter(model.ForwardCashflow.deal_id==deal.id)
         cash = back.union_all(forward).order_by(literal_column("settlement_date").asc(), literal_column("amount").asc())
         for c in cash:
             print c
             cash_flows.append(c)
         irr = IRRCalculator(Decimal('0.10'), cash_flows)
         irr2 = IRRCalculator(Decimal('0.10'), [])
         #print irr.theIRR
         #print irr2.theIRR
         mm = MMCalculator(cash_flows)
         print mm.theMM
         print mm.errors
Пример #54
0
    def get_turnover(cls, company, year):
        """
        Compute the annual turnover for a given company
        """
        from autonomie.models.task import (
            Task,
        )
        query = DBSESSION.query(func.sum(Task.ht))
        query = query.filter(Task.company_id == company.id)
        query = query.filter(func.year(Task.date) == year)
        query = query.filter(Task.status == 'valid')

        invoice_sum = query.filter(
            Task.type_.in_(('invoice', 'cancelinvoice'))
        ).first()[0]
        if invoice_sum is None:
            invoice_sum = 0
        return invoice_sum
Пример #55
0
    def scoreboard(self, format='html'):
        """
        A list of users ordered by their score. The score is computed by
        by assigning every dataset a score (10 divided by no. of maintainers)
        and then adding that score up for all maintainers.

        This does give users who maintain a single dataset a higher score than
        those who are a part of a maintenance team, which is not really what
        we want (since that rewards single points of failure in the system).

        But this is an adequate initial score and this will only be accessible
        to administrators (who may be interested in findin these single points
        of failures).
        """

        # If user is not an administrator we abort
        if not (c.account and c.account.admin):
            abort(403, _("You are not authorized to view this page"))

        # Assign scores to each dataset based on number of maintainers
        score = db.session.query(Dataset.id,
                                 (10 / func.count(Account.id)).label('sum'))
        score = score.join('managers').group_by(Dataset.id).subquery()

        # Order users based on their score which is the sum of the dataset
        # scores they maintain
        user_score = db.session.query(
            Account.name, Account.email,
            func.coalesce(func.sum(score.c.sum), 0).label('score'))
        user_score = user_score.outerjoin(Account.datasets).outerjoin(score)
        user_score = user_score.group_by(Account.name, Account.email)
        # We exclude the system user
        user_score = user_score.filter(Account.name != 'system')
        user_score = user_score.order_by(desc('score'))

        # Fetch all and assign to a context variable score and paginate them
        # We paginate 42 users per page, just because that's an awesome number
        scores = user_score.all()
        c.page = templating.Page(scores, items_per_page=42,
                                 item_count=len(scores),
                                 **request.params)

        return templating.render('account/scoreboard.html')
Пример #56
0
 def __check_user_jobs( self, job ):
     if job.user:
         # Check the hard limit first
         if self.app.config.registered_user_job_limit:
             # Cache the job count if necessary
             if not self.user_job_count:
                 query = self.sa_session.execute(select([model.Job.table.c.user_id, func.count(model.Job.table.c.user_id)]) \
                         .where(and_(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING)), (model.Job.table.c.user_id is not None))) \
                         .group_by(model.Job.table.c.user_id))
                 for row in query:
                     self.user_job_count[row[0]] = row[1]
             if self.user_job_count.get(job.user_id, 0) >= self.app.config.registered_user_job_limit:
                 return JOB_WAIT
         # If we pass the hard limit, also check the per-runner count
         if job.job_runner_name in self.app.config.job_limits:
             # Cache the job count if necessary
             if job.job_runner_name not in self.user_job_count_per_runner:
                 self.user_job_count_per_runner[job.job_runner_name] = {}
                 query_url, limit = self.app.config.job_limits[job.job_runner_name]
                 base_query = select([model.Job.table.c.user_id, model.Job.table.c.job_runner_name, func.count(model.Job.table.c.user_id).label('job_count')]) \
                             .where(model.Job.table.c.state.in_((model.Job.states.QUEUED, model.Job.states.RUNNING))) \
                             .group_by(model.Job.table.c.user_id, model.Job.table.c.job_runner_name)
                 if '%' in query_url or '_' in query_url:
                     subq = base_query.having(model.Job.table.c.job_runner_name.like(query_url)).alias('subq')
                     query = self.sa_session.execute(select([subq.c.user_id, func.sum(subq.c.job_count).label('job_count')]).group_by(subq.c.user_id))
                 else:
                     query = self.sa_session.execute(base_query.having(model.Job.table.c.job_runner_name == query_url))
                 for row in query:
                     self.user_job_count_per_runner[job.job_runner_name][row['user_id']] = row['job_count']
             if self.user_job_count_per_runner[job.job_runner_name].get(job.user_id, 0) >= self.app.config.job_limits[job.job_runner_name][1]:
                 return JOB_WAIT
     elif job.galaxy_session:
         # Anonymous users only get the hard limit
         if self.app.config.anonymous_user_job_limit:
             count = self.sa_session.query( model.Job ).enable_eagerloads( False ) \
                         .filter( and_( model.Job.session_id == job.galaxy_session.id,
                                        or_( model.Job.state == model.Job.states.RUNNING,
                                             model.Job.state == model.Job.states.QUEUED ) ) ).count()
             if count >= self.app.config.anonymous_user_job_limit:
                 return JOB_WAIT
     else:
         log.warning( 'Job %s is not associated with a user or session so job concurrency limit cannot be checked.' % job.id )
     return JOB_READY
Пример #57
0
def _resource_consumption_stats_view():
    time_difference_expr = func.sum(KojiTask.finished - KojiTask.started)
    time_difference = extract('EPOCH', time_difference_expr)
    time_difference_all = select([time_difference]).select_from(KojiTask)
    return (
        select([
            Package.name,
            KojiTask.arch,
            time_difference_expr.label('time'),
            cast(time_difference / time_difference_all, Float).label('time_percentage'),
        ])
        .select_from(
            join(
                join(Package, Build, Package.id == Build.package_id),
                KojiTask,
            )
        )
        .group_by(Package.name, KojiTask.arch)
    )