topic Reg Custom Table creation in Alfresco DB in Alfresco Archive

Reg Custom Table creation in Alfresco DB

dynamolalit — Thu, 29 Apr 2010 04:19:32 GMT

Hi,I want to store the status of content i.e. approved/rejected as well as total number of contents uploaded in repository per user for my project. I tried with Audit but could not find much details here so i decided to create my own table in which i can store user name, total content uploaded by h

Re: Reg Custom Table creation in Alfresco DB

dynamolalit — Fri, 30 Apr 2010 09:53:41 GMT

Hi,

I created a table in alfresco schema as

DROP TABLE IF EXISTS `alfresco`.`km_user_statistics`;
CREATE TABLE `alfresco`.`km_user_statistics` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`userName` varchar(160) NOT NULL,
`contentNodeRef` varchar(500) NOT NULL,
`contentRating` bigint(20) DEFAULT NULL,
`contentStatus` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Also i have created following classes for accessing the same:

KMUserValueObject.java to get/set values in table

package com.xxxx.alfresco.km.db;

/**
 * @author Lalit Jangra
 * Class to get/set values in KM_User_Statistics Table.
 */
public class KMUserValueObject {
   
   private String userName;
   private String contentNodeRef;
   private int contentRating;
   private String contentStatus;
   private int id;
   
   public int getId() {
      return id;
   }
   public void setId(int id) {
      this.id = id;
   }
   public String getUserName() {
      return userName;
   }
   public void setUserName(String userName) {
      this.userName = userName;
   }
   public int getContentRating() {
      return contentRating;
   }
   public void setContentRating(int contentRating) {
      this.contentRating = contentRating;
   }
   public String getContentNodeRef() {
      return contentNodeRef;
   }
   public void setContentNodeRef(String contentNodeRef) {
      this.contentNodeRef = contentNodeRef;
   }
   public String getContentStatus() {
      return contentStatus;
   }
   public void setContentStatus(String contentStatus) {
      this.contentStatus = contentStatus;
   }   

}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

KMUserDAOImpl.java for storing data

package com.xxxx.alfresco.km.db;

import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

/**
 * @author Lalit Jangra
 * Class to save/update User Statistics in DB.
 * Implementing KMUserDAO.
 */

public class KMUserDAOImpl extends HibernateDaoSupport implements KMUserDAO{

   @Override
   public void saveKMUSerStatistics(KMUserValueObject kmUserValueObject) {
        
        getHibernateTemplate().save(kmUserValueObject);
       

   }   

