diff options
| -rw-r--r-- | maconomy/mssql.py | 13 | ||||
| -rw-r--r-- | maconomy/repositories.py | 43 | ||||
| -rw-r--r-- | maconomy/utils.py | 8 | ||||
| -rw-r--r-- | requirements/common.txt | 1 | ||||
| -rw-r--r-- | requirements/mssql.txt | 2 | ||||
| -rw-r--r-- | requirements/oracle.txt | 2 |
6 files changed, 52 insertions, 17 deletions
diff --git a/maconomy/mssql.py b/maconomy/mssql.py new file mode 100644 index 0000000..63121b1 --- /dev/null +++ b/maconomy/mssql.py @@ -0,0 +1,13 @@ +import pyodbc + + +# Based on https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-python +def connect(user, password, server_uri): + driver = '{ODBC Driver 13 for SQL Server}' + if ':' in server_uri: + server, rest = server_uri.split(':') + port, database = server_uri.split('/') + else: + server, database = server_uri.split('/') + port = '1443' + return pyodbc.connect('DRIVER={};PORT={};SERVER={};DATABASE={};UID={};PWD={}'.format(driver, port, server, database, user, password)) diff --git a/maconomy/repositories.py b/maconomy/repositories.py index 2dcb019..54d106e 100644 --- a/maconomy/repositories.py +++ b/maconomy/repositories.py @@ -1,11 +1,18 @@ -import cx_Oracle +try: + import cx_Oracle as db_driver +except: + from maconomy import mssql as db_driver +from datetime import date from maconomy.models import Timesheet +from maconomy.utils import previous_monday + def create_db(config): user = config.get("db", "user") pw = config.get("db", "password") server = config.get("db", "server") - return cx_Oracle.connect(user, pw, server) + return db_driver.connect(user, pw, server) + class DBRepository: def __init__(self, dbcon): @@ -15,21 +22,23 @@ class DBRepository: def close(self): self.cursor.close() + class TimeRegistrationRepository(DBRepository): def all_active(self): - query = """SELECT e.EMPLOYEENUMBER,e.NAME1,e.ELECTRONICMAILADDRESS, t.WEEKNUMBER, t.SUBMITTED, t.APPROVED, e.SUPERIOREMPLOYEE from EMPLOYEE e - LEFT OUTER JOIN TIMESHEETHEADER t - ON e.EMPLOYEENUMBER = t.EMPLOYEENUMBER - and t.PERIODSTART=to_char(trunc(sysdate-7, 'IW'),'YYYY.MM.DD') - WHERE e.blocked=0 - and (e.DATEENDEMPLOYMENT >= to_char(sysdate,'YYYY.MM.DD') or e.DATEENDEMPLOYMENT = ' ') - and e.employeenumber <> '99' - and e.MUSTUSETIMESHEETS=1 - ORDER BY e.employeenumber - """ - - res = self.cursor.execute(query) - rows = res.fetchall() - - return [Timesheet.from_result(r) for r in rows] + period_start = previous_monday().strftime("%Y.%m.%d") + today = date.today().strftime("%Y.%m.%d") + query = """SELECT e.EMPLOYEENUMBER,e.NAME1,e.ELECTRONICMAILADDRESS, t.WEEKNUMBER, t.SUBMITTED, t.APPROVED, e.SUPERIOREMPLOYEE from EMPLOYEE e + LEFT OUTER JOIN TIMESHEETHEADER t + ON e.EMPLOYEENUMBER = t.EMPLOYEENUMBER + and t.PERIODSTART='{period_start}' + WHERE e.blocked=0 + and (e.DATEENDEMPLOYMENT >= '{today}' or e.DATEENDEMPLOYMENT = ' ') + and e.employeenumber <> '99' + and e.MUSTUSETIMESHEETS=1 + ORDER BY e.employeenumber + """.format(period_start=period_start, today=today) + + res = self.cursor.execute(query) + rows = res.fetchall() + return [Timesheet.from_result(r) for r in rows] diff --git a/maconomy/utils.py b/maconomy/utils.py index 3cfeb2d..de5ccdd 100644 --- a/maconomy/utils.py +++ b/maconomy/utils.py @@ -1,4 +1,6 @@ from collections import defaultdict +import datetime + def per_manager(timesheets): per_manager = defaultdict(list) @@ -8,5 +10,11 @@ def per_manager(timesheets): per_manager[manager_id].append(timesheet) return per_manager + def employees(timesheets): return dict([(t.employee.id, t.employee) for t in timesheets]) + + +def previous_monday(date=None): + today = date or datetime.date.today() + return today + datetime.timedelta(days=-today.weekday(), weeks=-1) diff --git a/requirements/common.txt b/requirements/common.txt new file mode 100644 index 0000000..af42dda --- /dev/null +++ b/requirements/common.txt @@ -0,0 +1 @@ +configparser diff --git a/requirements/mssql.txt b/requirements/mssql.txt new file mode 100644 index 0000000..5855442 --- /dev/null +++ b/requirements/mssql.txt @@ -0,0 +1,2 @@ +-r common.txt +pyodbc diff --git a/requirements/oracle.txt b/requirements/oracle.txt new file mode 100644 index 0000000..14ba882 --- /dev/null +++ b/requirements/oracle.txt @@ -0,0 +1,2 @@ +-r common.txt +cx_Oracle |