db_mgr.py
1.89 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
from pprint import pprint
from app.models import db, Agent, Charge
def agents():
sql_txt = """
select a.id, a.firstname, a.secondname, sum(tc.charge_rate) as total_charge
from agent as a left join
( select c.agent_id, c.charge_rate from charge c )
tc
on a.id = tc.agent_id
group by a.id
order by total_charge desc;
"""
all_agents = db.session.execute(sql_txt).fetchall()
return all_agents
def stacked_charges_by_agent(agent_id):
all_charges = {}
for (period_id, period_name) in db.session.execute("select id, name from period order by id"):
charge_by_project_req = """select p.name, c.charge_rate from project p left join
charge c on p.id = c.project_id and agent_id = {} and period_id={}
order by p.id""".format(agent_id, period_id)
period_charge = {}
for project_name, project_rate in db.session.execute(charge_by_project_req):
period_charge[project_name] = project_rate
all_charges[period_name] = period_charge
pprint(all_charges)
return all_charges
def charges_by_agent(agent_id):
# all_charges = db.session.query(Charge).all()
periods = db.session.execute("select name from period")
sql_txt = "select p.name, sum(charge_rate) from charge inner join period p on charge.period_id = p.id\
where agent_id={} group by period_id order by p.name" \
.format(agent_id)
request = db.session.execute(sql_txt)
all_charges = []
charges_list = [(period, charge) for (period, charge) in request.fetchall()]
charges_dict = dict(charges_list)
for (p,) in periods:
if p in charges_dict.keys():
all_charges.append([p, charges_dict[p]])
else:
all_charges.append([p, 0])
return all_charges