· 8 years ago · Aug 24, 2018, 11:20 AM
1{includes/dsUserUserRole.i}
2
3
4
5 PROCEDURE submitUsers:
6 DEFINE INPUT PARAMETER DATASET FOR dsUserUserRole.
7
8 DEFINE VARIABLE iInitialUserId AS INTEGER NO-UNDO.
9 DEFINE VARIABLE returnError AS CHARACTER NO-UNDO.
10 DEFINE VARIABLE iAuxUserId AS INTEGER NO-UNDO.
11
12
13 /* Delete */
14 RUN deleteUserRole(DATASET dsUserUserRole).
15 IF RETURN-VALUE <> '' THEN
16 DO:
17 returnError = returnError + CHR(10) + RETURN-VALUE.
18 END.
19
20
21 RUN deleteAppUsers (DATASET dsUserUserRole).
22 IF RETURN-VALUE <> '' THEN
23 DO:
24 returnError = returnError + CHR(10) + RETURN-VALUE.
25 END.
26
27
28 FOR EACH ttAppUser
29 WHERE NOT ROW-STATE(ttAppUser) EQ ROW-UNMODIFIED:
30
31 CASE ROW-STATE(ttAppUser):
32 /* Update */
33 WHEN 2 THEN
34 DO:
35 RUN updateUser(BUFFER ttAppUser).
36 IF RETURN-VALUE <> '' THEN
37 DO:
38 returnError = returnError + CHR(10) + RETURN-VALUE.
39
40 END.
41
42 END.
43 /* Create*/
44 WHEN 3 THEN
45 DO:
46 /* saves the id of the parent record that came from the GUI <iInitialUserId> */
47 iInitialUserId = ttAppUser.AppUserId.
48
49 /* holds the actual id with which the record was added to the db <iAuxUserId> */
50 RUN createUser (BUFFER ttAppUser, INPUT-OUTPUT iAuxUserId).
51
52 DELETE ttAppUser.
53
54 IF RETURN-VALUE <> '' THEN
55 DO:
56 returnError = returnError + CHR(10) + RETURN-VALUE.
57 END.
58 /* finds all the roles of the added user using the the Id that came from the GUI <iInitialUserId> */
59 FOR EACH ttUserRole EXCLUSIVE-LOCK
60 WHERE ttUserRole.AppUserId = iInitialUserId:
61 /* adds the roles to the db with the actual current Id of the parent record */
62 RUN createuserRole(BUFFER ttUSerRole, INPUT iAuxUserId).
63 DELETE ttUserRole.
64
65
66
67 END.
68 END.
69
70 END CASE.
71 END.
72
73
74
75 FOR EACH ttUserRole
76 WHERE NOT ROW-STATE(ttUserRole) EQ ROW-UNMODIFIED:
77 CASE ROW-STATE(ttUserRole):
78 WHEN 2 THEN
79 DO:
80 RUN updateUserRole(BUFFER ttUserRole).
81 IF RETURN-VALUE <> '' THEN
82 DO:
83 returnError = returnError + CHR(10) + RETURN-VALUE.
84 END.
85 END.
86 WHEN 3 THEN
87 DO:
88 RUN createUserRole(BUFFER ttUserRole, ttUserRole.AppUserId).
89 IF RETURN-VALUE <> '' THEN
90 DO:
91 returnError = returnError + CHR(10) + RETURN-VALUE.
92 END.
93 END.
94
95 END CASE.
96
97 END.
98
99
100
101
102 RETURN returnError.
103
104END PROCEDURE.
105/* ------------------------------User------------------------------- */
106
107
108/* -----------------------------Validations--------------------------------*/
109
110
111
112
113
114/* ------------------------------UserCreate------------------------------- */
115PROCEDURE createUser:
116 DEFINE PARAMETER BUFFER ttAppUser FOR TEMP-TABLE ttAppUser.
117
118 DEFINE INPUT-OUTPUT PARAMETER iFinalUserId AS INTEGER NO-UNDO.
119
120 DEFINE VARIABLE iAuxAppUserId AS INTEGER NO-UNDO.
121 DEFINE VARIABLE iAuxUserRoleId AS INTEGER NO-UNDO.
122
123
124 FIND FIRST AppUser
125 WHERE AppUser.Username = ttAppUser.Username NO-LOCK NO-ERROR.
126
127 /*Check if username exists */
128 IF AVAILABLE AppUser THEN
129 RETURN 'Create new User Error: Username already exists'.
130
131
132 FOR LAST AppUser NO-LOCK:
133 IF AVAILABLE AppUser THEN
134 ttAppUser.AppUserId = AppUser.AppUserId + 1.
135 ELSE
136 ttAppUser.AppUserId = 1.
137
138 END.
139 /* Set User info*/
140 CREATE AppUser.
141
142 ASSIGN
143 AppUser.AppUserId = ttAppUser.AppUserId
144 iFinalUserId = ttAppUser.AppUserId
145 AppUser.NAME = Trim(ttAppUser.NAME)
146 AppUser.UserName = Trim(ttAppUser.UserName)
147 AppUser.Password = Trim(ttAppUser.password).
148
149
150 RETURN.
151
152END PROCEDURE.
153
154
155/* ------------------------------UserRead------------------------------- */
156
157/*----------------------------Filter Users ---------------------------- */
158PROCEDURE filterUsers:
159 DEFINE OUTPUT PARAMETER DATASET FOR dsUserUserRole.
160 DEFINE INPUT PARAMETER ipcFieldValue AS CHARACTER NO-UNDO.
161 DEFINE INPUT PARAMETER ipcFieldName AS CHARACTER NO-UNDO.
162
163
164 DEFINE VARIABLE queryStatement AS CHARACTER NO-UNDO.
165 DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
166 DEFINE VARIABLE hTableBuffer AS HANDLE NO-UNDO.
167 DEFINE VARIABLE cQueryStatement AS CHARACTER NO-UNDO.
168 DEFINE VARIABLE lResult AS LOGICAL NO-UNDO.
169 DEFINE VARIABLE iNumEntries AS INTEGER NO-UNDO.
170 DEFINE VARIABLE iX AS INTEGER NO-UNDO.
171
172 iNumEntries = NUM-ENTRIES (ipcFieldName, ',').
173 EMPTY TEMP-TABLE ttAppUser.
174 EMPTY TEMP-TABLE ttUserRole.
175
176 CREATE BUFFER hTableBuffer FOR TABLE "AppUser".
177 CREATE QUERY hQuery.
178 hQuery:SET-BUFFERS(hTableBuffer).
179
180 cQueryStatement = 'For each AppUser where '.
181 REPEAT ix = 1 TO iNumEntries - 1:
182 cQueryStatement = cQueryStatement + SUBSTITUTE ('appUser.&1 Matches (~'*&2*~') or ', ENTRY(ix, ipcFieldName, ','), ipcFieldValue).
183 END.
184
185 cQueryStatement = cQueryStatement + SUBSTITUTE ('appUser.&1 Matches (~'*&2*~') .', ENTRY(iNumEntries, ipcFieldName, ','), ipcFieldValue).
186
187
188
189 MESSAGE cQueryStatement
190 VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
191 lResult = hQuery:QUERY-PREPARE(cQueryStatement) NO-ERROR.
192
193 IF lResult = FALSE THEN
194 DO:
195 MESSAGE SUBSTITUTE ("Query statement is invalid: &1",
196 cQueryStatement)
197 VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
198 RETURN.
199 END.
200
201 hQuery:QUERY-OPEN.
202
203 hQuery:GET-FIRST (NO-LOCK).
204
205 DO WHILE NOT hQuery:QUERY-OFF-END:
206 CREATE ttAppUser.
207 ttAppUser.AppUserId = hTableBuffer:BUFFER-FIELD("appUserId"):buffer-value.
208 ttAppUser.NAME = hTableBuffer:BUFFER-FIELD("NAME"):buffer-value.
209 ttAppUser.UserName = hTableBuffer:BUFFER-FIELD("UserName"):buffer-value.
210 ttAppUser.Password = hTableBuffer:BUFFER-FIELD("Password"):buffer-value.
211
212
213
214 hQuery:GET-NEXT (NO-LOCK).
215 END.
216
217 FOR EACH ttAppUser NO-LOCK,
218 EACH UserRole NO-LOCK
219 WHERE UserRole.AppUserId = ttAppUser.AppUserID:
220 CREATE ttUserRole.
221 BUFFER-COPY UserRole TO ttUserRole.
222
223 END.
224
225 hQuery:QUERY-CLOSE.
226 DELETE OBJECT hQuery.
227 DELETE OBJECT hTableBuffer.
228
229END PROCEDURE.
230
231PROCEDURE getAllUsers:
232 DEFINE OUTPUT PARAMETER DATASET FOR dsUserUserRole.
233 DATASET dsUserUserRole:EMPTY-DATASET().
234 FOR EACH AppUser NO-LOCK:
235 CREATE ttAppUser.
236 BUFFER-COPY AppUser TO ttAppUser.
237 END.
238 RETURN.
239END PROCEDURE.
240
241/* ------------------------------UserUpdate------------------------------- */
242
243PROCEDURE updateUsersPass:
244 DEFINE PARAMETER BUFFER ttAppUser FOR TEMP-TABLE ttAppUser.
245 FIND FIRST AppUser WHERE AppUser.AppUserId = ttAppUser.AppUserId NO-LOCK NO-ERROR.
246 IF NOT AVAILABLE AppUser THEN
247 DO:
248 RETURN 'Update error: Cannot find User'.
249 END.
250
251 ASSIGN
252 AppUser.Password = ttAppUser.Password.
253
254 RETURN.
255END PROCEDURE.
256
257PROCEDURE updateUser:
258 DEFINE PARAMETER BUFFER ttAppUser FOR TEMP-TABLE ttAppUser.
259
260 FIND FIRST AppUser WHERE AppUser.AppUserId = ttAppUser.AppUserId NO-LOCK NO-ERROR.
261 IF NOT AVAILABLE AppUser THEN
262 DO:
263 RETURN 'Update error: Cannot find User'.
264 END.
265
266 FIND FIRST appUser WHERE appUser.username = ttAppUser.username NO-LOCK NO-ERROR.
267 IF NOT AVAILABLE AppUser THEN
268 DO:
269 ASSIGN
270 AppUser.UserName = Trim(ttAppUser.Username)
271 AppUser.NAME = Trim(ttAppUser.NAME)
272 AppUser.Password = Trim(ttAppUser.Password).
273 END.
274
275 ELSE DO:
276 RETURN 'Update error: There already is an user with that Username'.
277 END.
278
279
280 RETURN.
281END PROCEDURE.
282
283
284/* ------------------------------UserDelete------------------------------- */
285PROCEDURE deleteAppUsers:
286
287 DEFINE INPUT PARAMETER DATASET FOR dsUserUserRole.
288 FOR EACH bttAppUser NO-LOCK
289 WHERE ROW-STATE(bttAppUser) EQ ROW-DELETED:
290
291
292 FIND FIRST AppUser EXCLUSIVE-LOCK WHERE AppUser.Username = bttAppUser.Username NO-ERROR.
293 IF NOT AVAILABLE AppUser THEN
294 DO:
295 RETURN 'An error has occured please try again'.
296 END.
297 /*Check for active Roles*/
298 FIND FIRST UserRole NO-LOCK
299 WHERE UserRole.AppUserId = bttAppUser.AppUserId NO-ERROR.
300
301 IF AVAILABLE UserRole THEN
302 DO:
303 RETURN 'User has active roles. Please delete them first'.
304 END.
305 ELSE DO:
306 DELETE AppUser.
307 END.
308 END.
309
310 RETURN.
311END PROCEDURE.
312
313/* ------------------------------User Role------------------------------- */
314
315
316
317/* ------------------------------Create UserRole------------------------------- */
318PROCEDURE createUserRole:
319 DEFINE PARAMETER BUFFER bttUserRole FOR TEMP-TABLE ttUserRole.
320 DEFINE INPUT PARAMETER iAppUserId AS INTEGER NO-UNDO.
321
322 DEFINE VARIABLE iAuxUserRoleId AS INTEGER NO-UNDO.
323
324 /*Check if the user already has that role for the store*/
325
326 FIND FIRST Store NO-LOCK WHERE Store.StoreId = bttUserRole.StoreId NO-ERROR.
327
328 IF NOT AVAILABLE Store THEN
329 DO:
330 RETURN 'Create failed: Could not find store'.
331 END.
332
333
334 FIND FIRST UserRole NO-LOCK WHERE UserRole.AppUserId = iAppUserId AND
335
336 UserRole.RoleId = bttUserRole.RoleId AND
337 UserRole.StoreId = bttUserRole.StoreId NO-ERROR.
338 IF AVAILABLE UserRole THEN
339 DO:
340 RETURN 'User already has a role of that type for this store' .
341
342 END.
343
344 ELSE
345 DO:
346 FOR LAST UserRole EXCLUSIVE-LOCK:
347 iAuxUserRoleId = UserRole.UserRoleId + 1.
348 END.
349 CREATE UserRole.
350 BUFFER-COPY bttUserRole EXCEPT bttUserRole.UserRoleId bttUserRole.AppUSerId TO UserRole.
351 UserRole.UserRoleId = iAuxUserRoleId.
352 UserRole.AppUserId = iAppUserId.
353
354 END.
355
356
357
358 RETURN.
359 END PROCEDURE.
360
361/* ------------------------------Read UserRole------------------------------- */
362PROCEDURE getAllUserRoles:
363 DEFINE INPUT-OUTPUT PARAMETER DATASET FOR dsUserUserRole.
364 EMPTY TEMP-TABLE ttUserRole.
365 FOR EACH UserRole NO-LOCK:
366 CREATE ttUserRole.
367 BUFFER-COPY UserRole TO ttUserRole.
368 END.
369RETURN.
370END PROCEDURE.
371
372PROCEDURE getAllUserRolesforUser:
373
374
375 DEFINE INPUT-OUTPUT PARAMETER DATASET FOR dsUserUserRole.
376 EMPTY TEMP-TABLE ttUserRole.
377 FOR EACH ttAppUser NO-LOCK,
378 EACH UserRole NO-LOCK
379 WHERE UserRole.AppUserId = ttAppUser.AppUserID:
380 CREATE ttUserRole.
381 BUFFER-COPY UserRole TO ttUserRole.
382 MESSAGE ttUserRole.UserRoleId
383 VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
384 END.
385RETURN.
386END PROCEDURE.
387
388
389/* ------------------------------Update UserRole------------------------------- */
390
391PROCEDURE updateUserRole:
392 DEFINE PARAMETER BUFFER ttUserRole FOR TEMP-TABLE ttUserRole.
393 FIND FIRST UserRole NO-LOCK WHERE UserRole.UserRoleId = ttUserRole.UserRoleId NO-ERROR.
394 IF NOT AVAILABLE UserRole THEN
395 DO:
396 RETURN ERROR 'An error has occured please try again'.
397 END.
398
399 FIND FIRST Store NO-LOCK WHERE Store.StoreId = ttUserRole.StoreId NO-ERROR.
400 IF NOT AVAILABLE Store THEN
401 DO:
402 MESSAGE 'In if not available'
403 VIEW-AS ALERT-BOX INFORMATION BUTTONS OK.
404 RETURN 'Update failed: Could not find store'.
405
406 END.
407 ELSE DO:
408 BUFFER-COPY ttUserRole EXCEPT UserRoleId AppUserId TO UserRole .
409 END.
410
411
412
413
414
415RETURN.
416END PROCEDURE.
417
418/* ------------------------------Delete UserRole------------------------------- */
419
420PROCEDURE deleteUserRole:
421 DEFINE INPUT PARAMETER DATASET FOR dsUserUserRole.
422
423
424 FOR EACH bttUserRole NO-LOCK
425 WHERE ROW-STATE(bttUserRole) EQ ROW-DELETED:
426 /*Check if the role was an admin type */
427 IF bttUserRole.RoleId = 2 THEN
428 DO:
429 /* Check if there exists another manager for that store */
430 FIND FIRST UserRole NO-LOCK WHERE UserRole.UserRoleId <> bttUserRole.UserRoleId AND
431 UserRole.RoleId = 2 AND UserRole.StoreId = bttUserRole.StoreId NO-ERROR.
432
433 IF NOT AVAILABLE UserRole THEN
434 DO:
435 RETURN-VALUE = 'Cannot delete manager, please add another manager for this store first'.
436 END.
437 /* If there exists another admin delete the role*/
438 ELSE DO:
439 FIND FIRST userRole EXCLUSIVE-LOCK
440 WHERE UserRole.UserRoleId = bttUserRole.UserRoleId.
441
442 IF NOT AVAILABLE UserRole THEN
443 DO:
444 RETURN ERROR 'An error has occured please try again'.
445
446 END.
447 DELETE UserRole.
448
449 END.
450
451 END.
452 /*Case where the role is of customer type */
453 ELSE DO:
454
455 FIND FIRST userRole EXCLUSIVE-LOCK
456 WHERE UserRole.UserRoleId = bttUserRole.UserRoleId NO-ERROR.
457
458 IF NOT AVAILABLE UserRole THEN
459 DO:
460 RETURN ERROR 'An error has occured please try again'.
461
462 END.
463 DELETE UserRole.
464 END.
465
466
467 END.
468 RETURN.
469END PROCEDURE.
470
471/* Checks for user roles that have no coresponding user and deletes them (since they are invalid) */
472PROCEDURE cleanseUserRoles:
473 FOR EACH UserRole EXCLUSIVE-LOCK:
474 FIND FIRST AppUser WHERE AppUser.AppUserId = UserRole.AppUserId NO-ERROR.
475 IF NOT AVAILABLE AppUser THEN
476 DO:
477 DELETE UserRole.
478 END.
479 END.
480END PROCEDURE.
481
482
483
484/*--------------------------------------Tests-----------------------------*/
485
486
487/*------------------------------------Set-Up-------------------------------*/
488
489/* DEFINE VARIABLE Icount AS INTEGER NO-UNDO. */
490/* DO TRANSACTION: */
491/* TEMP-TABLE ttAppUser:TRACKING-CHANGES = TRUE. */
492/* TEMP-TABLE ttUserRole:TRACKING-CHANGES = TRUE. */
493
494
495
496/*---------------------------Create---------------------------------*/
497
498/* DO iCount = 90 TO 93: */
499/* CREATE ttAppUser. */
500/* ASSIGN ttAppUser.AppUserID = iCount */
501/* ttAppUser.NAME = SUBSTITUTE('Name&1', iCount) */
502/* ttAppUser.UserName = SUBSTITUTE('User&1', iCount) */
503/* ttAppUser.Password = SUBSTITUTE('password&1', iCount). */
504/* END. */
505/* */
506/* FOR EACH ttAppUser: */
507/* DISPLAY ttAppUser. */
508/* END. */
509/* */
510/* DO iCount = 90 TO 93: */
511/* CREATE ttUserRole. */
512/* ASSIGN */
513/* ttUserRole.UserRoleId = iCount */
514/* ttUserRole.AppUserId = iCount */
515/* ttUserRole.DESCRIPTION = 'Description' */
516/* ttUserRole.StoreId = iCount. */
517/* ttUserRole.RoleId = 3. */
518/* END. */
519/* */
520/* FOR EACH ttUserRole: */
521/* DISPLAY ttUserRole EXCEPT ttUserRole.DESCRIPTION. */
522/* END. */
523/* */
524/* */
525/* RUN submitUsers (INPUT DATASET dsUserUserRole). */
526/* */
527/* FOR EACH AppUser NO-LOCK: */
528/* DISPLAY AppUser.AppUserID AppUser.NAME FORMAT 'x(5)' AppUser.UserName FORMAT 'x(10)' AppUser.Password FORMAT 'x(10)'. */
529/* END. */
530/* */
531/* */
532/* FOR EACH UserRole NO-LOCK: */
533/* DISPLAY UserRole EXCEPT UserRole.DESCRIPTION. */
534/* END. */
535/* UNDO. */
536/* END. */
537
538/* */
539/*-------------------------Update----------------------------------*/
540
541
542
543
544
545/*-------------------------Delete----------------------------------*/
546/* iCount = 1. */
547/* RUN getAllUsers(OUTPUT DATASET dsUserUserRole). */
548/* DATASET dsUserUserRole:ACCEPT-CHANGES(). */
549/* FOR EACH ttAppUser EXCLUSIVE-LOCK */
550/* WHERE ttAppUser.AppUserId > 13: */
551/* DELETE ttAppUser. */
552/* IF iCount = 6 THEN */
553/* DO: */
554/* LEAVE. */
555/* END. */
556/* iCount = iCount + 1. */
557/* END. */
558/* */
559/* /* FOR EACH ttAppUser: */ */
560/* /* DISPLAY ttAppUser. */ */
561/* /* END. */ */
562/* */
563/* RUN submitUsers(INPUT DATASET dsUserUserRole). */
564/* */
565/* FOR EACH AppUser NO-LOCK: */
566/* DISPLAY AppUser.AppUserID AppUser.NAME FORMAT 'x(5)' AppUser.UserName FORMAT 'x(10)' AppUser.Password FORMAT 'x(10)'. */
567/* END. */
568/* */
569/* */
570/* */
571/* UNDO. */
572/* END. */
573
574
575/*--------------------------------Test Create-------------------------------*/
576
577/* FOR EACH AppUser WHERE AppUser.AppUserId > 80: */
578/* DELETE AppUser. */
579/* END. */
580/* */
581
582
583
584
585/* RUN createUsers (INPUT DATASET dsUserUserRole). */
586/* DATASET dsUserUserRole:ACCEPT-CHANGES(). */
587/* */
588/* */
589/* FOR EACH AppUser NO-LOCK, */
590/* EACH UserRole NO-LOCK */
591/* WHERE UserRole.AppUserId = AppUser.AppUserId AND AppUser.AppUserId > 14: */
592/* DELETE UserRole. */
593/* */
594/* */
595/* END. */
596/* */
597/* FOR EACH ttAppUser NO-LOCK: */
598/* DELETE ttAppUser. */
599/* END. */
600/* RUN deleteAppUser( INPUT DATASET dsUserUserRole). */
601/* */
602/* FOR EACH AppUser NO-LOCK: */
603/* DISPLAY AppUser.AppUserID AppUser.NAME FORMAT 'x(5)' AppUser.UserName FORMAT 'x(10)' AppUser.Password FORMAT 'x(10)'. */
604/* END. */
605/* UNDO. */
606/* END.
607 */
608
609
610
611/* DO TRANSACTION: */
612/* CREATE ttUserRole. */
613/* FOR FIRST UserRole NO-LOCK: */
614/* BUFFER-COPY UserRole TO ttUserRole. */
615/* END. */
616/* */
617/* TEMP-TABLE ttUserRole:TRACKING-CHANGES = TRUE. */
618/* */
619/* DISPLAY ttUserRole EXCEPT DESCRIPTION. */
620/* */
621/* ttUserRole.StoreId = 1000. */
622/* */
623/* RUN submitUsers(DATASET dsUserUserRole) . */
624/* */
625/* */
626/* END. */