   @Override
   public void updateKMUSerStatistics(KMUserValueObject kmUserValueObject) {
       HibernateTemplate template = getHibernateTemplate();
       template.flush();
       template.clear();
       template.update(kmUserValueObject);         
   }
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

kmuserstatstics.hbm.xml

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
       "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
   <class name="com.xxxx.alfresco.km.db.KMUserValueObject" table="km_user_statistics">
      <id name="id" column="id" type="integer">
         <generator class="native" />
      </id>
      <property name="userName" column="userName" type="string"
         not-null="true" />
      <property name="contentNodeRef" column="contentNodeRef"
         type="string" not-null="true" />
      <property name="contentRating" column="contentRating"
         type="integer" not-null="false" />
      <property name="contentStatus" column="contentStatus"
         type="string" not-null="false" />
   </class>
</hibernate-mapping>‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

hibernate-context.xml

<!– Added by Lalit –>
<value>com/xxxx/alfresco/km/db/kmuserstatstics.hbm.xml</value>
<!– Added by Lalit –>

Now when i am trying to insert data into km_user_statics table, i am getting NullpointerException & data is not getting inserted into it.

Here are error logs


java.lang.NullPointerException
   at com.xxxx.alfresco.km.db.KMUserDAOImpl.saveKMUSerStatistics(KMUserDAOImpl.java:19)
   at com.xxxx.alfresco.km.bpm.KMReviewProcessAction$1.doWork(KMReviewProcessAction.java:120)
   at org.alfresco.repo.security.authentication.AuthenticationUtil.runAs(AuthenticationUtil.java:514)
   at com.xxxx.alfresco.km.bpm.KMReviewProcessAction.execute(KMReviewProcessAction.java:64)
   at org.jbpm.graph.def.Action.execute(Action.java:129)
   at org.jbpm.graph.def.GraphElement.executeAction(GraphElement.java:284)
   at org.jbpm.graph.def.GraphElement.executeActions(GraphElement.java:241)
   at org.jbpm.graph.def.GraphElement.fireAndPropagateEvent(GraphElement.java:213)
   at org.jbpm.graph.def.GraphElement.fireEvent(GraphElement.java:196)
   at org.jbpm.graph.def.Node.leave(Node.java:466)
   at org.jbpm.graph.def.Node.leave(Node.java:438)
   at org.jbpm.graph.def.Node.execute(Node.java:429)
   at org.jbpm.graph.def.Node.enter(Node.java:390)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
   at org.jbpm.graph.def.Node$$EnhancerByCGLIB$$7586d505.enter(<generated>)
   at org.jbpm.graph.def.Transition.take(Transition.java:167)
   at org.jbpm.graph.def.Node.leave(Node.java:479)
   at org.jbpm.graph.node.TaskNode.leave(TaskNode.java:213)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
   at org.jbpm.graph.node.TaskNode$$EnhancerByCGLIB$$afcccb4f.leave(<generated>)
   at org.jbpm.graph.exe.Token.signal(Token.java:223)
   at org.jbpm.graph.exe.Token.signal(Token.java:188)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.hibernate.proxy.pojo.cglib.CGLIBLazyInitializer.invoke(CGLIBLazyInitializer.java:157)
   at org.jbpm.graph.exe.Token$$EnhancerByCGLIB$$22157b99.signal(<generated>)
   at org.jbpm.taskmgmt.exe.TaskInstance.end(TaskInstance.java:495)
   at org.alfresco.repo.workflow.jbpm.WorkflowTaskInstance.end(WorkflowTaskInstance.java:141)
   at org.jbpm.taskmgmt.exe.TaskInstance.end(TaskInstance.java:436)
   at org.alfresco.repo.workflow.jbpm.JBPMEngine$26.doInJbpm(JBPMEngine.java:1712)
   at org.springmodules.workflow.jbpm31.JbpmTemplate$1.doInHibernate(JbpmTemplate.java:87)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:372)
   at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:338)
   at org.springmodules.workflow.jbpm31.JbpmTemplate.execute(JbpmTemplate.java:80)
   at org.alfresco.repo.workflow.jbpm.JBPMEngine.endTask(JBPMEngine.java:1680)
   at org.alfresco.repo.workflow.WorkflowServiceImpl.endTask(WorkflowServiceImpl.java:627)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:304)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
   at org.alfresco.repo.security.permissions.impl.AlwaysProceedMethodInterceptor.invoke(AlwaysProceedMethodInterceptor.java:40)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.alfresco.repo.security.permissions.impl.ExceptionTranslatorMethodInterceptor.invoke(ExceptionTranslatorMethodInterceptor.j
ava:49)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.alfresco.repo.audit.AuditMethodInterceptor.proceedWithAudit(AuditMethodInterceptor.java:238)
   at org.alfresco.repo.audit.AuditMethodInterceptor.proceed(AuditMethodInterceptor.java:205)
   at org.alfresco.repo.audit.AuditMethodInterceptor.invoke(AuditMethodInterceptor.java:153)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:106)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy48.endTask(Unknown Source)
   at org.alfresco.web.bean.workflow.ManageTaskDialog.transition(ManageTaskDialog.java:449)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.apache.myfaces.el.MethodBindingImpl.invoke(MethodBindingImpl.java:132)
   at org.apache.myfaces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:61)
   at javax.faces.component.UICommand.broadcast(UICommand.java:109)
   at javax.faces.component.UIViewRoot._broadcastForPhase(UIViewRoot.java:97)
   at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:171)
   at org.apache.myfaces.lifecycle.InvokeApplicationExecutor.execute(InvokeApplicationExecutor.java:32)
   at org.apache.myfaces.lifecycle.LifecycleImpl.executePhase(LifecycleImpl.java:95)
   at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:70)
   at javax.faces.webapp.FacesServlet.service(FacesServlet.java:139)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.alfresco.web.app.servlet.AuthenticationFilter.doFilter(AuthenticationFilter.java:110)
   at sun.reflect.GeneratedMethodAccessor500.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy210.doFilter(Unknown Source)
   at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.alfresco.repo.web.filter.beans.NullFilter.doFilter(NullFilter.java:74)
   at sun.reflect.GeneratedMethodAccessor500.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
   at java.lang.reflect.Method.invoke(Method.java:597)
   at org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:122)
   at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
   at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
   at $Proxy210.doFilter(Unknown Source)
   at org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:88)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
   at java.lang.Thread.run(Thread.java:619)
