<?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_Company_Daoimple implements Trainingssystem_Plugin_Database_Company_Dao
{

    public static $dbprefix = TRAININGSSYSTEM_PLUGIN_DB_FIRMA;

    public function __construct()
    {
        $this->iniDB();

    }

    public function iniDB()
    {

//         $user_grouper_plugin_firma					
// id	int(11)	NO	PRI		auto_increment
// name	text	NO			
// bossid	int(11)	NO			
// createrid	int(11)	NO			
// createrdate	datetime	NO		

        global $wpdb;
        $table_name = $wpdb->prefix . self::$dbprefix;
        $charset_collate = $wpdb->get_charset_collate();
        $tableversionsstr =[
            "CREATE TABLE IF NOT EXISTS $table_name(
                id INT NOT NULL AUTO_INCREMENT ,
                name TEXT NOT NULL ,
                bossid INT NOT NULL ,
                createrid INT NOT NULL ,
                createrdate DATETIME NOT NULL,
                PRIMARY KEY (id)
                )$charset_collate;"
        ];
        Trainingssystem_Plugin_Database_Coach_impl::updateDBTable(self::$dbprefix, $table_name, $tableversionsstr);

    }


	public function insertCompany($name, $bossid, $kAdminId) {
		global $wpdb;
		$table_name = $wpdb->prefix.$this::$dbprefix;
			
		$updaterows = $wpdb->insert($table_name,
            array(
                'name' => $name, 
                'bossid' => $bossid,
                'createrdate'=> current_time('mysql'),
                'createrid' => $kAdminId
                )
			);
	}
	
	public function updateCompany($company_id, $company_name, $boss_id, $kAdminId) {
        global $wpdb;
        $table_name = $wpdb->prefix.$this::$dbprefix;
        $updates = [];
        $updates['name'] = $company_name;
        if($boss_id !== false) {
            $updates['bossid'] = $boss_id;
        }
        if($kAdminId !== false) {
            $updates['createrid'] = $kAdminId;
            $this->updateGroupkAdmin($company_id, $kAdminId);
        }

        $wpdb->update($table_name, $updates, array('id' => $company_id));
	}
	
	public function deleteCompany($id) {
		
		// alle Gruppen der Firma löschen
		Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao->deleteAllGroups($id);
		
		// aus Tabelle Firma löschen
        global $wpdb;
        $table_name = $wpdb->prefix.$this::$dbprefix;
        $res = $wpdb->delete($table_name, array('id' => $id));
    }
	
    public function getAllCompanies($includeBoss = true, $includekAdmin = true, $includeDetailledUserInfo = true, $companyMemberCount = false) 
    {
        return $this->getCompanies(null, $includeBoss, $includekAdmin, $includeDetailledUserInfo, $companyMemberCount);
    }

    public function getMyCompanies($user_id, $includeBoss = true, $includekAdmin = true, $includeDetailledUserInfo = true, $companyMemberCount = false)
    {
        return $this->getCompanies($user_id, $includeBoss, $includekAdmin, $includeDetailledUserInfo, $companyMemberCount);
    }

    private function getCompanies($forUser, $includeBoss, $includekAdmin, $includeDetailledUserInfo, $companyMemberCount)
    {
        global $wpdb;
        $table_name_firma = $wpdb->prefix.$this::$dbprefix;

        if(!is_null($forUser)) {
            $sql = $wpdb->prepare("SELECT * FROM $table_name_firma	WHERE createrid = %d order by name", $forUser);
        } else {
            $sql = "SELECT * FROM $table_name_firma order by name";
        }
        $result = $wpdb->get_results($sql);


        $companies = array();
        
        foreach ($result as $element) {
            
            //id, name, bossid, kAdmin

            $boss = $includeBoss ? Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($element->bossid, $includeDetailledUserInfo) : null;
            $kAdmin = $includekAdmin ? Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($element->createrid, $includeDetailledUserInfo) : null;
            $c = new Trainingssystem_Plugin_Database_Company(
                        $element->id, 
                        $element->name,
                        $boss, 
                        $kAdmin);
            $c->setGroups($this->getGroups($element->id, false, $includeDetailledUserInfo));
            if($companyMemberCount) {
                $c->setMemberCount($this->countCompanyMembers($element->id));
            }
            $companies[] = $c;
        }

        return $companies;

    }
	
	public function getCompanyById($id) {
		global $wpdb;
		$table_companies = $wpdb->prefix . $this::$dbprefix;	
			
		$sql = $wpdb->prepare("SELECT * FROM $table_companies where id=%d", $id);
		$results = $wpdb->get_results($sql);	
		
		//return null if no result
		foreach ($results as $row) {
            $boss = Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($row->bossid);	
            $kAdmin = Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($row->createrid);
            	
			return new Trainingssystem_Plugin_Database_Company(
								$row->id, 
								$row->name,
                                $boss,
                                $kAdmin);
		}						
    }
    
    public function getCompanyByName($name) {
		global $wpdb;
		$table_companies = $wpdb->prefix . $this::$dbprefix;	
			
		$sql = $wpdb->prepare("SELECT * FROM $table_companies where name=%s", $name);
        $results = $wpdb->get_results($sql);
        if($results == null) return null;	
		
		//return null if no result
		foreach ($results as $row) {
            $boss = Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($row->bossid);	
            $kAdmin = Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($row->createrid);
            	
			return new Trainingssystem_Plugin_Database_Company(
								$row->id, 
								$row->name,
                                $boss,
                                $kAdmin);
		}						
    }

    public function getAllUsersByCreaterId($createrid) {
        global $wpdb;
        $table_companies = $wpdb->prefix . $this::$dbprefix;	
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupUserDao::$dbprefix;
			
		$sql = $wpdb->prepare("SELECT DISTINCT userid FROM $table_g2u as g2u 
                                left join $table_groups as g on g2u.gruppenid = g.id 
                                left join $table_companies as f on f.id = g.firmaid 
                                where (f.createrid = %d or g.createrid = %d) 
                                order by userid ASC", $createrid, $createrid);
        $results = $wpdb->get_results($sql);

        $users = array();

        if($results == null) return $users;	
		
		//return null if no result
		foreach ($results as $row) {
			$users[] = Trainingssystem_Plugin_Database::getInstance()->NutzerDao->getNutzer($row->userid);
        }
        return $users;
    }

    public function getAllUserIdsByCreaterId($createrid) {
        global $wpdb;
        $table_companies = $wpdb->prefix . $this::$dbprefix;	
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupUserDao::$dbprefix;
			
		$sql = $wpdb->prepare("SELECT DISTINCT userid FROM $table_g2u as g2u 
                                left join $table_groups as g on g2u.gruppenid = g.id 
                                left join $table_companies as f on f.id = g.firmaid 
                                where (f.createrid = %d or g.createrid = %d) 
                                order by userid ASC", $createrid, $createrid);
        $results = $wpdb->get_results($sql);

        $users = array();

        if($results == null) return null;	
		
		//return null if no result
		foreach ($results as $row) {
			$users[] = $row->userid;
        }
        return $users;
    }
	
	public function getGroups($company_id, $includecompanyingroup = true, $includeallinfo = true) {
		global $wpdb;
		$table_companies = $wpdb->prefix . $this::$dbprefix;				
		$table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
				
		$sql = $wpdb->prepare("SELECT id FROM $table_groups where firmaid = %d", $company_id);
		$group_ids = $wpdb->get_col($sql);
		
		$groups = array();		
		foreach ($group_ids as $group_id) {
			$groups[] = Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao->getCompanygroupById($group_id, $includecompanyingroup, $includeallinfo);
		}
		
		return $groups;		
    }
    
    public function updateGroupkAdmin($company_id, $kAdminId) {
        global $wpdb;
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $sql = $wpdb->prepare("UPDATE $table_groups SET createrid = %d where firmaid = %d", $kAdminId, $company_id);

        return $wpdb->query($sql);
    }

    /**
     * Helper Funtion for DataExport
     */
    public function getCompanyDataForAllUsers() {

        global $wpdb;
        $table_companies = $wpdb->prefix . $this::$dbprefix;
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupUserDao::$dbprefix;


        $sql = "SELECT userid, $table_companies.ID as companyid, $table_companies.name as companyname, 
                        $table_groups.ID as groupid, $table_groups.name as groupname 
                FROM $table_companies 
                INNER JOIN $table_groups ON $table_companies.ID = $table_groups.firmaid 
                INNER JOIN $table_g2u ON $table_groups.ID = $table_g2u.gruppenid 
                GROUP BY $table_g2u.userid, $table_companies.ID, $table_groups.ID 
                ORDER BY $table_g2u.userid";

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

        $data = array();

        foreach($results as $row) {
            if(!isset($data[$row->userid])) {
                $data[$row->userid] = array();
                $data[$row->userid]['companies'] = array();
                $data[$row->userid]['groups'] = array();
            }
            if(!isset($data[$row->userid]['companies'][$row->companyid])) {
                $data[$row->userid]['companies'][$row->companyid] = $row->companyname;
            }
            if(!isset($data[$row->userid]['groups'][$row->groupid])) {
                $data[$row->userid]['groups'][$row->groupid] = $row->groupname;
            }
        }

        return $data;
    }

    /**
     * Helper Funtion for getting companies and companygroups without all the users
     * 
     * Used by csv import for testing purposes 
     * AND
     * Used by training mgr to add all users in a company or companygroup
     */
    public function getCompanyandCompanygroups($include_users = false) {

        global $wpdb;
        $table_companies = $wpdb->prefix . $this::$dbprefix;
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database_CompanygroupUser_Daoimple::$dbprefix;

        $sql = "SELECT $table_companies.ID as companyid, $table_companies.name as companyname, 
                        $table_groups.ID as groupid, $table_groups.name as groupname";
        if($include_users) {
            $sql .= ", $table_g2u.userid ";
        }
        $sql .= " FROM $table_companies 
                LEFT JOIN $table_groups ON $table_companies.ID = $table_groups.firmaid";
        if($include_users) {
            $sql .= " LEFT JOIN $table_g2u ON $table_groups.id = $table_g2u.gruppenid";
        }

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

        $data = array();

        foreach($results as $row) {
            if(!isset($data[$row->companyid])) {
                $data[$row->companyid] = array();
                $data[$row->companyid]['companyid'] = $row->companyid;
                $data[$row->companyid]['companyname'] = $row->companyname;
                $data[$row->companyid]['groups'] = array();
                if($include_users) {
                    $data[$row->companyid]['users'] = array();
                    $data[$row->companyid]['all_users'] = array();
                }
            }
            if(!isset($data[$row->companyid]['groups'][$row->groupid])) {
                if($row->groupid != null && $row->groupname != null) {
                    $data[$row->companyid]['groups'][$row->groupid] = $row->groupname;
                }
            }
            if($include_users) {
                if(!isset($data[$row->companyid]['users'][$row->groupid])) {
                    if($row->groupid != null && $row->groupname != null) {
                        $data[$row->companyid]['users'][$row->groupid] = array();
                    }
                }
                if($row->groupid != null && $row->groupname != null && $row->userid != null && !in_array($row->userid, $data[$row->companyid]['all_users'])) {
                    $data[$row->companyid]['users'][$row->groupid][] = $row->userid;
                    $data[$row->companyid]['all_users'][] = $row->userid;
                }
            }
        }

        return $data;
    }

    /*
     * Used by training mgr to add all users in a company or companygroup which kadmin is user_id 
     */
    public function getMyCompanyandCompanygroups($user_id, $include_users = false) {

        global $wpdb;
        $table_companies = $wpdb->prefix . $this::$dbprefix;
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database_CompanygroupUser_Daoimple::$dbprefix;

        $sql = "";
        if($include_users){
            $sql = $wpdb->prepare("SELECT $table_companies.id AS companyid, $table_companies.name AS companyname,
                                    $table_groups.id AS groupid, $table_groups.name AS groupname, $table_g2u.userid
                                    FROM $table_companies LEFT JOIN $table_groups ON $table_companies.id = $table_groups.firmaid
                                    LEFT JOIN $table_g2u ON $table_groups.id = $table_g2u.gruppenid
                                    WHERE $table_companies.createrid = %d OR $table_groups.createrid = %d", $user_id, $user_id);
        }
        else{
            $sql = $wpdb->prepare("SELECT $table_companies.id AS companyid, $table_companies.name AS companyname,
                                    $table_groups.id AS groupid, $table_groups.name AS groupname
                                    FROM $table_companies LEFT JOIN $table_groups ON $table_companies.id = $table_groups.firmaid
                                    WHERE $table_companies.createrid = %d OR $table_groups.createrid = %d", $user_id, $user_id);
        }

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

        $data = array();

        foreach($results as $row) {
            if(!isset($data[$row->companyid])) {
                $data[$row->companyid] = array();
                $data[$row->companyid]['companyid'] = $row->companyid;
                $data[$row->companyid]['companyname'] = $row->companyname;
                $data[$row->companyid]['groups'] = array();
                if($include_users) {
                    $data[$row->companyid]['users'] = array();
                    $data[$row->companyid]['all_users'] = array();
                }
            }
            if(!isset($data[$row->companyid]['groups'][$row->groupid])) {
                if($row->groupid != null && $row->groupname != null) {
                    $data[$row->companyid]['groups'][$row->groupid] = $row->groupname;
                }
            }
            if($include_users) {
                if(!isset($data[$row->companyid]['users'][$row->groupid])) {
                    if($row->groupid != null && $row->groupname != null) {
                        $data[$row->companyid]['users'][$row->groupid] = array();
                    }
                }
                if($row->groupid != null && $row->groupname != null && $row->userid != null && !in_array($row->userid, $data[$row->companyid]['all_users'])) {
                    $data[$row->companyid]['users'][$row->groupid][] = $row->userid;
                    $data[$row->companyid]['all_users'][] = $row->userid;
                }
            }
        }

        return $data;
    }

    public function getBossAndKAdminByUser($userid){

        global $wpdb;

        $tableCompany = $wpdb->prefix . $this::$dbprefix;
        $tablegroups = $wpdb->prefix . Trainingssystem_Plugin_Database_Companygroup_Daoimple::$dbprefix;
        $tablegroupuser = $wpdb->prefix . Trainingssystem_Plugin_Database_CompanygroupUser_Daoimple::$dbprefix;

        $sql = $wpdb->prepare("SELECT $tableCompany.bossid, $tableCompany.createrid AS kadminid FROM $tablegroupuser INNER JOIN $tablegroups ON $tablegroupuser.gruppenid = $tablegroups.id INNER JOIN $tableCompany ON $tablegroups.firmaid = $tableCompany.id WHERE $tablegroupuser.userid = %d", $userid);
        
        $data = $wpdb->get_row($sql, OBJECT);

        return $data;

    }

    /**
    * Returns all groups with users of companies which boss is uid
    * 
    * @param uid User-ID of boss 
    * 
    * @return array of all groups of user uid
    */
    public function getGroupsByBossId($uid){
        global $wpdb;

        $table_company = $wpdb->prefix . $this::$dbprefix;
        $table_groups = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupDao::$dbprefix;
        $table_g2u = $wpdb->prefix . Trainingssystem_Plugin_Database::getInstance()->CompanygroupUserDao::$dbprefix;

        // Alle Teilnehmenden der Firma, in der User Boss ist.
        $sql = $wpdb->prepare("SELECT g.id,u.userid,g.name FROM $table_company as f
                                LEFT JOIN $table_groups as g on (f.id = g.firmaid)
                                LEFT JOIN $table_g2u as u on (g.id = u.gruppenid)
                                WHERE f.bossid = '%d'", $uid);
        $res = $wpdb->get_results($sql);

        $groups = array();
        foreach ($res as $row) {
            if (trim($row->userid) != "") {
                $groups[$row->id]['users'][] = $row->userid;
                $groups[$row->id]['name'] = $row->name;
            }
        }

        return $groups;
    }

    /**
    * Returns all user-IDs of employees of the companies which boss is uid
    * 
    * @param uid User-ID
    * 
    * @return Array of all User-IDs
    */
    public function getUsersByBossId($uid){
        global $wpdb;

        $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;

        // Alle Teilnehmenden der Firma, in der User Boss ist.
        $sql = $wpdb->prepare("SELECT u.userid FROM $table_company as f
                                LEFT JOIN $table_groups as g on (f.id = g.firmaid)
                                LEFT JOIN $table_g2u as u on (g.id = u.gruppenid)
                                WHERE f.bossid = '%d' GROUP by u.userid", $uid);

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

        $users = array();
        foreach ($res as $row) {
            if (trim($row->userid) != "") {
                $users[] = $row->userid;
            }
        }

        return $users;
    }

    /**
     * Count the number of members in a given company
     * 
     * @param companyId ID of company to count for
     * 
     * @return Integer, defaults to 0
     */
    private function countCompanyMembers($companyId)
    {
        global $wpdb;
        $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;

        // Alle Teilnehmenden der Firma, in der User Boss ist.
        $sql = $wpdb->prepare("SELECT COUNT(u.userid) FROM $table_company as f
                                LEFT JOIN $table_groups as g on (f.id = g.firmaid)
                                LEFT JOIN $table_g2u as u on (g.id = u.gruppenid)
                                WHERE f.id = '%d' GROUP by f.id", $companyId);

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

    /**
     * database updating beim plugin update
     *
     * @since    1.0.0
     */
    public function updating()
    {
        $this->iniDB();
    }


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

}
