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()