def process(self, *args, **kwargs): self.logtimes() with make_db(self.db) as db: try: nodes = db.query(models.TrNode) for node in nodes: r = db.query( func.sum(models.TrOnline.input_total).label("input_total"), func.sum(models.TrOnline.output_total).label("output_total") ).filter( models.TrOnline.account_number == models.TrAccount.account_number, models.TrAccount.customer_id == models.TrCustomer.customer_id, models.TrCustomer.node_id == node.id ).first() if r and all([r.input_total,r.output_total]): stat = models.TrFlowStat() stat.node_id = node.id stat.stat_time = int(time.time()) stat.input_total = r.input_total stat.output_total = r.output_total db.add(stat) # clean expire data _time = int(time.time()) - (86400 * 2) db.query(models.TrFlowStat).filter(models.TrFlowStat.stat_time < _time).delete() db.commit() logger.info("flow stat task done") except Exception as err: db.rollback() logger.error('flow_stat_job err,%s'%(str(err))) return self.get_notify_interval()
def single_order_list(id): order_list = database.DbOrderList.query.get(id) orders = [] customers = [] if order_list is None: flash('This order list does not exist') else: orders = database.DbOrder.query.filter_by(list_id=id).all() # count_payed = case([(database.DbOrderItem.payed, # database.DbItem.price * database.DbOrderItem.quantity)], # else_=0) # Very similar to query in single_order, could re-use code customers = (db.session.query(func.sum(database.DbOrderItem.quantity).label('quantity'), func.sum(database.DbItem.price * database.DbOrderItem.quantity).label('total_price'), # func.sum(count_payed).label('payed'), database.User) .join(database.DbOrder) .join(database.DbOrderList) .join(database.DbItem) .filter(database.DbOrderList.id == order_list.id) .join(database.User) .group_by(database.User.id) .all()) return render_template('order_list_single.html', list=order_list, orders=orders, customers=customers)
def allocation(): loc = "nav-allocation" if request.method == "POST" and request.form["action"] == "insert": #insert new allocation amount = request.form["amount"] category_id = request.form["categoryid"] allocation = Transaction(g.user.id, amount, "Allocation", category_id) db.session.add(allocation) db.session.commit() success = (True if allocation.id != -1 else False) return render_template("allocation.html", success = success, locid = loc) elif request.method == "POST" and request.form["action"] == "add": #show add allocation return render_template("allocation.html", action = "add", net_income = net_income(), category = Category.query.filter_by(category_type="Expense"), locid = loc) else: #show allocation list allocation_total = 0 qry = db.session.query(Transaction, func.sum(Transaction.amount).label("total_amount")).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id) for _res in qry.all(): allocation_total = _res.total_amount percentage_list = [] qry = db.session.query(Transaction, Category, func.sum(Transaction.amount).label("total_amount")).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id).join(Category).group_by(Category.description) for _res in qry.all(): a = { "percent" : int(round(((_res.total_amount / allocation_total) * 100))), "description" : _res.Category.description, "amount" : _res.total_amount } percentage_list.append(a) rs = db.session.query(Transaction, Category).filter_by(transaction_type="Allocation").filter_by(user_id=g.user.id).join(Category) return render_template("allocation.html", action = "show", allocation = rs, percentage_list=percentage_list, locid = loc)
def project_outline(name): # name=request.args.get('name') P=models.Projects.query.all() project=models.Projects.query.filter_by(id=name).first() G=project.goals.all() gform=goal_form(request.values) delete_form=DeleteRow_form() q_sum = (db.session.query( Projects.id.label("project_id"), Goals.id.label("goal_id"), func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"), func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"), func.count(Tasks.id).label("total"), ).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id).filter(Projects.id == name) ) if request.method == 'POST' and gform.submit.data: if gform.validate() == False: flash('Failed Field validation.') flash_errors(gform) return redirect(url_for('project_outline', name=name)) else: p=models.Goals(goal=gform.goal.data,proj=project) db.session.add(p) db.session.commit() return redirect(url_for('project_outline', name=name)) if request.method == 'POST' and delete_form.submitd.data: pstratrow = delete_form.row_id.data pstrat=models.Goals.query.filter_by(id=pstratrow).first() db.session.delete(pstrat) db.session.commit() return redirect(url_for('project_outline',name=name)) # if request.method == 'POST' and delete_form.submit.data: # delete_row= return render_template("index_for_goal.html",project=project,G=G,gform=gform,P=P,zipit=zip(G,q_sum),delete_form=delete_form)
def get_biggest_donations(cls, limit=None, offset=None): """Getter for biggest donations. Donations from the same person are grouped. Args: limit: Maximum number of donations to be returned. offset: Offset of the result. Returns: Tuple with two items. First is total number if donations. Second is a list of donations sorted by amount with a specified offset. """ query = db.session.query( cls.first_name.label("first_name"), cls.last_name.label("last_name"), cls.editor_name.label("editor_name"), func.max(cls.payment_date).label("payment_date"), func.sum(cls.amount).label("amount"), func.sum(cls.fee).label("fee"), ) query = query.filter(cls.anonymous == False) query = query.group_by(cls.first_name, cls.last_name, cls.editor_name) query = query.order_by(desc("amount")) count = query.count() # Total count should be calculated before limits if limit is not None: query = query.limit(limit) if offset is not None: query = query.offset(offset) return count, query.all()
def get_cash_flows(): date_range_filter_schema = DateRangeFilterSchema().load(request.args) if date_range_filter_schema.errors: return {'errors': date_range_filter_schema.errors}, 400 cash_flow_schema = CashFlowSchema() amounts = db.session.query( func.sum(Record.amount).label("cash_flow"), func.sum( case([(Record.record_type == Record.RECORD_TYPE_INCOME, Record.amount)], else_=0) ).label('income'), func.sum( case([(Record.record_type == Record.RECORD_TYPE_EXPENSE, Record.amount)], else_=0) ).label('expense'), func.date_trunc('month', Record.date).label("date"), ).group_by( func.date_trunc('month', Record.date) ).order_by( func.date_trunc('month', Record.date) ) if 'date_from' in date_range_filter_schema.data: amounts = amounts.filter(Record.date >= date_range_filter_schema.data['date_from']) if 'date_to' in date_range_filter_schema.data: amounts = amounts.filter(Record.date < date_range_filter_schema.data['date_to']) return {'objects': cash_flow_schema.dump(amounts, many=True).data}
def get_taxi_data_count(color): if color == 'all': base_query = session.query(func.sum(TripStats.total_record_cnt)) yellow_count = base_query.filter(TripStats.taxi_type == 2).first()[0] green_count = base_query.filter(TripStats.taxi_type == 1).first()[0] return return_json({ "yellow": { "day": int(yellow_count/365), "week": int(yellow_count/52), "month": int(yellow_count/12), "year": int(yellow_count) }, "green": { "day": int(green_count/365), "week": int(green_count/52), "month": int(green_count/12), "year": int(green_count) } }) taxi_type = COLOR_CODE[color] count = session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == taxi_type).first()[0] if 'average' in request.args: if request.args['average'] == 'day': return return_json({"count": int(count/365), "type": color}) elif request.args['average'] == 'month': return return_json({"count": int(count/12), "type": color}) elif request.args['average'] == 'week': return return_json({"count": int(count/52), "type": color}) return return_json({"count": count, "type": color})
def _repayments(self): today_repayments = Plan.query.filter( cast(Plan.plan_time, Date) == date.today(), Plan.status == get_enum('PLAN_PENDING') ).order_by('plan_time desc').limit(10) today_repay_amount = db_session.query( func.sum(Plan.amount) ).filter( cast(Plan.plan_time, Date) == date.today(), Plan.status == get_enum('PLAN_PENDING') ).scalar() if not today_repay_amount: today_repay_amount = 0 total_repay_amount = db_session.query( func.sum(Plan.amount) ).filter(Plan.status == get_enum('PLAN_PENDING')).scalar() if not total_repay_amount: total_repay_amount = 0 app.jinja_env.globals['today_repay_amount'] = today_repay_amount app.jinja_env.globals['total_repay_amount'] = total_repay_amount app.jinja_env.globals['today_repayments'] = today_repayments
def strategy_outline(name,goal): P=models.Projects.query.all() project=models.Projects.query.filter_by(id=name).first() pgoal=models.Goals.query.filter_by(id=goal).first() S=pgoal.strategies.all() sform=strategy_form(request.values) delete_form=DeleteRow_form() q_sum = (db.session.query( Projects.id.label("project_id"), func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"), func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"), func.count(Tasks.id).label("total"), Strategies.id.label("strategy_id"), Goals.id.label("goal_id"), ).join(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id,Goals.id,Strategies.id).filter(Goals.id == goal) ) if request.method == 'POST' and sform.submit.data: print sform.validate() if sform.validate() == False: flash('Failed Field validation.') flash_errors(sform) return redirect(url_for('strategy_outline',name=name,goal=goal)) else: p=models.Strategies(strategy=sform.strategy.data,goa=pgoal) db.session.add(p) db.session.commit() return redirect(url_for('strategy_outline',name=name,goal=goal)) if request.method == 'POST' and delete_form.submitd.data: pstratrow = delete_form.row_id.data pstrat=models.Strategies.query.filter_by(id=pstratrow).first() db.session.delete(pstrat) db.session.commit() return redirect(url_for('strategy_outline',name=name,goal=goal)) return render_template("index_for_strategy.html",project=project,S=S,sform=sform,pgoal=pgoal,P=P,zipit=zip(S,q_sum),delete_form=delete_form)
def _columns(self, total_row=False): columns = ( self._column_helper("rations", "male", "st"), self._column_helper("rations", "female", "st"), self._column_helper("rations", "male", "sc"), self._column_helper("rations", "female", "sc"), self._column_helper("rations", "male", "others"), self._column_helper("rations", "female", "others"), self._column_helper("rations", "male", "disabled"), self._column_helper("rations", "female", "disabled"), self._column_helper("rations", "male", "minority"), self._column_helper("rations", "female", "minority"), self._column_helper("absent", "male"), self._column_helper("absent", "female"), self._column_helper("partial", "male"), self._column_helper("partial", "female"), self._column_helper("rations", "migrant", "male"), self._column_helper("rations", "migrant", "female"), func.count(self.table.c.case_id).filter( self.table.c.sex == 'M').label("child_count_male"), func.count(self.table.c.case_id).filter( self.table.c.sex == 'F').label("child_count_female"), func.sum(self.table.c.num_rations_distributed).filter( self.table.c.sex == 'M').label("thr_total_rations_male"), func.sum(self.table.c.num_rations_distributed).filter( self.table.c.sex == 'F').label("thr_total_rations_female"), ) if not total_row: return (self.table.c.awc_id.label("owner_id"),) + columns return columns
def child_victimisation_worksheet(self, wb): from dexter.models.views import DocumentChildrenView ws = wb.add_worksheet("child_secondary_victimisation") rows = self.filter( db.session.query( func.sum( DocumentChildrenView.c.secondary_victim_source == "secondary-victim-source", type_=Integer ).label("secondary_victim_source"), func.sum( DocumentChildrenView.c.secondary_victim_identified == "secondary-victim-identified", type_=Integer ).label("secondary_victim_identified"), func.sum( DocumentChildrenView.c.secondary_victim_victim_of_abuse == "secondary-victim-abused", type_=Integer ).label("secondary_victim_victim_of_abuse"), func.sum( DocumentChildrenView.c.secondary_victim_source_identified_abused == "secondary-victim-source-identified-abused", type_=Integer, ).label("secondary_victim_source_identified_abused"), ).join(Document) ).all() if not rows: return d = rows[0]._asdict() data = [[k, d[k]] for k in sorted(d.keys(), key=len)] ws.add_table( 0, 0, len(data), 1, {"name": "ChildSecondaryVictimisation", "data": data, "columns": [{"header": ""}, {"header": "count"}]}, )
def agg_supply(timeid): s_dict = {k: 0 for k in range(1, 14)} ##pull units devoted to production... if timeid == 1 or session.query(Inventory.id).filter(Inventory.timeid==timeid-1).count() == 0: result = session.query(Business.industryid, func.sum(Business.produnits)).group_by(Business.industryid).order_by(Business.industryid).all() for id, produnits in result: s = produnits * 500 s_dict[id] = s ##commit supply to the database supply = Supply(timeid=timeid, industryid=id, endunits=s) session.add(supply) session.commit() else: result = session.query(Business.industryid, func.sum(Business.produnits), func.sum(Inventory.units)).filter(Inventory.timeid==timeid-1, Business.id==Inventory.businessid).group_by(Business.industryid).order_by(Business.industryid).all() for id, produnits, inventory in result: ##calculate the supply for each record... s = inventory + produnits * 500 s_dict[id] = s ##commit supply to the database supply = Supply(timeid=timeid, industryid=id, endunits=s) session.add(supply) session.commit() ##return a supply dataframe for program df = pd.DataFrame(s_dict.values(), index=s_dict.keys(), columns=["supply"]) return df
def get_line_sum(): line = int(request.form['line']) chosen_direction = request.form['direction'] if chosen_direction in ['Inbound','Outbound']: for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction): length = result[0] zones, fare, cost = initialize_variables(length) i = 0 while (i < length): zones[i] = db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line, DIRECTION = chosen_direction)[i][0] for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction): fare[i] = fare_sum[0] for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i], DIRECTION = chosen_direction): cost[i] = cost_sum[0] i += 1 return zones, fare, cost else: for result in db.session.query(func.count(Output.ZONE_NUMBER.distinct())).filter_by(LINE_NUMBER = line): length = result[0] zones, fare, cost = initialize_variables(length) i = 0 while (i < length): zones[i] = db.session.query(Output.ZONE_NUMBER.distinct()).filter_by(LINE_NUMBER = line)[i][0] for fare_sum in db.session.query(func.sum(Output.FARE_COLLECTED)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]): fare[i] = fare_sum[0] for cost_sum in db.session.query(func.sum(Output.TOTAL_OPERATING_COST)).filter_by(LINE_NUMBER = line, ZONE_NUMBER = zones[i]): cost[i] = cost_sum[0] i += 1 return zones, fare, cost
def _investments(self): today_investments = Investment.query.filter( cast(Investment.added_at, Date) == date.today(), Investment.status.in_( (get_enum('INVESTMENT_PENDING'), get_enum('INVESTMENT_SUCCESSED')) ) ).order_by("added_at desc").limit(10) history_investments = db_session.query( func.date_trunc('day', Investment.added_at), func.sum(Investment.amount)).group_by( func.date_trunc('day', Investment.added_at) ).order_by(func.date_trunc('day', Investment.added_at)).all() total_investments = db_session.query( func.sum(Investment.amount)).scalar() today_invest_amount = db_session.query( func.sum(Investment.amount)).filter( cast(Investment.added_at, Date) == date.today(), Investment.status.in_( (get_enum('INVESTMENT_PENDING'), get_enum('INVESTMENT_SUCCESSED')) ) ).scalar() if not today_invest_amount: today_invest_amount = 0 app.jinja_env.globals['today_invest_amount'] = today_invest_amount app.jinja_env.globals['today_investments'] = today_investments app.jinja_env.globals['total_investments'] = total_investments app.jinja_env.globals['history_investments'] = history_investments
def get_scan_information(): if not ValidateClass.check_login(): return redirect(ADMIN_URL + '/index') if request.method == "POST": start_time_stamp = request.form.get("start_time_stamp")[0:10] end_time_stamp = request.form.get("end_time_stamp")[0:10] start_time_array = datetime.datetime.fromtimestamp(int(start_time_stamp)) end_time_array = datetime.datetime.fromtimestamp(int(end_time_stamp)) if start_time_stamp >= end_time_stamp: return jsonify(tag="danger", msg="wrong date select.", code=1002) task_count = CobraTaskInfo.query.filter( and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp) ).count() vulns_count = CobraResults.query.filter( and_(CobraResults.created_at >= start_time_array, CobraResults.created_at <= end_time_array) ).count() projects_count = CobraProjects.query.filter( and_(CobraProjects.last_scan >= start_time_array, CobraProjects.last_scan <= end_time_array) ).count() files_count = db.session.query(func.sum(CobraTaskInfo.file_count).label('files')).filter( and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp) ).first()[0] code_number = db.session.query(func.sum(CobraTaskInfo.code_number).label('codes')).filter( and_(CobraTaskInfo.time_start >= start_time_stamp, CobraTaskInfo.time_start <= end_time_stamp) ).first()[0] return jsonify(code=1001, task_count=task_count, vulns_count=vulns_count, projects_count=projects_count, files_count=int(files_count), code_number=int(code_number))
def calc_level_type_skill(self, until=None): query1 = ( db.session.query(func.count(TriggeredEvent.id), func.sum(TriggeredEvent.given_skill_points).label("sum_1")) .join(Participation) .join(LevelInstance) .join(Level) .join(Level.level_types) .filter(Participation.pid == self.pid) .filter(LevelType.id == self.ltid) ) query2 = ( db.session.query(func.sum(TriggeredEvent.given_score_points).label("sum_1")) .join(Participation) .join(LevelInstance) .join(Level) .join(Level.level_types) .filter(Participation.pid == self.pid) .filter(LevelType.id == self.ltid) .group_by(LevelInstance.id) .order_by(desc("sum_1")) ) if until: query1 = query1.filter(TriggeredEvent.timestamp < until) query2 = query2.filter(TriggeredEvent.timestamp < until) considered_rows, skill_points = query1.all()[0] if query1.all()[0] else 0 score_points = query2.all()[0][0] if query2.all() else 0 return considered_rows, skill_points, score_points
def execute(mk_db): log.msg("start flow stat task..") db = mk_db() try: nodes = db.query(models.SlcNode) for node in nodes: r = db.query( func.sum(models.SlcRadOnline.input_total).label("input_total"), func.sum(models.SlcRadOnline.output_total).label("output_total") ).filter( models.SlcRadOnline.account_number == models.SlcRadAccount.account_number, models.SlcRadAccount.member_id == models.SlcMember.member_id, models.SlcMember.node_id == node.id ).first() if r: stat = models.SlcRadFlowStat() stat.node_id = node.id stat.stat_time = int(time.time()) stat.input_total = r.input_total stat.output_total = r.output_total db.add(stat) db.commit() log.msg("flow stat task done") except Exception as err: db.rollback() log.err(err,'flow_stat_job err') finally: db.close()
def get_taxi_data_distribution(color): if color != 'all': taxi_type = COLOR_CODE[color] # TODO: # By default, we will just return a key-value pair of month and count out_data = [] if 'interval' in request.args: if request.args['interval'] == 'month': # distribution by month # NOTE: Using a different mechanism now!!!!!! for i in range(1, 13): # out_data.append({ # "date": i, # "count": session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == taxi_type, extract('month', TripStats.datetime) == i).first()[0] # }) out_data.append({ "date": i, "type": 2, "count": session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == 2, extract('month', TripStats.datetime) == i).first()[0] }) out_data.append({ "date": i, "type": 1, "count": session.query(func.sum(TripStats.total_record_cnt)).filter(TripStats.taxi_type == 1, extract('month', TripStats.datetime) == i).first()[0] }) elif request.args['interval'] == 'week': # distribution by weekday yellow_trips = TripStats.query.filter(TripStats.taxi_type == 2).all() green_trips = TripStats.query.filter(TripStats.taxi_type == 1).all() mid_data_yellow = {} mid_data_green = {} for i in range(0, 8): for trip in yellow_trips: if i in mid_data_yellow and trip.datetime.weekday() == i: mid_data_yellow[i] += trip.total_record_cnt elif trip.datetime.weekday() == i: mid_data_yellow[i] = trip.total_record_cnt for i in range(0, 8): for trip in green_trips: if i in mid_data_green and trip.datetime.weekday() == i: mid_data_green[i] += trip.total_record_cnt elif trip.datetime.weekday() == i: mid_data_green[i] = trip.total_record_cnt for key, val in mid_data_yellow.items(): out_data.append({ "date": key, "type": 2, "count": val }) for key, val in mid_data_green.items(): out_data.append({ "date": key, "type": 1, "count": val }) return return_json(out_data)
def get_avg_amount(self): c1 = func.sum(DBEntry.amount) c2 = func.sum(DBProjectUser.balancing) sum_balancing_q = database.session.query(c2)\ .filter(DBProjectUser.project_id == self.project_id) avg_q = database.session.query(c1.op("/")(sum_balancing_q.subquery()))\ .filter(DBEntry.project_id == self.project_id) return avg_q.scalar()
def analytics_time_spent_to_music(): return dict(users=[ (db.session.query(User).get(user_id), timedelta_in_words(int(time_spent), 2)) for user_id, time_spent in db.session.query(Scrobble.user_id, func.sum(ApproximateTrackLength.length)).\ outerjoin((ApproximateTrackLength, Scrobble.approximate_track_length)).\ group_by(Scrobble.user_id).\ order_by(-func.sum(ApproximateTrackLength.length)) ])
def __call__(self, column_clause, cuboid=None): if cuboid and cuboid.fact_count_column is not None: count = func.sum(cuboid.fact_count_column) return case([(count == 0, 0)], else_=( func.sum(column_clause * cuboid.fact_count_column) / cast(count, types.Numeric))) return func.avg(column_clause)
def graphs_stats(): P=models.Projects.query.all() q_sum = (db.session.query( Projects.id.label("project_id"), func.sum(case([(Tasks.complete == True, 1)], else_=0)).label("x"), func.sum(case([(and_(Tasks.deadline != None, Tasks.completeDate != None, Tasks.deadline > Tasks.completeDate), 1)], else_=0)).label("y"), func.count(Tasks.id).label("total"), ).outerjoin(Goals, Projects.goals).outerjoin(Strategies, Goals.strategies).outerjoin(Tasks, Strategies.tasks).group_by(Projects.id)) return render_template("graph_stats.html", P=P,q_sum=q_sum,zipit=zip(P,q_sum))
def get_billing_data_per_year(year, output_type): if output_type == 'day': billing_data = db_session.query(func.unix_timestamp(Usage.usage_date), func.sum(Usage.cost), Usage.usage_value, Usage.measurement_unit). \ filter(func.extract('year', Usage.usage_date) == year).group_by(func.unix_timestamp(Usage.usage_date)) else: billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \ filter(func.extract('year', Usage.usage_date) == year).group_by(func.extract(output_type, Usage.usage_date)) return billing_data
def balance(self): #empty tables return None instead of 0. So replace Nones with 0s money_in=int(self.session.query(func.sum(db.Pay.amount)).filter(db.Pay.user==self._user).scalar() or 0) money_out=int(self.session.query(func.sum(db.Sale.amount)).filter(db.Sale.user==self._user).scalar() or 0) transfers_in=int(self.session.query(func.sum(db.Transfer.amount)).filter(db.Transfer.recipient==self._user).scalar() or 0) transfers_out=int(self.session.query(func.sum(db.Transfer.amount)).filter(db.Transfer.sender==self._user).scalar() or 0) external_in=int(self.session.query(func.sum(db.PayExternal.amount)).filter(db.PayExternal.user==self._user).scalar() or 0) res=money_in-money_out+transfers_in-transfers_out+external_in return res
def get_billing_data_per_year_month(year, value_to_match, output_type): if year == value_to_match: billing_data = db_session.query(Usage.project_id, func.sum(Usage.cost)). \ filter(func.extract('year', Usage.usage_date) == year).group_by(Usage.project_id) else: billing_data = db_session.query(Usage.project_id, func.sum(Usage.cost)). \ filter(func.extract('year', Usage.usage_date) == year, func.extract(output_type, Usage.usage_date) == value_to_match).group_by(Usage.project_id) return billing_data
def fetch_dates_counts(word, exact_search, request): ''' vrati pole poli typu [date, count] ''' if exact_search: rows = request.db_session.query(Occurence.date, func.sum(Occurence.count).label("count")).\ join(Word).filter(Word.text.like(word)).group_by(Occurence.date).order_by(Occurence.date).all() else: word = strip_accents(word.lower()) rows = request.db_session.query(Occurence.date, func.sum(Occurence.count).label("count")).\ join(Word).filter(Word.base_word.like(word+'%')).group_by(Occurence.date).having(Occurence.date > DATE).order_by(Occurence.date).all() return rows
def get_total_row(self): filters = self._filter_filters(self.helper.sql_alchemy_filters) filter_values = self.helper.sql_alchemy_filter_values query = ( self.helper.adapter.session_helper.Session.query( func.sum(self.table.c.count), func.sum(self.table.c.visit_on_time), ) .filter(*filters) .params(filter_values) ) return ["Total"] + [r for r in query.first()]
def get_billing_data_per_year_per_center(year, project_ids, output_type): if output_type == 'week': billing_data = db_session.query(Usage.project_id, func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \ filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \ group_by(func.extract(output_type, Usage.usage_date)) else: billing_data = db_session.query(func.extract(output_type, Usage.usage_date), func.sum(Usage.cost)). \ filter(func.extract('year', Usage.usage_date) == year, Usage.project_id.in_(project_ids)). \ group_by(func.extract(output_type, Usage.usage_date)) return billing_data
def _instance_data_get_for_user(context, project_id, user_id): result = model_query(context, models.Instance, ( func.count(models.Instance.id), func.sum(models.Instance.vcpus), func.sum(models.Instance.memory_mb))).\ filter_by(project_id=project_id) if user_id: result = result.filter_by(user_id=user_id).first() else: result = result.first() # NOTE(vish): convert None to 0 return (result[0] or 0, result[1] or 0, result[2] or 0)
def analytics_track_length_artist_top(): user = db.session.query(User).get(int(request.args.get("user"))) return dict(user=user, top=[ (artist, timedelta_in_words(int(length), 2)) for artist, length in db.session.query(Scrobble.artist, func.sum(ApproximateTrackLength.length)).\ outerjoin((ApproximateTrackLength, Scrobble.approximate_track_length)).\ filter(Scrobble.user == user, ApproximateTrackLength.track != None).\ group_by(Scrobble.artist).\ order_by(-func.sum(ApproximateTrackLength.length)) [:1000] ])
def test_recursive_union_alias_two(self): # I know, this is the PG VALUES keyword, # we're cheating here. also yes we need the SELECT, # sorry PG. t = select(func.values(1).label("n")).cte("t", recursive=True) t = t.union_all(select(t.c.n + 1).where(t.c.n < 100)).alias("ta") s = select(func.sum(t.c.n)) self.assert_compile( s, "WITH RECURSIVE t(n) AS " "(SELECT values(:values_1) AS n " "UNION ALL SELECT t.n + :n_1 AS anon_1 " "FROM t " "WHERE t.n < :n_2) " "SELECT sum(ta.n) AS sum_1 FROM t AS ta", )
def _build_R_ui(self): top_songs_sorted = session.query(TopSong)\ .order_by(TopSong.date.desc(), TopSong.song_id.desc())\ .limit(self.n_top_songs)\ .subquery('top_songs_sorted') top_songs = session.query( top_songs_sorted.c.country_id, top_songs_sorted.c.song_id, func.sum(top_songs_sorted.c.streams).label('total_streams'))\ .group_by(top_songs_sorted.c.country_id, top_songs_sorted.c.song_id)\ .all() ratings_matrix = pd.DataFrame(top_songs).pivot( 'country_id', 'song_id', 'total_streams').fillna(0).astype(float) return np.log(1 + ratings_matrix / self.eps)
def summarise(cls, start_ts=0, end_ts=0): """"Gets job statistics finished between start_ts and end_ts. Grouped by host """ id_query = Job.id_between(start_ts, end_ts) query = cls.query.join(Host).\ filter(cls.job_id.in_(id_query)).\ group_by(Host.name).\ with_entities(Host.name, func.count(cls.job_id), func.sum(cls.cores)) fields = ['host', 'job_count', 'cores'] return [dict(zip(fields, q)) for q in query.all()]
def workspace_hour_data_active(today=None, session=None): today = _today(today) created, session = _session(session) spec = and_(ActivityLog.date == today, ActivityLog.command != "idle") cols = ['Workspace', 'Hour', 'Time'] title = "Workspace & Hour - Active" res = session.query(ActivityLog.workspace, ActivityLog.hour, func.sum(ActivityLog.seconds))\ .filter(spec)\ .group_by(ActivityLog.workspace, ActivityLog.hour) res = _if_created(created, res, session) return {"title": title, "cols": cols, "data": res}
def workspace_command_data(today=None, session=None): today = _today(today) spec = and_(ActivityLog.date == today) cols = ['Workspace', 'Command', 'Time'] title = "Workspace & Command" created, session = _session(session) res = session.query(ActivityLog.workspace, ActivityLog.command, func.sum(ActivityLog.seconds))\ .filter(spec)\ .group_by(ActivityLog.workspace, ActivityLog.command) res = _if_created(created, res, session) return {"title": title, "cols": cols, "data": res}
def show_todo_list(): #form = TodoListForm() if request.method == 'GET': #Businesslist = Business.query.filter_by(id=id).first_or_404() Businesslists = db.session.query(TodoList,TodoList.id,TodoList.user_id,TodoList.title,TodoList.score, Business.savetime, func.sum(Business.number).label('number') )\ .filter_by(user_id=current_user.id)\ .join(Business, TodoList.id == Business.id).filter(Business.savetime < datetime.today(),Business.savetime > (datetime.today() + timedelta(days = -1)))\ .group_by(TodoList.title).all() BusinessSum = db.session.query(TodoList, TodoList.user_id,func.sum(Business.number).label('sum'))\ .filter_by(user_id=current_user.id)\ .join(Business, TodoList.id == Business.id).filter(Business.savetime < datetime.today(),Business.savetime > (datetime.today() + timedelta(days = -1))).all() if len(Businesslists) < 1: flash("没有家务日志,请添加") return render_template('index.html', todolists=Businesslists, todosum=BusinessSum)
def mostPopular(): pID = [] pSrc = [] pName = [] pRecord = sessionDB.query(OrderList.pID, func.sum(OrderList.quantity).label('total'))\ .group_by(OrderList.pID).order_by('total')[:-4:-1] for p in pRecord: pID.append(p.pID) pPicture = sessionDB.query(Product.picture, Product.p_name).filter(Product.pID.in_(pID)) for p in pPicture: pSrc.append(p.picture) pName.append(p.p_name) return pSrc, pName
def investpercentage(): overview = {} data = [] items = db.session.query(func.sum(Invest.money), Invest.p2p_id, P2P.name).filter(Invest.p2p_id == P2P.id, Invest.status == 0).group_by( Invest.p2p_id).all() for i in items: item = {} item["sum_money"] = float(i[0]) item["p2p_id"] = int(i[1]) item["p2p_name"] = str(i[2]) data.append(item) overview["percentage"] = data return jsonify(overview)
def check_funds_blackjack(): try: data = json.loads(request.data) bet_amount = data["amount"] total_tickets = (db.session.query(func.sum( Transaction.ticket_amount)).filter( Transaction.user_id == session["user_id"]).scalar()) if bet_amount > total_tickets: return { "success": False, "message": "You are wagering more tickets than you currently have", } return {"success": True} except json.decoder.JSONDecodeError: return {"error": "Malformed request"}, 400
def Create(self, config: corpus_pb2.Corpus): with self.Session() as session: if not self.IsDone(session): self.Import(session, config) self.SetDone(session) session.commit() # Logging output. num_input_files = session.query(PreprocessedContentFile).count() num_files = ( session.query(PreprocessedContentFile) .filter(PreprocessedContentFile.preprocessing_succeeded == True) .count() ) input_chars, input_lines, total_walltime, total_time, = session.query( func.sum(PreprocessedContentFile.charcount), func.sum(PreprocessedContentFile.linecount), func.sum(PreprocessedContentFile.wall_time_ms), func.sum(PreprocessedContentFile.preprocess_time_ms), ).first() char_count, line_count = ( session.query( func.sum(PreprocessedContentFile.charcount), func.sum(PreprocessedContentFile.linecount), ) .filter(PreprocessedContentFile.preprocessing_succeeded == True) .first() ) app.Log( 1, "Content files: %s chars, %s lines, %s files.", humanize.Commas(input_chars), humanize.Commas(input_lines), humanize.Commas(num_input_files), ) app.Log( 1, "Pre-processed %s files in %s (%.2fx speedup).", humanize.Commas(num_input_files), humanize.Duration((total_walltime or 0) / 1000), (total_time or 1) / (total_walltime or 1), ) app.Log( 1, "Pre-processing discard rate: %.1f%% (%s files).", (1 - (num_files / max(num_input_files, 1))) * 100, humanize.Commas(num_input_files - num_files), ) app.Log( 1, "Pre-processed corpus: %s chars, %s lines, %s files.", humanize.Commas(char_count), humanize.Commas(line_count), humanize.Commas(num_files), )
def summary(): end_sum = db.session.query(trans.act_group.label("act_group"),func.sum(trans.amount).label('total')).group_by(trans.act_group).order_by(trans.act_int) result = convert_q_json(end_sum) final_totals = {} final_totals['bounds'] = bounds #final_totals['totals'] = result response = app.response_class( response=json.dumps(final_totals), status=200, mimetype='application/json' ) print(response) return response
def Global_sales_year_data(): results = db.session.query(video_game.Year, func.sum(video_game.Global_Sales)).group_by( video_game.Year).all() video_game_year = [result[0] for result in results] video_game_global_sales_year = [result[1] for result in results] trace = { "x": video_game_year, "y": video_game_global_sales_year, "type": "bar", "marker": { "color": "black" } } return jsonify(trace)
def total_received_incl_pending_wei(self): """ The total received by an account, counting transfers that are either pending or complete locally """ amount = ( db.session.query( func.sum(server.models.credit_transfer.CreditTransfer._transfer_amount_wei).label('total') ) .execution_options(show_all=True) .filter(server.models.credit_transfer.CreditTransfer.recipient_transfer_account_id == self.id) .filter(or_( server.models.credit_transfer.CreditTransfer.transfer_status == TransferStatusEnum.COMPLETE, server.models.credit_transfer.CreditTransfer.transfer_status == TransferStatusEnum.PENDING)) .first().total ) return amount or 0
def get_cluster_stats(self, context, project_id=None): query = model_query(models.Cluster) node_count_col = models.Cluster.node_count master_count_col = models.Cluster.master_count ncfunc = func.sum(node_count_col + master_count_col) if project_id: query = query.filter_by(project_id=project_id) nquery = query.session.query(ncfunc.label("nodes")).filter_by( project_id=project_id) else: nquery = query.session.query(ncfunc.label("nodes")) clusters = query.count() nodes = int(nquery.one()[0]) if nquery.one()[0] else 0 return clusters, nodes
def get(self, year): notas = \ Despesas.query.with_entities( Endereco.regiaoIes, func.sum(Rubrica.valor_pago_reais).label('despesa') ).filter( Despesas.id == Rubrica.id_despesa, Despesas.ano_mes_lancamento.like(f"{year}%"), Endereco.cod_ies == Despesas.idIes ).group_by( Endereco.regiaoIes ).all() serialized_schema = RegionsWithExpenses(many=True) output = serialized_schema.dump(notas).data return output
def _get_patients(payment_min, payment_max): stmt = db.session.query(Payment.patient_id, func.sum(Payment.amount).label('total_amount')) if payment_min: stmt = stmt.filter(Payment.amount >= payment_min) if payment_max: stmt = stmt.filter(Payment.amount <= payment_max) stmt = stmt.group_by(Payment.patient_id).subquery() query = db.session.query(Patient, func.ifnull(stmt.c.total_amount, 0.0)). \ outerjoin(stmt, Patient.id == stmt.c.patient_id) patients_tuple = query.all() patients_list = map(_sql_tuple_to_patients, patients_tuple) patients_json = getPatientsSchema.dump(patients_list) return patients_json
def _record_test_rerun_counts(self, test_list): job = self.step.job rerun_count = db.session.query(func.count(TestCase.id)).filter( TestCase.job_id == job.id, TestCase.reruns > 0, ).as_scalar() create_or_update(ItemStat, where={ 'item_id': self.step.id, 'name': 'test_rerun_count', }, values={ 'value': sum(1 for t in test_list if t.reruns), }) create_or_update(ItemStat, where={ 'item_id': job.id, 'name': 'test_rerun_count', }, values={ 'value': rerun_count, }) instance = try_create(ItemStat, where={ 'item_id': job.build_id, 'name': 'test_rerun_count', }, defaults={'value': rerun_count}) if not instance: ItemStat.query.filter( ItemStat.item_id == job.build_id, ItemStat.name == 'test_rerun_count', ).update( { 'value': select([func.sum(ItemStat.value)]).where( and_( ItemStat.name == 'test_rerun_count', ItemStat.item_id.in_( select([Job.id]).where( Job.build_id == job.build_id, )))), }, synchronize_session=False)
def monthly_breakdown(today=None, session=None): created, session = _session(session) _really_today = _today() today = _today(today) link_format = "<div class='{css_class}'><a href='/{date}/'>{day}<br>{day_data}</a></div>" title = "Month Activity - %s" % today.strftime("%b %Y") cols = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'] rows = [] row = [] cal = calendar.Calendar(6) cnt = 0 for t in cal.itermonthdates(today.year, today.month): spec = and_(ActivityLog.date == t, ActivityLog.command != 'idle') res = session.query(ActivityLog.date, ActivityLog.workspace, func.sum(ActivityLog.seconds))\ .filter(spec)\ .group_by(ActivityLog.workspace)\ .order_by(ActivityLog.workspace) if cnt % 7 == 0 and len(row) != 0: rows.append(row) row = [] day_data = [] print("RES:", res) for r in res: if r[2] > 0: contents = "{workspace} <span class='pull-right'>{hh_mm_ss}</span>".format( workspace=r[1], hh_mm_ss=hh_mm_ss(r[2])) day_data.append(contents) css_class = ["day"] if t == _really_today: css_class.append('really_today') if t == today: css_class.append("today") link = link_format.format(date="%s" % t, day="%s" % t.day, day_data="%s" % "<br>".join(day_data), css_class=" ".join(css_class)) row.append(link) cnt += 1 rows.append(row) ssr(created, session) return {"title": title, "cols": cols, "data": rows}
def get_amount_pledged_datapoints(self): pledges_per_day = db.session.query(func.date( Pledge.time_created), func.sum(Pledge.amount)).filter( Project.id == self.id, Project.id == Pledge.project_id).group_by( func.date(Pledge.time_created)).all() datapoints = [[i + 1, 0] for i in range(self.duration + 2) ] # [(0,0), (1,0), ..., (30,0)] for p in pledges_per_day: time_pledged = datetime.datetime.strptime(p[0], "%Y-%m-%d") day_num = (time_pledged.date() - self.time_start.date()).days amount = p[1] datapoints[day_num] = [day_num, amount] return datapoints
def get_squad(self, club_id): squad = [] s = self.session() players = s.query(Player).filter(Player.appearances.any(team_id=club_id)).all() for player in players: goals = s.query(Event).filter(Event.player_id == player.id, Event.type == 'i-report-ball').count() yellow_cards = s.query(Event).filter(Event.player_id == player.id, Event.type == 'i-yellow-card').count() red_cards = s.query(Event).filter(Event.player_id == player.id, Event.type == 'i-red-card').count() last_number = s.query(Squad.number).filter(Squad.player_id == player.id).limit(1).first()[0] numbers = s.query(Squad.number, func.count(Squad.number)).filter(Squad.player_id == player.id).group_by( Squad.number).all() minutes_played = s.query(func.sum(Squad.time_played)).filter(Squad.player_id == player.id).first()[0] squad.append((player.name, minutes_played, numbers, last_number, goals, yellow_cards, red_cards)) return squad
def get_profile_view(user_id): if user_id is None and "user_id" in session: user_id = session["user_id"] try: user_profile = get_user_profile(user_id) total_tickets = (db.session.query(func.sum( Transaction.ticket_amount)).filter( Transaction.user_id == user_id).scalar()) return { "name": user_profile.name, "username": user_profile.username, "registration_datetime": user_profile.registration_datetime, "total_tickets": total_tickets, } except NoResultFound: return {"error": "Result not found"}, 404
def get_current_month_put_amount(user_id=None): """ 获取当月投资总额 :return: """ start_time, end_time = get_current_month_time_ends() condition = [ ApplyPut.create_time >= start_time, ApplyPut.create_time <= end_time ] if user_id: condition.append(ApplyPut.user_id == user_id) res = db.session \ .query(func.sum(ApplyPut.money_apply).label('amount')) \ .filter(*condition) \ .first() return res.amount or 0
def posterpoints_badge(pid): post = Post.query.filter_by(post_id=pid).first() points = db.session.query( Userpoints.user_id, func.sum(Pointrules.add_points)).outerjoin( Pointrules, Userpoints.points_id == Pointrules.point_id).group_by( Userpoints.user_id).filter( Userpoints.user_id == post.user_id).all() #print(points) if points: if points[0][1] >= 100: #print(points[0][1]) pointbadgefirst = Userbadges.query.filter( Userbadges.badge_id == 17, Userbadges.user_id == points[0][0]).all() if not pointbadgefirst: pointbadge = Userbadges(badge_id=17, user_id=points[0][0]) db.session.add(pointbadge) db.session.commit() if points[0][1] >= 390: #print(points[0][1]) pointbadgefirst = Userbadges.query.filter( Userbadges.badge_id == 18, Userbadges.user_id == points[0][0]).all() if not pointbadgefirst: pointbadge = Userbadges(badge_id=18, user_id=points[0][0]) db.session.add(pointbadge) db.session.commit() if points[0][1] >= 930: #print(points[0][1]) pointbadgefirst = Userbadges.query.filter( Userbadges.badge_id == 19, Userbadges.user_id == points[0][0]).all() if not pointbadgefirst: pointbadge = Userbadges(badge_id=19, user_id=points[0][0]) db.session.add(pointbadge) db.session.commit() if points[0][1] >= 1390: #print(points[0][1]) pointbadgefirst = Userbadges.query.filter( Userbadges.badge_id == 20, Userbadges.user_id == points[0][0]).all() if not pointbadgefirst: pointbadge = Userbadges(badge_id=20, user_id=points[0][0]) db.session.add(pointbadge) db.session.commit() return post, points
def get_aggregated_stats(environment_id): unit = db_session.get_session() now = timeutils.utcnow_ts() query = unit.query( models.Instance.instance_type, func.sum( func.coalesce(models.Instance.destroyed, now) - models.Instance.created), func.count()).filter( models.Instance.environment_id == environment_id) res = query.group_by(models.Instance.instance_type).all() return [{ 'type': int(record[0]), 'duration': int(record[1]), 'count': int(record[2]) } for record in res]
def Stats(self, request, context): with session_scope() as session: date_ = func.date_trunc("day", Report.time_received) stats = (session.query(date_, func.sum(StrandInReport.state)).join( StrandInReport, StrandInReport.report_id == Report.report_id).filter( StrandInReport.strand_id == request.strand_id).group_by( date_).all()) return sb_pb2.StatsRes( strand_id=1, times=[timestamp_from_datetime(datetime.datetime.now())], total_incubating_strands=[6], total_infected_strands=[3], total_removed_strands=[2], )
def resolve_token_votes(self, args, context, info): votes = [] for token_category in self.get_token_categories(): query = self.db.query( func.sum(getattr(self.get_vote_class(), "vote_value"))).filter_by( vote_spec_id=self.id, tombstone_date=None, token_category_id=token_category.id) # when there is no votes, query.first() equals (None,) # in this case set num_token to 0 num_token = query.first()[0] or 0 votes.append( VotesByCategory(token_category_id=token_category.graphene_id(), num_token=num_token)) return votes
def index(): if current_user.role != 1: return redirect(url_for('base')) if request.method == 'POST': data = my_json.loads(request.form['data']) airline = data['airline'] aircraft = data['aircraft'] services = list(data['services']) try: act = LandingAct(ID_AIRLINE=airline, ID_DISPATCHER=current_user.get_id(), ID_AIRCRAFT=aircraft, DATE=date.today()) db.session.add(act) db.session.commit() for i in range(len(services)): list_service = ListService(ID_ACT=act.ID_ACT, ID_SERVICE=services[i]) db.session.add(list_service) db.session.commit() cost = db.session.query(func.sum(Tariff.COST).label('total')).join( ListService, ListService.ID_SERVICE == Tariff.ID_SERVICE).filter( Tariff.ID_AIRCRAFT == aircraft, ListService.ID_ACT == act.ID_ACT).first() if cost.total is None: db.session.add(Score(SCORE=0, ID_ACT=act.ID_ACT)) else: db.session.add(Score(SCORE=cost.total, ID_ACT=act.ID_ACT)) except Exception as e: print() print(e) db.session.rollback() return " ", 202 else: db.session.commit() return " ", 200 airlines = db.session.query(Airline).all() aircrafts = db.session.query(Aircraft).all() tex = db.session.query(TechService).all() earth = db.session.query(EarthService).all() return render_template('add.html', airline=airlines, aircraft=aircrafts, tex_service=tex, earth_service=earth)
def index(self): lst = [] SumDoanhSoTheoNgay = db.session.query( func.sum(Bill.total_price).label('sum'), Bill.order_time).filter( func.month(Bill.order_time) == datetime.datetime.today().month).group_by( func.date(Bill.order_time)).all() # #co check status # SumDoanhSoTheoNgay = db.session.query(func.sum(Bill.total_price).label('sum'), Bill.order_time).filter( # Bill.status == 4).filter(func.month(Bill.order_time) == datetime.datetime.today().month).group_by( # func.date(Bill.order_time)).all() for i in SumDoanhSoTheoNgay: lst.append([i[1].day, int(i[0])]) lst.sort(key=myFunc) return self.render('admin/analytics.html', lst=lst)
def size_report(self): from ckan import model from ckanext.archiver.model import Archival kb = 1024 mb = 1024*1024 gb = pow(1024, 3) size_bins = [ (kb, '<1 KB'), (10*kb, '1-10 KB'), (100*kb, '10-100 KB'), (mb, '100 KB - 1 MB'), (10*mb, '1-10 MB'), (100*mb, '10-100 MB'), (gb, '100 MB - 1 GB'), (10*gb, '1-10 GB'), (100*gb, '10-100 GB'), (gb*gb, '>100 GB'), ] previous_bin = (0, '') counts = [] total_sizes = [] print '{:>15}{:>10}{:>20}'.format( 'file size', 'no. files', 'files size (bytes)') for size_bin in size_bins: q = model.Session.query(Archival) \ .filter(Archival.size > previous_bin[0]) \ .filter(Archival.size <= size_bin[0]) \ .filter(Archival.cache_filepath != '') \ .join(model.Resource, Archival.resource_id == model.Resource.id) \ .filter(model.Resource.state != 'deleted') \ .join(model.Package, Archival.package_id == model.Package.id) \ .filter(model.Package.state != 'deleted') count = q.count() counts.append(count) total_size = model.Session.query(func.sum(Archival.size)) \ .filter(Archival.size > previous_bin[0]) \ .filter(Archival.size <= size_bin[0]) \ .filter(Archival.cache_filepath != '') \ .join(model.Resource, Archival.resource_id == model.Resource.id) \ .filter(model.Resource.state != 'deleted') \ .join(model.Package, Archival.package_id == model.Package.id) \ .filter(model.Package.state != 'deleted') \ .all()[0][0] total_size = int(total_size or 0) total_sizes.append(total_size) print '{:>15}{:>10,}{:>20,}'.format(size_bin[1], count, total_size) previous_bin = size_bin print 'Totals: {:,} {:,}'.format(sum(counts), sum(total_sizes))
def index(): if 'username' in session: name = session['username'] catdata = db.session.query(category_master).filter( category_master.user_id == session['username']).all() expdata = db.session.query( expense_master.cat_id, func.sum(expense_master.amount).label('total')).filter( expense_master.user_id == session['username']).group_by( expense_master.cat_id).all() return render_template('index.html', catdata=catdata, expdata=expdata, name=session['username']) else: error = 'Invalid request. Please enter username and password!' return render_template('login.html', error=error)