#webscript

Like writing script for spreadsheets and all that.

I think I require some assistance. Actually I'm pretty sure I need some help hahahahaha -headdesk-

Recently a client wanted something, which couldn't be retrieved from the normal Alfresco API. The data was present in Alfresco, I did a DB query and got the desired results, but now I wanted to present that in a Dashlet.

After doing some research, I finally found the way to do it.

I created a Java-Backed Webscript, like stated at the Alfresco Wiki.

That is quite easily done, the next step was to know which DB-class I needed to pick. I know that Alfresco uses Hibernate, so making a separate JDBC-connection is just stupid.

After struggling with different classes, I eventually found the right one. I need to extend my class from org.springframework.orm.hibernate3.support.HibernateDaoSupport

One can use HQL Queries or plain SQL.

Beginning with plain SQL I ended up doing more translations and comparisons than using HQL. It was sometimes a bit hard to creating the right Query, because for testing purposes I directly queried the DB. So re-writing SQL to HQL is something which can take a while.

This is an example class which queries directly the DB:

public class DBQuery extends HibernateDaoSupport {     public List<Object[]> getResultSet(final Date startDate, final Date endDate) {         HibernateCallback callback = new HibernateCallback() {             public Object doInHibernate(Session session) throws HibernateException, SQLException {                                 Date startQuery = null;                 Date endQuery = new Date();;                                 if (startDate == null){                     startQuery = new Date();                     Calendar cal = Calendar.getInstance();                     cal.setTime(endQuery);                     cal.add(Calendar.MONTH, -1);                     startQuery = cal.getTime();                 } else                     startQuery = startDate;                                 if (endDate != null)                     endQuery = endDate;                                 StringBuilder query = new StringBuilder();                                 query.append("SELECT distinct date_format(aaf.date, '%d-%m-%Y'), aaf.userId ");                 query.append("FROM org.alfresco.repo.audit.hibernate.AuditFactImpl aaf, org.alfresco.repo.audit.hibernate.AuditSourceImpl aas ");                 query.append("WHERE aaf.auditSource = aas ")                     .append("AND aas.method = 'authenticate' ")                     .append("AND aas.service = 'AuthenticationService' ")                     .append("AND aaf.date BETWEEN :start AND :end ")                     .append("ORDER BY aaf.date DESC");                                 Query queryResult = session.createQuery(query.toString());                 queryResult.setParameter("start", startQuery);                 queryResult.setParameter("end", endQuery);                                 return queryResult.list();             }         };         List<Object[]> result = (List<Object[]>) (getHibernateTemplate().execute(callback));         return result;     }

What does this query do, it retrieves the Unique Logins through the AuthenticationService, which should be enabled in the AuditConfig-Custom.xml.

So you've got your results in an List<Object[]>, so how can we loop through these results?

The Object[] is an array containing the results from the specified columns. So Object[0] = aaf.date and Object[1] is aaf.userId.

Map<String, Object> map = new HashMap<String, Object>(); for (Object[] objects : result) {     String date = Object[0];     String userId = Object[1];     //Do whatever you like with the results     //Eventually put the results in a Map so they can be used in the Webscript/FTL     map.put("user", objects[1]);     map.put("date", objects[0]); }

Full code of this Java-Backed Webscript:

public class RetreiveUniqueLogin extends DeclarativeWebScript {         private DBQuery DBQuery;         private SimpleDateFormat sf = new SimpleDateFormat("dd MM yyyy");     protected Map<String, Object> executeImpl(WebScriptRequest req, WebScriptStatus status) {                 String start = req.getParameter("startDate");         String end = req.getParameter("endDate");                 Date startDate = null;         Date endDate = null;         try {             if (start != null && start.length() > 0)                 startDate = sf.parse(start);             if (end != null && end.length() > 0)                 endDate = sf.parse(end);         } catch (ParseException e) {             // TODO Auto-generated catch block             e.printStackTrace();         }                 List<Object[]> results = DBQuery.getResultSet(startDate, endDate);         Map<String, Object> map = new HashMap<String, Object>();                 // loop through the results and send smart objects instead of a Object[]         for (Object[] objects : results) {             map.put("user", objects[1]);             map.put("date", objects[0]);         }                 Map<String, Object> model = new HashMap<String, Object>(7, 1.0f);         model.put("auditResults", map);         return model;     }

  public DBQuery getDBQuery() {         return DBQuery;     }     public void setDBQuery(DBQuery DBQuery) {         this.DBQuery = DBQuery;     }

And we need a Freemarker Template to present the results.

<table id="auditPreviewTable" border="0" class="tablesorter">     <thead>     <tr>         <th>Date</th>         <th>User</th>     </tr>     </thead>     <#if auditResults??>         <#list auditResults as result>             <tr>                 <td>${result["date"]}</td>                 <td>${result["user"]}</td>             </tr>         </#list>     </#if>     </tbody> </table>

And of course you need to register the Java-Backed Webscript in a context file.