15:21:39,695 User:jindaladmin WARN  [engine.StatefulPersistenceContext.ProxyWarnLog] Narrowing proxy to class org.jbpm.graph.node.
StartState - this operation breaks ==‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I debugged to find that getHibernateTemplate() is null which is root cause.

Any idea on how to achieve this?

Re: Reg Custom Table creation in Alfresco DB

loftux — Fri, 30 Apr 2010 10:45:51 GMT

Hi,

You are on the wrong track. There is no need to create a custom table to do what you want. Instead create a custom object, see
http://wiki.alfresco.com/wiki/Data_Dictionary_Guide

Jeff Potts book "Alfresco Developer Guide" has some good examples, including a rating component that is similar to what you want to do.

Re: Reg Custom Table creation in Alfresco DB

dynamolalit — Fri, 07 May 2010 04:30:45 GMT

Hi Loftux,

Thanks for your suggestion.I modified Content Model to set user statistics & used webscripts to get it per user , its working fine.

http://forums.alfresco.com/en/viewtopic.php?f=36&t=26649&start=0

Re: Reg Custom Table creation in Alfresco DB

sanket — Tue, 18 May 2010 12:11:49 GMT

Hi Lalit. I am facing some problems regarding this issue.
For the time being, I have created my two new tables in alfresco.
I want that using my class, the values should be inserted into one of these tables.
I want to use alfresco's built-in mechanism to insert values in db.

So, I have not created cfg.xml file and I have made entry of my hbm.xml file in hibernate-context.xml.


