« back to all changes in this revision
Viewing changes to WorkTime/WorkTimeModel.py
- browse files at revision 3
- compare with another revision
- download diff
- download tarball
- view history from revision 3
- Committer: Sebastian Żurek
- Date: 2008-08-18 19:20:26 UTC
- Revision ID: sebzur@gmail.com-20080818192026-0eeqz24hbol6apme
Some fixes in model applied
added
removed
WorkTime/WorkTimeModel.py
28
28
""" Returns list with tables names to be created during system initialization
29
29
30
30
"""
31
return ['cases','actions','terms']
32
33
34
def _createTable_cases_postgres(self):
35
return """CREATE TABLE cases(
36
cid SERIAL,
37
name varchar NOT NULL,
38
identifier varchar NOT NULL,
39
signature VARCHAR,
40
leader INTEGER NOT NULL,
41
client INTEGER NOT NULL,
42
type INTEGER,
43
state INTEGER NOT NULL DEFAULT 1,
44
begin DATE NOT NULL DEFAULT current_date,
45
finish DATE,
46
UNIQUE (identifier),
47
CONSTRAINT client_exists FOREIGN KEY (client) REFERENCES clients (cid) ON DELETE CASCADE ON UPDATE CASCADE,
48
CONSTRAINT leader_exists FOREIGN KEY (leader) REFERENCES users (uid) ON DELETE CASCADE ON UPDATE CASCADE
49
)
50
WITH OIDS;"""
51
#CONSTRAINT identifier_check (identifier ~ [a-zA-Z]),
31
return ['actions','terms']
32
52
33
53
34
def _createTable_actions_postgres(self):
54
35
return """CREATE TABLE actions(
87
68
def GetUsersNames(self):
88
69
return self._models['Users'].GetUsersNames()
89
70
90
def GetClientsWithType(self,client_type):
91
return self._models['Klienci'].GetClientsWithType(client_type)
92
93
def GetClientIdByIdentifier(self,identifier):
94
return self._models['Klienci'].GetClientIdByIdentifier(identifier)
95
96
def GetClients(self,cid=None):
97
return self._models['Klienci'].GetClients(cid)
98
99
def GetClientByIdentifier(self,identifier):
100
return self._models['Klienci'].GetClientByIdentifier(identifier)
101
102
103
104
def GetCases(self,client=None,cid=None):
105
""" Returns Clients data in dictionary
106
107
"""
108
query=("select name, identifier, signature, leader, client,cid from cases",
109
"select name, identifier, signature, leader, client,cid from cases where client=%s",
110
"select name, identifier, signature, leader, client, cid from cases where cid=%s")
111
112
if client==None and cid==None:
113
self._cursor.execute(query[0])
114
elif client!=None and cid==None:
115
self._cursor.execute(query[1],(client,))
116
elif client==None and cid!=None:
117
self._cursor.execute(query[2],(cid,))
118
119
result=self._cursor.fetchall()
120
to_return=[]
121
122
if self._dbType=='postgres':
123
if result:
124
for row in result:
125
r_dict={
126
'name':row[0].decode('utf-8'),
127
'identifier':row[1].decode('utf-8'),
128
'signature':row[2],
129
'leader':row[3],
130
'client':row[4],
131
'cid':row[5],
132
'type':0
133
}
134
to_return.append(r_dict)
135
if cid==None:
136
return to_return
137
else:
138
return to_return[0]
139
140
141
def GetCaseClientId(self,cid):
142
""" Returns Clients data in dictionary
143
144
"""
145
query="SELECT client FROM cases WHERE cid=%s"
146
self._cursor.execute(query,(cid,))
147
return self._cursor.fetchone()[0]
148
149
def GetCaseClient(self,cid):
150
""" Returns Clients data in dictionary
151
152
"""
153
query="SELECT client FROM cases WHERE cid=%s"
154
self._cursor.execute(query,(cid,))
155
clientID=self._cursor.fetchone()[0]
156
157
query="SELECT full_name FROM clients WHERE cid=%s"
158
self._cursor.execute(query,(clientID,))
159
160
return self._cursor.fetchone()[0].decode('utf-8')
161
162
163
def GetCaseLeader(self,cid):
164
""" Returns Clients data in dictionary
165
166
"""
167
query="SELECT leader FROM cases WHERE cid=%s"
168
self._cursor.execute(query,(cid,))
169
leaderId=self._cursor.fetchone()[0]
170
171
query="SELECT name, surname FROM users WHERE uid=%s"
172
self._cursor.execute(query,(leaderId,))
173
174
return self._cursor.fetchone()[0].decode('utf-8')
175
176
177
def GetCaseIdentifier(self,cid):
178
""" Returns Clients data in dictionary
179
180
"""
181
query="SELECT identifier FROM cases WHERE cid=%s"
182
self._cursor.execute(query,(cid,))
183
return self._cursor.fetchone()[0]
184
185
def GetCaseIdByIdentifier(self,identifier):
186
""" Returns Clients data in dictionary
187
188
"""
189
query="SELECT cid FROM cases WHERE identifier=%s"
190
self._cursor.execute(query,(identifier,))
191
return self._cursor.fetchone()[0]
192
193
194
195
196
def GetCaseType(self,cid):
197
""" Returns Clients data in dictionary
198
199
"""
200
query="SELECT type FROM cases WHERE cid=%s"
201
self._cursor.execute(query,(cid,))
202
return self._cursor.fetchone()[0]
71
def GetContacts(self,parent = -1, search = '', group = None, children = False, basic=True, address=True, contact=True, invoice=True):
72
return self._models['Klienci'].GetContacts(parent, search , group , children , basic, address, contact, invoice)
203
73
204
74
205
75
# - Insert Methods --------------------------------------------------------------
206
76
207
def InsertCase(self,data):
208
""" Inserts Client data into table.
209
210
Arguments:
211
212
data - dictionary where keys are table columns
213
214
215
"""
216
217
try:
218
print data
219
self._cursor.execute(self.ParseInsertQuery(data,'cases'),data.values())
220
self._db.commit()
221
pubsub.Publisher().sendMessage("Cases.Model.Events", data=('NewCase',[]))
222
except Exception, error:
223
self._db.rollback()
224
raise Model.DatabaseError(error.message)
225
226
77
227
78
228
79
def InsertAction(self,data):
296
147
297
148
298
149
299
def UpdateCase(self,data,where):
300
""" Inserts Client data into table.
301
302
Arguments:
303
304
data - dictionary where keys are table columns
305
where - dictionary holding where statement
306
307
"""
308
309
try:
310
values=data.values()
311
values.append(where.values()[0])
312
self._cursor.execute(self.ParseUpdateQuery(data,'cases',where),values)
313
self._db.commit()
314
pubsub.Publisher().sendMessage("Cases.Model.Events", data=('NewCase',[]))
315
except Exception, error:
316
self._db.rollback()
317
raise Exceptions.DatabaseError(error.message)
318
319
150
def UpdateTerm(self,data,where):
320
151
""" Inserts Client data into table.
321
152
422
253
def GetActionItem(self,index,cid=None,leader=None, client=None,start=None,stop=None):
423
254
query=( """SELECT data, duration,type, description, (SELECT identifier FROM users WHERE users.uid=actions.user_id),\
424
255
(SELECT identifier FROM cases WHERE cases.cid=case_id),\
425
(SELECT identifier FROM clients WHERE clients.cid=client_id),aid \
256
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),aid \
426
257
FROM actions WHERE data>=%s AND data<=%s ORDER BY data LIMIT 1 OFFSET %s""",
427
258
428
259
"""SELECT data, duration, type,description, (SELECT identifier FROM users WHERE users.uid=actions.user_id),\
429
260
(SELECT identifier FROM cases WHERE cases.cid=case_id),\
430
(SELECT identifier FROM clients WHERE clients.cid=client_id),aid\
261
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),aid\
431
262
FROM actions WHERE case_id=%s AND data>=%s AND data<=%s ORDER BY data LIMIT 1 OFFSET %s""",
432
263
433
264
"""SELECT data, duration, type,description, (SELECT identifier FROM users WHERE users.uid=actions.user_id),\
434
265
(SELECT identifier FROM cases WHERE cases.cid=case_id),\
435
(SELECT identifier FROM clients WHERE clients.cid=client_id),aid\
266
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),aid\
436
267
FROM actions WHERE user_id=%s AND data>=%s AND data<=%s ORDER BY data LIMIT 1 OFFSET %s""",
437
268
438
269
"""SELECT data, duration, type,description, (SELECT identifier FROM users WHERE users.uid=actions.user_id),\
439
270
(SELECT identifier FROM cases WHERE cases.cid=case_id),\
440
(SELECT identifier FROM clients WHERE clients.cid=client_id),aid\
271
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),aid\
441
272
FROM actions WHERE client_id =%s AND data>=%s AND data<=%s\
442
273
ORDER BY data LIMIT 1 OFFSET %s"""
443
274
)
601
432
def GetTermsItem(self,index,cid=None,leader=None, client=None,start=None,stop=None):
602
433
query=( """SELECT data, time, description, (SELECT identifier FROM users WHERE users.uid=terms.user_id),\
603
434
(SELECT identifier FROM cases WHERE cases.cid=terms.case_id),\
604
(SELECT identifier FROM clients WHERE clients.cid=client_id),tid \
435
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),tid \
605
436
FROM terms WHERE data>=%s AND data<=%s ORDER BY data LIMIT 1 OFFSET %s""",
606
437
607
438
"""SELECT data, time, description, (SELECT identifier FROM users WHERE users.uid=terms.user_id),\
608
439
(SELECT identifier FROM cases WHERE cases.cid=terms.case_id),\
609
(SELECT identifier FROM clients WHERE clients.cid=client_id),tid\
440
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),tid\
610
441
FROM terms WHERE case_id=%s AND data>=%s AND data<=%s ORDER BY data LIMIT 1 OFFSET %s""",
611
442
612
443
"""SELECT data, time, description, (SELECT identifier FROM users WHERE users.uid=terms.user_id),\
613
444
(SELECT identifier FROM cases WHERE cases.cid=terms.case_id),\
614
(SELECT identifier FROM clients WHERE clients.cid=client_id),tid\
445
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),tid\
615
446
FROM terms WHERE user_id=%s AND data>=%s AND data<=%s ORDER BY data LIMIT 1 OFFSET %s""",
616
447
617
448
"""SELECT data, time, description, (SELECT identifier FROM users WHERE users.uid=terms.user_id),\
618
449
(SELECT identifier FROM cases WHERE cases.cid=terms.case_id),\
619
(SELECT identifier FROM clients WHERE clients.cid=client_id),tid\
450
(SELECT identifier FROM contacts WHERE contacts.cid=client_id),tid\
620
451
FROM terms WHERE terms.client_id =%s AND data>=%s AND data<=%s \
621
452
ORDER BY data LIMIT 1 OFFSET %s"""
622
453
)
Loggerhead is a web-based interface for Breezy
Version: 2.0.1