import cx_Oracle
from maconomy.models import Timesheet

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)

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):
      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]