def profile_landing():
total = Product.query.filter_by(user_id=current_user.id).count()
available = Product.query.filter_by(user_id=current_user.id, status='available').count()
expected = db.session.query(func.sum(Product.price).label('Sum')).filter_by(user_id=current_user.id, status='available').scalar()
current = db.session.query(func.sum(Product.price).label('Sum')).filter_by(user_id=current_user.id, status='sold').scalar()
return render_template('profile.html', title='Profile', page='profile_landing', total=total, available=available,
expected=expected, current=current, items_in_cart=get_num_items())
def total(deck_id):
issuer = session.query(Deck).filter(Deck.id == deck_id).first().issuer
short_id = deck_id[0:10]
balances = []
Balances = session.query(Balance).filter(Balance.short_id == short_id)
Issued = Balances.filter(Balance.account.contains(issuer)).filter(
func.char_length(Balance.account) > 34)
Accounts = Balances.filter(func.char_length(Balance.account) == 34)
#Calling abs() on this function produces a "Bad operand type for abs(): NoneType error"
#issued = abs( Issued.with_entities(func.sum(Balance.value)).scalar() )
#Do a check for when supply is returned null
#If supply is not null, return abs(issued)
issued = Issued.with_entities(func.sum(Balance.value)).scalar()
total = Accounts.with_entities(func.sum(Balance.value)).scalar()
if (issued):
if (abs(issued) == total):
return jsonify({'supply': abs(issued)})
else:
return jsonify({
'Error:': 'Invalid card transfers found in database',
'Total issued:': abs(issued),
'Total valid:': total
})
else:
return jsonify({'Error:': 'No cards found for this deck.'})
def upgrade():
op.add_column(
'request',
sa.Column('payout',
sa.Numeric(precision=15, scale=2),
index=True,
nullable=True))
bind = op.get_bind()
absolute = select([abs_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(abs_table, mod_table,
mod_table.c.id == abs_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
relative = select([rel_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(rel_table, mod_table,
mod_table.c.id == rel_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
abs_sum = select([request.c.id.label('request_id'),
request.c.base_payout.label('base_payout'),
func.sum(absolute.c.value).label('sum')])\
.select_from(outerjoin(request, absolute,
request.c.id == absolute.c.request_id))\
.group_by(request.c.id)\
.alias()
rel_sum = select([request.c.id.label('request_id'),
func.sum(relative.c.value).label('sum')])\
.select_from(outerjoin(request, relative,
request.c.id == relative.c.request_id))\
.group_by(request.c.id)\
.alias()
total_sum = select([abs_sum.c.request_id.label('request_id'),
((
abs_sum.c.base_payout +
case([(abs_sum.c.sum == None, Decimal(0))],
else_=abs_sum.c.sum)) *
(
1 +
case([(rel_sum.c.sum == None, Decimal(0))],
else_=rel_sum.c.sum))).label('payout')])\
.select_from(join(abs_sum, rel_sum,
abs_sum.c.request_id == rel_sum.c.request_id))
payouts = bind.execute(total_sum)
for request_id, payout in payouts:
up = update(request).where(request.c.id == request_id).values(
payout=payout)
bind.execute(up)
op.alter_column('request',
'payout',
nullable=False,
existing_type=sa.Numeric(precision=15, scale=2))
def get(self, group_id):
group = Group.query.filter_by(id=group_id).first()
if group is None:
return {'message': 'No such group'}
members = (db.session.query(
User.name, User.id,
func.sum(Task.value).label('points'), User.password,
User.balance).filter_by(
group=group_id).outerjoin(Journal).outerjoin(Task).group_by(
User.id).order_by(func.sum(Task.value).desc()))
group.members = _all(members)
return group.toJSON()
def get_quantity(location, product):
added_products = Movement.query.\
filter(Movement.to_location_id==location,Movement.product_id==product)\
.from_self(func.sum(Movement.quantity,)).all()
added_products = added_products[0][0]
if added_products == None:
added_products = 0
removed_products = Movement.query.\
filter(Movement.from_location_id==location,Movement.product_id==product)\
.from_self(func.sum(Movement.quantity,name="removed")).all()
removed_products = removed_products[0][0]
if removed_products == None:
removed_products = 0
return added_products - removed_products
def upgrade():
op.add_column('request',
sa.Column('payout', sa.Numeric(precision=15, scale=2), index=True,
nullable=True))
bind = op.get_bind()
absolute = select([abs_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(abs_table, mod_table,
mod_table.c.id == abs_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
relative = select([rel_table.c.value.label('value'),
mod_table.c.request_id.label('request_id')])\
.select_from(join(rel_table, mod_table,
mod_table.c.id == rel_table.c.id))\
.where(mod_table.c.voided_user_id == None)\
.alias()
abs_sum = select([request.c.id.label('request_id'),
request.c.base_payout.label('base_payout'),
func.sum(absolute.c.value).label('sum')])\
.select_from(outerjoin(request, absolute,
request.c.id == absolute.c.request_id))\
.group_by(request.c.id)\
.alias()
rel_sum = select([request.c.id.label('request_id'),
func.sum(relative.c.value).label('sum')])\
.select_from(outerjoin(request, relative,
request.c.id == relative.c.request_id))\
.group_by(request.c.id)\
.alias()
total_sum = select([abs_sum.c.request_id.label('request_id'),
((
abs_sum.c.base_payout +
case([(abs_sum.c.sum == None, Decimal(0))],
else_=abs_sum.c.sum)) *
(
1 +
case([(rel_sum.c.sum == None, Decimal(0))],
else_=rel_sum.c.sum))).label('payout')])\
.select_from(join(abs_sum, rel_sum,
abs_sum.c.request_id == rel_sum.c.request_id))
payouts = bind.execute(total_sum)
for request_id, payout in payouts:
up = update(request).where(request.c.id == request_id).values(
payout=payout)
bind.execute(up)
op.alter_column('request', 'payout', nullable=False,
existing_type=sa.Numeric(precision=15, scale=2))
def pay_incoming(incoming_id):
incoming_model = Incoming.query.filter_by(id=incoming_id, paid=False).first()
if not incoming_model:
flash(u'Поступления: %s не существует или уже оплачено' % incoming_id, 'danger')
else:
incoming_model.paid = True
Goods.query.filter_by(incoming_id=incoming_id).update({'paid': 1})
total = Goods.query.with_entities(func.sum(Goods.incoming_price).label('sum')).\
filter_by(incoming_id=incoming_id).first()
action = AccountActions(account_id=incoming_model.account_id,
document_id=incoming_model.document_id,
action_type='outgoing',
amount=total.sum,
datetime=incoming_model.incoming_date,
incoming_id=incoming_model.id)
connection.session.add(action)
try:
connection.session.commit()
flash(u'Поступление оплачено', 'success')
except Exception as e:
connection.session.rollback()
flash(u'Ошибка DB: %s' % e.message, 'danger')
return redirect(url_for('b_acc.incomings'))
def sales(page):
data = request_filter(request.args,
filtered=['to_date', 'from_date', 'nomenclature_id', 'attribute_id'],
default=None)
form = SalesFilter(formdata=data)
form.nomenclature_id.choices = [(0, u'')] + [(nom.id, "%d - %s" % (nom.internal_code, nom.name))
for nom in Nomenclatures.query.all()]
form.attribute_id.choices = [(0, u'')] + [(attr.id, attr.name) for attr in Attributes.query.all()]
req = Goods.query.filter(Goods.outgoing_price != None, Goods.outgoing_date != None).\
order_by(Goods.outgoing_date.desc())
sales_sum = Goods.query.with_entities(func.sum(Goods.outgoing_price).label('sum')).\
filter(Goods.outgoing_price != None, Goods.outgoing_date != None)
if form.from_date.data:
req = req.filter(Goods.outgoing_date > form.from_date.data)
sales_sum = sales_sum.filter(Goods.outgoing_date > form.from_date.data)
if form.to_date.data:
req = req.filter(Goods.outgoing_date < form.to_date.data)
sales_sum = sales_sum.filter(Goods.outgoing_date < form.to_date.data)
if form.nomenclature_id.data:
req = req.filter(Goods.nomenclature_id == form.nomenclature_id.data)
sales_sum = sales_sum.filter(Goods.nomenclature_id == form.nomenclature_id.data)
if form.attribute_id.data:
req = req.filter(Goods.attribute_id == form.attribute_id.data)
sales_sum = sales_sum.filter(Goods.attribute_id == form.attribute_id.data)
pagination = req.paginate(page, 10)
return render_template('b_acc/sales.html',
pagination=pagination,
sum=sales_sum.first().sum,
form=form)
def selectables(cls, bag, agg_spec):
""" Create a list of statements from spec
:type bag: mongosql.bag.ModelPropertyBags
:rtype: list[sqlalchemy.sql.elements.ColumnElement]
"""
# TODO: calculation expressions for selection: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/
selectables = []
for comp_field, comp_expression in agg_spec.items():
# Column reference
if isinstance(comp_expression, basestring):
selectables.append(bag.columns[comp_expression].label(comp_field))
continue
# Computed expression
assert isinstance(comp_expression, dict), 'Aggregate: Expression should be either a column name, or an object'
assert len(comp_expression) == 1, 'Aggregate: expression can only contain a single operator'
operator, expression = comp_expression.popitem()
# Expression statement
if isinstance(expression, int) and operator == '$sum':
# Special case for count
expression_stmt = expression
elif isinstance(expression, basestring):
# Column name
expression_stmt = bag.columns[expression]
# Json column?
if bag.columns.is_column_json(expression):
# PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
expression_stmt = cast(expression_stmt, Float)
elif isinstance(expression, dict):
# Boolean expression
expression_stmt = MongoCriteria.statement(bag, expression)
# Need to cast it to int
expression_stmt = cast(expression_stmt, Integer)
else:
raise AssertionError('Aggregate: expression should be either a column name, or an object')
# Operator
if operator == '$max':
comp_stmt = func.max(expression_stmt)
elif operator == '$min':
comp_stmt = func.min(expression_stmt)
elif operator == '$avg':
comp_stmt = func.avg(expression_stmt)
elif operator == '$sum':
if isinstance(expression_stmt, int):
# Special case for count
comp_stmt = func.count()
if expression_stmt != 1:
comp_stmt *= expression_stmt
else:
comp_stmt = func.sum(expression_stmt)
else:
raise AssertionError('Aggregate: unsupported operator "{}"'.format(operator))
# Append
selectables.append(comp_stmt.label(comp_field))
return selectables
def get(self, group_id, user_id):
journal = (db.session.query(Journal.date,
func.sum(Task.value).label('points'))
.join(Task)
.join(User)
.filter_by(id=user_id)
.group_by(Journal.date)
.order_by(Journal.date))
return {'user': user_id,'gist': _all(journal)}
def query_aggregate(self, ctx, drills, cuts, limit=5000):
mappings = self.sql_mappings(ctx)
joins = self.sql_joins(ctx, None)
pk = self.pk(ctx)
connection = self.sqltable.connection.connection()
engine = self.sqltable.connection._engine
# Build query
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
q = session.query()
#q = q.add_columns(self.sqltable.sa_table.columns['is_bot_id'].label("x"))
#q = q.add_entity(self.sqltable.sa_table)
# Include measures
for measure in [m for m in mappings if isinstance(m.field, Measure)]:
sa_column = self.sqltable.sa_table.columns[measure.sqlcolumn.name]
q = q.add_columns(func.avg(sa_column).label(measure.field.name + "_avg"))
q = q.add_columns(func.sum(sa_column).label(measure.field.name + "_sum"))
q = q.add_columns(func.count(self.sqltable.sa_table).label("record_count"))
# Drills
for dimension in [m for m in mappings if isinstance(m.field, Dimension)]:
# We shoulld check the dimension-path here, with drills, and use key/lookup for drill
if dimension.field.name in drills:
sa_column = None
try:
sa_column = self.sqltable.sa_table.columns[dimension.sqlcolumn.name]
except KeyError as e:
raise ETLException("Unknown column in backend SQL table (table=%s, column=%s). Columns: %s" % (self.sqltable.sa_table, dimension.sqlcolumn.name, [c.name for c in self.sqltable.sa_table.columns]))
q = q.add_columns(sa_column)
q = q.group_by(sa_column)
# Cuts
# TODO: Filterng on any dimension attribute, not only the key
# (ie filter cities with type A or icon B), but then again
# that could be a different (nested) dimension.
for dimension in [m for m in mappings if isinstance(m.field, Dimension)]:
# We shoulld check the dimension-path here, with drills
if dimension.field.name in cuts.keys():
sa_column = self.sqltable.sa_table.columns[dimension.sqlcolumn.name]
cut_value = cuts[dimension.field.name]
q = q.filter(sa_column==cut_value)
# Limit
q = q.limit(5000)
statement = q.statement
logger.debug("Statement: %s", str(statement).replace("\n", " "))
rows = connection.execute(statement).fetchall()
return rows
def getQuantity(location, product):
incoming = ProductMovement.query.filter(
ProductMovement.toLocationId == location,
ProductMovement.productId == product).from_self(
func.sum(ProductMovement.productQuantity, )).all()
outgoing = ProductMovement.query.filter(
ProductMovement.fromLocationId == location,
ProductMovement.productId == product).from_self(
func.sum(ProductMovement.productQuantity, )).all()
if incoming[0][0] == None:
incoming = 0
else:
incoming = incoming[0][0]
if outgoing[0][0] == None:
outgoing = 0
else:
outgoing = outgoing[0][0]
available = incoming - outgoing
return available if available >= 0 else 0
async def get_total_user_spent_time_subactivity(user_id, chat_id,
subactivity_id) -> timedelta:
result = await db.select([func.sum(Activity.duration)]).where(
and_(Activity.user_id == user_id, Activity.chat_id == chat_id,
Activity.subactivity == subactivity_id)).gino.scalar()
if result is None:
return timedelta()
else:
return result
def cumm_farmer():
# data = Farmer.query.all()
start_date = date.today().replace(day=1)
#end_date = datetime.strptime(form.end_date.data, "%m/%d/%Y %H:%M %p").date() #
data = db.session.query(
TripPayment.farmer_id,
func.sum(TripPayment.net).label('net'),
func.sum(TripPayment.net * 0.75).label('farmer_loan_amount')).filter(
TripPayment.status == 'NOT PAID').group_by(
TripPayment.farmer_id).all()
colls = Collection.query.all()
return render_template('main/farmer_cumm.html',
data=data,
colls=colls,
start_date=start_date)
def get_cost_leaderboard(self):
with self._session() as session:
sum_size = func.sum(Job.size).label('sum_size')
nick = func.substr(JSONMetadata.started_by, 1, 4).label('nick')
rows = session.query(nick, sum_size)\
.filter(Job.id == JSONMetadata.job_id)\
.group_by(nick)\
.order_by(sum_size.desc())
for row in rows:
yield row.nick, row.sum_size
def accounts(page):
"""
SELECT
accounts.id AS accounts_id,
accounts.name AS accounts_name,
accounts.actived AS accounts_actived,
SUM(anon_1.amount) AS amount
FROM
accounts
LEFT OUTER JOIN
(SELECT
anon_2.account_id AS account_id, anon_2.amount AS amount
FROM
(SELECT
account_actions.account_id AS account_id,
account_actions.amount AS amount
FROM
account_actions
WHERE
account_actions.action_type = 'incoming' UNION ALL SELECT
account_actions.account_id AS account_id,
account_actions.amount * - 1 AS anon_3
FROM
account_actions
WHERE
account_actions.action_type = 'outgoing') AS anon_2) AS anon_1 ON accounts.id = anon_1.account_id
GROUP BY accounts.id
"""
subreq_out = AccountActions.query. \
with_entities(AccountActions.account_id.label('account_id'),
(AccountActions.amount.label('amount') * -1)). \
filter_by(action_type='outgoing')
total = AccountActions.query. \
with_entities(AccountActions.account_id.label('account_id'),
AccountActions.amount.label('amount')). \
filter_by(action_type='incoming'). \
union_all(subreq_out). \
subquery(name='total')
subreq = aliased(total)
accounts_req = Accounts.query. \
with_entities(Accounts.id,
Accounts.name,
Accounts.actived,
func.sum(subreq.c.amount).label('amount')). \
outerjoin(subreq). \
group_by(Accounts.id)
pagination = accounts_req.paginate(page, 10)
return render_template('b_acc/accounts.html', pagination=pagination)
def farmer(id):
farmer = Farmer.query.get_or_404(id)
contract_farmer = db.session.query(FarmerContract.price)
contracts = FarmerContract.query.filter_by(farmer_id=farmer.id)
advances = FarmerAdvance.query.filter_by(farmer_id=farmer.id)
collections = Collection.query.filter_by(farmer_id=farmer.id)
total_advance = db.session.query(
FarmerAdvance.farmer_id,
func.sum(FarmerAdvance.amount * 100).label('total_advance')).group_by(
FarmerAdvance.farmer_id).all()
farmer_cumm = db.session.query(Collection.farmer_id,
func.sum(
Collection.produce_weight)).group_by(
Collection.farmer_id).all()
return render_template('main/farmer.html',
farmer=farmer,
collections=collections,
contracts=contracts,
advances=advances,
total_advance=total_advance,
farmer_cumm=farmer_cumm)
def get_cost_leaderboard(self):
with self._session() as session:
sum_size = func.sum(Job.size).label('sum_size')
nick = func.lower(
func.substr(JSONMetadata.started_by, 1, 4)
).label('nick')
rows = session.query(nick, sum_size)\
.filter(Job.id == JSONMetadata.job_id)\
.group_by(nick)\
.order_by(sum_size.desc())
for row in rows:
yield row.nick, row.sum_size
def product_view(view_id):
view = ProductView.query.get_or_404(view_id)
form = EditProductViewForm(obj=view)
if request.method != "POST":
# Empty form - populate pvps
for pvp in view.product_view_products:
form.pvps.append_entry()
f = form.pvps[-1]
f.product_id.data = pvp.product_id
f.order.data = pvp.order
pvp_dict = {pvp.product_id: pvp for pvp in view.product_view_products}
for f in form.pvps:
pvp = pvp_dict[f.product_id.data]
pvp._field = f
if form.validate_on_submit():
if form.update.data:
view.name = form.name.data
view.type = form.type.data
view.cfp_accepted_only = form.cfp_accepted_only.data
view.vouchers_only = form.vouchers_only.data
for f in form.pvps:
pvp_dict[f.product_id.data].order = f.order.data
else:
for f in form.pvps:
if f.delete.data:
pvp = pvp_dict[f.product_id.data]
db.session.delete(pvp)
db.session.commit()
active_vouchers = Voucher.query.filter_by(view=view).filter(
not_(Voucher.expiry.isnot(None) & (Voucher.expiry < func.now()))
& not_(Voucher.is_used))
stats = {
"active":
active_vouchers.count(),
"total_tickets":
active_vouchers.with_entities(func.sum(
Voucher.tickets_remaining)).scalar(),
}
return render_template("admin/products/view-edit.html",
view=view,
form=form,
voucher_stats=stats)
def cart():
string = ''
try:
items = session['items']
num = len(items)
except KeyError:
flash('No items in Basket')
return redirect(url_for('index'))
if len(session['items']) is 0:
flash('No items in Basket')
return redirect(url_for('index'))
items = session['items']
db_items = Product.query.filter(Product.id.in_(items)).all()
total = db.session.query(func.sum(Product.price).label('Sum')).filter(Product.id.in_(items)).scalar()
return render_template('cart.html', products=db_items, total=total, items_in_cart = len(items), title='Basket | PyraSpace')
def feature_index(company_symbol: str,
metadata_context: BaseContext = Provide[
ApplicationContainer.context_factory]):
with metadata_context.get_session() as session:
query = session.query(
Statistics.company_symbol, Statistics.feature_name,
func.sum(Statistics.row_count).label("row_count")).filter(
Statistics.company_symbol == company_symbol).group_by(
Statistics.company_symbol, Statistics.feature_name)
statistics = query.all()
return jsonify([{
"company_symbol": entry.company_symbol,
"feature_name": entry.feature_name,
"total_row_count": int(entry.row_count)
} for entry in statistics])
def total(metric, task, session):
"""Returns the total value for a duration metric belonging to the
specified task.
:param metric: The :class:`maxify.metrics.Metric` that the duration
is being measured for.
:param task: The :class:`maxify.projects.Task` that the duration is
being measured for.
:param session: The sqlalchemy database session used to query the
datastore.
:return: The total duration as a :class:`datetime.timedelta`.
"""
return session.query(func.sum(Duration.value).label("total"))\
.filter_by(metric_id=metric.id, task_id=task.id).scalar()
async def get_today_total_activity_duration(user_id, chat_id, activity_type_id,
subactivity_id) -> timedelta:
result = await db.select([func.sum(Activity.duration)]).where(
and_(
Activity.user_id == user_id, Activity.chat_id == chat_id,
Activity.activity_type == activity_type_id,
Activity.subactivity == subactivity_id,
Activity.start_time > datetime.combine(
datetime.now(tz=local_tz).date(),
datetime.min.time()).replace(tzinfo=local_tz).astimezone(
tz=tz.UTC).replace(tzinfo=None),
Activity.start_time < datetime.combine(
datetime.now(tz=local_tz).date(),
datetime.max.time()).replace(tzinfo=local_tz).astimezone(
tz=tz.UTC).replace(tzinfo=None))).gino.scalar()
return result
def report_exist():
req = Goods.query.\
with_entities(func.sum(Goods.incoming_price).label('sum')).\
filter(Goods.paid == True)
data = dict()
if request.args.get('on_date', None):
data.update(on_date=request.args.get('on_date', None))
form = ExistFilter(formdata=MultiDict(data))
if form.on_date.data:
req = req.filter(Goods.incoming_date <= form.on_date.data,
or_(Goods.outgoing_date > form.on_date.data,
func.isnull(Goods.outgoing_date)))
else:
req = req.filter(func.isnull(Goods.outgoing_price))
return render_template('b_acc/report_exist.html', sum=req.first().sum, form=form)
def compile(self):
# Remember that there is this special case: { $sum: 1 }
if isinstance(self.expression, int):
# Special case for count
stmt = func.count()
if self.expression != 1:
# When $sum: N, we count N per row. That's multiplication
stmt *= self.expression
else:
# Compile the boolean statement
stmt = self.expression.compile_statement()
# Sum the value of this expression (column, boolean, whatever)
# Need to cast it to int
stmt = cast(stmt, Integer)
# Now, sum it
stmt = func.sum(stmt)
# Done
return self.labeled_expression(stmt)
def incomings(page):
"""
SELECT
incoming.id AS incoming_id,
incoming.incoming_date AS incoming_incoming_date,
(SELECT sum(goods.incoming_price) FROM goods WHERE goods.incoming_id = incoming.id) AS sum,
documents.name AS documents_name
FROM incoming
JOIN documents ON documents.id = incoming.document_id
"""
subreq = Goods.query.with_entities(func.sum(Goods.incoming_price)).filter_by(incoming_id=Incoming.id).subquery()
pagination = Incoming.query.with_entities(Incoming.id,
Incoming.incoming_date,
Incoming.paid,
subreq.as_scalar().label('sum'),
Documents.name).join(Documents).paginate(page, 10)
return render_template('b_acc/incomings.html', pagination=pagination)
def add(money):
# sql = select([select([func.coalesce(func.sum(Transaction.money), 0) + money]).alias('tmp')])
# sql = select([func.sum(Transaction.money).label('g')])
Tr = aliased(Transaction, name="tr")
sql = select([func.coalesce(func.sum(Tr.balance), 0) + money])
# sql = sa_session.query(func.sum(Transaction.money))
# .as_scalar()
# .label('aa')
# print dir(sql)
# print 'sql', sql
tr = Transaction(money=money)
tr.balance = sql
print "tr.created_at0", tr.created_at
# tr.moeny = 4294967295
# tr.balance = -1
sa_session.add(tr)
print "tr.created_at1", tr.created_at
sa_session.commit()
print "tr.created_at2", tr.created_at
def company_index(company_symbol: str,
metadata_context: BaseContext = Provide[
ApplicationContainer.context_factory]):
with metadata_context.get_session() as session:
query = session.query(
Statistics.company_symbol,
func.sum(Statistics.row_count).label("row_count")).filter(
Statistics.company_symbol == company_symbol).group_by(
Statistics.company_symbol)
try:
statistics = query.one()
except NoResultFound:
response = make_response('Cannot find company requested', 404)
return response
return jsonify({
"company_symbol": statistics.company_symbol,
"total_row_count": int(statistics.row_count)
})
def publish_sales(self, channel_id: str, designated_date: datetime.datetime):
"""
ITBカフェの売上高に関するレポートを発行します。
"""
date_from = datetime.datetime(*designated_date.timetuple()[:3])
date_to = date_from + datetime.timedelta(days=1)
amount = self._db_context.session \
.query(func.sum(ShopOrder.price)) \
.filter(ShopOrder.ordered_at >= date_from) \
.filter(ShopOrder.ordered_at < date_to) \
.all()[0][0]
if amount is None:
amount = Decimal("0")
self._slackclient.send_message(
channel_id,
"昨日のITBカフェ売上高は「{:.0f} ITB」でした。"
.format(amount)
)
def get_usage_count(cls, sticker_id, label, user_id=None):
uses = 0
label_id = Label.get_ids([label])
if not label_id:
return uses
label_id, = label_id
if user_id is None:
query = Association.query.filter_by(sticker_id=sticker_id,
label_id=label_id)
uses = query.with_entities(func.sum(Association.uses)).scalar()
else:
select_uses = Association.query.with_entities(Association.uses)
query = select_uses.filter_by(user_id=user_id,
sticker_id=sticker_id,
label_id=label_id)
result = query.first()
if result:
uses, = result
return uses
def publish_grosshappiness(self, channel_id: str, designated_date: datetime.datetime):
"""
いいね！総生産量に関するレポートを発行します。
"""
date_from = datetime.datetime(*designated_date.timetuple()[:3])
date_to = date_from + datetime.timedelta(days=1)
amount = self._db_context.session \
.query(func.sum(WithdrawalRequest.amount)) \
.filter(WithdrawalRequest.symbol == Symbol.ITB) \
.filter(WithdrawalRequest.updated_at >= date_from) \
.filter(WithdrawalRequest.updated_at < date_to) \
.all()[0][0]
if amount is None:
amount = Decimal("0")
self._slackclient.send_message(
channel_id,
"昨日のいいね！総生産量は「{:.0f} ITB」でした。"
.format(amount)
)
def compile(self):
# Json column?
if self.is_column_json:
# PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
column = cast(self.column, Float)
else:
# Simply use
column = self.column
# Now, handle the operator, and apply it to the expression
if self.operator == '$max':
stmt = func.max(column)
elif self.operator == '$min':
stmt = func.min(column)
elif self.operator == '$avg':
stmt = func.avg(column)
elif self.operator == '$sum':
stmt = func.sum(column)
else:
raise InvalidQueryError(
'Aggregate: unsupported operator "{}"'.format(self.operator))
return self.labeled_expression(stmt)
def ticket_report():
# This is an admissions-based view, so includes expired tickets
totals = Ticket.query.outerjoin(Payment).filter(
Ticket.refund_id.is_(None),
or_(Ticket.paid == True, # noqa
~Payment.state.in_(['new', 'cancelled', 'refunded']))
).join(TicketType).with_entities(
TicketType.admits,
func.count(),
).group_by(TicketType.admits).all()
totals = dict(totals)
query = db.session.query(TicketType.admits, func.count(), func.sum(TicketPrice.price_int)).\
select_from(Ticket).join(TicketType).join(TicketPrice).\
filter(TicketPrice.currency == 'GBP', Ticket.paid == True).group_by(TicketType.admits) # noqa
accounting_totals = {}
for row in query.all():
accounting_totals[row[0]] = {
'count': row[1],
'total': row[2]
}
return render_template('admin/ticket-report.html', totals=totals, accounting_totals=accounting_totals)
def ticket_report():
# This is an admissions-based view, so includes expired tickets
totals = Ticket.query.outerjoin(Payment).filter(
Ticket.refund_id.is_(None),
or_(
Ticket.paid == True, # noqa
~Payment.state.in_(['new', 'cancelled', 'refunded'
]))).join(TicketType).with_entities(
TicketType.admits,
func.count(),
).group_by(TicketType.admits).all()
totals = dict(totals)
query = db.session.query(TicketType.admits, func.count(), func.sum(TicketPrice.price_int)).\
select_from(Ticket).join(TicketType).join(TicketPrice).\
filter(TicketPrice.currency == 'GBP', Ticket.paid == True).group_by(TicketType.admits) # noqa
accounting_totals = {}
for row in query.all():
accounting_totals[row[0]] = {'count': row[1], 'total': row[2]}
return render_template('admin/ticket-report.html',
totals=totals,
accounting_totals=accounting_totals)
def query_aggregate(self, ctx, drills, cuts, limit=5000):
mappings = self.sql_mappings(ctx)
joins = self.sql_joins(ctx, None)
pk = self.pk(ctx)
connection = self.sqltable.connection.connection()
engine = self.sqltable.connection._engine
# Build query
Session = sessionmaker()
Session.configure(bind=engine)
session = Session()
q = session.query()
#q = q.add_columns(self.sqltable.sa_table.columns['is_bot_id'].label("x"))
#q = q.add_entity(self.sqltable.sa_table)
# Include measures
for measure in [m for m in mappings if isinstance(m.field, Measure)]:
sa_column = self.sqltable.sa_table.columns[measure.sqlcolumn.name]
q = q.add_columns(
func.avg(sa_column).label(measure.field.name + "_avg"))
q = q.add_columns(
func.sum(sa_column).label(measure.field.name + "_sum"))
q = q.add_columns(
func.count(self.sqltable.sa_table).label("record_count"))
# Drills
for dimension in [
m for m in mappings if isinstance(m.field, Dimension)
]:
# We shoulld check the dimension-path here, with drills, and use key/lookup for drill
if dimension.field.name in drills:
sa_column = None
try:
sa_column = self.sqltable.sa_table.columns[
dimension.sqlcolumn.name]
except KeyError as e:
raise ETLException(
"Unknown column in backend SQL table (table=%s, column=%s). Columns: %s"
% (self.sqltable.sa_table, dimension.sqlcolumn.name,
[c.name for c in self.sqltable.sa_table.columns]))
q = q.add_columns(sa_column)
q = q.group_by(sa_column)
# Cuts
# TODO: Filterng on any dimension attribute, not only the key
# (ie filter cities with type A or icon B), but then again
# that could be a different (nested) dimension.
for dimension in [
m for m in mappings if isinstance(m.field, Dimension)
]:
# We shoulld check the dimension-path here, with drills
if dimension.field.name in cuts.keys():
sa_column = self.sqltable.sa_table.columns[
dimension.sqlcolumn.name]
cut_value = cuts[dimension.field.name]
q = q.filter(sa_column == cut_value)
# Limit
q = q.limit(5000)
statement = q.statement
logger.debug("Statement: %s", str(statement).replace("\n", " "))
rows = connection.execute(statement).fetchall()
return rows
def NewClaim(v):
dataIn = request.form
securityText = str(uuid.uuid4())
# securityText = dataIn['securityText']
# email = dataIn['email']
amount = dataIn['amount']
days = dataIn['days']
# Check owner balance to see if there is enough tokens.
we3 = GetWeb3()
contract_instance = ConnectToContract(we3)
owner_address = app.config['PUBLIC_ADDRESS']
mySupply = GetMyBalance(contract_instance, owner_address)
waitingTokens = db.session.query(func.sum(TokenClaims.amount)).filter(
TokenClaims.tx_delivered == False,
TokenClaims.expiration_date >= datetime.now()).scalar()
try:
waitingTokens = int(waitingTokens) * (10**18)
except:
waitingTokens = 0
freeTokens = mySupply - waitingTokens
if freeTokens < 0:
freeTokens = 0
print("MySupply:", mySupply)
print("Amount:", amount)
print("FreeTokens:", freeTokens)
if ((int(amount) * 10**18) > freeTokens):
return 'Not enough tokens', 400
if int(amount) == 0:
return "Amount can't be zero", 400
if int(days) == 0:
return "Days can't be zero", 400
creation_date = datetime.now()
daysDelta = timedelta(days=int(days))
expiration_date = creation_date + daysDelta
if (len(dataIn['email']) > 0):
email = Emails.query.filter(Emails.email == dataIn['email']).first()
if (email == None):
newemail = Emails(dataIn['email'], '', '')
claim = TokenClaims(securityText, amount, newemail, creation_date,
expiration_date, False, '', None)
else:
claim = TokenClaims(securityText, amount, email, creation_date,
expiration_date, False, '', None)
else:
email = None
claim = TokenClaims(securityText, amount, email, creation_date,
expiration_date, False, '', None)
try:
db.session.add(claim)
db.session.commit()
except:
print(
"!!!! Internal server error when trying to add new claim to databse"
)
return "Internal server error", 500
print(dataIn)
return jsonify({'security_text': securityText})
def generateClaims(v):
skipped = 0
generated = 0
dataIn = request.form
type = dataIn['type']
# amount = 1
print(dataIn)
amount = dataIn['amount']
days = dataIn['days']
if amount == '0':
print("quitting")
return "Amount can't be zero", 401
if days == '0':
print("quitting")
return "Days can't be zero", 401
# print (amount)
if not (type == 'all' or type == 'emptyemail'):
return "Error: Type must be either all or emptyemail.", 401
# emails_query = db.session.query(Emails.id,Emails.email,(func.count(TokenClaims.email_id).label('tokensAmount'))).filter(('tokenclaims.email_id = emails.id'), Emails.id > 0).group_by(Emails.id)
emails_query = db.session.query(Emails.id, Emails.email, (func.count(
TokenClaims.id)).label('tokenAmount')).outerjoin(TokenClaims).filter(
Emails.id > 0).group_by(Emails.id)
print(emails_query)
if type == 'emptyemail':
emails = emails_query.having('count(tokenclaims.id)=0').all()
emailsCount = emails_query.having('count(tokenclaims.id)=0').count()
else:
emails = emails_query.all()
emailsCount = emails_query.count()
print(emails, emailsCount)
# Getting my supply. Total amount of free tokens.
we3 = GetWeb3()
contract_instance = ConnectToContract(we3)
owner_address = app.config['PUBLIC_ADDRESS']
mySupply = GetMyBalance(contract_instance, owner_address)
mySupply = mySupply // (10**18)
# Checking amount of tokens waiting to be claimed
waitingTokens = db.session.query(func.sum(TokenClaims.amount)).filter(
TokenClaims.tx_delivered == False).scalar()
freeTokens = mySupply - waitingTokens
print('*** Total token balance:', mySupply)
print('*** Waiting Tokens:', waitingTokens)
print('*** Tokens available for delivery:', freeTokens)
print('*** Amount to deliver per email:', amount)
availableEmails = freeTokens // int(amount)
print('*** Emails which can be served:', availableEmails)
print('*** Emails to deliver to:', emailsCount)
if availableEmails > emailsCount:
print('*** Can serve all emails')
servedEmails = 0
for row in emails:
servedEmails = servedEmails + 1
if servedEmails > availableEmails:
output = {}
output['generated'] = generated
output[
'error'] = 'Not enough tokens to generate links for all emails.'
print('Not enough tokens. Quitting')
return jsonify(output)
break
securityText = str(uuid.uuid4())
creation_date = datetime.now()
daysDelta = timedelta(hours=24 * int(days))
expiration_date = creation_date + daysDelta
email = Emails.query.filter(Emails.id == row.id).first()
claim = TokenClaims(securityText, amount, email, creation_date,
expiration_date, False, '', None)
db.session.add(claim)
db.session.commit()
generated += 1
output = {}
output['generated'] = generated
return jsonify(output)
def tokenInformation(v):
try:
we3 = GetWeb3()
contract_instance = ConnectToContract(we3)
decimals = contract_instance.functions.decimals().call()
supply = contract_instance.functions.totalSupply().call() / (10**
decimals)
name = contract_instance.functions.name().call()
symbol = contract_instance.functions.symbol().call()
owner_address = app.config['PUBLIC_ADDRESS']
balance = contract_instance.functions.balanceOf(
owner_address).call() / (10**decimals)
owner_balance = we3.eth.getBalance(owner_address)
except:
return "Error.Cannot connect to blockchain node.", 504
token_holders = TokenClaims.query.with_entities(
TokenClaims.claimed_address).filter(
TokenClaims.claimed_address != '').group_by(
TokenClaims.claimed_address).count()
print(token_holders)
transfersCount = TokenClaims.query.filter(
TokenClaims.tx_delivered == True).count()
print(transfersCount)
waitingTokens = db.session.query(func.sum(TokenClaims.amount)).filter(
TokenClaims.tx_delivered == False,
TokenClaims.expiration_date >= datetime.now()).scalar()
try:
waitingTokens = int(waitingTokens)
except:
waitingTokens = 0
expiredTokens = db.session.query(func.sum(TokenClaims.amount)).filter(
TokenClaims.tx_delivered == False,
TokenClaims.expiration_date < datetime.now()).scalar()
try:
expiredTokens = int(expiredTokens)
except:
expiredTokens = 0
expiredTransactions = TokenClaims.query.filter(
TokenClaims.tx_delivered == False,
TokenClaims.expiration_date < datetime.now()).count()
waitingTransactions = TokenClaims.query.filter(
TokenClaims.tx_delivered == False,
TokenClaims.expiration_date >= datetime.now()).count()
print("Waiting Transaction:", waitingTransactions)
print("Waiting Tokens:", waitingTokens)
claimedTokens = db.session.query(func.sum(TokenClaims.amount)).filter(
TokenClaims.claimed_address != '').scalar()
try:
claimedtokens = int(claimedTokens)
except:
claimedTokens = 0
claimedTransactions = TokenClaims.query.filter(
TokenClaims.claimed_address != '').count()
try:
claimedTransactions = int(claimedTransactions)
except:
claimedTransactions = 0
emails_query = db.session.query(Emails.id, Emails.email, (func.count(
TokenClaims.id)).label('tokenAmount')).outerjoin(TokenClaims).filter(
Emails.id > 0).group_by(Emails.id)
print(emails_query)
emptyemailsCount = emails_query.having('count(tokenclaims.id)=0').count()
try:
emptyemailsCount = int(emptyemailsCount)
except:
emptyemailsCount = 0
fullemailsCount = emails_query.having('count(tokenclaims.id)>0').count()
try:
fullemailsCount = int(fullemailsCount)
except:
fullemailsCount = 0
information = {}
information['emptyEmails'] = str(emptyemailsCount)
information['fullEmails'] = str(fullemailsCount)
information['decimals'] = decimals
information['totalSupply'] = supply
information['name'] = name
information['symbol'] = symbol
information['ownerTokenBalance'] = balance
information['ownerETHBalance'] = str(we3.fromWei(owner_balance, "ether"))
information['contractAddress'] = app.config['ETHEREUM_CONTRACT']
information['managerAddress'] = owner_address
information['tokenHolders'] = str(token_holders)
information['transfersCount'] = str(transfersCount)
information['waitingTokens'] = str(waitingTokens)
information['expiredTokens'] = str(expiredTokens)
information['waitingTransactions'] = str(waitingTransactions)
information['expiredTransactions'] = str(expiredTransactions)
information['claimedTokens'] = str(claimedTokens)
information['claimedTransactions'] = str(claimedTransactions)
return jsonify({'information': information})
result = session.query(Office).order_by(Office.c.country, Office.c.state,
Office.c.city)
for row in result:
print("{}\n".format(row))
print("### 2. Combien d'employés y-a-t-il dans l'entreprise ?\n")
result = session.query(Employee).count()
print("Il y a {} employés dans l'entreprise\n".format(result))
print("### 3. Quel est le total des paiements reçus ?\n")
result = session.query(func.sum(Payment.c.amount)).first()[0]
print("L'entreprise a reçue un total de {} de paiements\n".format(result))
print(
"### 4. Dressez la liste des lignes de produits contenant des 'Voitures'\n"
)
result = session.query(ProductLine).filter(
ProductLine.c.productLine.like("%Cars%"))
for row in result:
print("{}\n".format(row))
print("### 5. Déclarez le total")
except Exception as e:
def selectables(cls, bag, agg_spec):
""" Create a list of statements from spec
:type bag: mongosql.bag.ModelPropertyBags
:rtype: list[sqlalchemy.sql.elements.ColumnElement]
"""
# TODO: calculation expressions for selection: http://docs.mongodb.org/manual/meta/aggregation-quick-reference/
selectables = []
for comp_field, comp_expression in agg_spec.items():
# Column reference
if isinstance(comp_expression, string_types):
selectables.append(
bag.columns[comp_expression].label(comp_field))
continue
# Computed expression
assert isinstance(
comp_expression, dict
), 'Aggregate: Expression should be either a column name, or an object'
assert len(
comp_expression
) == 1, 'Aggregate: expression can only contain a single operator'
operator, expression = comp_expression.popitem()
# Expression statement
if isinstance(expression, int) and operator == '$sum':
# Special case for count
expression_stmt = expression
elif isinstance(expression, string_types):
# Column name
expression_stmt = bag.columns[expression]
# Json column?
if bag.columns.is_column_json(expression):
# PostgreSQL always returns text values from it, and for aggregation we usually need numbers :)
expression_stmt = cast(expression_stmt, Float)
elif isinstance(expression, dict):
# Boolean expression
expression_stmt = MongoCriteria.statement(bag, expression)
# Need to cast it to int
expression_stmt = cast(expression_stmt, Integer)
else:
raise AssertionError(
'Aggregate: expression should be either a column name, or an object'
)
# Operator
if operator == '$max':
comp_stmt = func.max(expression_stmt)
elif operator == '$min':
comp_stmt = func.min(expression_stmt)
elif operator == '$avg':
comp_stmt = func.avg(expression_stmt)
elif operator == '$sum':
if isinstance(expression_stmt, int):
# Special case for count
comp_stmt = func.count()
if expression_stmt != 1:
comp_stmt *= expression_stmt
else:
comp_stmt = func.sum(expression_stmt)
else:
raise AssertionError(
'Aggregate: unsupported operator "{}"'.format(operator))
# Append
selectables.append(comp_stmt.label(comp_field))
return selectables
def home():
if session:
customer_count = CustomerModel.query.count()
product_count = Products.query.count()
order_count = Orders.query.count()
order_value = 0
orders = Orders.query.all()
for order in orders:
order_value += order.total
order_count_today = Orders.query.filter(
Orders.orderdate.between(date.today(),
date.today() +
timedelta(days=1))).count()
order_value_today = 0
orders_today = Orders.query.filter(
Orders.orderdate.between(date.today(),
date.today() + timedelta(days=1))).all()
for order in orders_today:
order_value_today += order.total
order_quantity_per_product = Orders.query.filter(
Orders.orderdate.between(
date.today(),
date.today() + timedelta(days=1))).with_entities(
Products.name,
func.sum(Orders.quantity)).join(Products).group_by(
Products.name).all()
quantity_bar_labels = []
quantity_bar_values = []
for label, value in order_quantity_per_product:
quantity_bar_labels.append(label)
quantity_bar_values.append(value)
order_value_per_product = Orders.query.filter(
Orders.orderdate.between(
date.today(),
date.today() + timedelta(days=1))).with_entities(
Products.name,
func.sum(Orders.total)).join(Products).group_by(
Products.name).all()
value_bar_labels = []
value_bar_values = []
for label, value in order_value_per_product:
value_bar_labels.append(label)
value_bar_values.append(value)
return render_template('index.html',
customer_count=customer_count,
product_count=product_count,
order_count=order_count,
order_value=f'{order_value:,.0f}',
order_count_today=order_count_today,
order_value_today=f'{order_value_today:,.0f}',
quantity_bar_labels=quantity_bar_labels,
quantity_bar_values=quantity_bar_values,
value_bar_labels=value_bar_labels,
value_bar_values=value_bar_values)
else:
flash('Please login to gain access', 'danger')
return redirect(url_for('admin'))
def view_account(account_id, page):
"""
SELECT
accounts.id AS accounts_id,
accounts.name AS accounts_name,
accounts.actived AS accounts_actived,
SUM(anon_1.amount) AS amount
FROM
accounts
LEFT OUTER JOIN
(SELECT
anon_2.account_id AS account_id, anon_2.amount AS amount
FROM
(SELECT
account_actions.account_id AS account_id,
account_actions.amount AS amount
FROM
account_actions
WHERE
account_actions.action_type = 'incoming' UNION ALL SELECT
account_actions.account_id AS account_id,
account_actions.amount * - 1 AS anon_3
FROM
account_actions
WHERE
account_actions.action_type = 'outgoing') AS anon_2) AS anon_1 ON accounts.id = anon_1.account_id
WHERE
accounts.id = <account_id>
GROUP BY accounts.id
"""
data = request_filter(request.args,
filtered=['to_date', 'from_date', 'nomenclature_id', 'action', 'incoming_id'],
default=None)
form = ActionsFilter(formdata=data)
form.nomenclature_id.choices = [(0, u'')] + [(nom.id, "%d - %s" % (nom.internal_code, nom.name))
for nom in Nomenclatures.query.all()]
form.incoming_id.choices = [(0, u'')] + [(incoming.id, incoming.incoming_date)
for incoming in Incoming.query.all()]
req = AccountActions.query.filter_by(account_id=account_id)
if form.from_date.data:
req = req.filter(AccountActions.datetime > form.from_date.data)
if form.to_date.data:
req = req.filter(AccountActions.datetime < form.to_date.data)
if form.nomenclature_id.data:
req = req.filter(AccountActions.goods_id.in_([item.id for item in Goods.query.filter_by(nomenclature_id=form.nomenclature_id.data).all()]))
if form.action.data:
req = req.filter(AccountActions.action_type == ('incoming' if form.action.data == 1 else 'outgoing'))
if form.incoming_id.data:
req = req.filter(AccountActions.incoming_id == form.incoming_id.data)
subreq_out = req. \
with_entities(AccountActions.account_id.label('account_id'),
(AccountActions.amount.label('amount') * -1)). \
filter_by(action_type='outgoing')
total = req. \
with_entities(AccountActions.account_id.label('account_id'),
AccountActions.amount.label('amount')). \
filter_by(action_type='incoming'). \
union_all(subreq_out). \
subquery(name='total')
subreq = aliased(total)
account_model = Accounts.query. \
filter_by(id=account_id). \
with_entities(Accounts.id.label('account_id'),
Accounts.name,
Accounts.actived,
func.sum(subreq.c.amount).label('amount')). \
outerjoin(subreq). \
group_by(Accounts.id).first()
if not account_model:
flash(u'Счет: %s не существует' % account_id, 'danger')
return redirect(url_for('b_acc.accounts'))
pagination = req.order_by(AccountActions.datetime.desc()).paginate(page, 10)
return render_template('b_acc/view_account.html', account=account_model, pagination=pagination, form=form)
def get_total_farmer_advance(farmer_id):
total_advance = db.session.query(
FarmerAdvance.farmer_id,
func.sum(FarmerAdvance.amount).label('total_advance')).filter(
FarmerAdvance.farmer_id == farmer_id).all()
return total_advance
