def get_popular_tags(db: Session = Depends(get_db)):
idea_tags = db.query(
IdeasTagsTable.columns.tags_value,
func.Count(IdeasTagsTable.columns.ideas_id),
).group_by(IdeasTagsTable.columns.tags_value).order_by(
desc(func.Count(IdeasTagsTable.columns.ideas_id))
).limit(6).all()
return db.query(Tag).filter(Tag.value.in_([value for value, _ in idea_tags])).all()
def save_data(self, obj):
obj.user_id = current_user.id
print(
Order.query.with_entities(func.Count(Order.id)).filter(
Order.retail_shop_id == obj.retail_shop_id).scalar() + 1)
obj.invoice_number = Order.query.with_entities(func.Count(
Order.id)).filter(
Order.retail_shop_id == obj.retail_shop_id).scalar() + 1
if obj.current_status_id is None:
obj.current_status_id = Status.query.with_entities(
Status.id).filter(Status.name == 'PLACED').first()
return obj
def similar_products(self):
if len(self.salts):
return [i[0] for i in Product.query.with_entities(Product.id)
.join(ProductSalt, and_(ProductSalt.product_id == Product.id))
.filter(ProductSalt.salt_id.in_([i.id for i in self.salts])).group_by(Product.id)
.having(func.Count(func.Distinct(ProductSalt.salt_id)) == len(self.salts)).all()]
return []
def stats():
data = request.get_json(force=True)
lower = datetime.strptime(data['lower'], '%d/%m/%Y')
upper = datetime.strptime(data['upper'], '%d/%m/%Y') + timedelta(days=1)
data = {'days': []}
for dt in rrule(DAILY, dtstart=lower, until=upper):
day = {'date': dt.strftime("%d/%m/%Y"), 'signins': 0, 'entries': []}
# attendance count
signed_in = db.session.query(func.Count(EntryModel.child_id))\
.filter(EntryModel.time >= dt)\
.filter(EntryModel.time <= (dt + timedelta(days=1)))\
.filter_by(status=True)\
.group_by(EntryModel.child_id).count()
day['signins'] = signed_in
entries = db.session.query(EntryModel)\
.filter(EntryModel.time >= dt)\
.filter(EntryModel.time <= (dt + timedelta(days=1)))
for entry in entries:
day['entries'].append(entry.as_dict())
data['days'].append(day)
return jsonify(data)
def aggregate_stats(campaign_id):
zipcodes = (db.session.query(Call.zipcode, func.Count(
Call.zipcode)).filter(Call.campaign_id == campaign_id).group_by(
Call.zipcode).all())
reps = (db.session.query(Call.member_id, func.Count(
Call.member_id)).filter(Call.campaign_id == campaign_id).group_by(
Call.member_id).all())
return {
'campaign': campaign_id,
'calls': {
'zipcodes': dict(tuple(z) for z in zipcodes),
'reps': dict(tuple(r) for r in reps)
}
}
def get_user(username):
session = DBManager.create_session()
user = session.query(User).filter_by(username=username).one_or_none()
if user is None:
return make_response(jsonify({'error': 'Not found'}), 404)
rank = session.query(func.Count(User.id)).filter(User.points > user.points).one()
rank = rank[0] + 1
session.close()
if user:
accessible_data = {
'id': user.id,
'username': user.username,
'username_raw': user.username_raw,
'points': user.points,
'rank': rank,
'level': user.level,
'last_seen': user.last_seen,
'last_active': user.last_active,
'subscriber': user.subscriber,
'num_lines': user.num_lines,
'minutes_in_chat_online': user.minutes_in_chat_online,
'minutes_in_chat_offline': user.minutes_in_chat_offline,
'banned': user.banned,
'ignored': user.ignored,
}
return jsonify(accessible_data)
return make_response(jsonify({'error': 'Not found'}), 404)
def call_count(campaign_id):
try:
return (db.session.query(func.Count(
Call.zipcode)).filter(Call.campaign_id == campaign_id).all())[0][0]
except SQLAlchemyError:
logging.error('Failed to get call_count:', exc_info=True)
return 0
def total_sales(self):
data = self.orders.with_entities(func.Sum(Order.total), func.Count(Order.id), func.Sum(Order.items_count)) \
.filter(Order.store_id == self.id, Order.created_on >= datetime.now().date()).all()[0]
return {
'total_sales': data[0],
'total_orders': data[1],
'total_items': str(data[2])
}
def total_sales(self):
data = self.orders.with_entities(func.Sum(Order.total), func.Count(Order.id), func.Sum(Order.items_count))\
.filter(Order.retail_shop_id == self.id).all()[0]
return {
'total_sales': data[0],
'total_orders': data[1],
'total_items': str(data[2])
}
def push_thread_callback(app: Flask):
"""Process outstanding MDM commands by issuing a push to device(s).
TODO: A push with no response needs an exponential backoff time.
Commands that are ready to send must satisfy these criteria:
- Command is in Queued state.
- Command.after is null.
- Command.ttl is not zero.
- Device is enrolled (is_enrolled)
"""
while not push_thread_stopped.wait(push_time):
app.logger.info('Push Thread checking for outstanding commands...')
with app.app_context():
pending: Tuple[Device, int] = db.session.query(Device, func.Count(Command.id)).\
filter(Device.id == Command.device_id).\
filter(Command.status == CommandStatus.Queued).\
filter(Command.ttl > 0).\
filter(Command.after == None).\
filter(Device.is_enrolled == True).\
group_by(Device.id).\
all()
for d, c in pending:
app.logger.info('PENDING: %d command(s) for device UDID %s', c,
d.udid)
if d.token is None or d.push_magic is None:
app.logger.warn(
'Cannot request push on a device that has no device token or push magic'
)
continue
try:
response = push_to_device(d)
except ssl.SSLError:
return stop()
app.logger.info(
"[APNS2 Response] Status: %d, Reason: %s, APNS ID: %s, Timestamp",
response.status_code, response.reason,
response.apns_id.decode('utf-8'))
d.last_push_at = datetime.utcnow()
if response.status_code == 200:
d.last_apns_id = response.apns_id
db.session.commit()
def get(self, hotelroom_id, start_date, end_date):
# parse the dates as they're sent as a string e.g. 2018-01-01
start_date = datetime.strptime(start_date, '%Y-%m-%d').date()
end_date = datetime.strptime(end_date, '%Y-%m-%d').date()
# get a session/connection to the database
session = get_session()
# get the hotelroom object to calculate capacity
hotelroom = session.query(HotelRooms).get(hotelroom_id)
# get number of bookings and cancellations
num_of_bookings = session.query(func.count(Bookings)).filter(
and_(
Bookings.hotelroom_id == hotelroom_id,
Bookings.reserved_night_date.between(
start_date, end_date
),
Bookings.row_type == 'booking'
)
).all()
num_of_cancellations = session.query(func.Count(Bookings)).filter(
and_(
Bookings.hotelroom_id == hotelroom_id,
Bookings.reserved_night_date.between(
start_date, end_date
),
Bookings.row_type == 'cancellations'
)
).all()
# calculate numerator and denominator for occupancy
net_bookings = num_of_bookings - num_of_cancellations
total_available_rooms = hotelroom.capacity * ((end_date - start_date).days + 1)
# check to make sure total_available_rooms is not 0 (division by zero error)
if total_available_rooms == 0:
occupancy = None
else:
# convert to string and round to 2 decimal places and calculate PERCENTAGE
occupancy = str(round(net_bookings * 100 / total_available_rooms, 2))
return {
'occupancy': occupancy
}
def user_profile(username):
session = DBManager.create_session()
user = session.query(User).filter_by(username=username).one_or_none()
if user is None:
return render_template('no_user.html'), 404
rank = session.query(func.Count(User.id)).filter(User.points > user.points).one()
rank = rank[0] + 1
user.rank = rank
user_duel_stats = session.query(UserDuelStats).filter_by(user_id=user.id).one_or_none()
try:
return render_template('user.html',
user=user,
user_duel_stats=user_duel_stats)
finally:
session.close()
def get(self):
shops = request.args.getlist('__retail_shop_id__in')
if len(shops) == 1:
shops = shops[0].split(',')
from_date = datetime.strptime(request.args['__from'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
to_date = datetime.strptime(request.args['__to'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
days = (to_date - from_date).days
collection_type = 'day'
if days > 28:
collection_type = 'week'
if days > 140:
collection_type = 'month'
orders = Order.query.join(Item,
and_(Item.order_id == Order.id)).filter(
Order.retail_shop_id.in_(shops))
total_orders, total_sales, total_items, total_quantity, total_due = \
orders.with_entities(func.Count(Order.id), func.sum(Order.total),
func.Count(func.Distinct(Item.product_id)), func.sum(Item.quantity),
func.Sum(Order.amount_due)).all()[0]
orders = Order.query.with_entities(func.count(Order.id),
func.cast(func.date_trunc(collection_type,
func.cast(Order.created_on, Date)), Text)
.label('dateWeek'))\
.filter(Order.created_on.between(from_date, to_date))\
.group_by('dateWeek').all()
# new_customers = orders.join(Customer, and_(Customer.id == Order.customer_id))\
# .with_entities(func.Count(Order.customer_id)).scalar()
#
# return_customers = orders.join(Customer, and_(Customer.id == Order.customer_id))\
# .with_entities(func.Count(Order.customer_id)).scalar()
#
items = Item.query.join(Order, and_(Order.id == Item.order_id))\
.filter(Order.retail_shop_id.in_(shops))
max_sold_items = items.join(Product, and_(Product.id == Item.product_id))\
.with_entities(func.Count(Item.id), Product.name,
func.cast(func.date_trunc(collection_type, func.cast(Order.created_on, Date)), Text)
.label('dateWeek'))\
.filter(Order.created_on.between(from_date, to_date))\
.group_by(Item.product_id, Product.name, 'dateWeek').order_by(-func.Count(Item.id)).limit(10).all()
# min_sold_items = items.join(Product, and_(Product.id == Item.product_id))\
# .with_entities(func.Count(Item.id), Item.product_id, Product.name)\
# .group_by(Item.product_id, Product.name).order_by(func.Count(Item.id)).limit(10).all()
return make_response(
jsonify(
dict(total_orders=total_orders,
total_sales=total_sales,
total_quantity=total_quantity,
max_sold_items=max_sold_items,
total_items=str(total_items),
orders=orders)), 200)
def get(self):
shops = request.args.getlist('__retail_shop_id__in')
for shop in shops:
if not current_user.has_shop_access(shop):
return make_response(jsonify({'message': 'Access Forbidden'}),
403)
if len(shops) == 1:
shops = shops[0].split(',')
from_date = datetime.strptime(request.args['__created_on__gte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
to_date = datetime.strptime(request.args['__created_on__lte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
days = (to_date - from_date).days
collection_type = 'day'
if days > 28:
collection_type = 'week'
if days > 360:
collection_type = 'month'
orders = self.model.query.join(Item, and_(Item.order_id == self.model.id)) \
.filter(self.model.retail_shop_id.in_(shops), self.model.created_on.between(from_date, to_date))
total_orders, total_sales, total_items, total_quantity, total_due = \
orders.with_entities(func.Count(func.Distinct(self.model.id)), func.sum(self.model.total),
func.Count(func.Distinct(Item.product_id)), func.sum(Item.quantity),
func.Sum(self.model.amount_due)).all()[0]
orders = self.model.query\
.with_entities(func.count(func.Distinct(self.model.id)), func.sum(self.model.total),
func.avg(self.model.total),
func.cast(func.date_trunc(collection_type, func.cast(self.model.created_on, Date)), Text)
.label('dateWeek')) \
.filter(self.model.created_on.between(from_date, to_date), self.model.retail_shop_id.in_(shops)) \
.group_by('dateWeek').order_by('dateWeek').all()
items = Item.query.join(self.model, and_(self.model.id == Item.order_id)) \
.filter(self.model.retail_shop_id.in_(shops))
max_sold_items = items.join(Product, and_(Product.id == Item.product_id)) \
.with_entities(func.Sum(Item.quantity), Product.name, ) \
.filter(self.model.created_on.between(from_date, to_date)) \
.group_by(Item.product_id, Product.name).order_by(-func.Sum(Item.quantity)).limit(10).all()
max_profitable_items = items.join(Product, and_(Product.id == Item.product_id)) \
.join(Stock, and_(Stock.id == Item.stock_id)) \
.with_entities(func.Sum((Item.unit_price - Stock.purchase_amount) * Item.quantity), Product.name, ) \
.filter(self.model.created_on.between(from_date, to_date)) \
.group_by(Item.product_id, Product.name) \
.order_by(-func.Sum((Item.unit_price - Stock.purchase_amount) * Item.quantity)) \
.limit(10).all()
return make_response(
jsonify(
dict(total_orders=total_orders,
total_sales=total_sales,
total_quantity=total_quantity,
max_sold_items=max_sold_items,
max_profitable_items=max_profitable_items,
total_items=str(total_items),
orders=orders)), 200)
def total_orders(self):
return self.orders.with_entities(func.coalesce(func.Count(Order.id),
0)).scalar()
def total_items(self):
return self.purchased_items.with_entities(func.Count(Stock.id)).scalar()
def user_profile(username):
with DBManager.create_session_scope() as db_session:
user = db_session.query(User).filter_by(
username=username).one_or_none()
if user is None:
return render_template('no_user.html'), 404
rank = db_session.query(func.Count(
User.id)).filter(User.points > user.points).one()
rank = rank[0] + 1
user.rank = rank
user_duel_stats = db_session.query(UserDuelStats).filter_by(
user_id=user.id).one_or_none()
roulettes = db_session.query(Roulette).filter_by(
user_id=user.id).order_by(Roulette.created_at.desc()).all()
roulette_stats = None
if len(roulettes) > 0:
profit = 0
total_points = 0
biggest_loss = 0
biggest_win = 0
biggest_winstreak = 0
biggest_losestreak = 0
num_wins = 0
num_losses = 0
winrate = 0
num_roulettes = len(roulettes)
cw = 0
for roulette in roulettes:
profit += roulette.points
total_points += abs(roulette.points)
if roulette.points > 0:
# a win!
num_wins += 1
if cw < 0:
if abs(cw) > biggest_losestreak:
biggest_losestreak = abs(cw)
cw = 0
cw += 1
else:
# a loss
num_losses += 1
if cw > 0:
if cw > biggest_winstreak:
biggest_winstreak = cw
cw = 0
cw -= 1
if roulette.points < biggest_loss:
biggest_loss = roulette.points
elif roulette.points > biggest_win:
biggest_win = roulette.points
# Calculate winrate
if num_losses == 0:
winrate = 1
elif num_wins == 0:
winrate = 0
else:
winrate = num_wins / num_roulettes
# Finalize win/lose streaks in case we're currently
# on the biggest win/lose streak
if cw < 0:
if abs(cw) > biggest_losestreak:
biggest_losestreak = abs(cw)
elif cw > 0:
if cw > biggest_winstreak:
biggest_winstreak = cw
if 'roulette' in app.module_manager:
roulette_base_winrate = 1.0 - app.module_manager[
'roulette'].settings['rigged_percentage'] / 100
else:
roulette_base_winrate = 0.45
roulette_stats = {
'profit': profit,
'total_points': total_points,
'biggest_win': biggest_win,
'biggest_loss': biggest_loss,
'num_roulettes': num_roulettes,
'biggest_winstreak': biggest_winstreak,
'biggest_losestreak': biggest_losestreak,
'winrate': winrate,
'winrate_str': '{:.2f}%'.format(winrate * 100),
'roulette_base_winrate': roulette_base_winrate,
}
return render_template('user.html',
user=user,
user_duel_stats=user_duel_stats,
roulette_stats=roulette_stats,
roulettes=roulettes)
def items_count(cls):
return select([func.Count(Item.id)
]).where(Item.order_id == cls.id).as_scalar()
def items_count(self):
return self.order_items.with_entities(func.Count(Item.id)).scalar()
def total_comments(self):
return self.comments.with_entities(func.Count(
Comment.id)).filter(Comment.post_id == self.id).scalar()
def get(self):
shops = request.args.getlist('__retail_shop_id__in')
for shop in shops:
if not current_user.has_shop_access(shop):
return make_response(jsonify({'message': 'Access Forbidden'}),
403)
brand_id = request.args.get('__retail_brand_id__equal')
if len(shops) == 1:
shops = shops[0].split(',')
from_date = datetime.strptime(request.args['__created_on__gte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
to_date = datetime.strptime(request.args['__created_on__lte'],
'%Y-%m-%dT%H:%M:%S.%fZ').date()
days = (to_date - from_date).days
collection_type = 'day'
if days > 56:
collection_type = 'week'
if days > 360:
collection_type = 'month'
new_customers = self.model.query.join(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Count(func.Distinct(Order.customer_id)), func.Sum(Order.total), func.avg(Order.total),
func.cast(func.date_trunc(collection_type, func.cast(self.model.created_on, Date)), Text)
.label('dateWeek')
) \
.filter(and_(self.model.created_on.between(from_date, to_date), Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops))).group_by('dateWeek').order_by('dateWeek')\
.having(func.Count(func.Distinct(Order.id)) > 0).all()
return_customers = self.model.query.join(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Count(func.Distinct(Order.customer_id)), func.Sum(Order.total), func.avg(Order.total),
func.cast(func.date_trunc(collection_type, func.cast(self.model.created_on, Date)), Text)
.label('dateWeek')
) \
.filter(and_(self.model.created_on.between(from_date, to_date), Order.created_on.between(from_date, to_date))) \
.having(func.Count(func.Distinct(Order.id)) > 1).group_by('dateWeek').order_by('dateWeek').all()
old_customers = Order.query.join(Customer, and_(Customer.id == Order.customer_id)) \
.with_entities(func.Count(func.Distinct(Order.customer_id)), func.Sum(Order.total), func.avg(Order.total),
func.cast(func.date_trunc(collection_type, func.cast(Order.created_on, Date)), Text)
.label('dateWeek')
) \
.filter(and_(Customer.created_on <= from_date, Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops), Customer.retail_brand_id == brand_id))\
.group_by('dateWeek').order_by('dateWeek') \
.having(func.Count(func.Distinct(Order.id)) > 0).all()
total_due = self.model.query.outerjoin(Order, and_(Order.customer_id == self.model.id)) \
.outerjoin(CustomerTransaction, and_(CustomerTransaction.customer_id == self.model.id)) \
.with_entities(func.coalesce(cast(func.Sum(Order.total), Float), 0.0) -
func.coalesce(cast(func.Sum(Order.amount_paid), Float), 0.0)
- func.coalesce(cast(func.Sum(CustomerTransaction.amount), Float), 0.0)) \
.filter(self.model.retail_brand_id == brand_id).scalar()
top_customers = self.model.query.outerjoin(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Count(func.Distinct(Order.id)), self.model.name) \
.filter(self.model.retail_brand_id == brand_id, Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops)).group_by(Order.customer_id, self.model.name) \
.order_by(-func.Count(func.Distinct(Order.id))).limit(10).all()
top_billed_customers = self.model.query.outerjoin(Order, and_(Order.customer_id == self.model.id)) \
.with_entities(func.Sum(Order.total), self.model.name) \
.filter(self.model.retail_brand_id == brand_id, Order.created_on.between(from_date, to_date),
Order.retail_shop_id.in_(shops)).group_by(Order.customer_id, self.model.name) \
.order_by(-func.Sum(Order.total)).limit(10).all()
return make_response(
jsonify(
dict(new_customers=new_customers,
return_customers=return_customers,
old_customers=old_customers,
top_billed_customers=top_billed_customers,
total_due=total_due,
top_customers=top_customers)), 200)
def total_items(cls):
return select([func.Count(func.Distinct(Stock.id))]).where(Stock.distributor_bill_id == cls.id).as_scalar()