public class DatabaseOperation extends HibernateDaoSupport {

   
   public void test1(){
      Session session = null;
           HibernateTemplate ht = new HibernateTemplate();
       
      SessionFactory sessionFactory = ht.getSessionFactory();
      session = sessionFactory.getCurrentSession();
      UserSessionMaster userSessionMaster = new UserSessionMaster();
      userSessionMaster.setUserId("A1010");
      userSessionMaster.setSessionStatus("submitted");
      userSessionMaster.setSessionCreatedDate(date.toLocaleString());
      userSessionMaster.setSessionEndDate(date.toLocaleString());
      
      //getHibernateTemplate().save(userSessionMaster);
      session.save(userSessionMaster);
      //HibernateTemplate template = getHibernateTemplate();
         // template.flush();
         // template.clear();
         // template.update(userSessionMaster);
      
      System.out.println("Done");
   }
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I have also tried to use getHibernateTemplate().save(userSessionMaster);
But getting NullPointerException.
I went thru this link - http://wiki.alfresco.com/wiki/Data_Dictionary_Guide

But actually according to my project requirement, I need to create two tables in alfresco db.
Can u plz help ?

Re: Reg Custom Table creation in Alfresco DB

dynamolalit — Thu, 20 May 2010 06:16:42 GMT

Hi Sanket,

I tried the same to create a custom table & insert data but was not fruitful as i was also getting NPE.

My requirement was to get user statistics per user in Alfresco,to get total number of doc uploaded by him in DM, no of approvals & rejection for him etc.

I tried hard but inserting into Alfresco DB schema seems to be trivial. I tried a lot but could not achieve it successfully.

Finally, i modified content model to get these details.Here is a snippet contentModel.xml:


…
<type name="cm:content">
         <title>Content</title>
         <parent>cm:cmobject</parent>
         <archive>true</archive>
         <properties>
            <property name="cm:content">
               <type>d:content</type>
               <mandatory>false</mandatory>
               <!– Although content is marked as indexed atomically it may end up asynchronous –>
               <!– if the content conversion will take too long. Content that does not require conversion –>
               <!– to UTF8 test/plain will always be indexed atomically –>
               <index enabled="true">
                  <atomic>true</atomic>
                  <stored>false</stored>
                  <tokenised>true</tokenised>
               </index>
            </property>
         <!–Custom Properties added by Lalit for User Statistics.–>
         <property name="cm:contentStatus">
            <type>d:text</type>
            <mandatory>false</mandatory>
         </property>
         <property name="cm:originalOwner">
            <type>d:text</type>
            <mandatory>false</mandatory>
         </property>
         <property name="cm:contentRating">
            <type>d:text</type>
            <mandatory>false</mandatory>
         </property>
         <!–Custom Properties added by Lalit for User Statistics.–>
         </properties>
      </type>
…
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I injected three more properties : contentStatus,originalOwner & contentRating.

Now once content is under workflow, i am setting values of these properties using NodeService in ActionHandler as :


package com.xxxx.alfresco.km.bpm;

import java.io.IOException;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.faces.context.FacesContext;

import org.alfresco.deployment.transformers.CompressionTransformer;
import org.alfresco.model.ContentModel;
import org.alfresco.repo.content.MimetypeMap;
import org.alfresco.repo.content.transform.ComplexContentTransformer;
import org.alfresco.repo.content.transform.ComplexContentTransformerTest;
import org.alfresco.repo.content.transform.ContentTransformer;
import org.alfresco.repo.content.transform.PoiHssfContentTransformer;
import org.alfresco.repo.security.authentication.AuthenticationUtil;
import org.alfresco.repo.workflow.jbpm.JBPMSpringActionHandler;
import org.alfresco.service.Auditable;
import org.alfresco.service.PublicService;
import org.alfresco.service.ServiceRegistry;
import org.alfresco.service.cmr.model.FileFolderService;
import org.alfresco.service.cmr.model.FileInfo;
import org.alfresco.service.cmr.repository.ChildAssociationRef;
import org.alfresco.service.cmr.repository.ContentData;
import org.alfresco.service.cmr.repository.ContentIOException;
import org.alfresco.service.cmr.repository.ContentReader;
import org.alfresco.service.cmr.repository.ContentService;
import org.alfresco.service.cmr.repository.ContentWriter;
import org.alfresco.service.cmr.repository.NodeRef;
import org.alfresco.service.cmr.repository.NodeService;
import org.alfresco.service.cmr.repository.TransformationOptions;
import org.alfresco.service.cmr.search.CategoryService;
import org.alfresco.service.namespace.QName;
import org.alfresco.web.bean.repository.Node;
import org.alfresco.web.bean.repository.Repository;
import org.alfresco.web.ui.common.Utils;
import org.alfresco.web.ui.common.Utils.URLMode;
import org.apache.log4j.Logger;
import org.jbpm.context.exe.ContextInstance;
import org.jbpm.graph.exe.ExecutionContext;
import org.springframework.beans.factory.BeanFactory;

import com.xxxx.alfresco.km.property.KmPropertyReader;

/** 
 * @author Lalit Jangra 
 * Action Class for xxxx Approval Workflow. Content is
 * moved to "Under Review" once it is uploaded. If content is approved,
 * it will be moved back to original location. If content is rejected,
 * it will be moved to User Home of Workflow initiator.Also as per approval 
 * or rejection, content will be assigned a property to get/set user statistics.
 */

@PublicService
public class KMReviewProcessAction extends JBPMSpringActionHandler {
   private static final long serialVersionUID = 1L;
   private static Logger logger = Logger
         .getLogger(KMReviewProcessAction.class);
   private ServiceRegistry services;
   private FileFolderService fileFolderService;
   private NodeService nodeService;
   int approvalRating = 5;
   int rejOrURRating = 0;
   ContentService contentService = null;
   String content = "";
   CategoryService categoryService = null;
   List<String> categoryList = new ArrayList<String>();
   private String solrServIp = null;
   private String solrFileLoc = null;
   private   String alfServerIp = null;
   
