/*
*
* Copyright 2005 AgileTec s.r.l. (http://www.agiletec.it) All rights reserved.
*
* This file is part of jAPS software.
* jAPS is a free software; 
* you can redistribute it and/or modify it
* under the terms of the GNU General Public License (GPL) as published by the Free Software Foundation; version 2.
* 
* See the file License for the specific language governing permissions   
* and limitations under the License
* 
* 
* 
* Copyright 2005 AgileTec s.r.l. (http://www.agiletec.it) All rights reserved.
*
*/
package com.agiletec.aps.cms.content;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import com.agiletec.aps.cms.content.model.CmsAttributeReference;
import com.agiletec.aps.cms.content.model.Content;
import com.agiletec.aps.cms.content.model.ContentRecordVO;
import com.agiletec.aps.cms.content.model.extraAttribute.IReferenceableAttribute;
import com.agiletec.aps.system.common.entity.AbstractEntityDAO;
import com.agiletec.aps.system.common.entity.model.attribute.AttributeInterface;
import com.agiletec.aps.system.common.util.EntityAttributeIterator;
import com.agiletec.aps.system.exception.ApsSystemException;
import com.agiletec.aps.system.services.category.Category;
import com.agiletec.aps.util.DateConverter;

/**
 * Data Access Object per gli oggetti contenuto (Content).
 * @version 1.0
 * @author M.Diana - E.Santoboni - S.Didaci
 */
public class ContentDAO extends AbstractEntityDAO implements IContentDAO {
        
        /**
         * Cerca un contenuto (in oggetto tipo ContentRecordVO) in base 
         * all'identificativo. null se non vi è nessun contenuto corrispondente.
         * @param id L'identificativo del contenuto.
         * @return Il contenuto cercato (in oggetto ContentRecordVO).
         */
        public ContentRecordVO loadContentVO(String id) {
                Connection conn = null;
                PreparedStatement stat = null;
                ResultSet res = null;
                ContentRecordVO contentVo = null;
                try {
                        conn = this.getConnection();
                        stat = conn.prepareStatement(LOAD_CONTENT_VO);
                        stat.setString(1, id);
                        res = stat.executeQuery();
                        if (res.next()) {
                                contentVo = this.createContentVo(res, null);
                        }
                } catch (Throwable t) {
                        processDaoException(t, "Errore in caricamento contenuto", "loadContent");
                } finally {
                        closeDaoResources(res, stat, conn);
                }
                return contentVo;
        }
        
        private ContentRecordVO createContentVo(ResultSet res, String descr) throws SQLException {
                ContentRecordVO contentVo = new ContentRecordVO();
                contentVo.setId(res.getString(1));
                contentVo.setTypeCode(res.getString(2));
                if (null == descr) {
                        contentVo.setDescr(res.getString(3));
                } else {
                        contentVo.setDescr(descr);
                }
                contentVo.setStatus(res.getString(4));
                String xmlWork = res.getString(5);
                contentVo.setCreate(DateConverter.parseData(res.getString(6), this.DATE_FORMAT));
                contentVo.setModify(DateConverter.parseData(res.getString(7), this.DATE_FORMAT));
                String xmlOnLine = res.getString(8);
                contentVo.setOnLine(null != xmlOnLine && xmlOnLine.length() > 0);
                contentVo.setSync(xmlWork.equals(xmlOnLine));
                String mainGroupCode = res.getString(9);
                contentVo.setMainGroupCode(mainGroupCode);
                contentVo.setXmlWork(xmlWork);
                contentVo.setXmlOnLine(xmlOnLine);
                return contentVo;
        }
        
        /**
         * Inserisce un contenuto nel database.
         * @param content Il contenuto da inserire nel db.
         */
        public void addContent(Content content) {
                Connection conn = null;
                try {
                        conn = this.getConnection();
                        conn.setAutoCommit(false);
                        this.addContentRecord(content, conn);
                        conn.commit();
                } catch (Throwable t) {
                        this.executeRollback(conn);
                        processDaoException(t, "Errore in aggiunta contenuto", "addContent");
                } finally {
                        closeConnection(conn);
                }
        }
        
