<?php

/**
 * Register all actions and filters for the plugin
 *
 * @link       -
 * @since      1.0.0
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 */

/**
 * 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.
 *
 * @package    Trainingssystem_Plugin
 * @subpackage Trainingssystem_Plugin/includes
 * @author     Markus Domin, Helge Nissen <->
 */
class Trainingssystem_Plugin_Database_Nutzer_Daoimple implements Trainingssystem_Plugin_Database_Nutzer_Dao{


	public static $dbprefix=TRAININGSSYSTEM_PLUGIN_DB_TRAINING_USER;
	public static $dbprefixlastlogin=TRAININGSSYSTEM_PLUGIN_DB_TRAINING_LASTLOGIN;
	public static $dbprefixseiten=TRAININGSSYSTEM_PLUGIN_DB_TRAINING_SEITEN;
	/**
	 * 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(
				user_id BIGINT(20) NOT NULL,
				training_id BIGINT(20) NOT NULL,
				lektion_id BIGINT(20) NOT NULL,
				training_index INT NOT NULL,
				lektion_index INT NOT NULL,
				training_auto INT NOT NULL,
				lektion_enable INT NOT NULL,
				last_seiten_id BIGINT(20) NOT NULL DEFAULT '0',
				lektion_fin_date DATETIME NOT NULL DEFAULT '2011-01-01 11:55:00',
				feedback varchar(100) NOT NULL DEFAULT '',
				feedback_date DATETIME NOT NULL DEFAULT '2011-01-01 11:55:00',
				PRIMARY KEY (user_id, training_id, lektion_id)
		)$charset_collate;",
		"ALTER TABLE $table_name CHANGE `lektion_fin_date` `lektion_fin_date` DATETIME NULL DEFAULT NULL;",
		"UPDATE $table_name SET lektion_fin_date = NULL WHERE lektion_fin_date = '2011-01-01 11:55:00';",
		"ALTER TABLE $table_name CHANGE `feedback_date` `feedback_date` DATETIME NULL DEFAULT NULL;",
		"UPDATE $table_name SET feedback_date = NULL WHERE feedback_date = '2011-01-01 11:55:00';",
		"ALTER TABLE $table_name CHANGE `feedback` `feedback` BIGINT(20) NOT NULL DEFAULT '0';",
		"ALTER TABLE $table_name ADD `last_update` TIMESTAMP on update CURRENT_TIMESTAMP NULL DEFAULT NULL AFTER `feedback_date`",
		"ALTER TABLE $table_name CHANGE `lektion_enable` `lektion_enable` BOOLEAN NOT NULL DEFAULT TRUE;",
		"UPDATE $table_name SET lektion_enable = '1', last_update = last_update where lektion_enable=0;"
        ];
		Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix,$table_name, $tableversionsstr);


	}

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

	}

	/**
	 * Liefert alle Nutzer des Trainingssystems ohne Vorlagennutzer
	 *
	 * @return Array of Trainingssystem_Plugin_Database_Nutzer
	 */
	public function getAllNutzer($includeuserobject = true, $includetrainings = true, $includelastlogin = true, $includevorlagennutzer = false, $indexed = false){

		$blogusers = array();
		$bloguserids = [];
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistall")) {
			foreach(get_users() as $u) {
				if(!in_array($u->ID, $bloguserids)) {
					$blogusers[] = $u;
					$bloguserids[] = $u->ID;
				}
			}
		} 
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("trainingedit")) {
			foreach(get_users( [ 'role__in' => ['subscriber', 'editor' ]]) as $u) {
				if(!in_array($u->ID, $bloguserids)) {
					$blogusers[] = $u;
					$bloguserids[] = $u->ID;
				}
			}
		} 
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistsubscriber")) {
			foreach(get_users( [ 'role__in' => ['subscriber' ]] ) as $u) {
				if(!in_array($u->ID, $bloguserids)) {
					$blogusers[] = $u;
					$bloguserids[] = $u->ID;
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistcoach")){
			foreach(get_users(array('meta_key' => 'coachid', 'meta_value' => get_current_user_id())) as $u) {
				if(!in_array($u->ID, $bloguserids)) {
					$blogusers[] = $u;
					$bloguserids[] = $u->ID;
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistown")){
			foreach(get_users(array('meta_key' => 'created_by', 'meta_value' => get_current_user_id())) as $u) {
				if(!in_array($u->ID, $bloguserids)) {
					$blogusers[] = $u;
					$bloguserids[] = $u->ID;
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistcompany")){
			foreach(get_users(array('include' => Trainingssystem_Plugin_Database::getInstance()->CompanyDao->getAllUserIdsByCreaterId( get_current_user_id()))) as $u) {
				if(!in_array($u->ID, $bloguserids)) {
					$blogusers[] = $u;
					$bloguserids[] = $u->ID;
				}
			}
		}

		$studienids = $this->getUsersStudienIds();
		$lastlogins = ($includelastlogin) ? Trainingssystem_Plugin_Database::getInstance()->LastLoginDao->getAllLastLoginIndexed() : [];

		$vorlagennutzer = $this->getVorlagenUserRaw();
		$vorlagennutzerids = array_column($vorlagennutzer, 'ID');

		$demouser = $this->getDemoUserRaw();
		$demouserids = array_column($demouser, 'ID');

		$result2 = array();
		// Array of WP_User objects.
		foreach ( $blogusers as $user ) {
			// Keine Vorlagennutzer
		    if(($includevorlagennutzer || !in_array($user->ID, $vorlagennutzerids)) && !in_array($user->ID, $demouserids) && !is_null($user))
			{
				$u = null;
				if($includeuserobject) {
					$u = $user;
				}

				$trainings = array();
				if($includetrainings) {
					$trainings = $this->getTrainings($user->ID);
				}

				$lastlogin = null;
				if($includelastlogin) {
					$lastlogin = $lastlogins[$user->ID] ?? null;
				}

				$studienid = $studienids[$user->ID] ?? "";

				$userObj = new Trainingssystem_Plugin_Database_Nutzer($user->display_name,$user->ID, $user->user_email, $u, $trainings, $lastlogin, $studienid);
				$userObj->setVorlagennutzer(in_array($user->ID, $vorlagennutzerids));
				if($indexed) {
					$result2[$user->ID] = $userObj;
				} else {
					$result2[] = $userObj;
				}

			}
		}
		//var_dump($result2);
		return $result2;

	}

	public function getUsersStudienIds() {
		$ret = array();

		$query_args = array(
			'meta_key' => 'studienid'
		);

		$query = get_users( $query_args );

		foreach($query as $user)
			$ret[$user->ID] = $user->studienid;

		return $ret;
	}

	public function getTrainer(){
		$args = array(
			'meta_key' => TRAININGSSYSTEM_PLUGIN_USER_HAS_ROLES,
			'meta_value' => 'pCoach',
			'meta_compare' => 'LIKE',
		);
		$user_query = new WP_User_Query( $args );

		return $user_query->get_results();
	}

	public function getBosses() {
		$args = array(
			'meta_key' => TRAININGSSYSTEM_PLUGIN_USER_HAS_ROLES,
			'meta_value' => 'boss',
			'meta_compare' => 'LIKE',
		);
		$user_query = new WP_User_Query( $args );

		return $user_query->get_results();
	}

	public function getTeamleader() {
		$args = array(
			'meta_key' => TRAININGSSYSTEM_PLUGIN_USER_HAS_ROLES,
			'meta_value' => 'teamLeader',
			'meta_compare' => 'LIKE',
		);
		$user_query = new WP_User_Query( $args );

		return $user_query->get_results();
	}

	public function getKAdmins(){
		$args = array(
			'meta_key' => TRAININGSSYSTEM_PLUGIN_USER_HAS_ROLES,
			'meta_value' => 'kAdmin',
			'meta_compare' => 'LIKE',
		);
		$user_query = new WP_User_Query( $args );

		return $user_query->get_results();
	}

	public function getEligibleUserIds($filter='', $column = 'all', $hideMails = false, $showCompanyInformation = true, $showUsername = true){
		global $wpdb;
		$userIds = array();

		$userstable = $wpdb->prefix . "users";
		$usermetatable = $wpdb->prefix . "usermeta";
		$metakeyname = $wpdb->prefix . 'capabilities';
		$lastlogintable = $wpdb->prefix . $this::$dbprefixlastlogin;
		$trainingsusertable = $wpdb->prefix . $this::$dbprefix;
		$poststable = $wpdb->prefix . "posts";
		$trainingsseitentable = $wpdb->prefix . $this::$dbprefixseiten;
		$table_company = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanyDao::$dbprefix;
		$table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
		$table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupUserDao::$dbprefix;

		$vorlagennutzer = $this->getVorlagenUserRaw();
		$vorlagennutzerids = array_column($vorlagennutzer, 'ID');

		$demouser = $this->getDemoUserRaw();
		$demouserids = array_column($demouser, 'ID');

		$sql = "";

		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistall")) {
			$sql = "SELECT DISTINCT users.ID as userid, lastlogin.lastlogin as lastlogin, users.user_nicename as nicename
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id";
			if($filter != '') {
				$sql .= " LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id
							LEFT JOIN $table_g2u as g2u on users.ID = g2u.userid
							LEFT JOIN $table_groups as g on g2u.gruppenid = g.id
							LEFT JOIN $table_company as f on f.id = g.firmaid
							LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id";
			}
			if($filter != '') {
				$sql .= " WHERE " . $this->getEligibleUserIdsFilter(false, $filter, $column, $hideMails, $showCompanyInformation, $showUsername);
			}
			$sql .= " ORDER BY lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";

			$result = $wpdb->get_results($sql);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids) && !in_array($row->userid, $userIds)) {
						$userIds[$this->getUserIdsArrayKey($row)] = $row->userid;
					}
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistsubscriber"))
		{
			$sql = "SELECT DISTINCT users.ID as userid, lastlogin.lastlogin as lastlogin, users.user_nicename as nicename
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
								LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id";
			if($filter != '') {
				$sql .= " LEFT JOIN $table_g2u as g2u on users.ID = g2u.userid
									LEFT JOIN $table_groups as g on g2u.gruppenid = g.id
									LEFT JOIN $table_company as f on f.id = g.firmaid
									LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id";
			}
			$sql .= " WHERE umeta.meta_key = '$metakeyname' AND umeta.meta_value LIKE '%subscriber%'";
			if($filter != '') {
				$sql .= $this->getEligibleUserIdsFilter(true, $filter, $column, $hideMails, $showCompanyInformation, $showUsername);
			}
			$sql .= " ORDER BY lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";

			$result = $wpdb->get_results($sql);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids) && !in_array($row->userid, $userIds)) {
						$userIds[$this->getUserIdsArrayKey($row)] = $row->userid;
					}
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistcompany"))
		{
			$sql = "SELECT DISTINCT users.ID as userid, lastlogin.lastlogin as lastlogin, users.user_nicename as nicename
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
								LEFT JOIN $table_g2u as g2u on users.ID = g2u.userid
								LEFT JOIN $table_groups as g on g2u.gruppenid = g.id
								LEFT JOIN $table_company as f on f.id = g.firmaid";
			if($filter != '') {
				$sql .= " LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id
									LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id";
			}
			$sql .= " WHERE (f.createrid = " . get_current_user_id() . " or g.createrid = " . get_current_user_id() . ")";
			if($filter != '') {
				$sql .= $this->getEligibleUserIdsFilter(true, $filter, $column, $hideMails, $showCompanyInformation, $showUsername);
			}
			$sql .= " ORDER BY lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";

			$result = $wpdb->get_results($sql);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids) && !in_array($row->userid, $userIds)) {
						$userIds[$this->getUserIdsArrayKey($row)] = $row->userid;
					}
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistcoach"))
		{
			$sql = "SELECT DISTINCT users.ID as userid, lastlogin.lastlogin as lastlogin, users.user_nicename as nicename
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
								LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id";
			if($filter != '') {
				$sql .= " LEFT JOIN $table_g2u as g2u on users.ID = g2u.userid
									LEFT JOIN $table_groups as g on g2u.gruppenid = g.id
									LEFT JOIN $table_company as f on f.id = g.firmaid
									LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id";
			}
			$sql .= " WHERE umeta.meta_key = 'coachid' AND umeta.meta_value = " . get_current_user_id();
			if($filter != '') {
				$sql .= $this->getEligibleUserIdsFilter(true, $filter, $column, $hideMails, $showCompanyInformation, $showUsername);
			}
			$sql .= " ORDER BY lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";

			$result = $wpdb->get_results($sql);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids) && !in_array($row->userid, $userIds)) {
						$userIds[$this->getUserIdsArrayKey($row)] = $row->userid;
					}
				}
			}
		}
		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistown"))
		{
			$sql = "SELECT DISTINCT users.ID as userid, lastlogin.lastlogin as lastlogin, users.user_nicename as nicename
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
								LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id";
			if($filter != '') {
				$sql .= " LEFT JOIN $table_g2u as g2u on users.ID = g2u.userid
									LEFT JOIN $table_groups as g on g2u.gruppenid = g.id
									LEFT JOIN $table_company as f on f.id = g.firmaid
									LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id";
			}
			$sql .= " WHERE umeta.meta_key = 'created_by' AND umeta.meta_value = " . get_current_user_id();
			if($filter != '') {
				$sql .= $this->getEligibleUserIdsFilter(true, $filter, $column, $hideMails, $showCompanyInformation, $showUsername);
			}
			$sql .= " ORDER BY lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";

			$result = $wpdb->get_results($sql);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids) && !in_array($row->userid, $userIds)) {
						$userIds[$this->getUserIdsArrayKey($row)] = $row->userid;
					}
				}
			}
		}

		krsort($userIds);

		return array_values($userIds);
	}

	private function getUserIdsArrayKey($row) {
		return (is_null($row->lastlogin) ? '1970-01-01 00:00:00' : $row->lastlogin). $row->nicename . $row->userid;
	}

	private function getEligibleUserIdsFilter($secondargument, $filter, $column, $hideMails, $showCompanyInformation, $showUsername) {
		$return = $secondargument ? " AND " : "";
		$return .= "(";
		switch($column) {
			case "all":
				$return .= "users.ID LIKE '$filter%' "; // If All Columns: search for User-IDs beginning with $filter
				if(!$hideMails) {
					$return .= "OR users.user_email LIKE '%$filter%' ";
				}
				if($showUsername){
					$return .= "OR users.display_name LIKE '%$filter%' ";
				}
				if($showCompanyInformation) {
					$return .= "OR f.name LIKE '%$filter%' ";
				}
				$return .= "OR tposts.post_title LIKE '%$filter%' ";
				
				$return .= "OR (umeta.meta_key = 'studienid' AND umeta.meta_value LIKE '%$filter%')";
				break;
			
			case "userid":
					$return .= "users.ID = '$filter'"; // If User-ID-Column: search for excact matches of User-ID
					break;

			case "mail":
				$return .= "users.user_email LIKE '%$filter%'";
				break;

			case "username":
				$return .= "users.display_name LIKE '%$filter%'";
				break;

			case "company":
				$return .= "f.name LIKE '%$filter%'";
				break;

			case "training":
				$return .= "tposts.post_title LIKE '%$filter%'";
				break;

			case "studienid":
				$return .= "(umeta.meta_key = 'studienid' AND umeta.meta_value LIKE '%$filter%')";
				break;
		}
		$return .= ")";
		return $return;
	}

	public function getEligibleUserIdsCoach($filter='', $column = 'all', $showUserID = true, $showStudyID = true, $showTrainingsCoaching = true){
		global $wpdb;
		$userIds = array();
		$userIdIndex = 0;

		$userstable = $wpdb->prefix . "users";
		$usermetatable = $wpdb->prefix . "usermeta";
		$metakeyname = $wpdb->prefix . 'capabilities';
		$lastlogintable = $wpdb->prefix . $this::$dbprefixlastlogin;
		$trainingsusertable = $wpdb->prefix . $this::$dbprefix;
		$poststable = $wpdb->prefix . "posts";
		$trainingsseitentable = $wpdb->prefix . $this::$dbprefixseiten;

		$vorlagennutzer = $this->getVorlagenUserRaw();
		$vorlagennutzerids = array_column($vorlagennutzer, 'ID');

		$demouser = $this->getDemoUserRaw();
		$demouserids = array_column($demouser, 'ID');

		$countingWaiting = "";
		$countingNotWaiting = "";

		if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("userlistcoach")){
			$countingWaiting = "SELECT DISTINCT users.ID as userid
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
								LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id";
			if($filter != '') {
				$countingWaiting .= " LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id";
			}
			$countingWaiting .= " WHERE users.ID IN (
									SELECT DISTINCT users.ID FROM $userstable as users
									LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
									LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id 
									WHERE t2u.training_auto = 2 AND t2u.lektion_fin_date IS NOT NULL AND (t2u.feedback = 0 OR t2u.feedback IS NULL)
								) AND umeta.meta_key = 'coachid' AND umeta.meta_value = " . get_current_user_id();
			if($filter != '') { 
				$countingWaiting .= $this->getEligibleUserIdsCoachFilter(true, $filter, $column, $showUserID, $showStudyID, $showTrainingsCoaching);
			}
			$countingWaiting .= " ORDER BY t2u.lektion_fin_date ASC, lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";

				$countingNotWaiting = "SELECT DISTINCT users.ID as userid
										FROM $userstable as users
										LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
										LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
										LEFT JOIN $usermetatable as umeta on users.ID = umeta.user_id";
				if($filter != '') {
				$countingNotWaiting .= " LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id ";
				}
				$countingNotWaiting .= " WHERE users.ID NOT IN (
										SELECT DISTINCT users.ID FROM $userstable as users
										LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
										LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id 
										WHERE t2u.training_auto = 2 AND t2u.lektion_fin_date IS NOT NULL AND (t2u.feedback = 0 OR t2u.feedback IS NULL)
									) AND umeta.meta_key = 'coachid' AND umeta.meta_value = " . get_current_user_id();
				if($filter != '') { 
				$countingNotWaiting .= $this->getEligibleUserIdsCoachFilter(true, $filter, $column, $showUserID, $showStudyID, $showTrainingsCoaching);
				}
				$countingNotWaiting .= " ORDER BY lastlogin.lastlogin DESC, users.user_nicename ASC, t2u.training_index ASC, t2u.lektion_index ASC";
		}

		if($countingNotWaiting != "" && $countingWaiting != "") {
			$result = $wpdb->get_results($countingWaiting);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids)) {
						$userIds[$userIdIndex] = array();
						$userIds[$userIdIndex]['status'] = 'waiting';
						$userIds[$userIdIndex]['userid'] = $row->userid;
						$userIdIndex++;
					}
				}
			}

			
			$result = $wpdb->get_results($countingNotWaiting);
			if($result != null) {
				foreach($result as $row) {
					if(!in_array($row->userid, $vorlagennutzerids) && !in_array($row->userid, $demouserids)) {
						$userIds[$userIdIndex] = array();
						$userIds[$userIdIndex]['status'] = 'idle';
						$userIds[$userIdIndex]['userid'] = $row->userid;
						$userIdIndex++;
					}
				}
			}
		}

		return $userIds;
	}

	private function getEligibleUserIdsCoachFilter($secondargument, $filter, $column, $showUserID, $showStudyID, $showTrainingsCoaching) {
		$return = $secondargument ? " AND " : "";
		$return .= "(";
		switch($column) {
			case "all":
				$return .= "users.display_name LIKE '%$filter%' ";
				if($showTrainingsCoaching){
					$return .= "OR tposts.post_title LIKE '%$filter%' ";
				}
				if($showUserID){
					$return .= "OR users.ID LIKE '$filter%' "; // If All Columns: search for User-IDs beginning with $filter
				}
				if($showStudyID){
					$return .= "OR (umeta.meta_key = 'studienid' AND umeta.meta_value LIKE '%$filter%')";
				}				
				break;
			
			case "userid":
					$return .= "users.ID = '$filter'"; // If User-ID-Column: search for excact matches of User-ID
					break;

			case "username":
				$return .= "users.display_name LIKE '%$filter%'";
				break;

			case "training":
				$return .= "tposts.post_title LIKE '%$filter%'";
				break;

			case "studienid":
				$return .= "(umeta.meta_key = 'studienid' AND umeta.meta_value LIKE '%$filter%')";
				break;
		}
		$return .= ")";
		return $return;
	}

	public function getMyNutzer($me=false, $userids = array()){
	
		if(empty($userids) && !$me) {
			return array();
		}
			
		global $wpdb;
		$result2 = array();
		$lektionsseiten = array();

		$userstable = $wpdb->prefix . "users";
		$usermetatable = $wpdb->prefix . "usermeta";
		$metakeyname = $wpdb->prefix . 'capabilities';
		$lastlogintable = $wpdb->prefix . $this::$dbprefixlastlogin;
		$trainingsusertable = $wpdb->prefix . $this::$dbprefix;
		$poststable = $wpdb->prefix . "posts";
		$trainingsseitentable = $wpdb->prefix . $this::$dbprefixseiten;
		$table_company = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanyDao::$dbprefix;
		$table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
		$table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupUserDao::$dbprefix;

		$sql = "";
		if($me) {
			$userids = array(
				0 => get_current_user_id()
			);
		}

		$studienids = array();
		$studienidsql = "SELECT user_id, meta_value
						FROM $usermetatable
						WHERE meta_key = 'studienid'
							AND user_id IN (" . implode(",", $userids) . ")";
		$studienresult = $wpdb->get_results($studienidsql);
		if($studienresult != null) {
			foreach($studienresult as $row) {
				$studienids[$row->user_id] = $row->meta_value;
			}
		}

		$studiengruppenids = array();
		$studiengruppenidsql = "SELECT user_id, meta_value
						FROM $usermetatable
						WHERE meta_key = 'studiengruppenid'
							AND user_id IN (" . implode(",", $userids) . ")";
		$studiengruppenresult = $wpdb->get_results($studiengruppenidsql);
		if($studiengruppenresult != null) {
			foreach($studiengruppenresult as $row) {
				$studiengruppenids[$row->user_id] = $row->meta_value;
			}
		}

		$companies = $this->getUserCompanySystem($userids);

		$sql = "SELECT users.ID as userid, users.display_name as name, users.user_email as mail, users.user_registered as registrationdate, lastlogin.lastlogin as lastlogin,
										t2u.training_id as training_id, tposts.post_title as ttitle, t2u.lektion_id as lektion_id, lposts.post_title as ltitle,
										t2u.feedback as feedback, DATE(t2u.lektion_fin_date) as lektion_fin_date, t2u.training_auto as training_auto,
										t2u.last_seiten_id, t2u.lektion_enable, seiten.seiten_id, seiten.seiten_index
								FROM $userstable as users
								LEFT JOIN $lastlogintable as lastlogin on users.ID = lastlogin.user_id
								LEFT JOIN $trainingsusertable as t2u on users.ID = t2u.user_id
								LEFT JOIN $poststable as tposts on tposts.ID = t2u.training_id
								LEFT JOIN $poststable as lposts on lposts.ID = t2u.lektion_id
								LEFT JOIN $trainingsseitentable as seiten on seiten.lektion_id = t2u.lektion_id
								WHERE users.ID IN (" . implode(',', $userids) . ")
								ORDER BY FIELD(users.ID," . implode(',', $userids) . "), t2u.training_index, t2u.lektion_index, seiten.seiten_index";

		$result = $wpdb->get_results($sql);
		if($result != null) {
			foreach($result as $row) {
				// found new user
				if(!isset($result2[$row->userid])) {
					$result2[$row->userid] = new Trainingssystem_Plugin_Database_List_User($row->userid, $row->name, $row->mail, $row->lastlogin, array(), $studienids[$row->userid] ?? '', $studiengruppenids[$row->userid] ?? '', $row->registrationdate);
					$result2[$row->userid]->setCompanys($companies[$row->userid] ?? array());
				}

				// found new training
				if(!is_null($row->training_id) && !$result2[$row->userid]->trainingExists($row->training_id)) {
					$newtraining = new Trainingssystem_Plugin_Database_List_Training($row->training_id, $row->ttitle, array(), $row->training_auto, $row->lektion_enable);
					$result2[$row->userid]->addTraining($row->training_id, $newtraining);
				}

				// found new lektion
				if(!is_null($row->training_id) && !is_null($row->lektion_id) && !$result2[$row->userid]->getTraining($row->training_id)->lektionExists($row->lektion_id)) {
					$newlektion = new Trainingssystem_Plugin_Database_List_Lektion($row->lektion_id, $row->ltitle, null, $row->lektion_fin_date, $row->feedback, null, $row->training_auto, $row->lektion_enable);
					$result2[$row->userid]->getTraining($row->training_id)->addLektion($row->lektion_id, $newlektion);
				}

				if(!is_null($row->training_id) && !is_null($row->lektion_id)) {
					// always find an new seite, last_seiten_ids stays the same with every lektion
					$lektionsseiten[$row->userid][$row->training_id][$row->lektion_id]['last_seiten_id'] = $row->last_seiten_id;
					$lektionsseiten[$row->userid][$row->training_id][$row->lektion_id]['seiten'][$row->seiten_index] = $row->seiten_id;
				}
			}

			// calculate fin for each user's training's lektionen
			foreach($lektionsseiten as $userid => $trainingelement)
			{
				foreach($trainingelement as $trainingsid => $lektionselement)
				{
					foreach($lektionselement as $lektionsid => $seitenelement)
					{
						$fin = 0;
						if($seitenelement['last_seiten_id'] != 0)
						{
							$total = count($seitenelement['seiten']);
							$arraypos = array_search($seitenelement['last_seiten_id'], $seitenelement['seiten']) + 1;

							$fin = intval($arraypos * 100 / $total);

						}

						$lektion = $result2[$userid]->getTraining($trainingsid)->getLektion($lektionsid);
						if($lektion != null)
							$lektion->setFin($fin);
					}
				}
			}
		}


		return $result2;
	}

	public function getNutzer($id, $includeDetailledUserInfo = true){
		$users = get_users(['include' => [$id]]);
		if (count($users)>0) {
			$user = $users[0];
			return new Trainingssystem_Plugin_Database_Nutzer(
				$user->display_name,
				$user->ID, 
				$user->user_email,
				$user, 
				($includeDetailledUserInfo) ? $this->getTrainings($user->ID) : array(), 
				($includeDetailledUserInfo) ? Trainingssystem_Plugin_Database::getInstance()->LastLoginDao->getLastlogin($user->ID) : null, 
				($includeDetailledUserInfo) ? get_user_meta($user->ID, 'studienid', true) : null
			);
		} else
			return null;
	}

	public function getNutzers($ids) {
		if(sizeof($ids) > 0) {
			$user_records = get_users(['include' => $ids]);

			$studienids = $this->getUsersStudienIds();
			$lastlogins = Trainingssystem_Plugin_Database::getInstance()->LastLoginDao->getAllLastLoginIndexed();

			$users = array();
			foreach ($user_records as $user) {
				$users[] = new Trainingssystem_Plugin_Database_Nutzer($user->display_name,$user->ID, $user->user_email,$user,$this->getTrainings($user->ID), $lastlogins[$user->ID] ?? null, $studienids[$user->ID] ?? '');
			}

			return $users;
		}
		return null;
	}

	public function updateNutzer($nutzer){

	}
	public function deleteNutzer($user_id){
		global $wpdb;
		$usertable=$wpdb->prefix.$this::$dbprefix;
		$wpdb->update( $usertable, array( 'lektion_enable' => 0),array( 'user_id' => $user_id ));
	}

	public function checkpermissions($trainingsID,$userid,$postID, $canSeeHiddenTrainings){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$tablenametrainingsseiten=$wpdb->prefix.$this::$dbprefixseiten;
		
		if($trainingsID != null && trim($trainingsID) != "" && is_numeric($trainingsID) &&
			$userid != null && trim($userid) != "" && is_numeric($userid) &&
			$postID != null && trim($postID) != "" && is_numeric($postID)) {

			$sql = "SELECT nutzer.*, seiten.*
							FROM $tablename as nutzer
							LEFT JOIN $tablenametrainingsseiten as seiten
							ON nutzer.lektion_id = seiten.lektion_id
							WHERE nutzer.training_id=$trainingsID
							AND nutzer.user_id = $userid
							AND seiten.seiten_id = $postID";
			if(!$canSeeHiddenTrainings) {
							$sql .= " AND nutzer.lektion_enable = 1";
			}


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


			if(count($result)>0) return true;
		}
		return false;
	}

	/**
	 * Maybe Updates the
	 * 
	 * @param  Trainingssystem_Plugin_Database_Trainingseite 	$currentPage 	the current Page the user is on
	 * @param  Integer									 		$userid 		Training User-ID
	 * @param  Boolean 											$usermodus
	 */
	public function maybeUpdateLastPageOfLektion($currentPage, $userid, $usermodus = false){
		global $wpdb;
		$tablename = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_TRAINING_USER;

		if(is_null($currentPage)) {
			return;
		}

		$sql =$wpdb->prepare("SELECT last_seiten_id 
								FROM $tablename 
								WHERE training_id=%d 
								AND user_id = %d 
								AND lektion_id =%d",
								$currentPage->getTrainingid(), $userid, $currentPage->getLektionid()
							);
		$dblastpageid = $wpdb->get_var($sql);
		
		if($dblastpageid == "0") {

			if(!$usermodus) {

				$wpdb->update(
					$tablename,
					array(
						'last_seiten_id' => $currentPage->getId()
					),
					array(
						'lektion_id' => $currentPage->getLektionid(),
						'training_id'=> $currentPage->getTrainingid(),
						'user_id' => $userid
					)
				);
			}
		} else {
			if($currentPage->getId() != $dblastpageid) {

				$dblastpage = Trainingssystem_Plugin_Database::getInstance()->TrainingseitenDao->getTrainingsseite($currentPage->getTrainingid(), $currentPage->getLektionid(), $dblastpageid);
				
				if(!is_null($dblastpage) && !$usermodus && $currentPage->getIndex() > $dblastpage->getIndex()){

					$wpdb->update(
						$tablename,
						array(
							'last_seiten_id' => $currentPage->getId()
						),
						array(
							'lektion_id' => $currentPage->getLektionid(),
							'training_id'=> $currentPage->getTrainingid(),
							'user_id' => $userid
						)
					);
				}
			}
		}
		if(!$usermodus) {
			Trainingssystem_Plugin_Database::getInstance()->TrainingseitenDao->checkfin($currentPage);
		}
	}

	public function getTrainings($userid){
		global $wpdb;
		$trainingsarr=array();
		$tablename = $wpdb->prefix.$this::$dbprefix;
		$sql = $wpdb->prepare("SELECT training_id FROM $tablename where user_id = %d GROUP BY training_id ORDER BY training_index ASC",$userid);
		$result = $wpdb->get_col($sql);
		if(count($result)>0){
			$trainingsarr = array();
			for($i=0;$i<count($result);$i++){
				$trainingsarr[]=Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getTrainingByUser($result[$i],$userid);
			}
		}

		return $trainingsarr;
	}

	public function getTrainingCopy($training) {
		$copytraining = array();

		$lektionsliste = $training->getLektionsliste();

		$copytraining["id"] = $training->getId();
		$copytraining["lektionen"] = array();
		$copytraining["enable"] = $training->trainingEnabled();
		$lekid = 0;
		foreach ($lektionsliste as $lektion) {
			$copytraining["lektionen"][$lekid]["id"] = $lektion->getId();
			$copytraining["lektionen"][$lekid]["auto"] = $lektion->getTrainingAuto();
			$copytraining["lektionen"][$lekid]["lektionEnable"] = $lektion->getEnable();
			$lekid++;
		}

		return $copytraining;
	}

	public function setTraining($training){
		$userid = get_current_user_id();
		$this->setTrainingByUserId($userid,$training);
	}

	public function setTrainingByUserId($userid, $training){
		
		$alltrainings = $this->getTrainings($userid);
		$trainingindex = sizeof($alltrainings);
		$auto_modus = 0;
		if(isset($training["auto"])) {
			$auto_modus = $training["auto"];
		} elseif(isset($training["lektionen"][0]["auto"])) {
			$auto_modus = $training["lektionen"][0]["auto"];
		}
		$lektion_enable = false;
		if(isset($training['enable'])) {
			$lektion_enable = $training['enable'];
		} else if(isset($training["lektionen"][0]["lektionEnable"])) {
			$lektion_enable = $training["lektionen"][0]["lektionEnable"];
		}
		$this->setTrainingWithValuesByUserId($userid, $training["id"], $training["lektionen"], $trainingindex, $auto_modus, $lektion_enable, 0);
		
	}

	public function setTrainingWithValuesByUserId($userid, $trainingid, $lektionsarray, $trainingindex, $auto_modus, $lektion_enable, $last_seiten_id) {
		
		for($i=0; $i < count($lektionsarray); $i++) {
			$this->setLektionWithValuesByUserId($userid, $trainingid, $lektionsarray[$i]["id"], $trainingindex, $i, $auto_modus, $lektion_enable, 0);
		 }
	}

	public function setLektionWithValuesByUserId($userid, $trainingid, $lektionsid, $trainingindex, $lektionindex, $auto_modus, $lektion_enable, $last_seiten_id) {
		global $wpdb;
		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;
		
		$wpdb->replace($tablename,
				array(
					'user_id' 			=> $userid,
					'training_id' 		=> $trainingid,
					'lektion_id'		=> $lektionsid,
					'training_index'	=> $trainingindex,
					'lektion_index'		=> $lektionindex,
					'training_auto'		=> $auto_modus,
					'lektion_enable'	=> $lektion_enable,
					'last_seiten_id'	=> $last_seiten_id,
					'last_update' 		=> current_time( 'mysql' ),
				),
				array(
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					'%d',
					"%s"
				)
			);
	}

	public function updateTrainingByUserId($userid, $trainings){
		global $wpdb;
		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;

		$alltrainings = $this->getTrainings($userid);

		$trainings_new = array();
		foreach($trainings as $t) {
			$trainings_new[] = $t;
		}
		
		// Training gelöscht?
		foreach($alltrainings as $t_old) {
			$found = false;
			foreach($trainings_new as $t_new) {
				if($t_old->getId() == $t_new['id']) {
					$found = true;
					break;
				}
			}

			if(!$found) { // Training nicht gefunden -> also gelöscht
				$this->removeTraining($userid, $t_old->getId());
			}
		}

		foreach($trainings_new as $t_index => $t_new) {
			$found = false;
			$found_index = array();
			foreach($alltrainings as $t_old_i => $t_old) {
				if($t_old->getId() == $t_new['id']) {
					$found = true;
					$found_index['old'] = $t_old_i;
					$found_index['new'] = $t_index;
					break;
				}
			}

			// Training Auto Modus bestimmen
			$auto_modus = 0;
			if(isset($t_new['auto']) && is_numeric($t_new['auto'])) {
				$auto_modus = $t_new['auto'];
			} else if(sizeof($t_new['lektionen']) > 0 && isset($t_new['lektionen'][0]['auto']) && is_numeric($t_new['lektionen'][0]['auto'])) {
				$auto_modus = $t_new['lektionen'][0]['lektionAuto'];
			}

			if(!$found) { // Neues Training gefunden
				for($i=0; $i < count($t_new['lektionen']); $i++) {
					$this->setLektionWithValuesByUserId($userid, $t_new['id'], $t_new['lektionen'][$i]['id'], $t_new['index'], $i, $t_new['lektionen'][$i]['lektionAuto'], $t_new['enable'], 0);
				}
			} else {
				// Training index und Training Auto Update
				
				$result = $wpdb->update($tablename, array("training_index" => $t_new['index'], "training_auto" => $auto_modus, "lektion_enable" => $t_new['enable']), array("user_id" => $userid, "training_id" => $t_new['id']), array("%d", "%d", "%d"), array("%d", "%d"));
				if(false === $result) {
					echo "sql error training";
					wp_die();
				}

				// Lekionen 
				// ------------------------------------

				// Lektion gelöscht?
				if(sizeof($found_index) > 0 && isset($alltrainings[$found_index['old']]) && isset($trainings_new[$found_index['new']])) {
					foreach($alltrainings[$found_index['old']]->getLektionsliste() as $l_old) {
						$found_l = false;
						foreach($trainings_new[$found_index['new']]['lektionen'] as $l_new) {
							if($l_old->getId() == $l_new['id']) {
								$found_l = true;
								break;
							}
						}

						if(!$found_l) { // Lektion nicht gefunden -> also gelöscht
							$this->removeLektion($userid, $alltrainings[$found_index['old']]->getId(), $l_old->getId());
						}
					}

					foreach($trainings_new[$found_index['new']]['lektionen'] as $l_index => $l_new) {
						$found_l = false;
						foreach($alltrainings[$found_index['old']]->getLektionsliste() as $l_index_i => $l_old) {
							if($l_old->getId() == $l_new['id']) {
								$found_l = true;
								break;
							}
						}

						if(!$found_l) { // Neue Lektion gefunden

							$this->setLektionWithValuesByUserId($userid,$trainings_new[$found_index['new']]['id'], $l_new['id'], $t_new['index'], $l_index, $l_new['lektionAuto'], $t_new['enable'], 0);

						} else {
							// Training index und Training Auto Update
							$result = $wpdb->update($tablename, array("lektion_index" => $l_index, "training_auto" => $l_new['lektionAuto'], "lektion_enable" =>  $t_new['enable']), array("user_id" => $userid, "training_id" => $t_new['id'], "lektion_id" => $l_new['id']), array("%d", "%d", "%d"), array("%d", "%d", "%d"));
				
							if(false === $result) {
								echo "sql error lektion";
								wp_die();
							}
						}
					}
				}
			}
		}
	}

	public function removeLektion($userid, $trainingsid, $lektionsid) {
		global $wpdb;
		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;
		$wpdb->delete($tablename, array( 'user_id' => $userid, 'training_id' => $trainingsid, 'lektion_id' => $lektionsid), array( '%d', '%d', '%d' ) );
	}

	public function removeTraining($userid, $trainingsid) {
		global $wpdb;
		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;
		$wpdb->delete($tablename, array( 'user_id' => $userid, 'training_id' => $trainingsid ), array( '%d', '%d' ) );
	}

	public function removeAllTrainings($userid){
		global $wpdb;
		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;
		$wpdb->delete($tablename, array( 'user_id' => $userid ), array( '%d' ) );
	}

	public function userHasTraining($userid, $trainingid) {
		global $wpdb;
		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;

		$sql = $wpdb->prepare("SELECT COUNT(*) as num_rows FROM $tablename WHERE user_id = %d AND training_id = %d", $userid, $trainingid);

		return $wpdb->get_var($sql) > 0;
	}


	/**
	 * alle vorlagen user als raw wp users zurück geben
	 *
	 * @since    1.0.0
	 */
	public function getVorlagenUserRaw(){
		$args = array(
			'meta_key'	=>	'vorlagenuser',
			'meta_value'	=> true,
			'meta_type'	=> 'BINARY',
			'orderby' => 'display_name',
			'order' => 'ASC'
		);

		return get_users( $args );
	}

	/**
	 * meine vorlagen user als raw wp users zurück geben
	 *
	 * @since    1.0.0
	 */
	public function getMyVorlagenUserRaw(){

		$args = array(
			'orderby' => 'display_name',
			'order' => 'ASC',
			'meta_query' => array(
				'relation' => 'AND',
				array(
					'key'     => 'vorlagenuser',
					'value'   => true,
					'compare' => '='
				),
				array(
					'key'     => 'vorlagenusercrearorid',
					'value'   => get_current_user_id(),
					'compare' => '='
				)
			)
		 );

		return get_users( $args );
	}

	/**
	 * alle vorlagen user als liste zurück geben
	 * 
	 * @param onlymine Boolean if only my vorlagen user should be returned
	 * 
	 * @since    1.0.0
	 */
	public function getVorlagenUser($onlymine = false){

		if($onlymine) {
			$blogusers = $this->getMyVorlagenUserRaw();
		} else {
			$blogusers = $this->getVorlagenUserRaw();
		}

		$result2 = array();
		// Array of WP_User objects.
		foreach ( $blogusers as $user ) {
				$vuser = new Trainingssystem_Plugin_Database_Nutzer($user->display_name,$user->ID, $user->user_email);
				$vuserTrainer = null;
				$vuserTrainerId = get_user_meta($user->ID, 'coachid', true);
				if($vuserTrainerId != "") {
					$tuser = get_userdata($vuserTrainerId);
					if($tuser != false && $tuser != null) {
						$vuserTrainer = $tuser->display_name;
					}
				}
				$vuser->setTrainerName($vuserTrainer);
				$vuser->setTrainerId($vuserTrainerId);
				$result2[] = $vuser;
		}

		return $result2;
	}

	public function getMyVorlagenUser() {
		return $this->getVorlagenUser(true);
	}

	/**
	 * Alle Demo-User zurück geben
	 */
	public function getDemoUserRaw(){
		$user_args = array(
            'meta_key' => 'demo_user',
            'meta_value' => '1',
            'meta_compare' => '=',
            'number' => -1
        );

		return get_users( $user_args );
	}

	/**
	 * Alle Demo-User als Objekte zurück geben
	 */
	public function getDemoUser(){

		$blogusers = $this->getDemoUserRaw();

		$result2 = array();
		// Array of WP_User objects.
		foreach ( $blogusers as $user ) {				
				$result2[] = new Trainingssystem_Plugin_Database_Nutzer($user->display_name,$user->ID, $user->user_email);
		}

		return $result2;
	}

	public function updateUsername($userid, $username) {
		$user_data = wp_update_user(array('ID' => $userid, 'display_name' => $username));
		
		if(is_wp_error($user_data)) {
			return false;
		} else {
			return true;
		}
	}

	public function getAllLektionFin(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "SELECT lektion_fin_date FROM $tablename WHERE lektion_fin_date IS NOT NULL ORDER BY lektion_fin_date DESC";
		return $wpdb->get_results( $sql,ARRAY_N);
	}

	// Anzahl abgeschlossene Lektion für ausgewählte Intervalle
	public function getAllLektionFinInterval($startDate = null, $endDate = null){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;

		// Es wurden keine Grenzen für das Intervall gewählt
		if($startDate == null && $endDate == null){
			$sql = "SELECT lektion_fin_date FROM $tablename WHERE lektion_fin_date IS NOT NULL ORDER BY lektion_fin_date DESC";
		}
		// Es wurde eine untere Grenze für das Intervall gewählt
		else if($startDate != null && $endDate == null){
			$sql = "SELECT lektion_fin_date FROM $tablename WHERE lektion_fin_date IS NOT NULL AND lektion_fin_date >= '$startDate' ORDER BY lektion_fin_date DESC";
		}
		// Es wurde eine obere Grenze für das Intervall gewählt
		else if($startDate == null && $endDate != null){
			$sql = "SELECT lektion_fin_date FROM $tablename WHERE lektion_fin_date IS NOT NULL AND lektion_fin_date <= '$endDate' ORDER BY lektion_fin_date DESC";
		}
		// Es wurden beide Grenzen gewählt
		else if($startDate != null && $endDate != null){
			$sql = "SELECT lektion_fin_date FROM $tablename WHERE lektion_fin_date IS NOT NULL AND lektion_fin_date >= '$startDate' AND lektion_fin_date <= '$endDate' ORDER BY lektion_fin_date DESC";
		}

		return $wpdb->get_results( $sql,ARRAY_N);
	}

	public function getTrainingFinUserCount(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "SELECT COUNT(trainings.user_id) as trainingcount, posts.post_title as title FROM $tablename as trainings
		left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
		where trainings.lektion_fin_date IS NOT NULL
		GROUP BY trainings.training_id";
		return $wpdb->get_results( $sql);
	}

	// Anzahl beendete Trainings für ausgewählte Intervalle
	public function getTrainingFinUserCountInterval($startDate = null, $endDate = null){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;

		// Es wurden keine Grenzen für das Intervall gewählt
		if($startDate == null && $endDate == null){
			$sql = "SELECT COUNT(trainings.user_id) as trainingcount, posts.post_title as title FROM $tablename as trainings
			left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
			where trainings.lektion_fin_date IS NOT NULL
			GROUP BY trainings.training_id";
		}
		// Es wurde eine untere Grenze für das Intervall gewählt
		else if($startDate != null && $endDate == null){
			$sql = "SELECT COUNT(trainings.user_id) as trainingcount, posts.post_title as title FROM $tablename as trainings
			left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
			where trainings.lektion_fin_date IS NOT NULL AND trainings.lektion_fin_date >= '$startDate'
			GROUP BY trainings.training_id";
		}
		// Es wurde eine obere Grenze für das Intervall gewählt
		else if($startDate == null && $endDate != null){
			$sql = "SELECT COUNT(trainings.user_id) as trainingcount, posts.post_title as title FROM $tablename as trainings
			left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
			where trainings.lektion_fin_date IS NOT NULL AND trainings.lektion_fin_date <= '$endDate'
			GROUP BY trainings.training_id";
		}
		// Es wurden sowohl obere als auch untere Grenze für das Intervall gewählt
		else if($startDate != null && $endDate != null){
			$sql = "SELECT COUNT(trainings.user_id) as trainingcount, posts.post_title as title FROM $tablename as trainings
			left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
			where trainings.lektion_fin_date IS NOT NULL AND trainings.lektion_fin_date >= '$startDate' AND trainings.lektion_fin_date <= '$endDate'
			GROUP BY trainings.training_id";
		}
		
		return $wpdb->get_results( $sql);
	}

	public function getTrainingNotFinUserCount(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "SELECT COUNT(trainings.user_id) as trainingcount, posts.post_title as title FROM $tablename as trainings
		left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
		where trainings.lektion_fin_date IS NULL
		GROUP BY trainings.training_id";
		return $wpdb->get_results( $sql);
	}

	public function getTrainingLektionFinUserCount(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "SELECT COUNT(trainings.user_id), posts.post_title,  posts2.post_title FROM $tablename as trainings
		left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
		left join {$wpdb->posts} as posts2 on posts2.ID = trainings.lektion_id
		where trainings.lektion_fin_date IS NOT NULL
		GROUP BY trainings.training_id";
		return $wpdb->get_results( $sql);
	}

	public function getTrainingLektionNotFinUserCount(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "SELECT COUNT(trainings.user_id), posts.post_title,  posts2.post_title FROM $tablename as trainings
		left join {$wpdb->posts} as posts on posts.ID = trainings.training_id
		left join {$wpdb->posts} as posts2 on posts2.ID = trainings.lektion_id
		where trainings.lektion_fin_date IS NULL
		GROUP BY trainings.training_id";
		return $wpdb->get_results( $sql);
	}

	public function getUsercountTrainingcountFin(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "Select count(tdata.user_id) as usercount,tdata.trainingscount From
		(SELECT user_id, COUNT(trainings.training_id) as trainingscount FROM $tablename as trainings
			where trainings.lektion_fin_date IS NOT NULL
			Group By trainings.user_id,trainings.training_id) as tdata
			Group By tdata.trainingscount";
		return $wpdb->get_results( $sql);
	}

	public function getUsercountTrainingcountNotFin(){
		global $wpdb;
		$tablename=$wpdb->prefix.$this::$dbprefix;
		$sql = "Select count(tdata.user_id) as usercount,tdata.trainingscount From
		(SELECT user_id, COUNT(trainings.training_id) as trainingscount FROM $tablename as trainings
			where trainings.lektion_fin_date IS NULL
			Group By trainings.user_id,trainings.training_id) as tdata
			Group By tdata.trainingscount";
		return $wpdb->get_results( $sql);
	}


	public function getUserCompanySystem($userid){
		global $wpdb;

		$array_requested_userids = [];
		$single = null;
		if(is_numeric($userid)) {
			$single = true;
			$array_requested_userids[] = $userid;
		} elseif(is_array($userid)) {
			$single = false;
			$array_requested_userids = $userid;
		} else {
			return array();
		}


		$cosawp_user_grouper_plugin_gruppe_two_user = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_GRUPPE_2_USER;
		$cosawp_user_grouper_plugin_gruppe = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_GRUPPE;
		$cosawp_user_grouper_plugin_firma = $wpdb->prefix.TRAININGSSYSTEM_PLUGIN_DB_FIRMA;

		$sql = "SELECT fguser.userid as userid, fgroup.name as groupname, firma.name as frimaname, wpuser.user_email as bossmail, wpuser.display_name as bossname
				FROM $cosawp_user_grouper_plugin_gruppe_two_user AS fguser
				LEFT JOIN $cosawp_user_grouper_plugin_gruppe AS fgroup
				ON fgroup.id = fguser.gruppenid
				LEFT JOIN $cosawp_user_grouper_plugin_firma AS firma
				ON firma.id = fgroup.firmaid
				LEFT JOIN $wpdb->users as wpuser
				ON wpuser.ID = firma.bossid
				where fguser.userid IN (" . implode(',', $array_requested_userids) . ");";

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

		$result=[];
		foreach ($dbresult as $key => $value) {
			if($single) {
				$result[$value->frimaname]["title"]=$value->frimaname;
				$result[$value->frimaname]["bossmail"]=$value->bossmail;
				$result[$value->frimaname]["bossname"]=$value->bossname;
				$result[$value->frimaname]["groups"][]=$value->groupname;
			} else {
				$result[$value->userid][$value->frimaname]["title"]=$value->frimaname;
				$result[$value->userid][$value->frimaname]["bossmail"]=$value->bossmail;
				$result[$value->userid][$value->frimaname]["bossname"]=$value->bossname;
				$result[$value->userid][$value->frimaname]["groups"][]=$value->groupname;
			}
		}

		return $result;
	}

	public function getTrainingLastUpdateByUserid($userid){
		global $wpdb;
		$table_name = $wpdb->prefix.$this::$dbprefix;

		$sql = $wpdb->prepare("SELECT training_id, last_update FROM $table_name WHERE user_id = %d", $userid);

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

		$result = array();
		foreach($data as $row){
			
			if(!isset($result[$row->training_id])){
				$result[$row->training_id] = $row->last_update;
			}
			else{
				if(!is_null($row->last_update)){
					if(is_null($result[$row->training_id])){
						$result[$row->training_id] = $row->last_update;
					}
					else{
						if($result[$row->training_id] < $row->last_update){
							$result[$row->training_id] = $row->last_update;
						}
					}
				}
			}
			
		}

		return $result;
	}

	/**
	 * Returns 
	 */
	function getTrainingAutoForLektion($userid, $trainingid, $lektionid) {
		global $wpdb;

		$tablename = $wpdb->prefix.Trainingssystem_Plugin_Database_Nutzer_Daoimple::$dbprefix;

		$sql = $wpdb->prepare("SELECT training_auto FROM $tablename WHERE user_id = %d AND training_id = %d AND lektion_id = %d", $userid, $trainingid, $lektionid);
		return $wpdb->get_var($sql);
	}

    function deleteUserInput($user_id){
        global $wpdb;
        $datatable=$wpdb->prefix.$this::$dbprefix;
        $wpdb->delete( $datatable, array( 'user_id' => $user_id ) );
    }

}
