<?php
    /**
    *CSRF class
    *
    * March 13 - 2013
    * V1.0 - Geroge
    * - Added orderByPosition flag to order the results by position.
    */
    require_once('api-adodb/config.php');
    require_once("database.class.php");

    class TableTraversal extends db{


        var $display;
        var $id;
        var $tableName;
        var $childTable;
        var $childTableField;
        var $fieldType; //Type of the display field (ex: photo_upload, mp3_upload, textfield etc...

        var $childrenTableField = array();
        var $children = array ();

        public static $forceSelectMenu = false; 

        public static $forcedChildren = array ();

        public static $limitExceededTables = array() ;
        public static $limit = 5; //Limit for the second order of the hierarchy
        public static $result = "";
        public static $visited = array();
        public static $visitedRows = array ();

        public static $originalFieldName = '';

        public static $previousIndent = 0;

        public static $parentsTableArray = array ();

        public static $orderByPosition = false; // A default order by position ASC

        public static $foreignTableTo; // The selectable options are from this table

        static $options = array();

        public $currentLevel = 0;// Please reset this number if you use this menu twice on the same page. to reset : MenuItem::reset();

        function __construct($id,$display,$tableName){
            db::__construct();

            $this->id = (int)$id;
            $this->display = (string)$display;
            $this->tableName = (string)$tableName;
            $this->getChildTable();
            /*
            $tableName = $this->adodb->qstr($tableName, get_magic_quotes_gpc());
            $sql = "SELECT table_name,field_name FROM `system` WHERE foreign_table=".$tableName." AND table_name != ".$tableName." LIMIT 1;";

            $res = $this->adodb->Execute($sql);
            if($res->RecordCount() == 1){
            $row = $res->FetchRow();
            $this->childTable = $row['table_name'];
            $this->childTableField = $row['field_name'];
            }*/
        }


        /**
        * Returns true if this field has forced parameters and false otherwise
        *
        */
        function hasForcedParameters(){
            return !empty(self::$forcedChildren);
        }

        function getChildTable(){

            $tableName = $this->adodb->qstr($this->tableName, get_magic_quotes_gpc());

            if($this->hasForcedParameters()){


                foreach(self::$forcedChildren as $key=>$value){


                    if($value == $this->tableName){
                        if($this->hasForeignKeyToSelf(true)){

                            $this->childTable = $this->tableName;
                        }else{
                            if(isset(self::$forcedChildren[$key+1])){
                                $this->childTable = self::$forcedChildren[$key+1];
                            }else{
                                $this->childTable = null;
                            }
                        }
                        break;
                    }
                }
                if(!$this->hasForeignKeyToSelf(true)){
                    $sql = "SELECT field_name FROM `system` WHERE foreign_table=".$tableName." AND table_name != ".$tableName." AND table_name='{$this->childTable}' ";
                }else{
                    $sql = "SELECT field_name FROM `system` WHERE foreign_table=".$tableName." AND table_name='{$this->childTable}' ";  
                }
                $res = $this->adodb->Execute($sql);
                $row = $res->FetchRow();
                $this->childTableField = $row['field_name'];


                return;
            }

            $sql = "SELECT table_name,field_name FROM `system` WHERE foreign_table=".$tableName." AND table_name != ".$tableName." ";

            $res = $this->adodb->Execute($sql);
            if($res->RecordCount() == 1){

                while($row = $res->FetchRow()){
                    $this->children [] = $row['table_name'];
                    $this->childrenTableField [$row['table_name']] = $row['field_name'];
                }
                $this->childTable = $this->children[0];
                $this->childTableField = $this->childrenTableField[$this->childTable];


            } else if($res->RecordCount() >= 2){

                $relatedToTable = explode('_',$this->tableName);
                $relatedToTable = $relatedToTable[0];
                $relatedToTable = substr($relatedToTable, 0, -1);

                $sql = "SELECT table_name,field_name FROM `system` WHERE foreign_table=".$tableName." AND table_name != ".$tableName." AND table_name LIKE '%{$relatedToTable}%'";


                $res = $this->adodb->Execute($sql);
                while($row = $res->FetchRow()){
                    $this->children [] = $row['table_name'];
                    $this->childrenTableField [$row['table_name']] = $row['field_name'];
                }


                if(isset($this->children[0])){
                    $this->childTable = $this->children[0];
                    $this->childTableField = $this->childrenTableField[$this->childTable];
                }



            }
        }

        static function resetResult(){
            self::$result = "";
        }

        public function hasForeignKeyToSelf($bool = false){
            $sql = "SELECT field_name FROM `system` WHERE foreign_table='{$this->tableName}' AND table_name='{$this->tableName}' LIMIT 1;";
            $res = $this->adodb->Execute($sql);
            if($res->RecordCount() == 0){
                return false;
            }

            $row = $res->FetchRow();

            if($bool === true){
                $sql = "SELECT {$row['field_name']} FROM {$this->tableName} WHERE id = {$this->id} LIMIT 1;";
                $res = $this->adodb->Execute($sql);
                if($res->RecordCount() == 0){
                    return false;
                }else{
                    $currRow = $res->FetchRow();
                    if(!empty($currRow[$row['field_name']])){

                        return $row['field_name'];
                    }else{
                        return false;
                    }
                }

            }

            return $row['field_name'];
        }

        /**
        * Function to check if the current row (id) has children in the child table
        *
        */
        public function hasChildren(){

            if($field = $this->hasForeignKeyToSelf()){
                $sql = "SELECT id FROM `".$this->tableName."` WHERE `".$field."` ='".$this->id."' LIMIT 1;";

                $res = $this->adodb->Execute($sql);
                if($res->RecordCount() == 1){

                    return true;
                }
            }

            if(!isset($this->childTable) ){

                return false;
            }




            //ParentsTableArray restricts the children fetch to be from the table inside that array
            //Check if the child table is a part of this array
            //If not, the content of the child table should not be fetched.
            if(!empty(self::$parentsTableArray)){
                if(!in_array($this->childTable, self::$parentsTableArray)){
                    return false;
                }
            }




            //   $sql = "SELECT id FROM `".$this->childTable."` WHERE ".$this->childTableField."='".$this->id."' LIMIT 1;";

            $sql = "SELECT `id` FROM `{$this->childTable}` WHERE `{$this->childTableField}` = '{$this->id}'  OR find_in_set( '{$this->id}' ,cast(REPLACE( `{$this->childTableField}` ,'|',',') as char)) > 0  LIMIT 1";


            $res = $this->adodb->Execute($sql);

            if($res->RecordCount() == 1){
                return true;
            }else{


                $children = $this->children;
                while($child = array_shift($children)){
                    $sql = "SELECT id FROM `".$child."` WHERE ".$this->childrenTableField[$child]."='".$this->id."' LIMIT 1;";
                    $res = $this->adodb->Execute($sql);
                    if($res->RecordCount() == 1){
                        return true;
                    }
                }

                return false;
            }

        }

        /**
        * Returns the text that will be displayed and visible to the user
        *
        */
        function display(){
            return displayInSitemap($this->tableName,$this->display);
        }

        /**
        * Checks if a table has been visited or not
        *
        * @param mixed $tableName
        */
        public static function beenVisited($tableName){
            if(in_array($tableName,self::$visited)){
                return true;
            }
            return false;
        }
        //Returns the name of each page
        public function pageName(){

            if(isset(self::$options[$this->tableName]['page_link']) && is_array(self::$options[$this->tableName]) ){
                return self::$options[$this->tableName]['page_link'];
            }
            return str_replace("'","",$this->tableName).'.php';
        }

        /**
        * Returns the menu item link
        *
        */
        public function link(){
            return $this->pageName()."?id=".$this->id;
        }

        /**
        * Returns the menu item link
        *
        */
        public function adminLink(){
            return pageLink("generate.php?table={$this->tableName}&id={$this->id}");
        }

        /**
        * Returns the database column name of the field that will be displayed to the user
        *
        * @param mixed $tableName
        */
        static function getFieldToDisplay($tableName){
            if(isset(self::$options[$tableName]['display_field']) && is_array(self::$options[$tableName]) && !empty(self::$options[$tableName]['display_field'])){
                return self::$options[$tableName]['display_field'];
            }


            if(isset(self::$options[$tableName]) && !is_array(self::$options[$tableName])){

                return self::$options[$tableName];
            }

            $table = new Table($tableName);
            if(($displayField = $table->getDisplayField()) !== false){
                return $displayField;
            }

            return 'name';
        }

        /**
        * Gets the order by clause for each table
        *
        */
        public static function getOrderBy($tableName){
            if(isset(self::$options[$tableName]['order']) && is_array(self::$options[$tableName]) ){
                return self::$options[$tableName]['order'];
            }elseif(self::$orderByPosition){
                return 'pos ASC';
            }else{
                return false;
            }
        }

        public function visited($tableName){
            if(!in_array($tableName,self::$visited)){
                self::$visited [] = $tableName;
            }
        }

        /**
        * Gets the children for each table
        *
        */
        public function getChildren(){
        
          
            self::resetLimitExcess($this->childTable);
            $this->visited($this->childTable);
            $this->visited($this->tableName);




            $displayField = self::getFieldToDisplay($this->childTable);


            $hasChildrenFromSameTable = false;
            if($field = $this->hasForeignKeyToSelf()){
                $sql = "SELECT id FROM `".$this->tableName."` WHERE `".$field."` ='".$this->id."' LIMIT 1;";

                $res = $this->adodb->Execute($sql);
                if($res->RecordCount() == 1){


                    $displayField = self::getFieldToDisplay($this->tableName);
                    $sql = "SELECT `id`,`{$displayField}` FROM `{$this->tableName}` WHERE `{$field}` = '{$this->id}' ";
                    if(self::getOrderBy($this->tableName)){
                        $sql .= " ORDER BY ".self::getOrderBy($this->tableName)." ";
                    }

                    $hasChildrenFromSameTable = true;
                }
            }



            if(!$hasChildrenFromSameTable){
                $sql = "SELECT `id`,`{$displayField}` FROM `{$this->childTable}` WHERE `{$this->childTableField}` = '{$this->id}'  OR find_in_set( '{$this->id}' ,cast(REPLACE( `{$this->childTableField}` ,'|',',') as char)) > 0  ";



                if(self::getOrderBy($this->childTable)){
                    $sql .= " ORDER BY ".self::getOrderBy($this->childTable)." ";
                }
            }





            $res = $this->adodb->Execute($sql);

            if($res->RecordCount() > self::$limit && self::$limit != 0 ){
                $sql .= " LIMIT ".self::$limit." ";
                $res = $this->adodb->Execute($sql);
               
                $this->limitExceededFor($this->childTable);
            }
            // echo $sql;
            $objectArray = array ();
            while( $row = $res->FetchRow() ){


                if($hasChildrenFromSameTable){
                    $table = new Table($this->tableName);
                    $object = new self($row['id'],$row[$displayField],$this->tableName);

                }else{
                    $table = new Table($this->childTable);
                    $object  = new self($row['id'],$row[$displayField],$this->childTable);
                }

                $fieldType = $table->getFieldType($displayField);
                $object->fieldType = $fieldType;
                $objectArray [] = $object;
            }




            if($hasChildrenFromSameTable){
                $displayField = self::getFieldToDisplay($this->childTable);
                if(!empty($displayField) && !empty($this->childTableField) && !empty($this->childTable) && !empty($this->id) ){
                    $sql = "SELECT `id`,`{$displayField}` FROM `{$this->childTable}` WHERE `{$this->childTableField}` = '{$this->id}' ";

                    if(self::getOrderBy($this->childTable)){
                        $sql .= " ORDER BY ".self::getOrderBy($this->childTable)." ";
                    }



                    $res = $this->adodb->Execute($sql);

                    if($res->RecordCount() > self::$limit && self::$limit != 0 ){
                        $sql .= " LIMIT ".self::$limit." ";
                        $res = $this->adodb->Execute($sql);
                      
                        $this->limitExceededFor($this->childTable);
                    }

                    while( $row = $res->FetchRow() ){

                        $table = new Table($this->childTable);

                        $object  = new self($row['id'],$row[$displayField],$this->childTable);


                        $fieldType = $table->getFieldType($displayField);
                        $object->fieldType = $fieldType;
                        $objectArray [] = $object;
                    }
                }
            }

            return $objectArray;
        }



        public function visitedRows($rowId,$tableName){
            self::$visitedRows[$tableName][] = $rowId;
        }
        public function hasVisitedRows($rowId,$tableName){
            if(isset(self::$visitedRows[$tableName]) && is_array(self::$visitedRows[$tableName])){
                return in_array($rowId,self::$visitedRows[$tableName]);
            }else{
                return false;
            }
        }

        /**
        * Appends to the array the list of table names whose limit was exceeded when fetching their records.
        *
        * @param string $tableName
        */
        public function limitExceededFor($tableName){
            self::$limitExceededTables [$tableName] = array ('foreignKey' => $this->childTableField, 'id' => $this->id);


        }

        public static function resetLimitExcess($tableName=NULL){
            if(isset($tableName)){
                unset(self::$limitExceededTables[$tableName]);
            }else{
                self::$limitExceededTables = array ();
            }
        }

        function incrementLevel(){
            $this->currentLevel++;
        }

        //Returns an array of menu items
        public static function createMenu($tableName, $options = null){



            if(isset($options)){
                self::$options = $options;
            }
            if(isset($options['options']['limit'])){
                self::$limit = $options['options']['limit'];
            }
            if(isset($options['parameters']) && $options['parameters'] !== false ){
                self::$forcedChildren = $options['parameters'];
            }

            $db = new MySQLDatabase();


            $sql = "SELECT `id`,`".self::getFieldToDisplay($tableName)."` FROM `$tableName`";

            $table = new Table($tableName);

            if( ($field = $table->hasForeignKeyRelatedTo($tableName,true)) !== false ){
                $sql .= " WHERE {$field} = '' ";
            }  

            if(isset($options['options']['foreignKeyId'],$options['options']['foreignKey']) ){
                $sql .= " WHERE `{$options['options']['foreignKey']}` = '{$options['options']['foreignKeyId']}'  OR find_in_set( '{$options['options']['foreignKeyId']}' ,cast(REPLACE( `{$options['options']['foreignKey']}` ,'|',',') as char)) > 0  ";
            }

            if(isset($options['options']['order_by'])){
                $sql .= " ORDER BY `{$options['options']['order_by']}` ";
            }else if(self::getOrderBy($tableName)){
                $sql .= " ORDER BY ".self::getOrderBy($tableName)." ";
            }
            
             if(isset($options['options']['userlimit'])){
                 $options['options']['userlimit'] =  intval($options['options']['userlimit']);
                  $sql .= " LIMIT {$options['options']['userlimit']} ";
            }
            
         
            
       


            $fieldType = $table->getFieldType(self::getFieldToDisplay($tableName));
            // echo $sql;
            $res = $db->query($sql);

            $object_array = array();
            while($row = $db->fetch_array($res)){
                $object = new self($row['id'],$row[self::getFieldToDisplay($tableName)],$tableName);
                $object->fieldType = $fieldType;

                $object_array [] = $object;
            }



            return $object_array;

        }

        static function tableExceededLimit($tableName){
            return isset(self::$limitExceededTables[$tableName]);
            return in_array($tableName,self::$limitExceededTables);
        }

        /**
        *Function that draws the menu, this function should be overridden in classes that extends this class in order to produce different results
        */
        function draw (Array $listOfItems) {
            echo "
            <ul class='parentMenu '>
            ";
            $i=0;
            foreach ($listOfItems as $item) {
                echo "<li class='child '>";
                echo"<a href='".$item->link()."' class='link'>";
                echo $item->display();
                echo "</a>";

                if ($item->hasChildren()) {

                    drawMenu($item->getChildren()); // here is the recursion
                }
                echo "</li>";

            }
            echo "
            </ul>
            ";
        }



    }

    /**
    * FUnction that draws the recursive menu up to a certain level
    *$printParentLinks is a boolean to have links on the parent items or not
    * @param array $listOfItems
    * @param integer $level
    * @param bool $printParentLinks
    */
    function drawMenu (Array $listOfItems) {
        echo "
        <ul class='parentMenu '>
        ";
        $i=0;
        foreach ($listOfItems as $item) {
            echo "<li class='child '>";
            echo"<a href='".$item->link()."' class='link'>";
            echo $item->display();
            echo "</a>";
            if ($item->hasChildren()) {

                drawMenu($item->getChildren()); // here is the recursion
            }
            echo "</li>";

        }

        echo "
        </ul>
        ";
    }


    function drawMenuTest (Array $listOfItems) {
        echo "
        <ul class='parentMenu '>
        ";
        $i=0;
        foreach ($listOfItems as $item) {
            echo "<li class='child '>";
            echo"<a href='".$item->link()."' class='link'>";
            echo $item->display();
            echo "</a>";
            if ($item->hasChildren()) {

                drawMenu($item->getChildren()); // here is the recursion
            }
            echo "</li>";

        }

        echo "
        </ul>
        ";
    }

    function getParentTableByRowId($currentTable,$currentFieldName,$currentRowId){
        $sql = "SELECT foreign_table FROM `system` WHERE table_name = '{$currentTable}' AND field_name ='{$currentFieldName}' LIMIT 1 ";
        $res = $this->adodb->Execute($sql);
        $row = $res->FetchRow();

        //Foreign key to self
        if($row['foreign_table'] == $currentTable){
            $sql = "SELECT $currentFieldName FROM $currentTable WHERE id = '{$currentRowId}' LIMIT 1";
            $res = $this->adodb->Execute($sql);
            $row = $res->FetchRow();

            if(!empty($row[$currentFieldName])){
                return $currentTable;
            }
        }

        return $row['foreign_table'];
    }


    /**
    * Function that draws the recursive menu up to a certain level
    * $printParentLinks is a boolean to have links on the parent items or not
    * @param array $listOfItems
    * @param integer $level
    * @param bool $printParentLinks
    */
    function drawSiteMapMenu (Array $listOfItems) {

        if(empty($listOfItems)){
            return;
        }

        echo "
        <ul class='parentMenu'>
        ";
        $i=0;

        foreach ($listOfItems as $item) {
            if($item->hasForeignKeyToSelf() && $item->hasVisitedRows($item->id,$item->tableName)){
                continue;
            }

            $item->visitedRows($item->id,$item->tableName);

            echo "<li class='child {$item->fieldType}'>";
            echo "<a href='".$item->adminLink()."' class='link'>";
            echo $item->display() .' <span class="item_id">#'.$item->id.'</span>';
            echo "</a>";

            if(isset($item->childTable)){


                foreach($item->children as $childTable){
                    $childTableField = $item->childrenTableField[$childTable];
                    echo '  <a class="addLink" href="'.pageLink("generate.php?table={$childTable}&{$childTableField}={$item->id}").'">[ Add '.printTableName($childTable).' ]</a>';
                }
            }

            if ($item->hasChildren()) {

                while($item->childTable = array_shift($item->children)){
                    $item->childTableField = $item->childrenTableField[$item->childTable];
                    drawSiteMapMenu($item->getChildren()); // here is the recursion
                }
            }
            echo "</li>";

        }

        if(TableTraversal::tableExceededLimit($item->tableName)){
            echo '<li  ><a class="loadMore" href="javascript:void(0)" data-table-name="'.$item->tableName.'" data-foreign-key="'.TableTraversal::$limitExceededTables[$item->tableName]['foreignKey'] .'"  data-foreign-key-id="'.TableTraversal::$limitExceededTables[$item->tableName]['id'] .'" >Load more '.printTableName($item->tableName).'...</a></li>';
        }

        echo "
        </ul>
        ";
    }


    function drawSelectMenu (Array $listOfItems,$value=NULL, $parentsTableArray = array () ) {

        if(!is_array($value)){
            $value = explode('|',$value);   
        }
        TableTraversal::$forceSelectMenu = true;

        if(empty(TableTraversal::$parentsTableArray)){
            TableTraversal::$parentsTableArray = $parentsTableArray;
        }


        $startIndent = TableTraversal::$previousIndent;



        foreach ($listOfItems as $item) {


            TableTraversal::$previousIndent = $startIndent;
            if($item->hasForeignKeyToSelf()){
                if($item->hasVisitedRows($item->id,$item->tableName)){
                    continue;
                }
            }
            $item->visitedRows($item->id,$item->tableName);

            if($item->hasForeignKeyToSelf()){
                if(!in_array($item->tableName,TableTraversal::$parentsTableArray)){
                    continue;
                }
            }

            $label = '';
            $delimiter = '&#8210;&#8210;';
            //$label .= str_repeat('---',$allLevels - $level);



            $label .= $item->display().' - #'.$item->id.'';
            $arrayWithoutLastChild = TableTraversal::$parentsTableArray;
            array_pop($arrayWithoutLastChild);

            if($item->hasChildren() || ((in_array($item->tableName, $arrayWithoutLastChild)) && !$item->hasForeignKeyToSelf()) || $item->tableName != TableTraversal::$foreignTableTo){

                if($item->tableName != TableTraversal::$foreignTableTo){
                    TableTraversal::$result .= "<optgroup label='".str_repeat($delimiter,TableTraversal::$previousIndent/5).$label."'>";
                }else{
                    TableTraversal::$result .= '<option value="'.$item->id.'" '.(in_array($item->id,$value) ? 'selected="selected"' : '' ).'>'.str_repeat($delimiter,TableTraversal::$previousIndent/5).$label.'</option>';
                }

                if($item->hasChildren()){
                    TableTraversal::$previousIndent += 10;
                }

            }else {


                TableTraversal::$result .= '<option value="'.$item->id.'" '.(in_array($item->id,$value) ? 'selected="selected"' : '' ).'>'.str_repeat($delimiter,TableTraversal::$previousIndent/5).$label.'</option>';
            }

            if ($item->hasChildren()) {

                drawSelectMenu($item->getChildren(),$value); // here is the recursion
            }
            if($item->hasChildren()){
                if($item->tableName != TableTraversal::$foreignTableTo){
                    TableTraversal::$result .= "</optgroup>";
                }
            }

        }
        TableTraversal::$previousIndent = $startIndent;
        TableTraversal::$forceSelectMenu = false;
    }


    function drawSelectMenuOriginal (Array $listOfItems,$level=NULL,$value=NULL) {

        static $allLevels;
        if(!$allLevels)
            $allLevels = $level;

        $i=0;
        foreach ($listOfItems as $item) {


            $label = '';
            //$label .= str_repeat('---',$allLevels - $level);
            $style = "text-indent:" . ( ($allLevels - $level) *10) .'px';
            $label .= $item->display();

            if($level != 0){


                TableTraversal::$result .= "<optgroup label='{$label}'  style='".$style."'>";
            }else {
                TableTraversal::$result .= '<option value="'.$item->id.'" '.($value == $item->id ? 'selected="selected"' : '' ).' style="'.$style.'">'.$label.'</option>';
            }

            if ($item->hasChildren()) {

                $item->incrementLevel();

                if($item->currentLevel > $level){

                    continue;
                }

                drawSelectMenu($item->getChildren(),$level-1,$value); // here is the recursion
            }
            if($level != 0){
                TableTraversal::$result .= "</optgroup>";
            }

        }





    }



?>