org.ednovo.gooru.infrastructure.persistence.hibernate.taxonomy.TaxonomyRepositoryHibernate.java Source code

Java tutorial

Introduction

Here is the source code for org.ednovo.gooru.infrastructure.persistence.hibernate.taxonomy.TaxonomyRepositoryHibernate.java

Source

/////////////////////////////////////////////////////////////
// TaxonomyRepositoryHibernate.java
// gooru-api
// Created by Gooru on 2014
// Copyright (c) 2014 Gooru. All rights reserved.
// http://www.goorulearning.org/
// Permission is hereby granted, free of charge, to any person obtaining
// a copy of this software and associated documentation files (the
// "Software"), to deal in the Software without restriction, including
// without limitation the rights to use, copy, modify, merge, publish,
// distribute, sublicense, and/or sell copies of the Software, and to
// permit persons to whom the Software is furnished to do so, subject to
// the following conditions:
// The above copyright notice and this permission notice shall be
// included in all copies or substantial portions of the Software.
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
/////////////////////////////////////////////////////////////
package org.ednovo.gooru.infrastructure.persistence.hibernate.taxonomy;

import java.io.File;
import java.io.OutputStreamWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.axis.utils.ByteArrayOutputStream;
import org.apache.commons.io.FileUtils;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.io.SAXReader;
import org.ednovo.gooru.application.util.ConfigProperties;
import org.ednovo.gooru.application.util.TaxonomyUtil;
import org.ednovo.gooru.core.api.model.Code;
import org.ednovo.gooru.core.api.model.CodeOrganizationAssoc;
import org.ednovo.gooru.core.api.model.CodeType;
import org.ednovo.gooru.core.api.model.CodeUserAssoc;
import org.ednovo.gooru.core.api.model.User;
import org.ednovo.gooru.core.api.model.UserGroupSupport;
import org.ednovo.gooru.core.constant.ConstantProperties;
import org.ednovo.gooru.core.constant.Constants;
import org.ednovo.gooru.core.constant.ParameterProperties;
import org.ednovo.gooru.domain.service.redis.RedisService;
import org.ednovo.gooru.infrastructure.persistence.hibernate.BaseRepositoryHibernate;
import org.ednovo.gooru.json.serializer.util.JsonSerializer;
import org.ednovo.goorucore.application.serializer.JsonDeserializer;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.StandardBasicTypes;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.fasterxml.jackson.core.type.TypeReference;

import flexjson.JSONSerializer;

