001: /***************************************************************
002: * This file is part of the [fleXive](R) project.
003: *
004: * Copyright (c) 1999-2008
005: * UCS - unique computing solutions gmbh (http://www.ucs.at)
006: * All rights reserved
007: *
008: * The [fleXive](R) project is free software; you can redistribute
009: * it and/or modify it under the terms of the GNU General Public
010: * License as published by the Free Software Foundation;
011: * either version 2 of the License, or (at your option) any
012: * later version.
013: *
014: * The GNU General Public License can be found at
015: * http://www.gnu.org/copyleft/gpl.html.
016: * A copy is found in the textfile GPL.txt and important notices to the
017: * license from the author are found in LICENSE.txt distributed with
018: * these libraries.
019: *
020: * This library is distributed in the hope that it will be useful,
021: * but WITHOUT ANY WARRANTY; without even the implied warranty of
022: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
023: * GNU General Public License for more details.
024: *
025: * For further information about UCS - unique computing solutions gmbh,
026: * please see the company website: http://www.ucs.at
027: *
028: * For further information about [fleXive](R), please see the
029: * project website: http://www.flexive.org
030: *
031: *
032: * This copyright notice MUST APPEAR in all copies of the file!
033: ***************************************************************/package com.flexive.ejb.beans;
034:
035: import com.flexive.core.Database;
036: import static com.flexive.core.DatabaseConst.*;
037: import com.flexive.core.security.UserTicketStore;
038: import com.flexive.shared.*;
039: import com.flexive.shared.content.FxPermissionUtils;
040: import com.flexive.shared.exceptions.*;
041: import com.flexive.shared.interfaces.SequencerEngine;
042: import com.flexive.shared.interfaces.SequencerEngineLocal;
043: import com.flexive.shared.interfaces.UserGroupEngine;
044: import com.flexive.shared.interfaces.UserGroupEngineLocal;
045: import com.flexive.shared.security.*;
046: import org.apache.commons.logging.Log;
047: import org.apache.commons.logging.LogFactory;
048:
049: import javax.annotation.Resource;
050: import javax.ejb.*;
051: import java.sql.*;
052: import java.util.ArrayList;
053: import java.util.List;
054:
055: /**
056: * User Group management beans.
057: *
058: * @author Gregor Schober (gregor.schober@flexive.com), UCS - unique computing solutions gmbh (http://www.ucs.at)
059: */
060: @Stateless(name="UserGroupEngine")
061: @TransactionManagement(TransactionManagementType.CONTAINER)
062: public class UserGroupEngineBean implements UserGroupEngine,
063: UserGroupEngineLocal {
064: private static transient Log LOG = LogFactory
065: .getLog(UserGroupEngineBean.class);
066: @Resource
067: javax.ejb.SessionContext ctx;
068: //@EJB AccountEngine account;
069: @EJB
070: SequencerEngineLocal seq;
071:
072: /**
073: * {@inheritDoc}
074: */
075: @TransactionAttribute(TransactionAttributeType.SUPPORTS)
076: public UserGroup load(long groupId) throws FxApplicationException {
077: Connection con = null;
078: Statement stmt = null;
079: String sql = "SELECT MANDATOR,NAME,COLOR,AUTOMANDATOR,ISSYSTEM FROM "
080: + TBL_GROUP + " WHERE ID=" + groupId;
081: try {
082:
083: // Obtain a database connection
084: con = Database.getDbConnection();
085:
086: // Create the new workflow instance
087: stmt = con.createStatement();
088:
089: // Build statement
090: ResultSet rs = stmt.executeQuery(sql);
091:
092: // Does the group exist at all?
093: if (rs == null || !rs.next()) {
094: FxNotFoundException nfe = new FxNotFoundException(
095: "ex.account.group.notFound.id", groupId);
096: if (LOG.isInfoEnabled())
097: LOG.info(nfe);
098: throw nfe;
099: }
100: long autoMandator = rs.getLong(4);
101: if (rs.wasNull())
102: autoMandator = -1;
103: return new UserGroup(groupId, rs.getLong(1), autoMandator,
104: rs.getBoolean(5), rs.getString(2), rs.getString(3));
105:
106: } catch (SQLException exc) {
107: FxLoadException de = new FxLoadException(exc,
108: "UserGroup.err.sqlError", exc.getMessage(), sql);
109: LOG.error(de);
110: throw de;
111: } finally {
112: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
113: }
114: }
115:
116: /**
117: * {@inheritDoc}
118: */
119: @TransactionAttribute(TransactionAttributeType.SUPPORTS)
120: public UserGroup loadMandatorGroup(long mandatorId)
121: throws FxApplicationException {
122: Connection con = null;
123: Statement stmt = null;
124: String sql = "SELECT ID,MANDATOR,NAME,COLOR,AUTOMANDATOR,ISSYSTEM FROM "
125: + TBL_GROUP + " WHERE AUTOMANDATOR=" + mandatorId;
126:
127: try {
128:
129: // Obtain a database connection
130: con = Database.getDbConnection();
131:
132: // Create the new workflow instance
133: stmt = con.createStatement();
134:
135: // Build statement
136: ResultSet rs = stmt.executeQuery(sql);
137:
138: // Does the group exist at all?
139: if (rs == null || !rs.next())
140: throw new FxNotFoundException(
141: "ex.account.group.notFound.id", mandatorId);
142: long autoMandator = rs.getLong(5);
143: if (rs.wasNull())
144: autoMandator = -1;
145: return new UserGroup(rs.getLong(1), rs.getLong(2),
146: autoMandator, rs.getBoolean(6), rs.getString(3), rs
147: .getString(4));
148: } catch (SQLException exc) {
149: FxLoadException de = new FxLoadException(exc,
150: "UserGroup.err.sqlError", exc.getMessage(), sql);
151: LOG.error(de);
152: throw de;
153: } finally {
154: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
155: }
156: }
157:
158: /**
159: * {@inheritDoc}
160: */
161: @TransactionAttribute(TransactionAttributeType.SUPPORTS)
162: public UserGroupList loadAll(long mandatorId)
163: throws FxApplicationException {
164: Connection con = null;
165: Statement stmt = null;
166: String sql = null;
167: try {
168:
169: // Obtain a database connection
170: con = Database.getDbConnection();
171:
172: // Create the new workflow instance
173: stmt = con.createStatement();
174:
175: // 1 2 3 4 5 6
176: sql = "SELECT ID,MANDATOR,NAME,COLOR,AUTOMANDATOR,ISSYSTEM FROM "
177: + TBL_GROUP +
178: // Never display the dummy NULL group
179: " WHERE ID!=" + UserGroup.GROUP_NULL;
180: if (mandatorId != -1)
181: sql += " AND (MANDATOR=" + mandatorId + " or ID in ("
182: + UserGroup.GROUP_EVERYONE + ","
183: + UserGroup.GROUP_OWNER + "))";
184: sql += " ORDER BY MANDATOR,NAME";
185:
186: ResultSet rs = stmt.executeQuery(sql);
187:
188: // Process resultset
189: ArrayList<UserGroup> aRes = new ArrayList<UserGroup>(100);
190: while (rs != null && rs.next()) {
191: long autoMandator = rs.getLong(5);
192: if (rs.wasNull())
193: autoMandator = -1;
194: aRes.add(new UserGroup(rs.getLong(1), rs.getLong(2),
195: autoMandator, rs.getBoolean(6),
196: rs.getString(3), rs.getString(4)));
197: }
198:
199: // Build result list
200: UserGroupList res = new UserGroupList(aRes
201: .toArray(new UserGroup[aRes.size()]));
202:
203: // Sanity check
204: if (!res.contains(UserGroup.GROUP_EVERYONE)
205: || !res.contains(UserGroup.GROUP_OWNER)) {
206: FxLoadException le = new FxLoadException(
207: "UserGroup.err.oneOfSystemGroupsIsMissing");
208: LOG.fatal(le);
209: throw le;
210: }
211:
212: return res;
213:
214: } catch (SQLException exc) {
215: FxLoadException de = new FxLoadException(exc,
216: "UserGroup.err.sqlError", exc.getMessage(), sql);
217: LOG.error(de);
218: throw de;
219: } finally {
220: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
221: }
222: }
223:
224: /**
225: * {@inheritDoc}
226: */
227: @TransactionAttribute(TransactionAttributeType.REQUIRED)
228: public int create(String name, String color, long mandatorId)
229: throws FxApplicationException {
230: final UserTicket ticket = FxContext.get().getTicket();
231: // Permission checks
232: try {
233: if (!ticket.isGlobalSupervisor()) {
234: if (ticket.getMandatorId() != mandatorId) {
235: throw new FxNoAccessException(
236: "UserGroup.create.foreignMandator");
237: }
238: FxPermissionUtils.checkRole(ticket,
239: Role.AccountManagement);
240: }
241: } catch (FxNoAccessException nae) {
242: if (LOG.isInfoEnabled())
243: LOG.info(nae);
244: throw nae;
245: }
246:
247: Connection con = null;
248: Statement stmt = null;
249: PreparedStatement pstmt = null;
250: String sql = null;
251: try {
252:
253: // Sanity checks
254: if (color == null || color.length() == 0)
255: color = "#000000";
256: if (color.charAt(0) != '#')
257: color = "#" + color;
258: IsValid(name, color);
259:
260: // Obtain a database connection
261: con = Database.getDbConnection();
262:
263: // Obtain a new id
264: long groupId = seq.getId(SequencerEngine.System.GROUP);
265:
266: // Create the new group
267: sql = "INSERT INTO "
268: + TBL_GROUP
269: + " "
270: + "(ID,MANDATOR,AUTOMANDATOR,ISSYSTEM,NAME,COLOR,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT) VALUES ("
271: + "?,?,NULL,FALSE,?,?,?,?,?,?)";
272: final long NOW = System.currentTimeMillis();
273: pstmt = con.prepareStatement(sql);
274: pstmt.setLong(1, groupId);
275: pstmt.setLong(2, mandatorId);
276: pstmt.setString(3, name);
277: pstmt.setString(4, color);
278: pstmt.setLong(5, ticket.getUserId());
279: pstmt.setLong(6, NOW);
280: pstmt.setLong(7, ticket.getUserId());
281: pstmt.setLong(8, NOW);
282: pstmt.executeUpdate();
283:
284: // Return the new id
285: return (int) groupId;
286:
287: } catch (SQLException exc) {
288: final boolean uniqueConstraintViolation = Database
289: .isUniqueConstraintViolation(exc);
290: ctx.setRollbackOnly();
291: if (uniqueConstraintViolation) {
292: FxEntryExistsException eee = new FxEntryExistsException(
293: "UserGroup.create.groupExists", name);
294: if (LOG.isInfoEnabled())
295: LOG.info(eee);
296: throw eee;
297: } else {
298: FxCreateException ce = new FxCreateException(exc,
299: "UserGroup.err.sqlError", exc.getMessage(), sql);
300: LOG.error(ce);
301: throw ce;
302: }
303: } finally {
304: Database.closeObjects(UserGroupEngineBean.class, null,
305: pstmt);
306: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
307: }
308: }
309:
310: /**
311: * {@inheritDoc}
312: */
313: @TransactionAttribute(TransactionAttributeType.REQUIRED)
314: public void update(long groupId, String name, String color)
315: throws FxApplicationException {
316:
317: final UserTicket ticket = FxContext.get().getTicket();
318:
319: // Load the group
320: UserGroup aGroup;
321: try {
322: aGroup = load(groupId);
323: } catch (FxLoadException exc) {
324: throw new FxUpdateException(exc.getMessage(), exc);
325: }
326:
327: // Permission checks
328: checkPermission(aGroup, "noUpdatePerms");
329:
330: Connection con = null;
331: Statement stmt = null;
332: PreparedStatement pstmt;
333: String sCurSql = null;
334: try {
335:
336: // Sanity checks
337: if (color != null && color.length() > 0
338: && color.charAt(0) != '#') {
339: color = "#" + color;
340: }
341: IsValid(name, color);
342:
343: // Fill in new values
344: if (name != null && color.length() > 0)
345: aGroup.setName(name);
346: if (color != null)
347: aGroup.setColor(color);
348:
349: // Obtain a database connection
350: con = Database.getDbConnection();
351:
352: // Create the new group
353: sCurSql = "UPDATE " + TBL_GROUP + " SET "
354: + "NAME=?, COLOR=?, MODIFIED_BY=?, MODIFIED_AT=? "
355: + "WHERE ID=" + groupId;
356: final long NOW = System.currentTimeMillis();
357: pstmt = con.prepareStatement(sCurSql);
358: pstmt.setString(1, aGroup.getName());
359: pstmt.setString(2, aGroup.getColor());
360: pstmt.setLong(3, ticket.getUserId());
361: pstmt.setLong(4, NOW);
362: pstmt.executeUpdate();
363: pstmt.close();
364:
365: } catch (SQLException exc) {
366: final boolean uniqueConstraintViolation = Database
367: .isUniqueConstraintViolation(exc);
368: ctx.setRollbackOnly();
369: if (uniqueConstraintViolation) {
370: FxEntryExistsException eee = new FxEntryExistsException(
371: "UserGroup.err.groupExists", name);
372: if (LOG.isInfoEnabled())
373: LOG.info(eee);
374: throw eee;
375: } else {
376: FxCreateException ce = new FxCreateException(exc,
377: "UserGroup.err.sqlError", exc.getMessage(),
378: sCurSql);
379: LOG.error(ce);
380: throw ce;
381: }
382: } finally {
383: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
384: }
385: }
386:
387: /**
388: * {@inheritDoc}
389: */
390: @TransactionAttribute(TransactionAttributeType.REQUIRED)
391: public void remove(long groupId) throws FxApplicationException {
392:
393: // Check special groups
394: if (groupId == UserGroup.GROUP_UNDEFINED)
395: return;
396:
397: // Load the group
398: UserGroup theGroup;
399: try {
400: theGroup = load(groupId);
401: } catch (FxLoadException le) {
402: throw new FxRemoveException(le.getMessage(), le);
403: }
404:
405: // Check special groups
406: if (theGroup.isSystem() && !FxContext.get().getRunAsSystem()) {
407: FxNoAccessException nae = new FxNoAccessException(
408: "UserGroup.err.mayNotDeleteSystemGroups", theGroup
409: .getName());
410: LOG.error(nae);
411: throw nae;
412: }
413:
414: // Caller may delete the group?
415: checkPermission(theGroup, "UserGroup.err.noDeletePerms");
416:
417: Connection con = null;
418: Statement stmt = null;
419: String sCurSql;
420: try {
421:
422: // Obtain a database connection
423: con = Database.getDbConnection();
424:
425: // First of delete all user assignments to this group ..
426: stmt = con.createStatement();
427: sCurSql = "DELETE FROM " + TBL_ASSIGN_GROUPS
428: + " WHERE USERGROUP=" + groupId;
429: stmt.executeUpdate(sCurSql);
430: stmt.close();
431:
432: // ... delete all roles assigned to this group
433: stmt = con.createStatement();
434: sCurSql = "DELETE FROM " + TBL_ASSIGN_ROLES
435: + " WHERE USERGROUP=" + groupId;
436: stmt.executeUpdate(sCurSql);
437: stmt.close();
438:
439: // ... then delete all ACL assignments to this group ..
440: stmt = con.createStatement();
441: sCurSql = "DELETE FROM " + TBL_ASSIGN_ACLS
442: + " WHERE USERGROUP=" + groupId;
443: stmt.executeUpdate(sCurSql);
444: stmt.close();
445:
446: // ... and finally delete the group itself;
447: stmt = con.createStatement();
448: sCurSql = "DELETE FROM " + TBL_GROUP + " WHERE ID="
449: + groupId;
450: stmt.executeUpdate(sCurSql);
451:
452: // Update all active user tickets that are affected
453: UserTicketStore.flagDirtyHavingGroupId(groupId);
454:
455: // Log
456: if (LOG.isInfoEnabled())
457: LOG.info("Group [" + theGroup
458: + "] was successfully deleted.");
459:
460: } catch (SQLException exc) {
461: ctx.setRollbackOnly();
462: FxRemoveException ce = new FxRemoveException(exc,
463: "UserGroup.err.deleteSqlException", theGroup
464: .getName());
465: LOG.error(ce);
466: throw ce;
467: } finally {
468: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
469: }
470:
471: }
472:
473: /**
474: * Checks if the group name is valid.
475: * Throws a FxInvalidParameterException if the name is invalid.
476: *
477: * @param sName the name to check
478: * @param sColor the color to check
479: * @throws FxInvalidParameterException if the name is not valid
480: */
481: private static void IsValid(final String sName, String sColor)
482: throws FxInvalidParameterException {
483: if (sName == null || sName.length() == 0) {
484: throw new FxInvalidParameterException("NAME",
485: "UserGroup.err.nameEmpty");
486: }
487: if (sName.indexOf("'") > -1 || sName.indexOf("\"") > -1) {
488: throw new FxInvalidParameterException("NAME",
489: "UserGroup.err.nameContainsInvalidChars");
490: }
491:
492: // Check the color
493: if (sColor != null && !FxFormatUtils.isRGBCode(sColor)) {
494: throw new FxInvalidParameterException("COLOR",
495: "UserGroup.err.invalidColor");
496: }
497: }
498:
499: /**
500: * {@inheritDoc}
501: */
502: public void setRoles(long groupId, List<Role> roles)
503: throws FxApplicationException {
504: long tmp[] = new long[roles == null ? 0 : roles.size()];
505: if (roles != null) {
506: int pos = 0;
507: for (Role role : roles) {
508: tmp[pos++] = role.getId();
509: }
510: }
511: setRoles(groupId, tmp);
512: }
513:
514: /**
515: * {@inheritDoc}
516: */
517: @TransactionAttribute(TransactionAttributeType.REQUIRED)
518: public void setRoles(long groupId, long[] roles)
519: throws FxApplicationException {
520:
521: final UserTicket ticket = FxContext.get().getTicket();
522:
523: // EJBLookup the group
524: UserGroup aGroup;
525: try {
526: aGroup = load(groupId);
527: } catch (FxLoadException exc) {
528: FxUpdateException ue = new FxUpdateException(exc
529: .getMessage(), exc);
530: if (LOG.isInfoEnabled())
531: LOG.info(ue);
532: throw ue;
533: }
534:
535: // Permission check
536: if (!ticket.isGlobalSupervisor())
537: try {
538: if (!ticket.isInRole(Role.AccountManagement)) {
539: throw new FxNoAccessException(
540: "UserGroup.err.noPermSetRoles", aGroup
541: .getName());
542: }
543: if (aGroup.getMandatorId() != ticket.getMandatorId()) {
544: // foreign mandator
545: throw new FxNoAccessException(
546: "UserGroup.err.noPermSetRoles", aGroup
547: .getName());
548: }
549: } catch (FxNoAccessException nae) {
550: if (LOG.isInfoEnabled())
551: LOG.info(nae);
552: throw nae;
553: }
554:
555: // Bye bye duplicates
556: roles = FxArrayUtils.removeDuplicates(roles);
557:
558: // Write roles to database
559: Connection con = null;
560: Statement stmt = null;
561: String sCurSql;
562:
563: try {
564: // Obtain a database connection
565: con = Database.getDbConnection();
566:
567: // Delete the old assignments of the user
568: sCurSql = "DELETE FROM " + TBL_ASSIGN_ROLES
569: + " WHERE USERGROUP=" + groupId;
570: stmt = con.createStatement();
571: stmt.executeUpdate(sCurSql);
572: stmt.close();
573:
574: // Store the new assignments of the user
575: for (long role : roles) {
576:
577: if (Role.isUndefined(role))
578: continue;
579:
580: stmt = con.createStatement();
581: sCurSql = "INSERT INTO " + TBL_ASSIGN_ROLES
582: + " (ACCOUNT,USERGROUP,ROLE) VALUES ("
583: + Account.NULL_ACCOUNT + "," + groupId + ","
584: + role + ")";
585: stmt.executeUpdate(sCurSql);
586: stmt.close();
587: }
588:
589: // Update all active user tickets that are affected
590: UserTicketStore.flagDirtyHavingGroupId(groupId);
591:
592: } catch (SQLException exc) {
593: ctx.setRollbackOnly();
594: FxUpdateException dbe = new FxUpdateException(exc,
595: "UserGroup.err.updateRolesSqlException", aGroup
596: .getName());
597: LOG.error(dbe);
598: throw dbe;
599: } finally {
600: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
601: }
602:
603: }
604:
605: /**
606: * Returns true if the caller may see the group and its roles and assignments.
607: *
608: * @param grp the group
609: * @return true if the caller may see the group its roles and assignments
610: */
611: public boolean mayAccessGroup(UserGroup grp) {
612: final UserTicket ticket = FxContext.get().getTicket();
613: return ticket.isGlobalSupervisor()
614: || grp.getId() == UserGroup.GROUP_EVERYONE
615: || grp.getId() == UserGroup.GROUP_OWNER
616: || grp.getMandatorId() == ticket.getMandatorId();
617: }
618:
619: /**
620: * Checks if the caller may update/edit/delete a given group.
621: *
622: * @param group the group to check for
623: * @param mode mode displayed in the error message, eg 'update', 'delete', ..
624: * @throws FxNoAccessException if the caller lacks the permissions
625: */
626: private static void checkPermission(UserGroup group, String mode)
627: throws FxNoAccessException {
628: final UserTicket ticket = FxContext.get().getTicket();
629: // Permission checks
630: try {
631: if (!ticket.isGlobalSupervisor()) {
632: if (ticket.getMandatorId() != group.getMandatorId()) {
633: throw new FxNoAccessException(mode);
634: }
635: FxPermissionUtils.checkRole(ticket,
636: Role.AccountManagement);
637: }
638: } catch (FxNoAccessException nae) {
639: if (LOG.isInfoEnabled())
640: LOG.info(nae);
641: throw nae;
642: }
643: }
644:
645: /**
646: * {@inheritDoc}
647: */
648: @TransactionAttribute(TransactionAttributeType.SUPPORTS)
649: public RoleList getRoles(long groupId)
650: throws FxApplicationException {
651: Connection con = null;
652: Statement stmt = null;
653: final String sql = "SELECT DISTINCT ROLE FROM "
654: + TBL_ASSIGN_ROLES + " WHERE USERGROUP=" + groupId;
655:
656: // EJBLookup the group
657: final UserGroup aGroup = load(groupId);
658:
659: // Permission check
660: if (!mayAccessGroup(aGroup)) {
661: FxNoAccessException nae = new FxNoAccessException(
662: "UserGroup.err.noPermissionsToReadRoles", aGroup
663: .getName());
664: if (LOG.isInfoEnabled())
665: LOG.info(nae);
666: throw nae;
667: }
668:
669: try {
670: // Obtain a database connection
671: con = Database.getDbConnection();
672: // Load the roles
673: stmt = con.createStatement();
674: ResultSet rs = stmt.executeQuery(sql);
675: RoleList result = new RoleList();
676: while (rs != null && rs.next()) {
677: result.add(rs.getByte(1));
678: }
679: if (LOG.isDebugEnabled()) {
680: LOG.debug("Roles for group [" + groupId + "]: "
681: + result.toNameArray());
682: }
683: return result;
684: } catch (SQLException exc) {
685: FxLoadException le = new FxLoadException(exc,
686: "UserGroup.err.sqlError", exc.getMessage(), sql);
687: LOG.error(le);
688: throw le;
689: } finally {
690: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
691: }
692: }
693:
694: /**
695: * {@inheritDoc}
696: */
697: @TransactionAttribute(TransactionAttributeType.REQUIRED)
698: public void rebuildMandatorGroups() throws FxApplicationException {
699: Connection con = null;
700: PreparedStatement ps = null;
701: Statement stmt = null;
702: String sql = "";
703: try {
704: con = Database.getDbConnection();
705: stmt = con.createStatement();
706: List<Mandator> missing = new ArrayList<Mandator>(5);
707: for (Mandator m : CacheAdmin.getEnvironment().getMandators(
708: true, true)) {
709: try {
710: UserGroup g = loadMandatorGroup(m.getId());
711: sql = "DELETE FROM " + TBL_ASSIGN_GROUPS
712: + " WHERE USERGROUP=" + g.getId();
713: stmt.executeUpdate(sql);
714: sql = "INSERT INTO " + TBL_ASSIGN_GROUPS
715: + " (ACCOUNT,USERGROUP) (SELECT a.id, "
716: + g.getId() + " FROM " + TBL_ACCOUNTS
717: + " a WHERE a.MANDATOR=" + m.getId()
718: + " AND a.ID!=" + Account.NULL_ACCOUNT
719: + ")";
720: stmt.executeUpdate(sql);
721: } catch (FxNotFoundException e) {
722: missing.add(m);
723: }
724: }
725: final long NOW = System.currentTimeMillis();
726: for (Mandator m : missing) {
727: sql = "INSERT INTO "
728: + TBL_GROUP
729: + " "
730: + "(ID,MANDATOR,AUTOMANDATOR,ISSYSTEM,NAME,COLOR,CREATED_BY,CREATED_AT,MODIFIED_BY,MODIFIED_AT) VALUES ("
731: + "?,?," + m.getId() + ",TRUE,?,?,?,?,?,?)";
732: if (ps == null)
733: ps = con.prepareStatement(sql);
734: long gid = seq.getId(SequencerEngine.System.GROUP);
735: ps.setLong(1, gid);
736: ps.setLong(2, m.getId());
737: ps.setString(3, "Everyone (" + m.getName() + ")");
738: ps.setString(4, "#00AA00");
739: ps.setLong(5, 0);
740: ps.setLong(6, NOW);
741: ps.setLong(7, 0);
742: ps.setLong(8, NOW);
743: ps.executeUpdate();
744: sql = "INSERT INTO " + TBL_ASSIGN_GROUPS
745: + " (ACCOUNT,USERGROUP) (SELECT a.ID, " + gid
746: + " FROM " + TBL_ACCOUNTS
747: + " a WHERE a.MANDATOR=" + m.getId()
748: + " AND a.ID!=" + Account.NULL_ACCOUNT + ")";
749: stmt.executeUpdate(sql);
750: }
751: } catch (SQLException exc) {
752: FxLoadException le = new FxLoadException(exc,
753: "UserGroup.err.sqlError", exc.getMessage(), sql);
754: LOG.error(le);
755: throw le;
756: } finally {
757: if (ps != null)
758: try {
759: ps.close();
760: } catch (SQLException e) {
761: //ignore
762: }
763: Database.closeObjects(UserGroupEngineBean.class, con, stmt);
764: }
765: }
766:
767: }
|