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)