001: /**
002: * LibreSource
003: * Copyright (C) 2004-2008 Artenum SARL / INRIA
004: * http://www.libresource.org - contact@artenum.com
005: *
006: * This file is part of the LibreSource software,
007: * which can be used and distributed under license conditions.
008: * The license conditions are provided in the LICENSE.TXT file
009: * at the root path of the packaging that enclose this file.
010: * More information can be found at
011: * - http://dev.libresource.org/home/license
012: *
013: * Initial authors :
014: *
015: * Guillaume Bort / INRIA
016: * Francois Charoy / Universite Nancy 2
017: * Julien Forest / Artenum
018: * Claude Godart / Universite Henry Poincare
019: * Florent Jouille / INRIA
020: * Sebastien Jourdain / INRIA / Artenum
021: * Yves Lerumeur / Artenum
022: * Pascal Molli / Universite Henry Poincare
023: * Gerald Oster / INRIA
024: * Mariarosa Penzi / Artenum
025: * Gerard Sookahet / Artenum
026: * Raphael Tani / INRIA
027: *
028: * Contributors :
029: *
030: * Stephane Bagnier / Artenum
031: * Amadou Dia / Artenum-IUP Blois
032: * ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
033: */package org.libresource.core.ejb;
034:
035: import org.libresource.Libresource;
036: import org.libresource.LibresourceServiceBase;
037:
038: import org.libresource.core.CoreConstants;
039: import org.libresource.core.LibresourceCoreServiceException;
040: import org.libresource.core.ejb.model.ProjectResourceValue;
041: import org.libresource.core.interfaces.LibresourceCoreService;
042:
043: import org.libresource.kernel.KernelConstants;
044: import org.libresource.kernel.LibresourceSecurityException;
045: import org.libresource.kernel.URINotExistException;
046: import org.libresource.kernel.interfaces.KernelService;
047:
048: import java.net.URI;
049:
050: import java.sql.Connection;
051: import java.sql.PreparedStatement;
052: import java.sql.ResultSet;
053:
054: import java.util.Date;
055: import java.util.Vector;
056:
057: /**
058: * @libresource.service name="LibresourceStats" depends="Kernel,LibresourceCore"
059: */
060: public abstract class LibresourceStatsServiceBean extends
061: LibresourceServiceBase {
062: // stats management
063:
064: /**
065: * @ejb.interface-method
066: * @ejb.transaction type="Supports"
067: */
068: public Object[] getProjectStats(URI projectURI)
069: throws LibresourceCoreServiceException,
070: URINotExistException, LibresourceSecurityException {
071: Connection connection = null;
072: PreparedStatement ps = null;
073: ResultSet rs = null;
074:
075: try {
076: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
077: .getService(CoreConstants.SERVICE);
078: ProjectResourceValue project = libresourceCoreService
079: .getProject(projectURI);
080: connection = LibresourceCoreServiceBean
081: .getConnectionForProjectStats(true);
082: ps = connection
083: .prepareStatement("SELECT * FROM projects_stats_ WHERE week_ = extract(WEEK from now()) AND project_id_ = ?");
084: ps.setString(1, project.getId());
085:
086: rs = ps.executeQuery();
087: rs.next();
088:
089: Object[] result = new Object[4];
090: result[0] = new Float(rs.getFloat("activity_"));
091: result[1] = new Float(rs.getFloat("popularity_"));
092: result[2] = new Integer(rs.getInt("reads_"));
093: result[3] = new Integer(rs.getInt("writes_"));
094:
095: return result;
096: } catch (LibresourceSecurityException se) {
097: throw se;
098: } catch (URINotExistException se) {
099: throw se;
100: } catch (Exception e) {
101: throw new LibresourceCoreServiceException(
102: "Error in getProjectActivity : " + e.getMessage());
103: } finally {
104: try {
105: if (ps != null) {
106: ps.close();
107: }
108: } catch (Exception e) {
109: }
110:
111: try {
112: if (rs != null) {
113: rs.close();
114: }
115: } catch (Exception e) {
116: }
117:
118: try {
119: if (connection != null) {
120: connection.close();
121: }
122: } catch (Exception e) {
123: }
124: }
125: }
126:
127: /**
128: * @ejb.interface-method
129: * @ejb.transaction type="Supports"
130: */
131: public Object[] getProjectStatsHistory(URI projectURI)
132: throws LibresourceCoreServiceException,
133: URINotExistException, LibresourceSecurityException {
134: Connection connection = null;
135: PreparedStatement ps = null;
136: ResultSet rs = null;
137:
138: try {
139: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
140: .getService(CoreConstants.SERVICE);
141: ProjectResourceValue project = libresourceCoreService
142: .getProject(projectURI);
143: connection = LibresourceCoreServiceBean
144: .getConnectionForProjectStats(true);
145: ps = connection
146: .prepareStatement("SELECT DISTINCT week_,* FROM projects_stats_ WHERE project_id_ = ?");
147: ps.setString(1, project.getId());
148:
149: rs = ps.executeQuery();
150:
151: Object[] history = new Object[52];
152:
153: while (rs.next()) {
154: Object[] result = new Object[4];
155: result[0] = new Float(rs.getFloat("activity_"));
156: result[1] = new Float(rs.getFloat("popularity_"));
157: result[2] = new Integer(rs.getInt("reads_"));
158: result[3] = new Integer(rs.getInt("writes_"));
159:
160: int week = rs.getInt("week_");
161: history[week] = result;
162: }
163:
164: return history;
165: } catch (LibresourceSecurityException se) {
166: throw se;
167: } catch (URINotExistException se) {
168: throw se;
169: } catch (Exception e) {
170: throw new LibresourceCoreServiceException(
171: "Error in getProjectStatsHistory : "
172: + e.getMessage());
173: } finally {
174: try {
175: if (ps != null) {
176: ps.close();
177: }
178: } catch (Exception e) {
179: }
180:
181: try {
182: if (rs != null) {
183: rs.close();
184: }
185: } catch (Exception e) {
186: }
187:
188: try {
189: if (connection != null) {
190: connection.close();
191: }
192: } catch (Exception e) {
193: }
194: }
195: }
196:
197: /**
198: * @ejb.interface-method
199: * @ejb.transaction type="Supports"
200: */
201: public Object[] getProjectMostActiveUsers(URI projectURI)
202: throws LibresourceCoreServiceException,
203: URINotExistException, LibresourceSecurityException {
204: Connection connection = null;
205: PreparedStatement ps = null;
206: ResultSet rs = null;
207:
208: try {
209: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
210: .getService(CoreConstants.SERVICE);
211: ProjectResourceValue project = libresourceCoreService
212: .getProject(projectURI);
213: connection = LibresourceCoreServiceBean
214: .getConnectionForProjectStats(true);
215: ps = connection
216: .prepareStatement("SELECT * FROM projects_mostactive_users_ WHERE week_ = extract(WEEK from now()) AND project_id_ = ? ORDER BY activity_ DESC");
217: ps.setString(1, project.getId());
218:
219: rs = ps.executeQuery();
220:
221: Vector topUsers = new Vector();
222:
223: while (rs.next()) {
224: Object[] topUser = new Object[2];
225: topUser[0] = rs.getString("user_");
226: topUser[1] = new Float(rs.getFloat("activity_"));
227: topUsers.add(topUser);
228: }
229:
230: Object[] result = new Object[topUsers.size()];
231:
232: for (int i = 0; i < result.length; i++) {
233: result[i] = topUsers.get(i);
234: }
235:
236: return result;
237: } catch (LibresourceSecurityException se) {
238: throw se;
239: } catch (URINotExistException se) {
240: throw se;
241: } catch (Exception e) {
242: throw new LibresourceCoreServiceException(
243: "Error in getProjectMostActiveUsers : "
244: + e.getMessage());
245: } finally {
246: try {
247: if (ps != null) {
248: ps.close();
249: }
250: } catch (Exception e) {
251: }
252:
253: try {
254: if (rs != null) {
255: rs.close();
256: }
257: } catch (Exception e) {
258: }
259:
260: try {
261: if (connection != null) {
262: connection.close();
263: }
264: } catch (Exception e) {
265: }
266: }
267: }
268:
269: /**
270: * @ejb.interface-method
271: * @ejb.transaction type="Supports"
272: */
273: public Object[] getProjectMostActiveUsersHistory(URI projectURI)
274: throws LibresourceCoreServiceException,
275: URINotExistException, LibresourceSecurityException {
276: Connection connection = null;
277: PreparedStatement ps = null;
278: ResultSet rs = null;
279:
280: try {
281: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
282: .getService(CoreConstants.SERVICE);
283: ProjectResourceValue project = libresourceCoreService
284: .getProject(projectURI);
285: connection = LibresourceCoreServiceBean
286: .getConnectionForProjectStats(true);
287: ps = connection
288: .prepareStatement("SELECT * FROM projects_mostactive_users_ WHERE project_id_ = ?");
289: ps.setString(1, project.getId());
290:
291: rs = ps.executeQuery();
292:
293: Object[] history = new Object[52];
294:
295: for (int i = 0; i < history.length; i++) {
296: history[i] = new Vector();
297: }
298:
299: while (rs.next()) {
300: Object[] topUser = new Object[2];
301: topUser[0] = rs.getString("user_");
302: topUser[1] = new Float(rs.getFloat("activity_"));
303:
304: Vector topUsers = (Vector) history[rs.getInt("week_")];
305: topUsers.add(topUser);
306: }
307:
308: return history;
309: } catch (LibresourceSecurityException se) {
310: throw se;
311: } catch (URINotExistException se) {
312: throw se;
313: } catch (Exception e) {
314: throw new LibresourceCoreServiceException(
315: "Error in getProjectMostActiveUsersHistory : "
316: + e.getMessage());
317: } finally {
318: try {
319: if (ps != null) {
320: ps.close();
321: }
322: } catch (Exception e) {
323: }
324:
325: try {
326: if (rs != null) {
327: rs.close();
328: }
329: } catch (Exception e) {
330: }
331:
332: try {
333: if (connection != null) {
334: connection.close();
335: }
336: } catch (Exception e) {
337: }
338: }
339: }
340:
341: /**
342: * @ejb.interface-method
343: * @ejb.transaction type="Supports"
344: */
345: public Object[] getProjectMostActiveResources(URI projectURI)
346: throws LibresourceCoreServiceException,
347: URINotExistException, LibresourceSecurityException {
348: Connection connection = null;
349: PreparedStatement ps = null;
350: ResultSet rs = null;
351:
352: try {
353: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
354: .getService(CoreConstants.SERVICE);
355: ProjectResourceValue project = libresourceCoreService
356: .getProject(projectURI);
357: connection = LibresourceCoreServiceBean
358: .getConnectionForProjectStats(true);
359: ps = connection
360: .prepareStatement("SELECT * FROM projects_mostactive_resources_ WHERE week_ = extract(WEEK from now()) AND project_id_ = ? ORDER BY activity_ DESC");
361: ps.setString(1, project.getId());
362:
363: rs = ps.executeQuery();
364:
365: Vector topUsers = new Vector();
366:
367: while (rs.next()) {
368: Object[] topUser = new Object[2];
369: topUser[0] = rs.getString("resource_");
370: topUser[1] = new Float(rs.getFloat("activity_"));
371: topUsers.add(topUser);
372: }
373:
374: Object[] result = new Object[topUsers.size()];
375:
376: for (int i = 0; i < result.length; i++) {
377: result[i] = topUsers.get(i);
378: }
379:
380: return result;
381: } catch (LibresourceSecurityException se) {
382: throw se;
383: } catch (URINotExistException se) {
384: throw se;
385: } catch (Exception e) {
386: throw new LibresourceCoreServiceException(
387: "Error in getProjectMostActiveResources : "
388: + e.getMessage());
389: } finally {
390: try {
391: if (ps != null) {
392: ps.close();
393: }
394: } catch (Exception e) {
395: }
396:
397: try {
398: if (rs != null) {
399: rs.close();
400: }
401: } catch (Exception e) {
402: }
403:
404: try {
405: if (connection != null) {
406: connection.close();
407: }
408: } catch (Exception e) {
409: }
410: }
411: }
412:
413: /**
414: * @ejb.interface-method
415: * @ejb.transaction type="Supports"
416: */
417: public Object[] getProjectMostActiveResourcesHistory(URI projectURI)
418: throws LibresourceCoreServiceException,
419: URINotExistException, LibresourceSecurityException {
420: Connection connection = null;
421: PreparedStatement ps = null;
422: ResultSet rs = null;
423:
424: try {
425: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
426: .getService(CoreConstants.SERVICE);
427: ProjectResourceValue project = libresourceCoreService
428: .getProject(projectURI);
429: connection = LibresourceCoreServiceBean
430: .getConnectionForProjectStats(true);
431: ps = connection
432: .prepareStatement("SELECT * FROM projects_mostactive_resources_ WHERE project_id_ = ?");
433: ps.setString(1, project.getId());
434:
435: rs = ps.executeQuery();
436:
437: Object[] history = new Object[52];
438:
439: for (int i = 0; i < history.length; i++) {
440: history[i] = new Vector();
441: }
442:
443: while (rs.next()) {
444: Object[] topUser = new Object[2];
445: topUser[0] = rs.getString("resource_");
446: topUser[1] = new Float(rs.getFloat("activity_"));
447:
448: Vector topUsers = (Vector) history[rs.getInt("week_")];
449: topUsers.add(topUser);
450: }
451:
452: return history;
453: } catch (LibresourceSecurityException se) {
454: throw se;
455: } catch (URINotExistException se) {
456: throw se;
457: } catch (Exception e) {
458: throw new LibresourceCoreServiceException(
459: "Error in getProjectMostActiveResourcesHistory : "
460: + e.getMessage());
461: } finally {
462: try {
463: if (ps != null) {
464: ps.close();
465: }
466: } catch (Exception e) {
467: }
468:
469: try {
470: if (rs != null) {
471: rs.close();
472: }
473: } catch (Exception e) {
474: }
475:
476: try {
477: if (connection != null) {
478: connection.close();
479: }
480: } catch (Exception e) {
481: }
482: }
483: }
484:
485: /**
486: * @ejb.interface-method
487: * @ejb.transaction type="Supports"
488: */
489: public Object[] getProjectPreferedResources(URI projectURI)
490: throws LibresourceCoreServiceException,
491: URINotExistException, LibresourceSecurityException {
492: Connection connection = null;
493: PreparedStatement ps = null;
494: ResultSet rs = null;
495:
496: try {
497: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
498: .getService(CoreConstants.SERVICE);
499: ProjectResourceValue project = libresourceCoreService
500: .getProject(projectURI);
501: connection = LibresourceCoreServiceBean
502: .getConnectionForProjectStats(true);
503: ps = connection
504: .prepareStatement("SELECT * FROM projects_prefered_resources_ WHERE week_ = extract(WEEK from now()) AND project_id_ = ? ORDER BY popularity_ DESC");
505: ps.setString(1, project.getId());
506:
507: rs = ps.executeQuery();
508:
509: Vector topUsers = new Vector();
510:
511: while (rs.next()) {
512: Object[] topUser = new Object[2];
513: topUser[0] = rs.getString("resource_");
514: topUser[1] = new Float(rs.getFloat("popularity_"));
515: topUsers.add(topUser);
516: }
517:
518: Object[] result = new Object[topUsers.size()];
519:
520: for (int i = 0; i < result.length; i++) {
521: result[i] = topUsers.get(i);
522: }
523:
524: return result;
525: } catch (LibresourceSecurityException se) {
526: throw se;
527: } catch (URINotExistException se) {
528: throw se;
529: } catch (Exception e) {
530: throw new LibresourceCoreServiceException(
531: "Error in getProjectPreferedResources : "
532: + e.getMessage());
533: } finally {
534: try {
535: if (ps != null) {
536: ps.close();
537: }
538: } catch (Exception e) {
539: }
540:
541: try {
542: if (rs != null) {
543: rs.close();
544: }
545: } catch (Exception e) {
546: }
547:
548: try {
549: if (connection != null) {
550: connection.close();
551: }
552: } catch (Exception e) {
553: }
554: }
555: }
556:
557: /**
558: * @ejb.interface-method
559: * @ejb.transaction type="Supports"
560: */
561: public Object[] getProjectPreferedResourcesHistory(URI projectURI)
562: throws LibresourceCoreServiceException,
563: URINotExistException, LibresourceSecurityException {
564: Connection connection = null;
565: PreparedStatement ps = null;
566: ResultSet rs = null;
567:
568: try {
569: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
570: .getService(CoreConstants.SERVICE);
571: ProjectResourceValue project = libresourceCoreService
572: .getProject(projectURI);
573: connection = LibresourceCoreServiceBean
574: .getConnectionForProjectStats(true);
575: ps = connection
576: .prepareStatement("SELECT * FROM projects_prefered_resources_ WHERE project_id_ = ?");
577: ps.setString(1, project.getId());
578:
579: rs = ps.executeQuery();
580:
581: Object[] history = new Object[52];
582:
583: for (int i = 0; i < history.length; i++) {
584: history[i] = new Vector();
585: }
586:
587: while (rs.next()) {
588: Object[] topUser = new Object[2];
589: topUser[0] = rs.getString("resource_");
590: topUser[1] = new Float(rs.getFloat("popularity_"));
591:
592: Vector topUsers = (Vector) history[rs.getInt("week_")];
593: topUsers.add(topUser);
594: }
595:
596: return history;
597: } catch (LibresourceSecurityException se) {
598: throw se;
599: } catch (URINotExistException se) {
600: throw se;
601: } catch (Exception e) {
602: throw new LibresourceCoreServiceException(
603: "Error in getProjectPreferedResourcesHistory : "
604: + e.getMessage());
605: } finally {
606: try {
607: if (ps != null) {
608: ps.close();
609: }
610: } catch (Exception e) {
611: }
612:
613: try {
614: if (rs != null) {
615: rs.close();
616: }
617: } catch (Exception e) {
618: }
619:
620: try {
621: if (connection != null) {
622: connection.close();
623: }
624: } catch (Exception e) {
625: }
626: }
627: }
628:
629: /**
630: * @ejb.interface-method
631: * @ejb.transaction type="Required"
632: */
633: public void cleanStats(Date date)
634: throws LibresourceCoreServiceException {
635: Connection connection = null;
636: PreparedStatement ps = null;
637:
638: try {
639: connection = LibresourceCoreServiceBean
640: .getConnectionForProjectStats(true);
641:
642: // delete old project stats
643: ps = connection
644: .prepareStatement("DELETE FROM projects_stats_ WHERE week_ = extract(WEEK from TIMESTAMP ?)");
645: ps.setDate(1, new java.sql.Date(date.getTime()));
646: ps.executeUpdate();
647: ps.close();
648:
649: // delete old top users
650: ps = connection
651: .prepareStatement("DELETE FROM projects_mostactive_users_ WHERE week_ = extract(WEEK from TIMESTAMP ?)");
652: ps.setDate(1, new java.sql.Date(date.getTime()));
653: ps.executeUpdate();
654: ps.close();
655:
656: // delete old top resources
657: ps = connection
658: .prepareStatement("DELETE FROM projects_mostactive_resources_ WHERE week_ = extract(WEEK from TIMESTAMP ?)");
659: ps.setDate(1, new java.sql.Date(date.getTime()));
660: ps.executeUpdate();
661: ps.close();
662:
663: // delete old prefered resources
664: ps = connection
665: .prepareStatement("DELETE FROM projects_prefered_resources_ WHERE week_ = extract(WEEK from TIMESTAMP ?)");
666: ps.setDate(1, new java.sql.Date(date.getTime()));
667: ps.executeUpdate();
668: } catch (Exception e) {
669: ctx.setRollbackOnly();
670: throw new LibresourceCoreServiceException(
671: "Error in cleanAllStats : " + e.getMessage());
672: } finally {
673: try {
674: if (ps != null) {
675: ps.close();
676: }
677: } catch (Exception e) {
678: }
679:
680: try {
681: if (connection != null) {
682: connection.close();
683: }
684: } catch (Exception e) {
685: }
686: }
687: }
688:
689: /**
690: * @ejb.interface-method
691: * @ejb.transaction type="Required"
692: */
693: public void computeStats(Date date)
694: throws LibresourceCoreServiceException,
695: LibresourceSecurityException, URINotExistException {
696: Connection connection = null;
697: PreparedStatement ps = null;
698: ResultSet rs = null;
699:
700: try {
701: LibresourceCoreService libresourceCoreService = (LibresourceCoreService) Libresource
702: .getService(CoreConstants.SERVICE);
703: cleanStats(date);
704:
705: if (ctx.getRollbackOnly()) {
706: throw new Exception(
707: "Stats can't be computed because cleanStats has failed");
708: }
709:
710: KernelService kernelService = (KernelService) Libresource
711: .getService(KernelConstants.SERVICE);
712: ProjectResourceValue[] projects = libresourceCoreService
713: .listAllProjects();
714:
715: for (int k = 0; k < projects.length; k++) {
716: ProjectResourceValue project = projects[k];
717: String pathPattern = kernelService
718: .normalizeAbsoluteURIPath(project.getUri())
719: + "%";
720: connection = LibresourceCoreServiceBean
721: .getConnectionForEvents(true);
722:
723: // count nb of reads for this project (for last 7 days)
724: ps = connection
725: .prepareStatement("SELECT count(date_) FROM events_log WHERE type_ LIKE 'libresourceWeb.display' AND from_ LIKE ? AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7;");
726: ps.setString(1, pathPattern);
727: ps.setDate(2, new java.sql.Date(date.getTime()));
728:
729: rs = ps.executeQuery();
730: rs.next();
731:
732: int nbReads = rs.getInt(1);
733: rs.close();
734: ps.close();
735:
736: // count total reads on the platform
737: ps = connection
738: .prepareStatement("SELECT count(date_) FROM events_log WHERE type_ LIKE 'libresourceWeb.display' AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7;");
739: ps.setDate(1, new java.sql.Date(date.getTime()));
740: rs = ps.executeQuery();
741: rs.next();
742:
743: int totalReads = rs.getInt(1);
744: rs.close();
745: ps.close();
746:
747: // count nb of writes for this project
748: ps = connection
749: .prepareStatement("SELECT count(date_) FROM events_log WHERE type_ NOT LIKE 'libresourceWeb.display' AND from_ LIKE ? AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7;");
750: ps.setString(1, pathPattern);
751: ps.setDate(2, new java.sql.Date(date.getTime()));
752: rs = ps.executeQuery();
753: rs.next();
754:
755: int nbWrites = rs.getInt(1);
756: rs.close();
757: ps.close();
758:
759: // count total writes on the platform
760: ps = connection
761: .prepareStatement("SELECT count(date_) FROM events_log WHERE type_ NOT LIKE 'libresourceWeb.display' AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7;");
762: ps.setDate(1, new java.sql.Date(date.getTime()));
763: rs = ps.executeQuery();
764: rs.next();
765:
766: int totalWrites = rs.getInt(1);
767: rs.close();
768: ps.close();
769:
770: // compute project popularity & project activity_
771: float popularity = ((float) nbReads / (float) totalReads) * 100;
772: float activity = ((float) nbWrites / (float) totalWrites) * 100;
773:
774: // avoid NaN
775: if (totalReads == 0) {
776: popularity = 0;
777: }
778:
779: if (totalWrites == 0) {
780: activity = 0;
781: }
782:
783: // find top 5 users
784: ps = connection
785: .prepareStatement("SELECT count(date_) AS score,by_ FROM events_log WHERE from_ LIKE ? AND by_ NOT LIKE '/users/guest' AND by_ NOT LIKE '' AND type_ NOT LIKE 'libresourceWeb.display' AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7 GROUP BY by_ ORDER BY score DESC LIMIT 5;");
786: ps.setString(1, pathPattern);
787: ps.setDate(2, new java.sql.Date(date.getTime()));
788: rs = ps.executeQuery();
789:
790: Vector topUsers = new Vector();
791:
792: while (rs.next()) {
793: String user = rs.getString("by_");
794: int count = rs.getInt("score");
795: Float userActivity = new Float(
796: ((float) count / (float) nbWrites) * 100);
797: Object[] stat = new Object[] { user, userActivity };
798: topUsers.add(stat);
799: }
800:
801: rs.close();
802: ps.close();
803:
804: // find most actives resources
805: ps = connection
806: .prepareStatement("SELECT count(date_) AS score,from_ FROM events_log WHERE from_ LIKE ? AND type_ NOT LIKE 'libresourceWeb.display' AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7 GROUP BY from_ ORDER BY score DESC LIMIT 5;");
807: ps.setString(1, pathPattern);
808: ps.setDate(2, new java.sql.Date(date.getTime()));
809: rs = ps.executeQuery();
810:
811: Vector topResources = new Vector();
812:
813: while (rs.next()) {
814: String resource = rs.getString("from_");
815: int count = rs.getInt("score");
816: Float resourceActivity = new Float(
817: ((float) count / (float) nbWrites) * 100);
818: Object[] stat = new Object[] { resource,
819: resourceActivity };
820: topResources.add(stat);
821: }
822:
823: rs.close();
824: ps.close();
825:
826: // find most populars resources
827: ps = connection
828: .prepareStatement("SELECT count(date_) AS score,from_ FROM events_log WHERE from_ LIKE ? AND type_ LIKE 'libresourceWeb.display' AND (extract(DAY from TIMESTAMP ?)-extract(DAY from date_))<=7 GROUP BY from_ ORDER BY score DESC LIMIT 5;");
829: ps.setString(1, pathPattern);
830: ps.setDate(2, new java.sql.Date(date.getTime()));
831: rs = ps.executeQuery();
832:
833: Vector preferedResources = new Vector();
834:
835: while (rs.next()) {
836: String resource = rs.getString("from_");
837: int count = rs.getInt("score");
838: Float resourceActivity = new Float(
839: ((float) count / (float) nbReads) * 100);
840: Object[] stat = new Object[] { resource,
841: resourceActivity };
842: preferedResources.add(stat);
843: }
844:
845: rs.close();
846: ps.close();
847: connection.close();
848: connection = LibresourceCoreServiceBean
849: .getConnectionForProjectStats(true);
850:
851: // insert computed project stats
852: ps = connection
853: .prepareStatement("INSERT INTO projects_stats_ (project_id_, week_, activity_, popularity_, reads_, writes_) VALUES (?,extract(WEEK from TIMESTAMP ?),?,?,?,?);");
854: ps.setString(1, project.getId());
855: ps.setDate(2, new java.sql.Date(date.getTime()));
856: ps.setFloat(3, activity);
857: ps.setFloat(4, popularity);
858: ps.setInt(5, nbReads);
859: ps.setInt(6, nbWrites);
860: ps.executeUpdate();
861: ps.close();
862:
863: // insert new top users
864: for (int i = 0; i < topUsers.size(); i++) {
865: String user = (String) ((Object[]) topUsers.get(i))[0];
866: float score = ((Float) ((Object[]) topUsers.get(i))[1])
867: .floatValue();
868: ps = connection
869: .prepareStatement("INSERT INTO projects_mostactive_users_ (project_id_, week_, activity_, user_) VALUES (?,extract(WEEK from TIMESTAMP ?),?,?);");
870: ps.setString(1, project.getId());
871: ps.setDate(2, new java.sql.Date(date.getTime()));
872: ps.setFloat(3, score);
873: ps.setString(4, user);
874: ps.executeUpdate();
875: ps.close();
876: }
877:
878: // insert new top resources
879: for (int i = 0; i < topResources.size(); i++) {
880: String resource = (String) ((Object[]) topResources
881: .get(i))[0];
882: float score = ((Float) ((Object[]) topResources
883: .get(i))[1]).floatValue();
884: ps = connection
885: .prepareStatement("INSERT INTO projects_mostactive_resources_ (project_id_, week_, activity_, resource_) VALUES (?,extract(WEEK from TIMESTAMP ?),?,?);");
886: ps.setString(1, project.getId());
887: ps.setDate(2, new java.sql.Date(date.getTime()));
888: ps.setFloat(3, score);
889: ps.setString(4, resource);
890: ps.executeUpdate();
891: ps.close();
892: }
893:
894: // insert new prefered resources
895: for (int i = 0; i < preferedResources.size(); i++) {
896: String resource = (String) ((Object[]) preferedResources
897: .get(i))[0];
898: float score = ((Float) ((Object[]) preferedResources
899: .get(i))[1]).floatValue();
900: ps = connection
901: .prepareStatement("INSERT INTO projects_prefered_resources_ (project_id_, week_, popularity_, resource_) VALUES (?,extract(WEEK from TIMESTAMP ?),?,?);");
902: ps.setString(1, project.getId());
903: ps.setDate(2, new java.sql.Date(date.getTime()));
904: ps.setFloat(3, score);
905: ps.setString(4, resource);
906: ps.executeUpdate();
907: ps.close();
908: }
909: }
910: } catch (LibresourceSecurityException se) {
911: ctx.setRollbackOnly();
912: throw se;
913: } catch (URINotExistException se) {
914: ctx.setRollbackOnly();
915: throw se;
916: } catch (Exception e) {
917: ctx.setRollbackOnly();
918: throw new LibresourceCoreServiceException(
919: "Error in computeStats : " + e.getMessage());
920: } finally {
921: try {
922: if (ps != null) {
923: ps.close();
924: }
925: } catch (Exception e) {
926: }
927:
928: try {
929: if (rs != null) {
930: rs.close();
931: }
932: } catch (Exception e) {
933: }
934:
935: try {
936: if (connection != null) {
937: connection.close();
938: }
939: } catch (Exception e) {
940: }
941: }
942: }
943:
944: /**
945: * @ejb.interface-method
946: * @ejb.transaction type="Required"
947: */
948: public void rebuildStats() throws LibresourceCoreServiceException {
949: Connection connection = null;
950: PreparedStatement ps = null;
951: ResultSet rs = null;
952:
953: try {
954: connection = LibresourceCoreServiceBean
955: .getConnectionForEvents(true);
956:
957: // delete old project stats
958: ps = connection
959: .prepareStatement("SELECT DISTINCT date_trunc('DAY',date_) as day from events_log");
960: rs = ps.executeQuery();
961:
962: while (rs.next()) {
963: Date date = rs.getDate("day");
964: computeStats(date);
965: }
966: } catch (Exception e) {
967: ctx.setRollbackOnly();
968: throw new LibresourceCoreServiceException(
969: "Error in rebuildStats : " + e.getMessage());
970: } finally {
971: try {
972: if (ps != null) {
973: ps.close();
974: }
975: } catch (Exception e) {
976: }
977:
978: try {
979: if (rs != null) {
980: rs.close();
981: }
982: } catch (Exception e) {
983: }
984:
985: try {
986: if (connection != null) {
987: connection.close();
988: }
989: } catch (Exception e) {
990: }
991: }
992: }
993: }
|