db_mgr.py
4.87 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
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
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_project(project_id):
"""
Build the flatten list of charges for one project, period by period
:param project_id:
:return:
"""
req_sql = f"""
select p.name as period_name,
a.firstname ||" "|| a.secondname as agent_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 project_id = {project_id}
order by c.period_id
"""
print(req_sql)
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", "Agent", "Service", "Capacity", "Charge"]
nocomma_results.insert(0, headers)
return nocomma_results
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