@Repository
public class TaxonomyRepositoryHibernate extends BaseRepositoryHibernate
        implements TaxonomyRespository, ConstantProperties, ParameterProperties {

    @javax.annotation.Resource(name = "jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @javax.annotation.Resource(name = "jdbcTemplateReadOnly")
    private JdbcTemplate jdbcTemplateReadOnly;

    @Autowired
    private ConfigProperties configProperties;

    @Autowired
    private RedisService redisService;

    private static final String FIND_MAX_DEPTH = "Select max(depth) as depth  from taxonomy_level_type  where code_id = %s and  organization_uid in(%s)";
    private static final String FIND_TAXCODE_BY_DEPTH = "Select t.type_id from taxonomy_level_type t  where t.depth = %s and t.code_id =%s and t.organization_uid in(%s)";
    private static final String FIND_ALL_TAXONOMY = "SELECT t.depth, t.label, c.code, c.code_id FROM taxonomy_level_type t , code c where t.code_id=c.code_id and t.organization_uid in (%s) and c.active_flag=1";
    private static final String UPDATE_ORDER = "update code  set display_order = %s where code_id = %s and organization_uid in(%s)";
    private static final String FIND_CODE_BY_CODEIDS = "select * from code c where c.code_id = ? and c.active_flag = ?";
    private static final String FIND_TAXONOMY_ASSOC = "select c.code_id, c.code_uid, c.label, group_concat(p.display_code) as display_code from code c inner join  taxonomy_association ta  on  c.code_id = ta.target_code_id inner join code p on p.code_id = ta.source_code_id where c.parent_id=:parentId  group by ta.target_code_id order by  c.sequence";

    @Override
    public void updateOrder(Code code) {
        String updateQuery = format(UPDATE_ORDER, code.getDisplayOrder(), code.getCodeId(),
                getUserOrganizationUidsAsString());
        this.getJdbcTemplate().update(updateQuery);
    }

    @SuppressWarnings("rawtypes")
    public String makeTree(Code rootCode) {
        int depth = findMaxDepthInTaxonomy(rootCode, rootCode.getOrganization().getPartyUid());
        char[] alphas = new char[depth + 1];
        for (int c = 0; c <= depth; c++)
            alphas[c] = (char) (97 + c);

        Document doc = DocumentHelper.createDocument();

        Element root = doc.addElement("node"); // need to update

        root.addAttribute("code", rootCode.getCode());
        root.addAttribute("type", rootCode.getCodeType().getLabel());
        root.addAttribute("depth", "0");
        root.addAttribute("codeId", rootCode.getCodeId().toString());
        root.addAttribute("codeUId", rootCode.getCodeUid().toString());
        root.addAttribute("taxonomyImageUrl", rootCode.getTaxonomyImageUrl());
        root.addAttribute("displayCode", rootCode.getdisplayCode());
        root.addAttribute("activeFlag", rootCode.getActiveFlag() + "");

        String q1 = "select label from taxonomy_level_type where code_id=" + rootCode.getRootNodeId()
                + " order by depth";
        @SuppressWarnings("unchecked")
        List<String> labels = this.getJdbcTemplate().query(q1, new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getString("label");
            }
        });

        for (int i = 1; i <= depth; i++) {

            StringBuilder query = new StringBuilder("SELECT ");

            for (int j = 0; j <= i; j++) {
                query.append(alphas[j] + ".display_code, " + alphas[j] + ".code_image, " + alphas[j] + ".code_uid, "
                        + alphas[j] + ".code_id, " + alphas[j] + ".display_order, " + alphas[j] + ".depth, "
                        + alphas[j] + ".label as '" + labels.get(j) + "', " + alphas[j] + ".code as '"
                        + labels.get(j) + "' ");
                if (j != i) {
                    query.append(",");
                }
            }

            query.append(" from ");

            for (int j = 0; j <= i; j++) {
                query.append(" code " + alphas[j]);
                if (j != i) {
                    query.append(",");
                }
            }

            query.append(" where ");

            for (int j = 0; j <= i; j++) {

                query.append(alphas[j] + ".depth=" + j);
                // if(j!=i)
                query.append(" and  ");
            }

            for (int j = 0; j < i; j++) {
                query.append(alphas[j + 1] + ".parent_id=" + alphas[j] + ".code_id");
                // if(j!=i-1)
                query.append(" and " + alphas[j + 1] + ".active_flag=1 and ");
            }

            query.append(alphas[0] + ".code_id=" + rootCode.getCodeId());
            query.append(" and " + alphas[0] + ".active_flag=1");

            @SuppressWarnings("unchecked")
            List<String[]> labelss = this.getJdbcTemplateReadOnly().query(query.toString(), new RowMapper() {
                public Object mapRow(final ResultSet rs, final int rowNum) throws SQLException {

                    final ResultSetMetaData metaData = rs.getMetaData();
                    final int noofcolumns = metaData.getColumnCount();
                    final String label[] = new String[noofcolumns];

                    for (int m = 1; m <= noofcolumns; m++) {
                        label[m - 1] = rs.getString(m);
                    }

                    return label;
                }
            });

            @SuppressWarnings("unchecked")
            List<Node> nodes = doc.selectNodes("//node");

            for (String[] array : labelss) {
                int length = array.length;

                for (Node node : nodes) {
                    Element parent = (Element) node;
                    String att = parent.attribute("code").getText();
                    if (att.equals(array[length - 9])) {
                        // Element child = parent.addElement(labels.get(i));
                        Element child = parent.addElement("node");
                        child.addAttribute("type", labels.get(i));
                        child.addAttribute("code", array[length - 1]);
                        child.addAttribute("label", array[length - 2]);
                        child.addAttribute("depth", array[length - 3]);
                        child.addAttribute("order", array[length - 4]);
                        child.addAttribute("codeId", array[length - 5]);
                        child.addAttribute("codeUId", array[length - 6]);
                        String taxonomyImageUrl = array[length - 7] != null
                                ? rootCode.getAssetURI() + array[length - 7]
                                : "";
                        child.addAttribute("taxonomyImageUrl", taxonomyImageUrl);
                        String displayCode = array[length - 8];
                        if (displayCode != null)
                            child.addAttribute("displayCode", array[length - 8]);
                        else
                            child.addAttribute("displayCode", "");
                        child.addAttribute("activeFlag", rootCode.getActiveFlag() + "");

                    }
                }
            }

        }
        return doc.asXML();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Code> findRootTaxonomies(Short depth, String creatorUid) {
        String hql = "from Code c where c.depth = '" + depth + "' and c.activeFlag = 1 and "
                + generateOrgAuthQueryWithData("c.");
        if (creatorUid != null) {
            hql += " and code.creator.partyUid ='" + creatorUid + "'";
        }
        return getSession().createQuery(hql).list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public Code findCodeByTaxCode(String taxonomyCode) {
        List<Code> code = getSession()
                .createQuery("from Code c where c.code = '" + taxonomyCode + "'or c.codeId = '" + taxonomyCode
                        + "' or c.displayCode = '" + taxonomyCode + "' or c.commonCoreDotNotation = '"
                        + taxonomyCode + "' and c.activeFlag = 1 and " + generateOrgAuthQueryWithData("c."))
                .list();
        return code.isEmpty() ? null : code.get(0);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public int findMaxDepthInTaxonomy(Code code, String organizationUid) {

        String organizationUids = null;

        if (organizationUid != null) {
            organizationUids = "'" + organizationUid + "'";
        } else {
            organizationUids = getUserOrganizationUidsAsString();
        }

        String depthQuery = format(FIND_MAX_DEPTH, code.getRootNodeId(), organizationUids);

        Integer maxDepth;
        maxDepth = (Integer) this.getJdbcTemplateReadOnly().queryForObject(depthQuery, new RowMapper() {
            public Object mapRow(ResultSet rs, int robwNum) throws SQLException {

                int depth = (Integer) rs.getInt("depth");

                return depth;
            }
        });

        return maxDepth.intValue();
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Override
    public CodeType findTaxonomyTypeBydepth(Code code, Short depth) {
        String depthQuery = format(FIND_TAXCODE_BY_DEPTH, depth, code.getRootNodeId(),
                getUserOrganizationUidsAsString());

        CodeType maxDepth;
        maxDepth = (CodeType) this.getJdbcTemplateReadOnly().queryForObject(depthQuery, new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                CodeType codetype = new CodeType();
                int typeId = (Integer) rs.getInt("type_id");
                codetype.setTypeId(typeId);

                return codetype;
            }
        });

        return maxDepth;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CodeType> findTaxonomyLevels(Code root) {
        List<CodeType> codeTypes = getSession()
                .createQuery("from CodeType c where c.codeId = :codeId and " + generateOrgAuthQueryWithData("c."))
                .setParameter("codeId", root.getCodeId()).list();

        return codeTypes.isEmpty() ? null : codeTypes;
    }

    @Override
    public List<Code> findChildTaxonomyCode(Integer codeId) {

        ProjectionList proList = Projections.projectionList();
        proList.add(Projections.property("codeId"));
        proList.add(Projections.property("label"));
        Criteria criteria = getSession().createCriteria(Code.class)
                .add(Restrictions.in("organization.partyUid", getUserOrganizationUids()))
                .add(Restrictions.eq("parentId", codeId));
        List<Code> codeList = criteria(criteria);

        return codeList;
    }

    @Override
    public List<Code> findAll() {
        return list(getSession()
                .createQuery("from Code c where c.activeFlag =1 and " + generateOrgAuthQueryWithData("c.")));
    }

    @Override
    public List<Code> findChildTaxonomyCodeByOrder(Integer codeId, String order) {
        Integer activeFlag = 1;
        return list(getSession()
                .createQuery("from Code c where c.parentId = ? and c.displayOrder >= ? and c.activeFlag = ? and "
                        + generateOrgAuthQueryWithData("c."))
                .setParameter(0, codeId).setParameter(1, Integer.valueOf(order)).setParameter(2, activeFlag));
    }

    @Override
    public List<Code> findChildTaxonomyCodeByDepth(Integer codeId, Integer depth) {
        return list(getSession().createQuery("from Code c where c.parentId =" + codeId + " and c.depth =" + depth
                + " and c.activeFlag =1 and " + generateOrgAuthQueryWithData("c.") + " order by c.sequence"));
    }

    @Override
    public List<Code> findChildTaxonomy(String parentIds, Integer depth) {
        if (parentIds.contains(",")) {
            parentIds = parentIds.replace(",", "','");
        }
        return list(getSession().createQuery("from Code c where c.parentId in ('" + parentIds + "') and c.depth ="
                + depth + " and c.activeFlag =1 and " + generateOrgAuthQueryWithData("c.")
                + " order by c.sequence"));
    }

    @Override
    public List<Code> getCurriculumCodeByDepth(Integer depth) {
        return list(getSession()
                .createQuery("from Code c where c.depth =" + depth + " and " + generateOrgAuthQueryWithData("c.")));
    }

    @Override
    public List<Map<String, Object>> getCurriculum(List<Integer> codeIds) {
        String sql = "select code, label, code_id as codeId from code where depth = 0  and root_node_id != 20000";
        Query query = getSession().createSQLQuery(sql);
        //query.setParameterList(CODE_ID, codeIds);
        query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
        return list(query);
    }

    @Override
    public Code findFirstChildTaxonomyCodeByDepth(Integer codeId, Integer depth) {
        List<Code> code = list(getSession().createQuery("from Code c where c.parentId =" + codeId + " and c.depth ="
                + depth + " and c.displayOrder = 1 and c.activeFlag = 1 " + "and "
                + generateOrgAuthQueryWithData("c.")));
        return (code != null && code.size() > 0) ? code.get(0) : null;
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    @Override
    public List<CodeType> findAllTaxonomyLevels() {
        List<CodeType> annotations = this.getJdbcTemplateReadOnly()
                .query(format(FIND_ALL_TAXONOMY, getUserOrganizationUidsAsString()), new RowMapper() {

                    public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
                        CodeType annotation = new CodeType();
                        annotation.setDepth(rs.getShort("depth"));
                        annotation.setLabel(rs.getString("label"));
                        Code code = new Code();
                        code.setCode(rs.getString("code"));
                        code.setCodeId(rs.getInt("code_id"));
                        annotation.setCode(code);
                        return annotation;
                    }
                });

        return annotations;
    }

    @Override
    public List<Code> findCodeByType(Integer taxonomyLevel) {

        ProjectionList proList = Projections.projectionList();
        proList.add(Projections.property("codeId"));
        proList.add(Projections.property("label"));
        Criteria criteria = getSession().createCriteria(Code.class)
                .add(Restrictions.in("organization.partyUid", getUserOrganizationUids())).setProjection(proList)
                .add(Restrictions.eq("codeType.typeId", taxonomyLevel));
        List<Code> codeList = criteria(criteria);

        return codeList;
    }

    @Override
    public List<Code> getCodesOfConent(Long contentId) {
        Integer activeFlag = 1;
        List<Integer> codeIds = getJdbcTemplateReadOnly().queryForList(
                "select cc.code_id from content_classification cc inner join code c on cc.code_id=c.code_id  where cc.content_id=? and c.active_flag="
                        + activeFlag + " and c.organization_uid in (" + getUserOrganizationUidsAsString() + ")",
                new Object[] { contentId }, Integer.class);
        List<Code> list = new ArrayList<Code>();
        for (Integer codeId : codeIds)
            findParentTaxonomyCodes(codeId, list);
        return list;
    }

    @Override
    @Cacheable("gooruCache")
    public List<Code> findParentTaxonomy(Integer codeId, boolean reverse) {
        List<Code> codeList = new ArrayList<Code>();
        findParentTaxonomyCodes(codeId, codeList);
        Collections.reverse(codeList);
        return codeList;
    }

    @Override
    public List<Code> findParentTaxonomyCodes(Integer codeId, List<Code> codeList) {
        Code code = (Code) get(Code.class, codeId);
        if (code != null) {
            codeList.add(code);
        }

        if (code != null && code.getDepth() != 1 && code.getCodeType() != null
                && code.getCodeType().getLabel() != null
                && code.getCodeType().getLabel().equalsIgnoreCase(Constants.TWENTY_FIRST_CENTURY_SKILLS)) {
            codeList = findParentTaxonomyCodes(code.getParentId(), codeList);
        }

        return codeList;
    }

    @Override
    public List<Code> findSiblingTaxonomy(Code code) {
        Criteria criteria = getSession().createCriteria(Code.class)
                .add(Restrictions.eq("parentId", code.getParentId()))
                .add(Restrictions.eq("codeType.typeId", code.getCodeType().getTypeId()));
        Criteria criteria2 = addOrgAuthCriterias(criteria);
        List<Code> codeList = criteria(criteria2);

        return codeList;
    }

    @Override
    public void updateTaxonomyAssociation(Code taxonomy, List<Code> codes) {
        for (Code code : codes) {
            this.getJdbcTemplate().update("insert into taxonomy_association values(" + taxonomy.getCodeId() + ","
                    + code.getCodeId() + ")");
        }
    }

    @Override
    public void deleteTaxonomyMapping(Code taxonomy, List<Code> codes) {
        for (Code code : codes) {
            this.getJdbcTemplate().update(
                    "delete ta.* from taxonomy_association ta inner join code c on c.code_id=ta.target_code_id where source_code_id ="
                            + taxonomy.getCodeId() + " and target_code_id =" + code.getCodeId() + " and "
                            + generateOrgAuthSqlQueryWithData("c."));
        }
    }

    @Cacheable("gooruCache")
    @Override
    public List<Code> findTaxonomyMappings(String codeIds) {
        List<Code> codeList = new ArrayList<Code>();
        if (codeIds != null) {
            String[] codeId = codeIds.split(",");
            for (String id : codeId) {
                if (id != null && id.length() > 0) {
                    Code code = new Code();
                    code.setCodeId(Integer.parseInt(id));
                    codeList.add(code);
                }
            }
        }
        return findTaxonomyMappings(codeList, true);
    }

    @Override
    public List<Code> findTaxonomyMappings(List<Code> codeIdList, boolean excludeTaxonomyPreference) {

        // Please don not change - there is a reason to specify this query
        // string here rather than a static string....
        String findCurriculum = "SELECT distinct c.label,c.display_order, c.code,c.description, c.type_id, c.code_id, c.depth, c.root_node_id,c.display_code FROM code c inner join taxonomy_association t on c.code_id = t.target_code_id where  t.source_code_id in (";

        if (codeIdList.size() == 0) {
            return null;
        }
        Object[] a = new Object[codeIdList.size()];

        for (int i = 0; i < codeIdList.size(); i++) {

            if (i < (codeIdList.size() - 1)) {
                findCurriculum = findCurriculum + "?,";
            } else {
                findCurriculum = findCurriculum + "?)";
            }

            a[i] = codeIdList.get(i).getCodeId();
        }

        findCurriculum += " and active_flag =1 and " + generateOrgAuthSqlQueryWithData("c.");

        if (UserGroupSupport.getTaxonomyPreference() != null && !excludeTaxonomyPreference) {
            findCurriculum += " and c.root_node_id in (" + UserGroupSupport.getTaxonomyPreference() + ")";
        }

        List<Code> codeList = new ArrayList<Code>();
        List<Map<String, Object>> rows = getJdbcTemplateReadOnly().queryForList(findCurriculum, a);

        for (Map<?, ?> row : rows) {
            Code code = new Code();
            code.setCode((String) row.get("code"));
            code.setDescription((String) row.get("description"));
            code.setCodeId((Integer) row.get("code_id"));
            code.setDepth(Short.valueOf(row.get("depth").toString()));
            code.setLabel((String) row.get("label"));
            code.setDisplayOrder(new Integer(row.get("display_order").toString()));
            code.setRootNodeId((Integer) row.get("root_node_id"));
            code.setdisplayCode((String) row.get("display_code"));

            CodeType codeType = new CodeType();
            codeType.setTypeId((Integer) row.get("type_id"));
            code.setCodeType(codeType);
            List<Code> parentCodeList = new ArrayList<Code>();
            List<Code> parentList = findParentTaxonomyCodesByCodeId(code.getCodeId(), parentCodeList);

            code.setParentsList(parentList);
            codeList.add(code);
        }

        return codeList.size() == 0 ? null : codeList;
    }

    public List<Code> findParentTaxonomyCodesByCodeId(int codeId, List<Code> parentCodeList) {

        Code code = (Code) get(Code.class, codeId);
        if (code != null) {
            parentCodeList.add(code);
        }

        if (code != null && code.getDepth() != 1) {
            parentCodeList = findParentTaxonomyCodes(code.getParentId(), parentCodeList);
        }

        return parentCodeList;
    }

    @Override
    @Cacheable("gooruCache")
    public String findRootLevelTaxonomy(Code code) {

        int index = code.getDepth().intValue();

        List<String> alias = new ArrayList<String>();
        for (int cIndex = 0; cIndex <= index; cIndex++) {
            alias.add("c" + cIndex);
        }

        String query = "Select " + alias.get(index) + ".label from ";

        for (int i = 0; i <= index; i++) {
            if (i == index) {
                query += "code " + alias.get(i) + " where ";
            } else {
                query += "code " + alias.get(i) + ", ";
            }
        }

        for (int i = 0; i < index; i++) {
            if (i < index) {
                query += alias.get(i) + ".parent_id = " + alias.get(i + 1) + ".code_id and ";
            }
        }

        query += alias.get(0) + ".code = '" + code.getCode() + "'";
        query += " and " + alias.get(0) + ".active_flag =1 and "
                + generateOrgAuthSqlQueryWithData(alias.get(index) + ".");

        String label = (String) getJdbcTemplateReadOnly().queryForObject(query, String.class);

        return label;
    }

    @Override
    public void writeToDisk(Code root) throws Exception {
        String rootXml = makeTree(root);

        @SuppressWarnings("resource")
        final String encoding = new OutputStreamWriter(new ByteArrayOutputStream()).getEncoding();

        FileUtils.writeStringToFile(new File(configProperties.getTaxonomyRepositoryPath().get("taxonomy.repository")
                + "/" + root.getCodeId() + ".xml"), rootXml, encoding);

        TaxonomyUtil.updateClassplanLibrary(configProperties.getTaxonomyRepositoryPath().get("taxonomy.repository"),
                root.getCodeId());
        TaxonomyUtil.updateResourceLibrary(configProperties.getTaxonomyRepositoryPath().get("taxonomy.repository"),
                root.getCodeId());
        TaxonomyUtil.updateTaxonomyTree(configProperties.getTaxonomyRepositoryPath().get("taxonomy.repository"),
                root.getCodeId());
    }

    @Override
    public String findTaxonomyTree(String taxonomyCode, String format) throws Exception {

        Code code = (Code) get(Code.class, new Integer(taxonomyCode));// (taxonomyCode);

        CodeType codetype = (CodeType) super.get(CodeType.class, code.getCodeType().getTypeId());

        Document taxonomyXML = null;
        SAXReader reader = new SAXReader();

        taxonomyXML = reader.read(configProperties.getTaxonomyRepositoryPath().get("taxonomy.repository") + "/"
                + codetype.getCodeId() + ".xml");

        Node nodeTree = null;

        if (taxonomyXML.getRootElement().attribute("code").getText().equals(code.getCode())) {
            nodeTree = taxonomyXML.getRootElement();
        } else {
            @SuppressWarnings("unchecked")
            List<Node> nodes = taxonomyXML.selectNodes("//node");

            for (Node node : nodes) {
                Element parent = (Element) node;
                String att = parent.attribute("code").getText();

                if (att.equals(code.getCode())) {
                    nodeTree = node;
                    break;
                }
            }
        }

        if (format.equalsIgnoreCase("json")) {
            TaxonomyNode taxonomyNode = convertXmlToJson(nodeTree);
            JSONObject jsonObject = new JSONObject();
            String nodeJson = new JSONSerializer().exclude("*.class").deepSerialize(taxonomyNode)
                    .replace("\"node\":null,", "").replace("\"order\":null,", "")
                    .replace("\"taxonomyImageUrl\":null,", "").replace("\"label\":null,", "");
            jsonObject.put("node", new JSONObject(nodeJson));
            return jsonObject.toString();
        }

        return nodeTree.asXML();
    }

    public TaxonomyNode convertXmlToJson(Node node) {
        Element element = (Element) node;
        TaxonomyNode taxonomyNode = new TaxonomyNode();
        @SuppressWarnings("unchecked")
        List<Node> nodes = element.elements("node");
        taxonomyNode.setCode(element.attributeValue("code"));
        taxonomyNode.setCodeId(
                element.attributeValue("codeId") != null ? Integer.parseInt(element.attributeValue("codeId"))
                        : null);
        taxonomyNode.setCodeUId(element.attributeValue("codeUId"));
        taxonomyNode.setTaxonomyImageUrl(element.attributeValue("taxonomyImageUrl"));
        taxonomyNode.setOrder(
                element.attributeValue("order") != null ? Integer.parseInt(element.attributeValue("order")) : null);
        taxonomyNode.setLabel(element.attributeValue("label"));
        taxonomyNode.setDepth(
                element.attributeValue("depth") != null ? Integer.parseInt(element.attributeValue("depth")) : null);
        taxonomyNode.setType(element.attributeValue("type"));
        taxonomyNode.setdisplayCode(element.attributeValue("displayCode"));
        if (nodes != null && nodes.size() > 0) {
            taxonomyNode.setNode(new ArrayList<TaxonomyNode>());
            for (Node nodeEmement : nodes) {
                taxonomyNode.getNode().add(convertXmlToJson((Element) nodeEmement));
            }
        }
        return taxonomyNode;
    }

    @SuppressWarnings("rawtypes")
    public void updateOrders() {
        String query = "SELECT code_id, code FROM code where code_id between 10000 and 11000 and active_flag =1 and "
                + generateOrgAuthSqlQueryWithData() + " order by code_id";
        @SuppressWarnings("unchecked")
        List<String[]> codes = this.getJdbcTemplate().query(query.toString(), new RowMapper() {
            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

                ResultSetMetaData metaData = rs.getMetaData();
                int noofcolumns = metaData.getColumnCount();
                String label[] = new String[noofcolumns];

                label[0] = rs.getString(1);

                String[] code = rs.getString(2).split("\\.");
                for (int k = code.length; k >= 0; k--) {
                    if (!code[k - 1].equals("00")) {
                        label[1] = code[k - 1];
                        if (code[k - 1].equals("A")) {
                            label[1] = "1";
                        } else if (code[k - 1].equals("B")) {
                            label[1] = "2";
                        } else if (code[k - 1].equals("C")) {
                            label[1] = "3";
                        } else if (code[k - 1].equals("D")) {
                            label[1] = "4";
                        } else if (code[k - 1].equals("E")) {
                            label[1] = "5";
                        } else if (code[k - 1].equals("F")) {
                            label[1] = "6";
                        } else if (code[k - 1].equals("G")) {
                            label[1] = "7";
                        } else if (code[k - 1].equals("H")) {
                            label[1] = "8";
                        } else if (code[k - 1].equals("I")) {
                            label[1] = "9";
                        } else if (code[k - 1].equals("J")) {
                            label[1] = "10";
                        }
                        break;
                    }
                }
                return label;
            }
        });

        for (String[] code : codes) {
            try {
                this.getJdbcTemplate().update("update code set display_order= " + code[1] + " where code_id = "
                        + code[0] + "and " + generateOrgAuthSqlQueryWithData());
            } catch (Exception e) {
            }

        }
    }

    @Override
    public Code findByLabel(String label) {
        Integer activeFlag = 1;
        @SuppressWarnings("unchecked")
        List<Code> cc = getSession()
                .createQuery(
                        "from Code c where c.label = ? and c.activeFlag = ?  " + generateOrgAuthQueryWithData("c."))
                .setString(0, label).setInteger(1, activeFlag).list();
        return cc.size() == 0 ? null : cc.get(0);
    }

    @Override
    @Cacheable("gooruCache")
    public Code findByParent(String label, Integer parentId) {
        Criteria criteria = getSession().createCriteria(Code.class);
        criteria.add(Restrictions.eq("label", label)).add(Restrictions.eq("parentId", parentId));
        Criteria criteria2 = addOrgAuthCriterias(criteria);
        List<Code> cc = criteria(criteria2);
        return cc.size() == 0 ? null : cc.get(0);
    }

    @Override
    public List<Code> findAllByRoot(Integer codeId) {
        Integer activeFlag = 1;
        List<Code> codeList = list(
                getSession()
                        .createQuery("from Code c where c.rootNodeId = ? and c.activeFlag = ? and "
                                + generateOrgAuthQueryWithData("c."))
                        .setInteger(0, codeId).setInteger(1, activeFlag));
        return codeList;
    }

    @Override
    public Code findCodeByCodeId(Integer codeId) {
        List<Code> cc = list(getSession().createQuery(
                "from Code c where c.codeId = ? and c.activeFlag = ?  and " + generateOrgAuthQueryWithData("c."))
                .setInteger(0, codeId).setInteger(1, 1));
        return cc.size() == 0 ? null : cc.get(0);
    }

    @Override
    public Code findCodeByCodeUId(String codeUId) {
        Integer activeFlag = 1;
        @SuppressWarnings("unchecked")
        List<Code> cc = getSession()
                .createQuery("from Code c where c.codeUid = ? and c.activeFlag = ?   and "
                        + generateOrgAuthQueryWithData("c."))
                .setString(0, codeUId).setInteger(1, activeFlag).list();
        return cc.size() == 0 ? null : cc.get(0);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Code> listTaxonomy(Map<String, String> filters) {
        int pageSize = Integer.parseInt(filters.get("pageSize"));
        int pageNum = Integer.parseInt(filters.get("pageNum"));
        String query = "FROM Code code WHERE 1=1 ";
        if (filters.containsKey("codeIds")) {
            query += " AND code.codeId IN (" + filters.get("codeIds") + ") ";
        }
        if (filters.containsKey("type")) {
            query += " AND code.codeType.label in (" + filters.get("type") + ") ";
        }
        if (filters.containsKey("rootNodeId")) {
            query += " AND code.rootNodeId = " + filters.get("rootNodeId");
        }
        query += "AND code.activeFlag =1 AND " + generateOrgAuthQueryWithData("code.");
        return getSession().createQuery(query).setMaxResults(pageSize).setFirstResult((pageNum - 1) * pageSize)
                .list();
    }

    @Override
    public List<Map<String, String>> findAllMappedStandards(String code, Map<String, String> filters) {

        String standardsCacheKey = "search-standards:" + code;
        List<Map<String, String>> standards = getRedisService().getValue(standardsCacheKey) != null
                ? JsonDeserializer.deserialize(getRedisService().getValue(standardsCacheKey),
                        new TypeReference<List<Map<String, String>>>() {
                        })
                : null;

        if (standards != null && (filters != null && !Boolean.parseBoolean(filters.get("skipCache")))) {
            return standards;
        }
        String sql = "SELECT distinct c.code,c.description FROM code c inner join taxonomy_association t on c.code_id = t.target_code_id  where c.code like '%"
                + code + "%' and c.active_flag=1 and " + generateOrgAuthSqlQueryWithData("c.");
        int pageNum = 1;
        int pageSize = 10;
        if (filters != null && filters.containsKey("pageNum")) {
            pageNum = Integer.parseInt(filters.get("pageNum"));
        }
        if (filters != null && filters.containsKey("pageSize")) {
            pageSize = Integer.parseInt(filters.get("pageSize"));
        }
        sql += " limit " + (pageNum - 1) + "," + (pageNum) * pageSize;

        List<Map<String, Object>> rows = getJdbcTemplate().queryForList(sql);
        standards = new ArrayList<Map<String, String>>();
        for (Map<?, ?> row : rows) {
            Map<String, String> standard = new HashMap<String, String>();
            standard.put("code", (String) row.get("code"));
            standard.put("description", (String) row.get("description"));
            standards.add(standard);
        }
        getRedisService().putValue(standardsCacheKey, JsonSerializer.serializeToJson(standards, true), 432000);
        return standards;
    }

    @Override
    public List<Integer> findSourceCodeByTargetCode(Integer targetCodeId) {
        String sql = "select source_code_id from taxonomy_association where target_code_id=:targetCodeId";
        Query query = getSession().createSQLQuery(sql).setParameter("targetCodeId", targetCodeId);
        @SuppressWarnings("unchecked")
        List<Integer> sourceCodeIds = query.list();
        return sourceCodeIds;
    }

    @Override
    @Cacheable("gooruCache")
    public Code findCode(Integer codeId, String organizationUid) {
        Integer activeFlag = 1;
        String hql = " From Code code WHERE code.codeId=:codeId AND code.organization.partyUid = :partyUid  AND code.activeFlag = :activeFlag";
        Query query = getSession().createQuery(hql);
        query.setParameter("codeId", codeId);
        query.setParameter("partyUid", organizationUid);
        query.setParameter("activeFlag", activeFlag);
        return (Code) query.uniqueResult();
    }

    /*
     * (non-Javadoc)
     * 
     * @see org.ednovo.gooru.domain.model.taxonomy.TaxonomyRespository#
     * getCodeIdByContentId(java.lang.String)
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Integer> getCodeIdByContentIds(String contentIds) {
        String sql = "select cc.code_id from content_classification cc WHERE cc.content.content_id IN ("
                + contentIds + ")";
        SQLQuery query = getSession().createSQLQuery(sql);
        return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Code> getCodeByContentIds(String contentIds) {
        String hql = "select c.code from ContentClassification c WHERE c.content.contentId IN (" + contentIds + ")";
        Query query = getSession().createQuery(hql);
        return (List<Code>) query.list();
    }

    @Override
    @Cacheable("gooruCache")
    public String getFindTaxonomyList(String excludeCode) {
        String hql = "select group_concat(code_id) as codes from code where depth = 0 and organization_uid  IN ("
                + getUserOrganizationUidsAsString() + ") and code_id not in (" + excludeCode + ")";
        Query query = getSession().createSQLQuery(hql).addScalar("codes", StandardBasicTypes.STRING);
        return (String) query.list().get(0);
    }

    @Cacheable("gooruCache")
    @Override
    public String getFindTaxonomyCodeList(String codeIds) {
        String hql = "select group_concat(code) as codes from code where depth = 0 and organization_uid  IN ("
                + getUserOrganizationUidsAsString() + ") and code_id  in (" + codeIds + ")";
        Query query = getSession().createSQLQuery(hql).addScalar("codes", StandardBasicTypes.STRING);
        return (String) query.list().get(0);
    }

    @Cacheable("gooruCache")
    @Override
    public String findTaxonomyRootCode(String code) {
        String hql = "select root_node_id from code where organization_uid  IN ("
                + getUserOrganizationUidsAsString() + ") and code=:code or display_code=:displayCode";
        Query query = getSession().createSQLQuery(hql).addScalar("root_node_id", StandardBasicTypes.STRING);
        query.setParameter("code", code);
        query.setParameter("displayCode", code);
        return (String) (query.list().size() > 0 ? query.list().get(0) : null);
    }

    @Override
    public Code findTaxonomyCodeById(Integer codeId) {
        String hql = "From Code code where code.codeId =:codeId and code.activeFlag=1 and "
                + generateOrgAuthQueryWithData("code.");
        Query query = getSession().createQuery(hql);
        query.setParameter("codeId", codeId);
        return (Code) query.list().get(0);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CodeOrganizationAssoc> findCodeByParentCodeId(String code, String creatorUid, Integer limit,
            Integer offset, String fetchType, String organizationCode, String rootNodeId, String depth) {
        String hql = " From CodeOrganizationAssoc  codeOrganizationAssoc  where  codeOrganizationAssoc.code.activeFlag=1  ";

        if (rootNodeId != null) {
            hql += " and  codeOrganizationAssoc.code.rootNodeId=:rootNodeId";
        }
        if (code != null) {
            if (code.equalsIgnoreCase("featured")) {
                hql += " and codeOrganizationAssoc.isFeatured >= 1 ";
            } else {
                hql += " and codeOrganizationAssoc.code.parentId =:parentCodeId  ";
            }
        }

        if (depth != null) {
            hql += " and  codeOrganizationAssoc.code.depth=:depth";
        }

        if (fetchType != null && fetchType.equalsIgnoreCase("library")) {
            hql += " and codeOrganizationAssoc.code.libraryFlag = 1 ";
        }

        hql += " and codeOrganizationAssoc.organizationCode =:organizationCode ";

        if (creatorUid != null) {
            hql += " and codeOrganizationAssoc.code.creator.partyUid =:creatorUid";
        }

        if (code != null && code.equalsIgnoreCase("featured")) {
            hql += " order by codeOrganizationAssoc.isFeatured";
        } else {
            hql += " order by codeOrganizationAssoc.sequence";
        }

        Query query = getSession().createQuery(hql);
        query.setParameter("organizationCode", organizationCode);
        if (code != null && !code.equalsIgnoreCase("featured")) {
            query.setParameter("parentCodeId", Integer.parseInt(code));
        }
        if (rootNodeId != null) {
            query.setParameter("rootNodeId", Integer.parseInt(rootNodeId));
        }

        if (depth != null) {
            query.setParameter("depth", Short.parseShort(depth));
        }
        if (creatorUid != null) {
            query.setParameter("creatorUid", creatorUid);
        }
        query.setFirstResult(offset == null ? OFFSET : offset);
        query.setMaxResults(limit != null ? (limit > MAX_LIMIT ? MAX_LIMIT : limit) : LIMIT);

        return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getFeaturedUser(String organizationCode) {
        String hql = "Select distinct(codeUserAssoc.user) From CodeUserAssoc codeUserAssoc where  codeUserAssoc.organizationCode=:organizationCode";
        Query query = getSession().createQuery(hql);
        query.setParameter("organizationCode", organizationCode);
        return query.list();
    }

    @Override
    public List<Code> findCodeByMappedLevel(Long contentId) {
        String hql = "SELECT cc.code from  ContentClassification cc where cc.content.contentId=:contentId";
        Query query = getSession().createQuery(hql);
        query.setParameter("contentId", contentId);
        return list(query);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<CodeUserAssoc> getUserCodeAssoc(Integer codeId, String organizationCode) {
        String hql = "From CodeUserAssoc codeUserAssoc where codeUserAssoc.code.codeId=:codeId and codeUserAssoc.organizationCode=:organizationCode";
        Query query = getSession().createQuery(hql);
        query.setParameter("codeId", codeId);
        query.setParameter("organizationCode", organizationCode);
        return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Code> getCodeByDepth(String organizationCode, Short depth, String creatorUid) {
        String hql = "Select codeUserAssoc.code From CodeUserAssoc codeUserAssoc where codeUserAssoc.code.depth=:depth and codeUserAssoc.organizationCode=:organizationCode and codeUserAssoc.user.partyUid =:creatorUid";
        Query query = getSession().createQuery(hql);
        query.setParameter("depth", depth);
        query.setParameter("organizationCode", organizationCode);
        query.setParameter("creatorUid", creatorUid);
        return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Object[]> getCollectionStandards(Integer codeId, String text, Integer limit, Integer offset) {
        String sql = "select distinct ifnull(c.common_core_dot_notation, c.display_code) as code_notation, c.code_id, c.label,c.code_uid, c.root_node_id from taxonomy_association ta inner join code c on ta.target_code_id = c.code_id  where ifnull(c.common_core_dot_notation, c.display_code) like '"
                + text + "%' and depth != 0";
        if (codeId != null) {
            sql += " and ta.source_code_id =" + codeId;
        }
        if (UserGroupSupport.getTaxonomyPreference() != null
                && !UserGroupSupport.getTaxonomyPreference().isEmpty()) {
            sql += " and c.root_node_id in (" + UserGroupSupport.getTaxonomyPreference() + ")";
        }
        Query query = getSession().createSQLQuery(sql);
        query.setFirstResult(offset == null ? OFFSET : offset);
        query.setMaxResults(limit != null ? (limit > MAX_LIMIT ? MAX_LIMIT : limit) : LIMIT);
        return query.list();
    }

    @Override
    public List<Code> findParentTaxonomyCodeLevels(Integer codeId, List<Code> codeList) {
        Code code = getTaxonomyCodeById(codeId);
        if (code != null) {
            Code parentCode = new Code();
            parentCode.setActiveFlag(code.getActiveFlag());
            parentCode.setAssetURI(code.getAssetURI());
            parentCode.setCode(code.getCode());
            parentCode.setCodeId(code.getCodeId());
            parentCode.setCodeImage(code.getCodeImage());
            parentCode.setCodeUid(code.getCodeUid());
            parentCode.setDepth(code.getDepth());
            parentCode.setDescription(code.getDescription());
            parentCode.setdisplayCode(code.getdisplayCode());
            parentCode.setDisplayOrder(code.getDisplayOrder());
            parentCode.setGrade(code.getGrade());
            parentCode.setLabel(code.getLabel());
            parentCode.setLibraryFlag(code.getLibraryFlag());
            parentCode.setParentId(code.getParentId());
            parentCode.setRootNodeId(code.getRootNodeId());
            parentCode.setS3UploadFlag(code.getS3UploadFlag());
            parentCode.setTaxonomyImageUrl(code.getTaxonomyImageUrl());
            codeList.add(parentCode);
        }

        if (code != null && code.getDepth() != 1) {
            codeList = findParentTaxonomyCodeLevels(code.getParentId(), codeList);
        }

        return codeList;
    }

    private Code getTaxonomyCodeById(Integer codeId) {
        String hql = "From Code code where code.codeId =:codeId  and " + generateOrgAuthQueryWithData("code.");
        Query query = getSession().createQuery(hql);
        query.setParameter("codeId", codeId);
        return query.list().size() > 0 ? (Code) query.list().get(0) : null;
    }

    @Cacheable("gooruCache")
    @Override
    public String findTaxonomyCodeLabels(String codeIds) {
        String sql = "select group_concat(label) as labels from code where depth = 0 and organization_uid  IN ("
                + getUserOrganizationUidsAsString() + ") and code_id  in (" + codeIds + ")";
        Query query = getSession().createSQLQuery(sql).addScalar("labels", StandardBasicTypes.STRING);
        return query.list() != null ? (String) query.list().get(0) : null;
    }

    @SuppressWarnings("unchecked")
    @Cacheable("gooruCache")
    @Override
    public List<Code> findCodeCommonCoreNotation() {
        String hql = "From Code code where code.organization.partyUid is not null and code.activeFlag is not null and code.commonCoreDotNotation is not null  and "
                + generateOrgAuthQueryWithData("code.");
        Query query = getSession().createQuery(hql);
        return query.list();
    }

    @Cacheable("gooruCache")
    @Override
    public String findGooruTaxonomyCourse(List<String> codeIds) {
        String sql = "select group_concat(label) as labels from code where root_node_id=20000 and organization_uid  IN ("
                + getUserOrganizationUidsAsString() + ") and code_id IN (:codeIds) ";
        Query query = getSession().createSQLQuery(sql).addScalar("labels", StandardBasicTypes.STRING);
        query.setParameterList("codeIds", codeIds);
        return query.list() != null ? (String) query.list().get(0) : null;
    }

    @Override
    @Cacheable("gooruCache")
    public Code findCodeByCodeIds(Integer codeId) {
        Integer activeFlag = 1;
        Query query = getSession().createSQLQuery(FIND_CODE_BY_CODEIDS).addEntity(Code.class);
        query.setInteger(0, codeId).setInteger(1, activeFlag).list();
        return query.list().size() > 0 ? (Code) query.list().get(0) : null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<Code> findCodeStartWith(String codeStartWith, Short depth) {
        String hql = "FROM Code c where code LIKE :code AND depth =:depth AND c.activeFlag = 1 and "
                + generateOrgAuthQueryWithData("c.") + " order by c.sequence";
        Query query = getSession().createQuery(hql);
        query.setParameter("code", codeStartWith + "%");
        query.setParameter("depth", depth);
        return query.list();
    }

    @Override
    public List<Object[]> getTaxonomySkills(Integer parentId) {
        Query query = getSession().createSQLQuery(FIND_TAXONOMY_ASSOC);
        query.setParameter("parentId", parentId);
        return arrayList(query);
    }

    public JdbcTemplate getJdbcTemplateReadOnly() {
        return jdbcTemplateReadOnly;
    }

    public RedisService getRedisService() {
        return redisService;
    }

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

}