<?php
    
    class KDB {
        
        function __construct() {
            $this->connect();
        }
        
        function connect() {
            $this->link = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD, true);
            mysql_set_charset("UTF8");
            
            if (!$this->link) {
                die('Could not connect: ' . mysql_error());
            }
            mysql_select_db(DB_NAME, $this->link);
        }

        function disconnect() {
            mysql_close();
        }

        function query($query)
        {
            //print $query;
            $ret = mysql_query($query, $this->link) or print(mysql_error());
            return $ret;
        }

        function getPages() {
            $ret = $this->query("SELECT * FROM pages");
            $objs = array();
            while ( $obj = mysql_fetch_array($ret) ) {
                array_push($objs, $obj);
            }
            return $objs;
        }

        function getTasks() {
            $ret = $this->query("SELECT * FROM types WHERE task=1");
            $objs = array();
            while ( $obj = mysql_fetch_array($ret) ) {
                array_push($objs, $obj);
            }
            $objs_trans = array();
            foreach ($objs as $ob) {
                $ob['title'] = gettext($ob['title']);
                $objs_trans []= $ob;
            }
            $sorter = new array_sorter($objs_trans, 'title', 1);
            return $sorter->sortit();
        }

        function getType($type) {
            $ret = $this->query("SELECT * FROM types where typeIdent='".$type."'");
            return mysql_fetch_array($ret);
        }

        function getMenuElements($menu, $grp) {
            $sql = "SELECT * FROM menus WHERE grp='".$grp."' AND menu='".$menu."'";
            $ret = $this->query($sql);
            $objs = array();
            while ( $obj = mysql_fetch_array($ret) ) {
                array_push($objs, $obj);
            }
            return $objs;
        }

        function getResourceTypeByRID($rid) {
            if ($rid != "new") {
                $ret = $this->query("SELECT type FROM resources WHERE resourceID=".$rid);
                $row = mysql_fetch_array($ret);
                return $row[0];
            }
            return false;
        }

        function getCommonData($rid) {
            $ret = $this->query("SELECT * FROM resources WHERE resourceID=".$rid);
            $row = mysql_fetch_array($ret);
            return $row;
        }

        function getTypeData($rid, $table) {
            $ret = $this->query("SELECT * FROM ".$table." WHERE resourceID=".$rid);
            $row = mysql_fetch_array($ret);
            return $row;
        }
        
       /*
        * RESOURCES
        * */
        
    function paginate($count, $limit, $offset) {
        global $krihvel;
        $url = $_SERVER['REQUEST_URI'];
        $up = strpos($url, "page=");
        if ($up !== false) {
            $url = substr($url, 0, $up-1);
        }
        $pnr = 0;
        if (isset($_GET['page']) && is_numeric($_GET['page'])) $pnr = $_GET['page'];
        $pagination = new PHPTAL();
        $pagination->setTemplateRepository('skins/'.SKIN.'/templates/');
        $pagination->setTemplate("pagination.html");
        $pagination->setTranslator($krihvel->getTranslator());
        $pagination->url = $url;
        $pagination->count = $count;
        $pagination->page = $pnr;
        $pagination->limit = $limit;
        $pagination->offset = $offset;
        $krihvel->out("pagination", $pagination->execute(), true);
    }
        
        private function getResourcesSQL($sql) {
            //print $sql;
            $ret = $this->query($sql);
            $objs = array();
            $num_rows = mysql_num_rows($ret);
            $i = 0;
            while ( $row = mysql_fetch_array($ret) ) {
                $classname = $row['typeID'];
                _load_file($row['pluginID'], $classname);
                mysql_data_seek($ret, $i);
                $obj = mysql_fetch_object($ret, $classname, array($row['resourceID']));
                array_push($objs, $obj);
                $i++;
            }
            return $objs;
        }
        
        function getResourcesByType($typ, $opts=array(), $allow_deleted=false) {
            $params = "";
            if ($typ!="all") {
                $params .= " AND types.typeIdent='".$typ."'";
            }
            foreach ($opts as $op => $val ) {
                $params .= " AND ".$op."=".$val;
            }
            $dele = " AND deleted=0";
            if ($allow_deleted) {
                $dele = "";
            }
            return $this->getResourcesSQL("SELECT * FROM resources JOIN types WHERE typeIdent=type".$dele.$params);
        }
        
        function getResourcesByTag($tag) {
            $keywords = "";
            if ($tag && strcasecmp("_all_", $tag)) {
                $keywords = " AND keywords like '%,".$tag.",%'";
            }
            $forcount = $this->query("SELECT * FROM resources JOIN types WHERE typeIdent=type AND deleted=0 AND security='public' AND state='final'".$keywords);
            $count = mysql_num_rows($forcount);
            $limit = 8;
            $offset = 0;
            if (isset($_GET['page']) && is_numeric($_GET['page'])) $offset = $_GET['page']*$limit;
            if ($count>4 && $tag) $this->paginate($count, $limit, $offset);
            return $this->getResourcesSQL("SELECT * FROM resources JOIN types WHERE typeIdent=type AND deleted=0 AND security='public' AND state='final'".$keywords." LIMIT ".$limit." OFFSET ".$offset);
        }

        function getResourcesByFTI($sword) {
            return $this->getResourcesSQL("SELECT * FROM resources JOIN types WHERE typeIdent=type AND deleted=0 AND security='public' AND state='final' AND resourceID IN (SELECT resourceID FROM fti WHERE MATCH(content) AGAINST ('".$sword."'))");
        }
        
        function getResourcesByMD($mds) {
            $md_st = array();
            $search = array();
            foreach ($mds as $md_key => $md_val) {
                $md_st []= "LEFT JOIN ".$md_key." ON ".$md_key.".resourceID = resources.resourceID";              
                $sub_search = array();
                foreach ($md_val as $val) {
                    $sub_search []= $md_key."='".$val."'";
                }
                if (count($sub_search)>1) {
                    $search []= "(".join($sub_search, " OR ").")";
                } else {
                    $search [] = join($sub_search, "");
                }            
            }
            $search_s = join($search, " AND ");
            //print "SELECT * FROM resources left JOIN types on types.typeIdent=resources.type ".join($md_st, " ")." WHERE deleted=0 AND security='public' AND state='final' AND ".$search_s;
            return $this->getResourcesSQL("SELECT * FROM resources left JOIN types on types.typeIdent=resources.type ".join($md_st, " ")." WHERE deleted=0 AND security='public' AND state='final' AND ".$search_s);
        }
        
        /*
         * ANSWERS
         * */
         
        function hasAnswers($rid) {
            $sql = "SELECT * FROM answers WHERE resourceID=".$rid;
            $ret = $this->query($sql);
            $num = mysql_num_rows($ret);
            return $num;
        }

        function getAnswerByID($aid, $cl="BaseAnswer") {
            $k = new $cl();
            $join = "";
            if ( isset($k->table) ) {
                $join = "JOIN ".$k->table." USING(answerID)";
            }
            $sql = "SELECT * FROM answers ".$join." WHERE answerID=".$aid;
            $ret = $this->query($sql);
            $obj = mysql_fetch_object($ret, $cl);
            return $obj;
        }

        function getAnswerByRID($rid, $opts=array(), $cl="BaseAnswer") {
            $k = new $cl();
            $join = "";
            if ( isset($k->table) ) {
                $join = "JOIN ".$k->table." USING(answerID)";
            }
            $params = "";
            $order = "";
            foreach ($opts as $op => $val ) {
                if ($op=="order") {
                    $order = " ORDER BY ".$val['by']." ".$val['order'];
                } else {
                    $params .= " AND ".$op."=".$val;
                }
            }
            $sql = "SELECT * FROM answers ".$join." WHERE resourceID=".$rid.$params.$order;
            $ret = $this->query($sql);
            $objs = array();
            while ( $obj = mysql_fetch_object($ret, $cl) ) {
                array_push($objs, $obj);
            }
            return $objs;
        }

        function getAnswerByLatest($rid, $uid, $cl="BaseAnswer") {
            $k = new $cl();
            $join = "";
            if ( isset($k->table) ) {
                $join = "JOIN ".$k->table." USING(answerID)";
            }
            $sql = "SELECT * FROM answers ".$join." WHERE resourceID=".$rid." and userID=".$uid." ORDER BY created DESC LIMIT 1";
            $ret = $this->query($sql);
            if ( mysql_num_rows($ret) == 0 || mysql_num_rows($ret) > 1 ) {
                return null;
            } 
            $obj = mysql_fetch_object($ret, $cl);
            return $obj;
        }

        function getAnswerByUser($rid, $uid, $cl="BaseAnswer") {
            $k = new $cl();
            $join = "";
            if ( isset($k->table) ) {
                $join = "JOIN ".$k->table." USING(answerID)";
            }
            $sql = "SELECT * FROM answers ".$join." WHERE resourceID=".$rid." AND userID=".$uid." ORDER BY created DESC";
            $ret = $this->query($sql);
            $objs = array();
            while ( $obj = mysql_fetch_object($ret, $cl) ) {
                array_push($objs, $obj);
            }
            return $objs;
        }
        
        /*
         * GROUPS
         * */
        function getGroupsByUserId($uid){
            $sql = "SELECT groupID FROM grouprelations WHERE userID='".$uid."'";
            $ret = $this->query($sql);
            $groups = array();
            while ( $groupI = mysql_fetch_array($ret) ) {
                $sql2 = "SELECT * FROM groups WHERE groupID='".$groupI['groupID']."'";
                $ret2 = $this->query($sql2);
                $groups []= mysql_fetch_array($ret2);
            }
            return $groups;
        }
        
        function getGroupNameById($gid){
            $sql = "SELECT groupName FROM groups WHERE groupID='".$gid."'";
            $ret = $this->query($sql);
            while ( $res = mysql_fetch_array($ret) ) {
                return $res['groupName'];
            }
            return false;
        }
        
        function getGroupMemberListing($gid){
            $sql = "SELECT userID FROM grouprelations WHERE groupID='".$gid."'";
            $ret = $this->query($sql);
            $list = array();
            while ( $res = mysql_fetch_array($ret) ) {
               $list []= $res[0];
            }
            return $list;
        }
        
        /*
        * ASSIGNMENTS
        * */
        function getAssignmentsByGroup($gid, $uid=false){
            $user = "";
            if ($uid) {
                $user = " AND userID=".$uid;
            }
            $ret = $this->query("SELECT * FROM assignments WHERE groupID=".$gid.$user." ORDER BY deadline DESC");
            $ass = array();
            while ( $as = mysql_fetch_array($ret) ) {
                $ass []= $as;
            }
            return $ass;
        }
        
        function getAssignmentListing($aid){
            $sql = "SELECT userID FROM assignment_relations WHERE assignmentID='".$aid."'";
            $ret = $this->query($sql);
            $list = array();
            while ( $res = mysql_fetch_array($ret) ) {
               $list []= $res[0];
            }
            return $list;
        }
        
        function getAssignmentsByRid($rid, $uid=false){
            $user = "";
            if ($uid) {
                $user = " AND userID=".$uid;
            }
            $ret = $this->query("SELECT * FROM assignments WHERE resourceID=".$rid.$user." ORDER BY deadline DESC");
            $ass = array();
            while ( $as = mysql_fetch_array($ret) ) {
                $ass []= $as;
            }
            return $ass;
        }
        
        function getRatingByResource($rid) {
            global $user;
            $uid = $user->getId();
            $ret = $this->query("SELECT * FROM comments WHERE resourceID=".$rid." AND userID=".$uid);
            while ( $rat = mysql_fetch_array($ret) ) {
                return $rat;
            }
            return false;
        }
        
        function getCommentsByResource($rid) {
            global $user;
            $uid = $user->getId();
            $ret = $this->query("SELECT * FROM comments WHERE resourceID=".$rid." AND comment!=\"\"");
            $cms = array ();
            while ( $rat = mysql_fetch_array($ret) ) {
                $cms []= $rat;
            }
            return $cms;
        }

    }

    if ( !isset($kdb)) {
        $kdb = new KDB();
    }
     
?>