        protected void addContentRecord(Content content, Connection conn) throws ApsSystemException {
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(ADD_CONTENT);
                        stat.setString(1, content.getId());
                        stat.setString(2, content.getTypeCode());
                        stat.setString(3, content.getDescr());
                        stat.setString(4, content.getStatus());
                        stat.setString(5, content.getXML());
                        String currentDate = DateConverter.getFormattedDate(new Date(), this.DATE_FORMAT);
                        stat.setString(6, currentDate);
                        stat.setString(7, currentDate);
                        stat.setString(8, content.getMainGroup());
                        stat.executeUpdate();
                } catch (Throwable t) {
                        processDaoException(t, "Errore in aggiunta contenuto",
                                        "addContentRecord");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        /**
         * Aggiorna un contenuto nel database.
         * @param content Il contenuto da aggiornare nel db.
         */
        public void updateContent(Content content){
                Connection conn = null;
                try {
                        conn = this.getConnection();
                        conn.setAutoCommit(false);
                        this.updateContentRecord(content, conn);
                        conn.commit();
                } catch (Throwable t) {
                        this.executeRollback(conn);
                        processDaoException(t, "Errore in aggiornamento contenuto", "updateContent");
                } finally {
                        closeConnection(conn);
                }
        }
        
        protected void updateContentRecord(Content content, Connection conn) throws ApsSystemException {
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(UPDATE_CONTENT);
                        stat.setString(1, content.getTypeCode());
                        stat.setString(2, content.getDescr());
                        stat.setString(3, content.getStatus());
                        stat.setString(4, content.getXML());
                        stat.setString(5, DateConverter.getFormattedDate(new Date(), this.DATE_FORMAT));
                        stat.setString(6, content.getMainGroup());
                        stat.setString(7, content.getId());
                        stat.executeUpdate();
                } catch (Throwable t) {
                        processDaoException(t, "Errore in aggiornamento contenuto",
                                        "updateContentRecord");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        /**
         * Mette un contenuto online.
         * @param content Il contenuto da mettere online.
         */
        public void insertOnLineContent(Content content) {
                Connection conn = null;
                try {
                        conn = this.getConnection();
                        conn.setAutoCommit(false);
                        this.deleteEntitySearchRecord(content.getId(), conn);
                        this.deleteContentRelationsRecord(content, conn);
                        this.updateContentRecordForInsertOnLine(content, conn);
                        this.addEntitySearchRecord(content.getId(), content, conn);
                        this.addContentRelationsRecord(content, conn);
                        conn.commit();
                } catch (Throwable t) {
                        this.executeRollback(conn);
                        processDaoException(t, "Errore in inserimento contenuto online",
                                        "insertOnLineContent");
                } finally {
                        this.closeConnection(conn);
                }
        }
        
        protected void updateContentRecordForInsertOnLine(Content content, Connection conn) throws ApsSystemException {
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(INSERT_ONLINE_CONTENT);
                        stat.setString(1, content.getTypeCode());
                        stat.setString(2, content.getDescr());
                        stat.setString(3, content.getStatus());
                        String xml = content.getXML();
                        stat.setString(4, xml);
                        stat.setString(5, DateConverter.getFormattedDate(new Date(), this.DATE_FORMAT));
                        stat.setString(6, xml);
                        stat.setString(7, content.getMainGroup());
                        stat.setString(8, content.getId());
                        stat.executeUpdate();
                } catch (Throwable t) {
                        processDaoException(t, "Errore in aggiornamento contenuto per inserimento onLine",
                                        "updateContentRecordForInsertOnLine");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        /**
         * Aggiorna le referenziazioni di un contenuto publico.
         * @param content Il contenuto publico.
         */
        public void reloadContentReferences(Content content) {
                if (content.isOnLine()) {
                        Connection conn = null;
                        try {
                                conn = this.getConnection();
                                conn.setAutoCommit(false);
                                this.deleteContentRelationsRecord(content, conn);
                                this.addContentRelationsRecord(content, conn);
                                conn.commit();
                        } catch (Throwable t) {
                                this.executeRollback(conn);
                                processDaoException(t, "Errore in ricaricamento referenziazioni",
                                                "reloadContentReferences");
                        } finally {
                                this.closeConnection(conn);
                        }
                }
        }
        
        /**
         * Rimuove un contenuto online. L'operazione non cancella il contenuto ma ne 
         * rimuove la possibilità di visualizzazione nel portale.
         * @param content Il contenuto da rimuovere da online.
         */
        public void removeOnLineContent(Content content) {
                Connection conn = null;
                try {
                        conn = this.getConnection();
                        conn.setAutoCommit(false);
                        this.removeOnLineContent(content, conn);
                        conn.commit();
                } catch (Throwable t) {
                        this.executeRollback(conn);
                        processDaoException(t, "Errore in rimozione contenuto online", 
                                "removeOnLineContent");
                } finally {
                        this.closeConnection(conn);
                }
        }
        
        /**
         * Rimuove un contenuto online. L'operazione non cancella il contenuto ma ne 
         * rimuove la possibilità di visualizzazione nel portale.
         * @param content Il contenuto da rimuovere da online.
         * @param conn La connessione al database.
         * @throws ApsSystemException in caso di errore nell'accesso al db.
         */
        private void removeOnLineContent(Content content, Connection conn) throws ApsSystemException {
                this.deleteEntitySearchRecord(content.getId(), conn);
                this.deleteContentRelationsRecord(content, conn);
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(REMOVE_ONLINE_CONTENT);
                        stat.setString(1, null);
                        stat.setString(2, content.getStatus());
                        stat.setString(3, content.getXML());
                        stat.setString(4, DateConverter.getFormattedDate(new Date(), this.DATE_FORMAT));
                        stat.setString(5, content.getId());
                        stat.executeUpdate();
                } catch (Throwable t) {
                        processDaoException(t, "Errore in rimozione contenuto online", 
                                        "removeOnLineContent");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        /**
         * Cancella un contenuto nel database. L'operazione è irreversibile 
         * ed il contenuto non sarà più accessibile.
         * @param content Il contenuto da cancellare dal db.
         */
        public void deleteContent(Content content) {
                Connection conn = null;
                try {
                        conn = this.getConnection();
                        conn.setAutoCommit(false);
                        this.deleteContent(content, conn);
                        conn.commit();
                } catch (Throwable t) {
                        this.executeRollback(conn);
                        processDaoException(t, "Errore in cancellazione contenuto", "deleteContent");
                } finally {
                        this.closeConnection(conn);
                }
        }
        
        private void deleteContent(Content content, Connection conn) throws ApsSystemException {
                this.deleteEntitySearchRecord(content.getId(), conn);
                this.deleteContentRelationsRecord(content, conn);
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(DELETE_CONTENT);
                        stat.setString(1, content.getId());
                        stat.executeUpdate();
                } catch (Throwable t) {
                        processDaoException(t, "Errore in cancellazione contenuto", "deleteContent");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        /**
         * Metodo di servizio.
         * Aggiunge un record nella tabella contentrelations per ogni categoria del contenuto.
         */
        private void addCategoryRelationsRecord(Content content, PreparedStatement stat) 
                        throws ApsSystemException {
                if (content.getCategories().size()>0) {
                        try {
                                Set<String> codes = new HashSet<String>();
                                Iterator<Category> categoryIter = content.getCategories().iterator();
                                while (categoryIter.hasNext()) {
                                        Category category = (Category) categoryIter.next();
                                        this.addCategoryCode(category, codes);
                                }
                                Iterator<String> codeIter = codes.iterator();
                                while (codeIter.hasNext()) {
                                        String code = codeIter.next();
                                        stat.setString(1, content.getId());
                                        stat.setString(2, null);
                                        stat.setString(3, null);
                                        stat.setBigDecimal(4, null);
                                        stat.setString(5, code);
                                        stat.setString(6, null);
                                        stat.addBatch();
                                        stat.clearParameters();
                                }
                        } catch (SQLException e) {
                                processDaoException(e.getNextException(), "Errore in aggiunta record tabella contentrelations", "addCategoryRelationsRecord");
                        }
                }
        }
        
        private void addCategoryCode(Category category, Set<String> codes) {
                codes.add(category.getCode());
                Category parentCategory = (Category) category.getParent();
                if (null != parentCategory && !parentCategory.getCode().equals(parentCategory.getParentCode())) {
                        this.addCategoryCode(parentCategory, codes);
                }
        }
        
        private void addGroupRelationsRecord(Content content, 
                        PreparedStatement stat) throws ApsSystemException {
                try {
                        content.addGroup(content.getMainGroup());
                        Iterator<String> groupIter = content.getGroups().iterator();
                        while (groupIter.hasNext()) {
                                String groupName = groupIter.next();
                                stat.setString(1, content.getId());
                                stat.setString(2, null);
                                stat.setString(3, null);
                                stat.setBigDecimal(4, null);
                                stat.setString(5, null);
                                stat.setString(6, groupName);
                                stat.addBatch();
                                stat.clearParameters();
                        }
                } catch (Throwable e) {
                        processDaoException(e, "Errore in aggiunta record tabella contentrelations", 
                                        "addGroupRelationsRecord");
                }
        }
        
        /**
         * Aggiunge un record nella tabella contentrelations per ogni risorsa, pagina, altro contenuto, 
         * ruolo e categoria associato al contenuto.
         * @param content Il contenuto corrente
         * @param conn La connessione al db.
         * @throws ApsSystemException in caso di errore nell'accesso al db.
         */
        protected void addContentRelationsRecord(Content content, Connection conn) throws ApsSystemException{
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(ADD_CONTENT_REL_RECORD);
                        this.addCategoryRelationsRecord(content, stat);
                        this.addGroupRelationsRecord(content, stat);
                        EntityAttributeIterator attributeIter = new EntityAttributeIterator(content);
                        while (attributeIter.hasNext()) {
                                AttributeInterface currAttribute = (AttributeInterface) attributeIter.next();
                                if (currAttribute instanceof IReferenceableAttribute) {
                                        IReferenceableAttribute cmsAttribute = (IReferenceableAttribute) currAttribute;
                                        List<CmsAttributeReference> refs = cmsAttribute.getReferences();
                                        for (int i=0; i<refs.size(); i++) {
                                                CmsAttributeReference ref = refs.get(i);
                                                stat.setString(1, content.getId());
                                                stat.setString(2, ref.getRefPage());
                                                stat.setString(3, ref.getRefContent());
                                                stat.setString(4, ref.getRefResource());
                                                stat.setString(5, null);
                                                stat.setString(6, null);
                                                stat.addBatch();
                                                stat.clearParameters();
                                        }
                                }
                        }
                        stat.executeBatch();
                } catch (BatchUpdateException e) {
                        processDaoException(e.getNextException(), "Errore in aggiunta record tabella contentrelations", 
                                        "addContentRelationsRecord");
                } catch (Throwable t) {
                        processDaoException(t, "Errore in aggiunta record tabella contentrelations", 
                                        "addContentRelationsRecord");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        /**
         * Metodo di servizio.
         * Cancella i record della tabella contentrelations relativi ad un contenuto.
         */
        private void deleteContentRelationsRecord(Content content, Connection conn) throws ApsSystemException{
                PreparedStatement stat = null;
                try {
                        stat = conn.prepareStatement(DELETE_CONTENT_REL_RECORD);
                        stat.setString(1, content.getId());
                        stat.executeUpdate();
                } catch (Throwable t) {
                        processDaoException(t, "Errore in cancellazione record tabella contentrelations", 
                                        "deleteContentRelationsRecord");
                } finally {
                        closeDaoResources(null, stat);
                }
        }
        
        public List<String> getContentUtilizers(String contentId) {
                List<String> contentIds = null;
                try {
                        contentIds = this.getUtilizers(contentId, LOAD_REFERENCED_CONTENTS_FOR_CONTENT);
                } catch (Throwable t) {
                        processDaoException(t, "Errore in caricamento lista contenuti " +
                                        "referenziati con contenuto " + contentId, "getContentUtilizers");
                }
                return contentIds;
        }
        
        public List<String> getPageUtilizers(String pageCode) {
                List<String> contentIds = null;
                try {
                        contentIds = this.getUtilizers(pageCode, LOAD_REFERENCED_CONTENTS_FOR_PAGE);
                } catch (Throwable t) {
                        processDaoException(t, "Errore in caricamento lista contenuti referenziati pagina " + pageCode, 
                                        "getPageUtilizers");
                }
                return contentIds;
        }
        
        public List<String> getGroupUtilizers(String groupName) {
                List<String> contentIds = null;
                try {
                        contentIds = this.getUtilizers(groupName, LOAD_REFERENCED_CONTENTS_FOR_GROUP);
                } catch (Throwable t) {
                        processDaoException(t, "Errore in caricamento lista contenuti referenziati gruppo " + groupName, 
                                        "getGroupUtilizers");
                }
                return contentIds;
        }
        
        public List<String> getResourceUtilizers(String resourceId) {
                List<String> contentIds = null;
                try {
                        contentIds = this.getUtilizers(resourceId, LOAD_REFERENCED_CONTENTS_FOR_RESOURCE);
                } catch (Throwable t) {
                        processDaoException(t, "Errore in caricamento lista contenuti referenziati gruppo " + resourceId, 
                                        "getResourceUtilizers");
                }
                return contentIds;
        }
        
        public List<String> getCategoryUtilizers(String categoryCode) {
                List<String> contentIds = null;
                try {
                        contentIds = this.getUtilizers(categoryCode, LOAD_REFERENCED_CONTENTS_FOR_CATEGORY);
                } catch (Throwable t) {
                        processDaoException(t, "Errore in caricamento lista contenuti referenziati categoria " + categoryCode, 
                                        "getCategoryUtilizers");
                }
                return contentIds;
        }
        
        protected List<String> getUtilizers(String referencedObjectCode, String query) throws Throwable {
                Connection conn = null;
                List<String> contentIds = new ArrayList<String>();
                PreparedStatement stat = null;
                ResultSet res = null;
                try {
                        conn = this.getConnection();
                        stat = conn.prepareStatement(query);
                        stat.setString(1, referencedObjectCode);
                        res = stat.executeQuery();
                        while (res.next()) {
                                String id = res.getString(1);
                                contentIds.add(id);
                        }
                } catch (Throwable t) {
                        throw t;
                } finally {
                        closeDaoResources(res, stat, conn);
                }
                return contentIds;
        }
        
        public String getAddingSearchRecordQuery() {
                return ADD_CONTENT_SEARCH_RECORD;
        }
        
        public String getRemovingSearchRecordQuery() {
                return DELETE_CONTENT_SEARCH_RECORD;
        }
        
        public String getExtractingAllEntityIdQuery() {
                return LOAD_ALL_CONTENTS_ID;
        }
        
        protected final String DATE_FORMAT = "yyyyMMddHHmmss";
        
        private final String DELETE_CONTENT =
                "DELETE FROM contents WHERE contentid = ? ";
        
        private final String DELETE_CONTENT_REL_RECORD =
                "DELETE FROM contentrelations WHERE contentid = ? ";
        
        private final String DELETE_CONTENT_SEARCH_RECORD =
                "DELETE FROM contentsearch WHERE contentid = ? ";
        
        private final String ADD_CONTENT_REL_RECORD =
                "INSERT INTO contentrelations " +
                "(contentid, refpage, refcontent, refresource, refcategory, refgroup) " +
                "VALUES ( ? , ? , ? , ? , ? , ? )";
        
        private final String ADD_CONTENT_SEARCH_RECORD =
                "INSERT INTO contentsearch (contentid, attrname, textvalue, datevalue, numvalue, langcode) " +
                "VALUES ( ? , ? , ? , ? , ? , ? )";
        
        private final String LOAD_CONTENTS_VO_MAIN_BLOCK = 
                "SELECT DISTINCT contents.contentid, contents.contenttype, contents.descr, contents.status, " +
                "contents.workxml, contents.created, contents.lastmodified, contents.onlinexml, contents.maingroup " +
                "FROM contents ";
        
        private final String LOAD_CONTENTS_ID_MAIN_BLOCK = 
                "SELECT DISTINCT contents.contentid FROM contents ";
        
        private final String LOAD_REFERENCED_CONTENTS_FOR_PAGE = 
                LOAD_CONTENTS_ID_MAIN_BLOCK + 
                " RIGHT JOIN contentrelations ON contents.contentid = contentrelations.contentid WHERE refpage = ? " +
                "ORDER BY contents.contentid";
        
        private final String LOAD_REFERENCED_CONTENTS_FOR_CONTENT = 
                LOAD_CONTENTS_ID_MAIN_BLOCK + 
                " RIGHT JOIN contentrelations ON contents.contentid = contentrelations.contentid WHERE refcontent = ? " +
                "ORDER BY contents.contentid";
        
        private final String LOAD_REFERENCED_CONTENTS_FOR_GROUP = 
                LOAD_CONTENTS_ID_MAIN_BLOCK + 
                " RIGHT JOIN contentrelations ON contents.contentid = contentrelations.contentid WHERE refgroup = ? " +
                "ORDER BY contents.contentid";
        
        private final String LOAD_REFERENCED_CONTENTS_FOR_RESOURCE = 
                LOAD_CONTENTS_ID_MAIN_BLOCK + 
                " RIGHT JOIN contentrelations ON contents.contentid = contentrelations.contentid WHERE refresource = ? " +
                "ORDER BY contents.contentid";
        
        private final String LOAD_REFERENCED_CONTENTS_FOR_CATEGORY = 
                LOAD_CONTENTS_ID_MAIN_BLOCK + 
                " RIGHT JOIN contentrelations ON contents.contentid = contentrelations.contentid WHERE refcategory = ? " +
                "ORDER BY contents.contentid";
        
        private final String LOAD_CONTENT_VO = 
                LOAD_CONTENTS_VO_MAIN_BLOCK + " WHERE contents.contentid = ? ";
        
        private final String ADD_CONTENT =
                "INSERT INTO contents (contentid, contenttype, descr, status, " +
                "workxml, created, lastmodified, maingroup) " +
                "VALUES ( ? , ? , ? , ? , ? , ? , ? , ?)";
        
        private final String INSERT_ONLINE_CONTENT =
                "UPDATE contents SET contenttype = ? , descr = ? , status = ? , " +
                "workxml = ? , lastmodified = ? , onlinexml = ? , maingroup = ? " +
                "WHERE contentid = ? ";
        
        private final String REMOVE_ONLINE_CONTENT = 
                "UPDATE contents SET onlinexml = ? , status = ? , " +
                "workxml = ? , lastmodified = ? WHERE contentid = ? ";
        
        private final String UPDATE_CONTENT =
                "UPDATE contents SET contenttype = ? , descr = ? , status = ? , " +
                "workxml = ? , lastmodified = ? , maingroup = ? " +
                "WHERE contentid = ? ";
        
        private final String LOAD_ALL_CONTENTS_ID = 
                "SELECT contentid FROM contents";
        
}

<< Back