From 3062d98f2c0db4d37953fdacc3f68f51572958b0 Mon Sep 17 00:00:00 2001
From: Markus Krogh <markus@nordu.net>
Date: Sat, 15 Jul 2017 00:04:38 +0200
Subject: MSSQL support

---
 maconomy/mssql.py        | 13 +++++++++++++
 maconomy/repositories.py | 43 ++++++++++++++++++++++++++-----------------
 maconomy/utils.py        |  8 ++++++++
 requirements/common.txt  |  1 +
 requirements/mssql.txt   |  2 ++
 requirements/oracle.txt  |  2 ++
 6 files changed, 52 insertions(+), 17 deletions(-)
 create mode 100644 maconomy/mssql.py
 create mode 100644 requirements/common.txt
 create mode 100644 requirements/mssql.txt
 create mode 100644 requirements/oracle.txt

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
-- 
cgit v1.1