1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
import cx_Oracle
from maconomy.models import Employee, 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 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 [(Employee(r), Timesheet(r[3:])) for r in rows]
|