Python func.sum函数代码示例

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