<?php
require_once 'modules/admin/models/ImportPlugin.php';
require_once 'modules/knowledgebase/models/KB_Article.php';

class PluginSupportsuite_kb extends ImportPlugin
{
    var $name = /*T*/'SupportSuite Knowledgebase'/*/T*/;
    var $description = /*T*/"This import plugin imports categories and articles from a SupportSuite knowledgebase."/*/T*/;
    
    var $DB;
    var $categories;    
    
    function getForm()
    {
        $this->tpl->define(array('PluginSupportsuitekb' => '../../plugins/import/supportsuite_kb/PluginSupportsuitekb.tpl'));
        return $this->tpl->subst('PluginSupportsuitekb_out', 'PluginSupportsuitekb');        
    }
    
    function process($post)
    {
        $port = ($post['port'])?$post['port']:'3306';
        $this->_connect($post['server_ip'].':'.$port, $post['username'], $post['password'], $post['database_name']);
        $this->importCategories();
        $this->changeParentIdOnImportedCategories();
        $this->_disconnect();
        NE_Lib::redirectPage('index.php?fuse=admin&view=ViewImportPlugins');
    }

    function _connect($HostName, $DBuser, $DBpass, $DataBase)
    {
        $this->DB = mysql_connect($HostName, $DBuser, $DBpass);
        mysql_select_db($DataBase, $this->DB);
    }

    function _disconnect()
    {
        mysql_close($this->DB);
    }
    
    function importCategories()
    {
        $query = "SELECT kbcategoryid, title, parentcategoryid, totalarticles FROM swkbcategories";
        $result = mysql_query($query, $this->DB);
        $sql = "INSERT INTO kb_categories (name, parent_id, counter) VALUES (?, ?, ?)";
        while ($row = mysql_fetch_row($result)){
            if ($row[2] == 0){
                $row[2] = -1;
            }
            NE_Lib::addMessage($this->user->lang('category %s has been successfully imported',$row[1]));
            $category = $this->db->query($sql, $row[1], $row[2], $row[3]);
            $this->categories[] = array('oldId'     => $row[0],
                                        'newId'     => $category->getInsertId(),
                                        'parent'    => $row[2],
                                  );
            $this->importArticlesVsCategories($row[0], $category->getInsertId());
        }
        $category = $this->db->query($sql, 'knowledgebase', -1, 0);
        $this->importArticlesVsCategories(0, $category->getInsertId());        
    }
    
    function importArticles($OldArticleId)
    {
        $query = "SELECT kbart.kbarticleid as id, kbart.subject as title, swkbarticledata.contents, kbart.dateline as created, kbart.editeddateline as modified, "; 
        $query.= "kbart.articlestatus, kbart.articlerating as rating, kbart.ratinghits as ratingvisitors, kbart.views as totalvisitors, ";
        $query.= "kbart.staffid, swstaff.fullname as author FROM swkbarticles as kbart ";
        $query.= "INNER JOIN swkbarticledata ON swkbarticledata.kbarticleid = kbart.kbarticleid ";
        $query.= "INNER JOIN swstaff ON swstaff.staffid = kbart.staffid WHERE kbart.kbarticleid =".$OldArticleId;
        $result = mysql_query($query, $this->DB);
        $sql = "INSERT INTO kb_articles (title, excerpt, content, author, publisher, is_draft, access, ";
        $sql.= "created, modified, modified_user_id, rating, ratingvisitors, totalvisitors) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        while ($row = mysql_fetch_row($result)){
           switch ($row[5]){
               case 'published':
                    $is_Draft = 0;
                    $access = KB_ARTICLE_ACCESS_PUBLIC;
                    break;
               case 'draft':
                    $is_Draft = 1;
                    $access = KB_ARTICLE_ACCESS_PUBLIC;
                    break;
               case 'private':
                    $is_Draft = 0;
                    $access = KB_ARTICLE_ACCESS_STAFF;           
                    break;
           }
           if ($row[4] == 0){
               $row[4] = $row[3];
           }           
           NE_Lib::addMessage($this->user->lang('article %s has been successfully imported',$row[1])); 
           $row[2] = nl2br($row[2]);
           $row[2] = preg_replace("/(\r\n|\n|\r)/", "\n", $row[2]);
           $article = $this->db->query($sql, $row[1],
                                       '', 
                                       $row[2], 
                                       $row[10], 
                                       $this->user->getId(), 
                                       $is_Draft, 
                                       $is_Internal, 
                                       date('Y-m-d H:i:s', $row[3]), 
                                       date('Y-m-d H:i:s',$row[4]),
                                       $this->user->getId(),
                                       $row[6],
                                       $row[7],
                                       $row[8]
                                );
        }
        return ($article->getInsertId());        
    }
    
    function importArticlesVsCategories($OldCategoryId, $NewCategoryId)
    {
        $query = "SELECT kbcategoryid, kbarticleid FROM swkbarticlelinks WHERE kbcategoryid =".$OldCategoryId;        
        $result = mysql_query($query, $this->DB);
        $sql = "INSERT INTO kb_articles_categories (articleid, categoryid) VALUES (?, ?)";
        while ($row = mysql_fetch_row($result)){
            $NewArticleId = $this->importArticles($row[1]);
            $this->db->query($sql, $NewArticleId, $NewCategoryId);
        }
        if ($OldCategoryId == 0){    
            $this->db->query("UPDATE kb_categories SET COUNTER = ? WHERE id = ?", mysql_num_rows($result), $NewCategoryId);
        }            
    }
    
    function changeParentIdOnImportedCategories()
    {
        $query = "UPDATE kb_categories SET parent_id = ? WHERE id= ? AND parent_id = ?";
        for ($i = 0; $i < count($this->categories); $i++){
            if ($this->categories[$i]['parent'] != -1){
                $this->db->query($query, $this->getNewParentId($this->categories[$i]['parent']), $this->categories[$i]['newId'], $this->categories[$i]['parent']);
            }
        }    
    }
    
    function getNewParentId($Old)
    {        
        for ($j = 0; $j < count($this->categories); $j++){
            if ($this->categories[$j]['oldId'] == $Old){
                return ($this->categories[$j]['newId']);
            }
        }
    }
    
}
?>
