MSSQL support

1 files changed, 26 insertions, 17 deletions

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]