db_mgr.py
3.66 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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
from app.models import db
def projects():
"""
Build the list of all agents, with their charges summed up in one total
:return:
"""
sql_txt = """
select p.id, p.name, sum(tc.charge_rate) as total_charge
from project as p left join
( select c.project_id, c.charge_rate from charge c )
tc
on p.id = tc.project_id
group by p.id
order by total_charge desc;
"""
all_projects = db.session.execute(sql_txt).fetchall()
return all_projects
def agents():
"""
Build the list of all agents, with their charges summed up in one total
:return:
"""
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 charges_by_agent_tabled(agent_id):
"""
Build the flatten list of charges for one agent, period by period
:param agent_id:
:return:
"""
req_sql = f"""
select p.name as period_name,
p2.name as project_name,
s.name as service_name,
c2.name as capacity_name,
c.charge_rate as charge_rate
from charge as c
join period p on c.period_id = p.id
join project p2 on c.project_id = p2.id
join service s on c.service_id = s.id
join agent a on a.id = c.agent_id
join capacity c2 on c2.id = c.capacity_id
where agent_id = {agent_id}
order by c.period_id
"""
req_res = db.session.execute(req_sql)
results = list(req_res)
# Remove comma
nocomma_results = []
for line in results:
nocomma_line = [str(cell) for cell in line]
nocomma_results.append(nocomma_line)
headers = ["Period", "Project", "Service", "Capacity", "Charge"]
nocomma_results.insert(0, headers)
return nocomma_results
def charges_by_agent_stacked(agent_id):
"""
Build the list of charges for one agent, period by period
:param agent_id:
:return:
"""
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
return all_charges
def charges_by_agent(agent_id):
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