   /**
    * Method to initialize services.
    */
   @Override
   protected void initialiseHandler(BeanFactory factory) {
      logger.debug("Inside initialiseHandler of KMReviewProcessAction!!!!");
      services = (ServiceRegistry) factory
            .getBean(ServiceRegistry.SERVICE_REGISTRY);
      fileFolderService = services.getFileFolderService();
      services.getAuditService();
      nodeService = services.getNodeService();
      contentService = services.getContentService();
      logger.debug("contentService "+contentService );
      categoryService = services.getCategoryService();
   }

   /**
    * Method to move content in workflow. It will move content depending upon
    * wfFlag variable value.Also it will get/set the user statistics by use of 'contentStatus' property.
    * For under review or rejected content, rating will be 0 & for approved content, rating will be 5.
    * Once content is approved , an XML is formed to be posted to SOLR.
    **/
   @Auditable
   public void execute(ExecutionContext context) throws Exception {
      logger.debug("Inside execute of KMReviewProcessAction");
       try{
              KmPropertyReader kmPropertyReader = new KmPropertyReader();
               solrServIp = kmPropertyReader.getProperty("solr.server.ip");
               logger.debug("solrServIp : "+solrServIp);
               solrFileLoc = kmPropertyReader.getProperty("solr.file.location");
               logger.debug("solrFileLoc : "+solrFileLoc);
               alfServerIp = kmPropertyReader.getProperty("alfresco.server.ip");
               logger.debug("alfServerIp : "+alfServerIp);
           }catch (Exception e) {
            logger.error("Error while reading property in KMReviewProcessAction : "+e.getMessage());
         }
      final ContextInstance contextInstance = context.getContextInstance();
      AuthenticationUtil.runAs(new AuthenticationUtil.RunAsWork<Object>() {
         String userName = (String) contextInstance.getVariable("userName");
         String wfFlag = (String) contextInstance.getVariable("wfFlag");
         NodeRef nodeRef = (NodeRef) contextInstance.getVariable("nodeRef");
         NodeRef targetNodeRef = (NodeRef) contextInstance
               .getVariable("targetNodeRef");
         NodeRef docParentRef = (NodeRef) contextInstance
               .getVariable("docParentRef");
         NodeRef userHomeRef = (NodeRef) contextInstance
               .getVariable("userHomeRef");
         List<ChildAssociationRef> children = null;
         String docPath = (String) contextInstance.getVariable("docPath");
         String docName = (String) contextInstance.getVariable("docName");
         public Object doWork() throws Exception {
            logger.debug("User name " + userName);
            logger.debug("wfFlag in  KMReviewProcessAction " + wfFlag);
            logger.debug("docPath in execute()  " + docPath);
            logger.debug("docName in execute()  " + docName);
            logger.debug("nodeRef in execute() " + nodeRef);
            logger.debug("targetNodeRef in execute()  " + targetNodeRef);
            logger.debug("docParentRef in execute()  " + docParentRef);
            logger.debug("userHomeRef in execute()  " + userHomeRef);

            if (wfFlag.equalsIgnoreCase("Moved")) { //Under Review Case
               // Moving Content from its original location to Under Review.
               logger.debug("Moving content to Under Review & set user statistics properties");
               try {
                  @SuppressWarnings("unused")
                  FileInfo fileInfo = fileFolderService.move(nodeRef,
                        targetNodeRef, null);
                  Map<QName, Serializable> propertyMap = nodeService
                  .getProperties(nodeRef);
                  QName statusQname = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentStatus");
                  QName contentRating = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentRating");
                  QName ownerQname = QName.createQName("{http://www.alfresco.org/model/content/1.0}originalOwner");
                  propertyMap.put(statusQname, "underreview");
                  propertyMap.put(contentRating,getRating(rejOrURRating, nodeRef, userName));
                  propertyMap.put(ownerQname, userName);
                  nodeService.setProperties(nodeRef, propertyMap);                  
               } catch (Exception e) {
                  logger
                        .error("Error while moving content to Under Review "
                              + e.getMessage());
               }
            } else if (wfFlag.equalsIgnoreCase("Approved")) { //Content Approval Case
               // Move Content back to original location.
               logger.debug("Moving Content back after Approval & set user statistics properties");
               try {
                  @SuppressWarnings("unused")
                  FileInfo fileInfo = fileFolderService.move(nodeRef,
                        docParentRef, null);
                  Map<QName, Serializable> propertyMap = nodeService
                        .getProperties(nodeRef);
                  QName statusQname = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentStatus");
                  QName ownerQname = QName.createQName("{http://www.alfresco.org/model/content/1.0}originalOwner");
                  QName contentRating = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentRating");
                  propertyMap.put(statusQname, "approved");
                  propertyMap.put(ownerQname, userName);
                  propertyMap.put(contentRating,getRating(approvalRating, nodeRef, userName));
                  nodeService.setProperties(nodeRef, propertyMap);
                  } catch (Exception e) {
                  logger
                        .error("Error while moving content to original location after approval : "
                              + e.getMessage());
                  e.printStackTrace();
               }
            } else if (wfFlag.equalsIgnoreCase("Rejected")) { //Content Rejection Case
               // Move content to User Home.
               logger.debug("Moving Content to User Home after Rejection & set user statistics properties");
               try {
                  FileInfo fileInfo = fileFolderService.move(nodeRef,
                        userHomeRef, null);
                  Map<QName, Serializable> propertyMap = nodeService
                        .getProperties(nodeRef);
                  QName statusQname = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentStatus");
                  QName ownerQname = QName.createQName("{http://www.alfresco.org/model/content/1.0}originalOwner");
                  QName contentRating = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentRating");
                  propertyMap.put(statusQname, "rejected");
                  propertyMap.put(ownerQname, userName);
                  propertyMap.put(contentRating,getRating(rejOrURRating, nodeRef, userName));
                  nodeService.setProperties(nodeRef, propertyMap);
                  System.out.println("After Rejection   "
                        + fileInfo.getName());
               } catch (Exception e) {
                  logger.error("Error while moving content to User Home after rejection "
                        + e.getMessage());
               }
            }
            return children;
         }
      }, "admin");
   }

