def save(self):
self.validate()
cur = db.get_conn().cursor()
query = self.get_save_command()
print(query)
cur.execute(query)
db.get_conn().commit()
cur.close()
def collect_projects():
cur = db.get_conn().cursor()
cur.execute(f"SELECT * FROM {Project.Table.name}")
result = [
Project.from_result(cur.column_names, row) for row in cur.fetchall()
]
cur.close()
return result
def get_contributors_for_project(project_nr):
cur = db.get_conn().cursor()
command = f"SELECT DISTINCT emplNr FROM {entry.Entry.Table.name} " \
f"WHERE projectNr={project_nr}"
cur.execute(command)
result = cur.fetchall()
cur.close()
return [employee.Employee.find(int(row[0])) for row in result]
def export_table(path: str) -> None:
table_name = os.path.basename(path).split(".")[0]
cur = db.get_conn().cursor()
cur.execute(f"SELECT * FROM {table_name}")
with open(path, "w", encoding="UTF-8") as f:
f.write(DELIMITER.join(cur.column_names) + "\n")
for row in cur.fetchall():
line = DELIMITER.join(map(to_str, row))
f.write(line + "\n")
def collect_employees():
cur = db.get_conn().cursor()
cur.execute(f"SELECT * FROM {employee.Employee.Table.name}")
result = [
employee.Employee.from_result(cur.column_names, row)
for row in cur.fetchall()
]
cur.close()
return result
def calculate_hours_for_project(project_nr):
cur = db.get_conn().cursor()
command = f"SELECT SUM(TIMEDIFF(end_, start_)) FROM {entry.Entry.Table.name} " \
f"WHERE projectNr={project_nr}"
cur.execute(command)
res = cur.fetchone()[0]
cur.close()
if res is None:
res = decimal.Decimal("0")
return res / 10_000 # TIMEDIFF() returns 10'000 per hour
def get_hours_per_day(project_nr: int):
query = f"SELECT DATE(e.start_) AS 'date', SUM(TIMEDIFF(e.end_, e.start_))/10000 AS 'hours' " \
f"FROM {entry.Entry.Table.name} AS e " \
f"WHERE e.projectNr = {project_nr} " \
f"GROUP BY DATE(e.start_);"
cur = db.get_conn().cursor()
cur.execute(query)
res = cur.fetchall()
cur.close()
return res
def insert(self, connection=None):
self.validate()
if connection is None:
connection = db.get_conn()
cur = connection.cursor()
command = self.get_insert_command()
print(command)
cur.execute(command)
connection.commit()
cur.close()
def calculate_worked_hours(empl_nr: int) -> float:
cur = db.get_conn().cursor()
command = f"SELECT SUM(TIMEDIFF(end_, start_)) FROM {entry.Entry.Table.name} " \
f"WHERE emplNr={empl_nr} " \
f"AND end_ <= {util.date_to_sql(datetime.date.today() + datetime.timedelta(days=1))}"
print(command)
cur.execute(command)
res = cur.fetchone()[0]
if res is None:
res = decimal.Decimal("0")
return res / 10_000 # TIMEDIFF() returns 10'000 per hour
def collect_absences(empl_nr, sort=False):
cur = db.get_conn().cursor()
command = f"SELECT * FROM {absence.Absence.Table.name} WHERE emplNr={empl_nr}"
print(command)
cur.execute(command)
res = [
absence.Absence.from_result(cur.column_names, res)
for res in cur.fetchall()
]
if sort:
res.sort(key=lambda ab: ab.start)
return res
def collect_entries(empl_nr: int, start: datetime.datetime,
end: datetime.datetime):
cur = db.get_conn().cursor()
sql_start = util.datetime_to_sql(start)
sql_end = util.datetime_to_sql(end)
command = f"SELECT * FROM {entry.Entry.Table.name} " \
f"WHERE emplNr={empl_nr} AND start_ > {sql_start} AND end_ < {sql_end}"
print(command)
cur.execute(command)
return [
entry.Entry.from_result(cur.column_names, res)
for res in cur.fetchall()
]
def get_hours_per_project_for_employee(empl_nr: int):
query = f"SELECT projectNr, SUM(TIMEDIFF(end_, start_)/10000) AS 'hours' " \
f"FROM {entry.Entry.Table.name} " \
f"WHERE emplNr={empl_nr} " \
f"GROUP BY projectNr ORDER BY 'hours' DESC;"
cur = db.get_conn().cursor()
cur.execute(query)
idx_emplnr = cur.column_names.index("projectNr")
idx_hours = cur.column_names.index("hours")
res = {
Project.find(row[idx_emplnr]): row[idx_hours]
for row in cur.fetchall()
}
cur.close()
return res
def import_file(path: str) -> None:
values = []
with open(path, encoding="UTF-8") as f:
columns = map(str.strip, f.readline().split(DELIMITER))
for line in f.readlines():
row = line.strip().split(DELIMITER)
new_row = [cell_to_sql(cell) for cell in row]
values.append(", ".join(new_row))
file_name = os.path.basename(path)
table_name = file_name.split(".")[0]
query = f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({'), ('.join(values)})"
print(query)
conn = db.get_conn()
cur = conn.cursor()
cur.execute("SET FOREIGN_KEY_CHECKS=0;")
cur.execute(query)
cur.execute("SET FOREIGN_KEY_CHECKS=1;")
conn.commit()
cur.close()
def get_number_of_employees():
query = f"SELECT since, until " \
f"FROM {employee.Employee.Table.name} " \
f"ORDER BY since ASC;"
print(query)
cur = db.get_conn().cursor()
cur.execute(query)
data = cur.fetchall()
cur.close()
start = data[0][0]
end = datetime.date.today()
days = [
start + datetime.timedelta(days=x)
for x in range((end - start).days + 1)
]
counts = []
for day in days:
num = 0
for since, until in data:
if (until is None and since < day) \
or (since < day < until):
num += 1
counts.append(num)
return days, counts
def find(empl_nr):
cur = db.get_conn().cursor()
cur.execute(
f"SELECT * FROM {Employee.Table.name} WHERE emplNr={empl_nr}")
return Employee.from_result(cur.column_names, cur.fetchone())
def find(id):
cur = db.get_conn().cursor()
cur.execute(f"SELECT * FROM {Entry.Table.name} WHERE id={id}")
return Entry.from_result(cur.column_names, cur.fetchone())
def get_all_projects_as_json() -> str:
cur = db.get_conn().cursor()
cur.execute(f"SELECT nr, name_ FROM {Project.Table.name}")
result = {int(row[0]): row[1] for row in cur.fetchall()}
cur.close()
return json.dumps(result)
