<?php

/**
 * Register all actions and filters for the plugin.
 *
 * @link       -
 * @since      1.0.0
 */

/**
 * Register all actions and filters for the plugin.
 *
 * Maintain a list of all hooks that are registered throughout
 * the plugin, and register them with the WordPress API. Call the
 * run function to execute the list of actions and filters.
 *
 * @author     Markus Domin, Helge Nissen <->
 */
class Trainingssystem_Plugin_Database_Training_Daoimple implements Trainingssystem_Plugin_Database_Training_Dao
{
    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_TRAINING_LEKTIONEN;
    public static $dbprefix_user = TRAININGSSYSTEM_PLUGIN_DB_TRAINING_USER;
    public static $dbprefix_seiten = TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;

    private $table_name;
    /**
     * Initialize the collections used to maintain the actions and filters.
     *
     * @since    1.0.0
     */
    public function __construct()
    {
        $this->iniDB();
    }

    public function iniDB()
    {

        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $charset_collate = $wpdb->get_charset_collate();
        $tableversionsstr =[
			"CREATE TABLE IF NOT EXISTS $table_name(
                training_id BIGINT(20) NOT NULL,
                lektion_id BIGINT(20) NOT NULL,
                lektion_index INT NOT NULL,
                PRIMARY KEY (training_id, lektion_id)
        )$charset_collate;",
		"ALTER TABLE $table_name CHANGE traning_id training_id BIGINT(20) NOT NULL;"
        ];
		Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);
		



    }

    /**
     * database updating beim plugin update
     *
     * @since    1.0.0
     */
	public function updating(){

	}

    public function getAllTrainings($includecategories = true)
    {
			global $wpdb;
			$table_name_lektion = $wpdb->prefix.$this::$dbprefix;

			$sql = "SELECT * FROM $table_name_lektion	order by
			training_id, lektion_index";

			$result = $wpdb->get_results($sql);

			$result2 = array();

		  foreach ($result as $element) {
				$post = get_post($element->lektion_id);
                if(!is_null($post)) {

                	$url = add_query_arg(array('idt1' => $element->training_id, 'idt2' => $element->lektion_id), get_permalink($element->lektion_id));//esc_url();//get_permalink( $id );
                    $imageurl = null;
                    if (has_post_thumbnail($post->ID)) {
                            $imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
                    }

                    $result2[$element->training_id]['lektionen'][] =new Trainingssystem_Plugin_Database_Lektion($element->lektion_id,
                                                                    $post->post_title, $post->post_excerpt, $url, $imageurl);
                }

			}

			$result3 = array();
			foreach ($result2 as $id => $element) {
				$lektionsliste = $element['lektionen'];
                $post = get_post($id);
                $url = add_query_arg("idt1", $id, get_permalink($id));
                $imageurl = null;
                if (has_post_thumbnail($id)) {
                    $imageurl = get_the_post_thumbnail_url($id);//, 'full' );
                }
                if($post!=null) {
                    $categories = ($includecategories) ? $this->getCategoriesForTraining($id) : [];
                    
                    $result3[] = new Trainingssystem_Plugin_Database_Training($id, $post->post_title, $post->post_excerpt, $url, $imageurl, $lektionsliste, $categories);
                }
				
			}

			return $result3;

    }

    public function getTrainingById($id) {
        global $wpdb;
        $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
        $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

        $sql = $wpdb->prepare("SELECT tposts.ID as tid, tposts.post_title as ttitle, tposts.post_excerpt as texcerpt, 
                                    lposts.ID as lid, lposts.post_title as ltitle, lposts.post_excerpt as lexcerpt,
                                    sposts.ID as sid, sposts.post_title as stitle, sposts.post_excerpt as sexcerpt, $table_name_seiten.seiten_index as sindex
                        FROM $table_name_lektion 
                        INNER JOIN $table_name_seiten ON $table_name_lektion.lektion_id = $table_name_seiten.lektion_id
                        INNER JOIN $wpdb->posts as tposts ON $table_name_lektion.training_id = tposts.ID
                        INNER JOIN $wpdb->posts as lposts ON $table_name_lektion.lektion_id = lposts.ID
                        INNER JOIN $wpdb->posts as sposts ON $table_name_seiten.seiten_id = sposts.ID
                        WHERE $table_name_lektion.training_id = %d
                        ORDER BY $table_name_lektion.lektion_index, $table_name_seiten.seiten_index", $id);
        
        $results = $wpdb->get_results($sql);

        if(count($results) > 0) {
            
            $seiten = array();
            $lektionen = array();
            $training = null;

            foreach($results as $row) {
                if(is_null($training)) {
                    $url = add_query_arg("idt1", $row->tid, get_permalink($row->tid));
                    $imageurl = null;
                    if (has_post_thumbnail($row->tid)) {
                        $imageurl = get_the_post_thumbnail_url($row->tid);//, 'full' );
                    }
                    $categories = $this->getCategoriesForTraining($id);
                    $training = new Trainingssystem_Plugin_Database_Training($row->tid, $row->ttitle, $row->texcerpt, $url, $imageurl, array(), $categories);
                }

                if(!isset($lektionen[$row->lid])) {
                    $seiten[$row->lid] = array();

                    $url = add_query_arg(array('idt1' => $row->tid, 'idt2' => $row->lid), get_permalink($row->lid));
                    $imageurl = null;
                    if (has_post_thumbnail($row->lid)) {
                            $imageurl = get_the_post_thumbnail_url($row->lid);//, 'full' );
                    }

                    $lektionen[$row->lid] = new Trainingssystem_Plugin_Database_Lektion($row->lid,
                                                $row->ltitle, $row->lexcerpt, $url, $imageurl, array(), null, null, array(), null);
                }

                $url = add_query_arg(array('idt1' => $row->tid, 'idt2' => $row->lid), get_permalink($row->sid));//esc_url();//get_permalink( $id );
                $imageurl = null;
                if (has_post_thumbnail($row->sid)) {
                        $imageurl = get_the_post_thumbnail_url($row->sid);//, 'full' );
                }
                $seiten[$row->lid][] = new Trainingssystem_Plugin_Database_Trainingseite(array(
                    "id"=>$row->sid,
                    "index"=>$row->sindex,
                    "title"=>$row->stitle,
                    "excerpt"=>$row->sexcerpt,
                    "url"=>$url,
                    "imageurl"=>$imageurl,
                    "trainingid"=>$row->tid,
                    "lektionid"=>$row->lid
                ));
            }

            foreach($lektionen as $lektion) {
                $lektion->setSeitenliste(isset($seiten[$lektion->getId()]) ? $seiten[$lektion->getId()] : array());
                $lektion->setFirstPageUrl(isset($lektion->getSeitenliste()[0]) ? $lektion->getSeitenliste()[0]->getUrl() : null);
            }

            $training->setLektionsliste($lektionen);
            return $training;

        } else {
            return null;
        }
    }

    /**
     * DEPRECATED!
     * Use new function above.
     * 
     * Can be deleted anytime
     */
    public function getTrainingByIdOld($id){
      global $wpdb;
			$table_name_lektion = $wpdb->prefix.$this::$dbprefix;

			$sql = $wpdb->prepare("SELECT * FROM $table_name_lektion where training_id = %d	order by
			training_id, lektion_index",$id);

			$result = $wpdb->get_results($sql);

			$result2 = array();

		    foreach ($result as $element) {
				$post = get_post($element->lektion_id);
				$url = add_query_arg(array('idt1' => $element->training_id, 'idt2' => $element->lektion_id), get_permalink($element->lektion_id));//esc_url();//get_permalink( $id );
				$imageurl = null;
				if (has_post_thumbnail($post->ID)) {
						$imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
				}

                $seiten = Trainingssystem_Plugin_Database::getInstance()->TrainingseitenDao->getLektionenSeiten($id, $element->lektion_id);

                $firstPageUrl = isset($seiten[0]) ? $seiten[0]->getUrl() : null;

                $result2[$element->training_id]['lektionen'][] =new Trainingssystem_Plugin_Database_Lektion($element->lektion_id,
																$post->post_title, $post->post_excerpt, $url, $imageurl, $seiten, null, null, array(), $firstPageUrl);

			}

			$result3 = array();
			foreach ($result2 as $id => $element) {
				$lektionsliste = $element['lektionen'];
                $post = get_post($id);
                $url = add_query_arg("idt1", $id, get_permalink($id));
                $imageurl = null;
                if (has_post_thumbnail($id)) {
                    $imageurl = get_the_post_thumbnail_url($id);//, 'full' );
                }
                $categories = $this->getCategoriesForTraining($id);
				$result3[] = new Trainingssystem_Plugin_Database_Training($id, $post->post_title, $post->post_excerpt, $url, $imageurl, $lektionsliste, $categories);
			}
            if(count($result3)>0) return $result3[0];
			return $result3;
    }

    //alt
    public function getTraining($id)
    {
        global $wpdb;
        $sql = $wpdb->prepare("Select lektion_id from {$this->table_name} Where training_id = %d ORDER BY training_id , lektion_index ", $id);

        $result = $wpdb->get_col($sql);//get_results($sql,ARRAY_N);

        $lektionsliste = $result;
        $post = get_post($id);
        $url = add_query_arg("idt1", $id, get_permalink($id));
        $imageurl = null;
        if (has_post_thumbnail($id)) {
            $imageurl = get_the_post_thumbnail_url($id);//, 'full' );
        }
        $categories = $this->getCategoriesForTraining($id);
        return new Trainingssystem_Plugin_Database_Training($id, $post->post_title, $post->post_excerpt, $url, $imageurl, $lektionsliste, $categories);
    }

    public function getTrainingNew($id)
    {
        global $wpdb;

        $table_name_user = $wpdb->prefix.$this::$dbprefix_user;
        $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
        $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

        $sql = $wpdb->prepare("SELECT seiten.*
			FROM $table_name_user as tuser
			left join $table_name_lektion as lekt
			on lekt.lektion_id = tuser.lektion_id
			left join $table_name_seiten as seiten
			on seiten.lektion_id = lekt.lektion_id
			where tuser.user_id = %d and tuser.training_id =%d
			order by
			tuser.lektion_index, seiten.seiten_index", $userid, $id);

        $result = $wpdb->get_results($sql);
        $result2 = array();
        foreach ($result as $element) {
            $post = get_post($element->seiten_id);
            $url = add_query_arg(array('idt1' => $element->training_id, 'idt2' => $element->lektion_id), get_permalink($element->seiten_id));//esc_url();//get_permalink( $id );
            $imageurl = null;
            if($post != null) {
                if (has_post_thumbnail($post->ID)) {
                    $imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
                }
                $result2[$element->lektion_id]['lektion_id'] = $element->lektion_id;
                $result2[$element->lektion_id]['training_id'] = $element->training_id;
                $result2[$element->lektion_id]['lastseite'] = $element->last_seiten_id;
                if ($element->last_seiten_id == $element->seiten_id) {
                    $result2[$element->lektion_id]['lastindex'] = $element->seiten_index;
                } elseif (!array_key_exists('lastindex', $result2[$element->lektion_id])) {
                    $result2[$element->lektion_id]['lastindex'] = 0;
                }
                $result2[$element->lektion_id]['lektion_fin_date'] = $element->lektion_fin_date;
                $result2[$element->lektion_id]['feedback'] = $element->feedback;
                $result2[$element->lektion_id]['training_auto'] = $element->training_auto;
                $result2[$element->lektion_id]['seiten'][] =
                    new Trainingssystem_Plugin_Database_Trainingseite(array(
                        'id' => $element->seiten_id,
                        'index' => $element->seiten_index,
                        'title' => $post->post_title,
                        'excerpt' => $post->post_excerpt,
                        'url' => $url,
                        'imageurl' => $imageurl,
                        'trainingid' => $element->training_id,
                        'lektionid' => $element->lektion_id,
                    ));
            }
        }

        $lektionarr = array();

        foreach ($result2 as $value) {
            $post = get_post($value['lektion_id']);
            $url = add_query_arg(array('idt1' => $value['training_id'], 'idt2' => $value['lektion_id']), get_permalink($value['lektion_id']));//esc_url(add_query_arg(array('idt1' => $trainingsID, 'idt2' => $value->lektion_id), get_permalink($value->lektion_id)));//get_permalink( $id );
            $imageurl = null;
            if (has_post_thumbnail($post->ID)) {
                $imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
            }

            $fin = 0;
            if ($value['lastseite'] != '0') {
                $tpagecount = count($value['seiten']);
                $tindex = $value['lastindex'] + 1;
                $fin = intval($tindex * 100 / $tpagecount);
            }

            $lektionarr[] = new Trainingssystem_Plugin_Database_Lektion($value['lektion_id'],
                                    $post->post_title, $post->post_excerpt, $url, $imageurl, null, $value['training_auto'], $value['lektion_enable'], $value['seiten'], $value['seiten'][0]->getUrl(), $fin, $value['lastseite'], $value['lastindex'], $value['lektion_fin_date'], $value['feedback']);
        }


        $lektionsliste = $lektionarr;
        $post = get_post($id);
        $url = add_query_arg("idt1", $id, get_permalink($id));
        $imageurl = null;
        if (has_post_thumbnail($id)) {
            $imageurl = get_the_post_thumbnail_url($id);//, 'full' );
        }

        $categories = $this->getCategoriesForTraining($id);

        return new Trainingssystem_Plugin_Database_Training($id, $post != null ? $post->post_title : '', $post != null ? $post->post_excerpt : '', $url, $imageurl, $lektionsliste, $categories);
    }

    public function getTrainingByUser($id, $userid)
    {
        global $wpdb;

        $table_name_user = $wpdb->prefix.$this::$dbprefix_user;
        $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
        $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

        $sql = $wpdb->prepare("SELECT seiten.*, tuser.last_seiten_id, DATE(tuser.lektion_fin_date) as lektion_fin_date, tuser.feedback AS feedback, tuser.training_auto as training_auto, tuser.lektion_enable as lektion_enable
			FROM $table_name_user as tuser
			left join $table_name_lektion as lekt
			on lekt.lektion_id = tuser.lektion_id
			left join $table_name_seiten as seiten
			on seiten.lektion_id = lekt.lektion_id
			where tuser.user_id = %d and tuser.training_id =%d
			order by
			tuser.lektion_index, seiten.seiten_index", $userid, $id);

        $result = $wpdb->get_results($sql);
        $result2 = array();
        foreach ($result as $element) {
            $post = get_post($element->seiten_id);
            $url = add_query_arg(array('idt1' => $element->training_id, 'idt2' => $element->lektion_id), get_permalink($element->seiten_id));//esc_url();//get_permalink( $id );
            $imageurl = null;
            if($post != null) {
                if (has_post_thumbnail($post->ID)) {
                    $imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
                }
                $result2[$element->lektion_id]['lektion_id'] = $element->lektion_id;
                $result2[$element->lektion_id]['training_id'] = $element->training_id;
                $result2[$element->lektion_id]['lastseite'] = $element->last_seiten_id;
                if ($element->last_seiten_id == $element->seiten_id) {
                    $result2[$element->lektion_id]['lastindex'] = $element->seiten_index;
                } elseif (!array_key_exists('lastindex', $result2[$element->lektion_id])) {
                    $result2[$element->lektion_id]['lastindex'] = 0;
                }
                $result2[$element->lektion_id]['lektion_fin_date'] = $element->lektion_fin_date;
                $result2[$element->lektion_id]['feedback'] = $element->feedback;
                $result2[$element->lektion_id]['training_auto'] = $element->training_auto;
                $result2[$element->lektion_id]['lektion_enable'] = $element->lektion_enable;
                $result2[$element->lektion_id]['seiten'][] =
                    new Trainingssystem_Plugin_Database_Trainingseite(array(
                        'id' => $element->seiten_id,
                        'index' => $element->seiten_index,
                        'title' => $post->post_title,
                        'excerpt' => $post->post_excerpt,
                        'url' => $url,
                        'imageurl' => $imageurl,
                        'trainingid' => $element->training_id,
                        'lektionid' => $element->lektion_id,
                    ));
            }
        }

        $lektionarr = array();

        foreach ($result2 as $value) {
            $post = get_post($value['lektion_id']);
            $url = add_query_arg(array('idt1' => $value['training_id'], 'idt2' => $value['lektion_id']), get_permalink($value['lektion_id']));//esc_url(add_query_arg(array('idt1' => $trainingsID, 'idt2' => $value->lektion_id), get_permalink($value->lektion_id)));//get_permalink( $id );
            $imageurl = null;
            if (has_post_thumbnail($post->ID)) {
                $imageurl = get_the_post_thumbnail_url($post->ID);//, 'full' );
            }

            $fin = 0;
            if ($value['lastseite'] != '0') {
                $tpagecount = count($value['seiten']);
                $tindex = $value['lastindex'] + 1;
                $fin = intval($tindex * 100 / $tpagecount);
            }

            $lektionarr[] = new Trainingssystem_Plugin_Database_Lektion($value['lektion_id'],
                                    $post->post_title, $post->post_excerpt, $url, $imageurl, null, $value['training_auto'], $value['lektion_enable'], $value['seiten'], $value['seiten'][0]->getUrl(), $fin, $value['lastseite'], $value['lastindex'], $value['lektion_fin_date'], $value['feedback']);
        }


        $lektionsliste = $lektionarr;
        $post = get_post($id);
        $url = add_query_arg("idt1", $id, get_permalink($id));
        $imageurl = null;
        if (has_post_thumbnail($id)) {
            $imageurl = get_the_post_thumbnail_url($id);//, 'full' );
        }

        $categories = $this->getCategoriesForTraining($id);

        return new Trainingssystem_Plugin_Database_Training($id, $post != null ? $post->post_title : '', $post != null ? $post->post_excerpt : '', $url, $imageurl, $lektionsliste, $categories);
    }

    public function updateTraining($training)
    {
        global $wpdb;
    }

    public function deleteTraining($id)
    {
        global $wpdb;
        $result = $wpdb->delete('table', array('training_id' => $id), array('%d'));

        return $result;
    }

    public function insertTraining($training)
    {
        global $wpdb;
        $training = array('lektionen' => [1, 2, 3, 4], 'id' => 11);

        for ($i = 0;$i < count($training['lektionen']);++$i) {
            $wpdb->insert($this->table_name,
                        array(
                            'training_id' => $training['id'],
                            'lektion_id' => $training['lektionen'][$i],
                            'lektion_index' => $i,
                        ),
                        array(
                            '%d',
                            '%d',
                            '%d',
                        ));
        }
    }

    public function createTrainingFull($training)
    {
        global $wpdb;
         for ($i = 0; $i < count($training['lektionen']); ++$i) {
             $wpdb->insert($wpdb->prefix.$this::$dbprefix,
                         array(
                             'training_id' => $training['id'],
                             'lektion_id' => $training['lektionen'][$i]['id'],
                             'lektion_index' => $i,
                         ),
                         array(
                             '%d',
                             '%d',
                             '%d',
                         ));
                         //training_seiten
                for ($k = 0; $k < count($training['lektionen'][$i]['seiten']); ++$k) {
                    $wpdb->insert($wpdb->prefix.Trainingssystem_Plugin_Database_Trainingseiten_Daoimple::$dbprefix,
                             array(
                                 'lektion_id' => $training['lektionen'][$i]['id'],
                                 'seiten_id' => $training['lektionen'][$i]['seiten'][$k]['id'],
                                 'seiten_index' => $k,
                                 'training_id' => $training['id'],
                             ),
                             array(
                                 '%d',
                                 '%d',
                                 '%d',
                             ));
                }
         }
    }

    public function clearTrainingyById($id)
    {
      global $wpdb;
      $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
      $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;
      $wpdb->delete( $table_name_lektion, array( 'training_id' => $id ), array( '%d' ) );
      $wpdb->delete( $table_name_seiten, array( 'training_id' => $id ), array( '%d' ) );
    }

    public function saveTrainingyById($id,$lektionen)
    {
      global $wpdb;
      $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
      $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

      $lektionindex=0;
      foreach ($lektionen as $lektion_key => $lektion_value) {
        
        foreach ($lektion_value->getPages() as $seiten_key => $seiten_value) {
          $wpdb->insert($table_name_seiten,
          	array(
          		'lektion_id' => $seiten_value->getLektionid() ,
          		'seiten_id' => $seiten_value->getId(),
              'seiten_index' => $seiten_value->getIndex() ,
              'training_id' => $seiten_value->getTrainingid(),
          	),
          	array(
          		'%d',
              '%d',
              '%d',
          		'%d'
          	)
          );
        }
        $wpdb->insert($table_name_lektion,
          array(
            'lektion_id' => $lektion_value->getId() ,
            'lektion_index' => $lektionindex ,
            'training_id' => $id,
          ),
          array(
            '%d',
            '%d',
            '%d'
          )
        );

        $lektionindex++;
      }

    }

    public function removeLektionFromUsers($lektionsid, $trainingsid = null) {
        global $wpdb;

        $table_name = $wpdb->prefix . $this::$dbprefix_user;

        if(is_null($trainingsid)) {
            return $wpdb->delete($table_name, array("lektion_id" => $lektionsid), array("%d")) !== false;
        } else {
            return $wpdb->delete($table_name, array("lektion_id" => $lektionsid, "training_id" => $trainingsid), array("%d", "%d")) !== false;
        }
        
    } 

	public function removeTrainingFromUsers($trainingid) {

        global $wpdb;
        $table_name = $wpdb->prefix . $this::$dbprefix_user;

		return $wpdb->delete($table_name, array("training_id" => $trainingid), array("%d")) !== false;
    }

    public function removeSeiteFromDatabase($seitenid, $lektionid = null, $trainingid = null) {
        global $wpdb;
        $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

        if(is_null($lektionid) || is_null($trainingid)) {
            return $wpdb->delete($table_name_seiten, array("seiten_id" => $seitenid), array("%d")) != false;
        } else {
            return $wpdb->delete($table_name_seiten, array("seiten_id" => $seitenid, "lektion_id" => $lektionid, "training_id" => $trainingid), array("%d", "%d", "%d")) != false;
        }
    }

    public function removeLektionFromDatabase($lektionsid, $trainingsid = null) {
        global $wpdb;
        $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
        $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

        if(is_null($trainingsid)) {
            return $wpdb->delete($table_name_lektion, array("lektion_id" => $lektionsid), array("%d")) !== false &&
                $wpdb->delete($table_name_seiten, array("lektion_id" => $lektionsid), array("%d")) !== false;
        } else {
            return $wpdb->delete($table_name_lektion, array("lektion_id" => $lektionsid, "training_id" => $trainingsid), array("%d", "%d")) !== false &&
                $wpdb->delete($table_name_seiten, array("lektion_id" => $lektionsid, "training_id" => $trainingsid), array("%d", "%d")) !== false;
        }
        
    }
    
    public function removeTrainingFromDatabase($trainingid) {
        global $wpdb;
        $table_name_lektion = $wpdb->prefix.$this::$dbprefix;
        $table_name_seiten = $wpdb->prefix.$this::$dbprefix_seiten;

        return $wpdb->delete($table_name_lektion, array("training_id" => $trainingid), array("%d")) !== false &&
                $wpdb->delete($table_name_seiten, array("training_id" => $trainingid), array("%d")) !== false;
    }

    private function getCategoriesForTraining($trainingid) {
        $category_objects = get_the_category($trainingid);

        $categories = array();

        if(sizeof($category_objects) != 0) {
            foreach($category_objects as $c_o) {
                $categories[] = $c_o->name;
            }
        }

        return $categories;
    }

    /*
     * Get the 'findates' for each 'lektion' for all $trainings and $userids in one query for the DataExport Module
     * 
     * @param Integer-Array Training-IDs
     * @param Integer-Array User-IDs
     * 
     * @return Array
     *          -> User-ID
     *              |-> Training-ID
     *                  |-> Lektion-ID
	 *						|-> entry
    */
    public function getSystemstatisticFinishedLektion($trainings, $userids){

        global $wpdb;
        $table_name_trainings = $wpdb->prefix . self::$dbprefix_user;
        $table_name_trainingsseiten = $wpdb->prefix . self::$dbprefix_seiten;

        $sql1 = "SELECT training_id, lektion_id
                 FROM $table_name_trainingsseiten
                 WHERE training_id IN (" . implode(",", $trainings) . ");
        "; 
        $result1 = $wpdb->get_results($sql1);

        $trainingTemp = array();

		if($result1){
			foreach($result1 as $row){

				if(!isset($trainingTemp[$row->training_id])){
					$trainingTemp[$row->training_id] = array();
				}
				if(!isset($trainingTemp[$row->training_id][$row->lektion_id])){
					$trainingTemp[$row->training_id][$row->lektion_id] = array();
				}

				$trainingTemp[$row->training_id][$row->lektion_id]["findate"] = null;
			}
		}

        // fin (date) for all $trainings and $userids 
        $sql2 = "SELECT user_id, training_id, lektion_id, lektion_index, lektion_fin_date
                 FROM $table_name_trainings
                 WHERE user_id IN (" . implode(",", $userids) . ") 
                    AND training_id IN (" . implode(",", $trainings) . ")";

        $result2 = $wpdb->get_results($sql2);

        $ret = array();

        foreach($userids as $user){
			$ret[$user] = $trainingTemp;
		}

        if($result2){
            foreach($result2 as $row2){
                if(!is_null($row2->lektion_fin_date)){
                    $ret[$row2->user_id][$row2->training_id][$row2->lektion_id]["findate"] = date("d.m.Y", strtotime($row2->lektion_fin_date));
                }
                
            }
        }
        return $ret;
    }

    /*
     * Get the last visited page for all $trainings and $userids in one query for the DataExport Module
     * 
     * @param Integer-Array Training-IDs
     * @param Integer-Array User-IDs
     * 
     * @return Array
     *          -> User-ID
     *              |-> Training-ID
     *                  |-> Lektion-ID
     *                      |-> Lektion-Index
     *                          |-> entry
     *                      |-> Last visited page
     *                          |-> entry
    */
    public function getSystemstatisticLastVisitedPage($trainings, $userids){

        global $wpdb;
        $table_name_trainings = $wpdb->prefix . self::$dbprefix_user;
        $table_name_posts = $wpdb->prefix . 'posts';

        $sql = "SELECT tu.user_id, tu.training_id, tu.lektion_id, tu.lektion_index, p.post_title
                FROM $table_name_trainings tu
                INNER JOIN $table_name_posts p ON tu.last_seiten_id = p.ID 
                WHERE user_id IN (" . implode(", ", $userids) . ")
                    AND tu.training_id IN (" . implode(",", $trainings) . ")
        ";

        $result = $wpdb->get_results($sql);

        $ret = array();
        if($result){
            foreach($result as $row){

                if(!isset($ret[$row->user_id])){
                    $ret[$row->user_id] = array();
                }

                if(!isset($ret[$row->user_id][$row->training_id])){
                    $ret[$row->user_id][$row->training_id] = array();
                }

                if(!isset($ret[$row->user_id][$row->training_id][$row->lektion_id])){
                    $ret[$row->user_id][$row->training_id][$row->lektion_id] = array();
                }
                
                $ret[$row->user_id][$row->training_id][$row->lektion_id]["lektion_index"] = $row->lektion_index;
                $ret[$row->user_id][$row->training_id][$row->lektion_id]["page"] = $row->post_title;
                
            }
        }
        return $ret;
    }

    /*
     * Get the training progress for all $trainings and $userids in one query for the DataExport Module
     * 
     * @param Integer-Array Training-IDs
     * @param Integer-Array User-IDs
     * 
     * @return Array
     *          -> User-ID
     *              |-> Training-ID
     *                      |-> progress => value
     *              |-> Training-ID
     *                      |-> progress => value
    */
    public function getSystemstatisticTrainingProgress($trainings, $userids) {

        $ret = array();
        $userData = Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getMyNutzer(false, $userids);

        foreach($userids as $userid) {
            foreach($trainings as $trainingid) {
                if(!isset($ret[$userid])){
                    $ret[$userid] = array();
                }

                if(!isset($ret[$userid][$trainingid])){
                    $ret[$userid][$trainingid] = array();
                }

                if(isset($userData[$userid]) && $userData[$userid]->trainingExists($trainingid)) {
                    $ret[$userid][$trainingid]['progress'] = $userData[$userid]->getTraining($trainingid)->getFin();
                }
            }
        }
        return $ret;
    }

    public function getTrainingTitle() {
        global $wpdb;

        $sql = "SELECT ID, post_title FROM $wpdb->posts WHERE post_type = 'trainings' AND post_status = 'publish'";
        $data = $wpdb->get_results($sql, OBJECT);
        $result = [];

        foreach ($data as $row){

            $result[$row->ID] = $row->post_title;
        }
        
        return $result;
    }

    public function getMultipleUsedLektionIds() {
        global $wpdb;
        
        $ret = [];

        $sql = "SELECT lektion_id FROM " . $wpdb->prefix . self::$dbprefix . " group by lektion_id having count(*) > 1;";
        $data = $wpdb->get_results($sql);

        foreach($data as $row) {
            $ret[] = $row->lektion_id;
        }

        return $ret;
    }
}