   /*
    * Method to get overall rating for a user.
    */
   public String getRating(int newRating, NodeRef nodeRef,String userName){
      QName contentRating = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentRating");
      String totalCurRating = null; 
      try{
         totalCurRating = nodeService.getProperty(nodeRef, contentRating).toString();
      }catch (NullPointerException e) {
         logger.debug("Null Rating for user in getRating()");
         totalCurRating = "0";
      }
      int tempRating = new Integer(totalCurRating).intValue();
      int overallRating = tempRating + newRating;
      logger.debug("Final rating for content "+overallRating);
      String result = "";
      return (result+overallRating);
   }
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Hereby the values are set for these properties.

Now to retrieve these properties, i used NodeService in a web script to be invoked from a dashlet JSP as:


package com.xxxx.alfresco.km.webscripts;

import java.io.IOException;
import org.alfresco.service.ServiceRegistry;
import org.alfresco.service.cmr.repository.NodeRef;
import org.alfresco.service.cmr.repository.NodeService;
import org.alfresco.service.cmr.repository.StoreRef;
import org.alfresco.service.cmr.search.ResultSet;
import org.alfresco.service.cmr.search.ResultSetRow;
import org.alfresco.service.cmr.search.SearchParameters;
import org.alfresco.service.cmr.search.SearchService;
import org.alfresco.service.namespace.QName;
import org.alfresco.web.scripts.AbstractWebScript;
import org.alfresco.web.scripts.WebScriptRequest;
import org.alfresco.web.scripts.WebScriptResponse;
import org.apache.log4j.Logger;

/**
 *  @author Lalit Jangra 
 *    Web Script to get user statistics per user. User name
 *  will be passed a parameter on basis of which all content on which he
 *  is owner will be searched. Once all content are available, it will be
 *  checked for approved, rejected & under review property.
 *  Finally an HTML string will be returned back as output to a custom dashlet for the same.  
 */
public class GetUserStatisticsWebScript extends AbstractWebScript {

