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 db_driver.connect(user, pw, server)


class DBRepository:
    def __init__(self, dbcon):
        self.db = dbcon
        self.cursor = dbcon.cursor()

    def close(self):
        self.cursor.close()


class TimeRegistrationRepository(DBRepository):
    def all_active(self):
        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]