   private static Logger logger = Logger
         .getLogger(GetUserStatisticsWebScript.class);
   private SearchService searchService;
   private ServiceRegistry serviceRegistry;
   private NodeService nodeService;
   
   /*
    * Method to get user statistics. User name will be passed as a request parameter & 
    * for this user, a search will be performed to get all the content of which he is
    * original owner. After this, each of content in resultset will be checked for the
    * status.
    */
   public void execute(WebScriptRequest req, WebScriptResponse res)
         throws IOException {
      int totalNoOfContent = 0;
      int approvedContent = 0;
      int rejectedContent = 0;
      int underReviewContent = 0;
      String ratingPerContent = null;
      NodeRef currentNodeRef = null;
      int ratingToDisplay = 0;
      
      String userName = req.getParameter("userName");
      logger.debug("User name to get statistics for : " + userName);
      nodeService = serviceRegistry.getNodeService();
      StoreRef storeRef = new StoreRef(StoreRef.PROTOCOL_WORKSPACE,"SpacesStore");
      SearchParameters sp = new SearchParameters();
      sp.addStore(storeRef);
      sp.setLanguage(SearchService.LANGUAGE_LUCENE);
      sp.setQuery("@cm\\:originalOwner:" + userName + "");
      ResultSet results = null;
      try {
         results = getSearchService().query(sp);
         totalNoOfContent = results.length();
         //logger.debug("Size of resultset is " + totalNoOfContent);         
         for (ResultSetRow row : results) {
            currentNodeRef = row.getNodeRef();
            QName statusQname = QName
                  .createQName("{http://www.alfresco.org/model/content/1.0}contentStatus");
            nodeService.getProperty(currentNodeRef, statusQname);
            //logger.debug("Status for "+ currentNodeRef.toString()+ " is "+ nodeService.getProperty(currentNodeRef, statusQname).toString());
            if((nodeService.getProperty(currentNodeRef, statusQname)
                  .toString()).equalsIgnoreCase("underreview")){
               underReviewContent = underReviewContent + 1;
            }else if ((nodeService.getProperty(currentNodeRef, statusQname)
                  .toString()).equalsIgnoreCase("approved")) {
               approvedContent = approvedContent + 1;
            } else if ((nodeService
                  .getProperty(currentNodeRef, statusQname).toString())
                  .equalsIgnoreCase("rejected")) {
               rejectedContent = rejectedContent + 1;
            }      
            //For User Rating.
            QName contentRating = QName.createQName("{http://www.alfresco.org/model/content/1.0}contentRating");
            try{
               ratingPerContent = nodeService.getProperty(currentNodeRef, contentRating).toString();
               ratingToDisplay += new Integer(ratingPerContent).intValue();
            }catch (NullPointerException e) {
               //logger.debug("Null Rating for user in GetUserStatisticsWebScript");
               ratingToDisplay = 0;
            }
         }
         
         logger.debug("totalNoOfContent for " +  userName+ " : " + totalNoOfContent);
         logger.debug("approvedContent for  "  + userName+ " : " + approvedContent);
         logger.debug("rejectedContent for  "  + userName+ " : " + rejectedContent);
         logger.debug("underReviewContent for "  + userName+ " : " + underReviewContent);
         logger.debug("ratingToDisplay for  "  + userName+ " : " + ratingToDisplay);
         
          //Creating an HTML String that include total output.
         res.setContentType("text/html");
          String finalOutput = "";
          finalOutput +=    "<html>"+
                     "<body>"+
                     "<table border=1>"+
                     "<tr>"+
                     "<th>"+
                     "Total No of KM Contribution"+
                     "</th>"+
                     "<th>"+
                     "Total No of KM Approvals"+
                     "</th>"+
                     "<th>"+
                     "Total No of KM Disapprovals"+
                     "</th>"+
                     "<th>"+
                     "Total No of KM Under Reviews"+
                     "</th>"+
                     "<th>"+
                     "Total User Rating"+
                     "</th>"+
                     "</tr>";
          finalOutput += "<tr><td align=center>";
          finalOutput +=totalNoOfContent;
          finalOutput += "</td><td align=center>";
          finalOutput += approvedContent;
          finalOutput += "</td><td align=center>";
          finalOutput += rejectedContent;
          finalOutput += "</td><td align=center>";
          finalOutput += underReviewContent;
          finalOutput += "</td><td align=center>";
          finalOutput += ratingToDisplay;
          finalOutput += "</td></tr></table></body></html>";
          res.getWriter().write(finalOutput);
      } finally {
         if (results != null) {
            totalNoOfContent = 0;
            approvedContent = 0;
            rejectedContent = 0;
            underReviewContent = 0;
            ratingToDisplay = 0;
            results.close();
         }
      }
   }
   
   public ServiceRegistry getServiceRegistry() {
      return serviceRegistry;
   }

   public void setServiceRegistry(ServiceRegistry serviceRegistry) {
      this.serviceRegistry = serviceRegistry;
   }

   public void setSearchService(SearchService searchService) {
      this.searchService = searchService;
   }

   public SearchService getSearchService() {
      return searchService;
   }
}
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Finally i got the output in jsp which is as:


<%@ taglib uri="/WEB-INF/repo.tld" prefix="r" %>
<%@ page import="com.xxxx.alfresco.km.webscripts.WebScriptInvoker"%>
<%@ page import="com.xxxx.alfresco.km.webscripts.WebScriptCall"%> 


<r:webScript scriptUrl="/wcservice/com/xxxx/alfresco/km/getuserstatistics?userName=#{NavigationBean.currentUser.userName}" />
‍‍‍‍‍‍‍‍

This is how i implemented the requirement.You can take a que from here if it is helpful.

Also if you find out how to implement DB tables in alfresco, please share it with me. :arrow:

Re: Reg Custom Table creation in Alfresco DB

sanket — Thu, 27 May 2010 02:37:00 GMT

Yes of course. YOur post was surely helpful.
But our project requirement is such that we have to track each n every activity happening in alfresco (in a db table).
Activities like New document arrival in space (wid all doc details), edit, delete of doc, session maintainance, checkin-out, doc rename, metadata change and a lot more.

So we are working on it according to the cases. Right now, v r working on "New doc arrival in space".
We haven't managed to connect to db using alfresco's hibernate layer. BUt v have made our own hibernate layer n connecting to db. THat's of course not d recommended method. But v r planning to come back to this, once v on track (meeting some
deadlines).
For the time being, the aspects that alfresco provides built-in r enough for us so v dont need to add more aspects to the model.

THanks