<?php
/**
 * Datenexport
 * 
 * @author      Max Sternitzke <max.sternitzke@th-luebeck.de> (General, TS Forms, ehemals auch NinjaForms) 
 *              <-> 
 *              Tim Mallwitz <tim.mallwitz@th-luebeck.de> (Systemstatistics)
 * @package     DataExport
 */
class Trainingssystem_Plugin_Module_DataExport {

    private $ignoreTraining = array();

    private static $csv_sep = ","; // Between fields
    private static $val_sep = ";"; // Between multiple values in one field
    private static $noval = "-"; // No value available 
    private static $headersep = "_"; // Between IDs and Text in Header
    private static $headerReplace = "_"; // Replacement of illegal chars in Form Title

    private static $ignoreFieldTypesTsForms = array("text", "upload"); // Ignore these TSForm Fields for export
    private static $multipleFieldTypesTsForms = array("scale", "matrix"); // These TSForm fields have more than one option

    /**
     * FRONTEND
     * 
     * Show FrontEnd
     */
    public function showExportOverview($atts) {
        $twig = Trainingssystem_Plugin_Twig::getInstance()->twig;

        if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("dataexport")) {

            $heading = isset($atts['titel']) ? $atts['titel'] : 'Datenexport'; 

            $current_user = wp_get_current_user();

            $alltrainings = Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getAllTrainings();

            $allusers = $this->getAllUsers();

            $firstuser = get_users( array(
                "orderby" => "user_registered",
                "order" => "ASC",
                "number" => 1,
            ));

            $alltsforms = get_posts(
                array(
                    "post_type" => "formulare",
                    "numberposts" => -1,
                    "orderby" => "ID",
                    "order" => "ASC",
                )
            );

            $singletsforms = [];
            $multitsforms = [];
            foreach($alltsforms as $tsform) {
                if($tsform->keepdata == "1") {
                    $multitsforms[] = $tsform;
                } else {
                    $singletsforms[] = $tsform;
                }
            }

            $studiengruppen = array();
            $sg_users = get_users(array("meta_key" => 'studiengruppenid'));
            foreach($sg_users as $sg_u) {
                if(!isset($studiengruppen[$sg_u->studiengruppenid]) && trim($sg_u->studiengruppenid) != "") {
                    $studiengruppen[$sg_u->studiengruppenid] = array();
                }
                if(trim($sg_u->studiengruppenid) != "") {
                    $studiengruppen[$sg_u->studiengruppenid][] = $sg_u->ID;
                }
            }

            $company_and_groups = array();
            if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("companymgrall")){
                $company_and_groups = Trainingssystem_Plugin_Database::getInstance()->CompanyDao->getCompanyandCompanygroups(true);
            }
            else if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed("companymgrown")){
                $company_and_groups = Trainingssystem_Plugin_Database::getInstance()->CompanyDao->getMyCompanyandCompanygroups($current_user->ID, true);
            }

            $settings = get_option(TRAININGSSYSTEM_PLUGIN_SETTINGS);
            $global_company_name = "Unternehmen";
            if(trim($settings['user_grouper_name']) != "") {
                $global_company_name = $settings['user_grouper_name'];
            }

            return $twig->render("data-export/data-export-overview.html", [
                                                                        "heading" => $heading,
                                                                        "alltsforms" => $singletsforms,
                                                                        "multitsforms" => $multitsforms,
                                                                        "alltrainings" => $alltrainings,
                                                                        "allusers" => $allusers,
                                                                        "firstregisterdate" => date("Y-m-d", strtotime($firstuser[0]->user_registered)),
                                                                        "today" => date("Y-m-d"),
                                                                        "tmp_dir" => trailingslashit((wp_get_upload_dir()['basedir'])) . "trainings-ex-import/",
                                                                        'hideMails' => isset($settings['hide_mails']),
                                                                        "global_company_name" => $global_company_name,
                                                                        "companies" => $company_and_groups,
                                                                        "studiengruppen" => $studiengruppen,
                    ]);
            
        } else {
            return $twig->render('modal-unauth.html', ["title" => get_bloginfo('name'), "url" => get_site_url(), "modalid" => uniqid()]);    
        }
    }
    
    /**
     * AJAX-REQUEST
     * 
     * Download DataExport as File
     */
    public function dataexportDownload() {
        
        $twig = Trainingssystem_Plugin_Twig::getInstance()->twig;
        
        if(isset($_POST['data']) && trim($_POST['data']) != "" && isset($_POST['users']) && trim($_POST['users']) != "") {
            $data = json_decode(stripslashes(sanitize_text_field($_POST['data'])),true);
            $users = json_decode(stripslashes(sanitize_text_field($_POST['users'])),true);

            if(count($data) >= 3 && count($users) > 0) {
                $csvdata = array();

                $include_studyid = (isset($data['studyid']) && $data['studyid'] == "true") ? true : false;
                $include_studygroupid = (isset($data['studygroupid']) && $data['studygroupid'] == "true") ? true : false;
                $include_groupdata = (isset($data['groupdata']) && $data['groupdata'] == "true") ? true : false;
                $cleanheader = (isset($data['cleanheader']) && $data['cleanheader'] == "true") ? true : false;

                // COLLECT DATA ----------------------------------------------
                $allusers_raw = $this->getAllUsers();
                $allusers = array();
                foreach($allusers_raw as $user_raw) {
                    $allusers[$user_raw->ID] = $user_raw;
                }

                $tsformheader = array();
                $tsformdata = array();
                if(isset($data['tsforms']) && sizeof($data['tsforms']) > 0) {
                    $tsformheader = Trainingssystem_Plugin_Database::getInstance()->Formfield->getDataExportData($data['tsforms']);
                    $tsformdata = Trainingssystem_Plugin_Database::getInstance()->Formdata->getDataExportData($data['tsforms'], $users);
                }

                $includeSystemstatistics = false;
                if(isset($data['systemstatistics']) && 
                    isset($data['systemstatistics']['statistics']) && sizeof($data['systemstatistics']['statistics']) > 0 && is_array($data['systemstatistics']['statistics']) &&
                    isset($data['systemstatistics']['trainings']) && sizeof($data['systemstatistics']['trainings']) > 0 && is_array($data['systemstatistics']['trainings'])) {
                        
                    $includeSystemstatistics = true;
                }
                // END DATA COLLECTION ----------------------------------------------

                // BUILD HEADER ----------------------------------------------
                $header = ["User-ID", "Benutzername", "Registrierungsdatum"];

                if($include_studyid) {
                    $header[] = "Studien-Teilnehmer-ID";
                }
                if($include_studygroupid) {
                    $header[] = "Studiengruppen-ID";
                }

                $groupdata = array();
                if($include_groupdata) {
                    $header[] = "Firmen-ID";
                    $header[] = "Firmenname";
                    $header[] = "Gruppenname";

                    $groupdata = Trainingssystem_Plugin_Database::getInstance()->CompanyDao->getCompanyDataForAllUsers();
                }

                $header = array_merge($header, $this->getTsFormsHeader($tsformheader, $cleanheader));

                $dataSystemstatisticsStartedLektion = array();
                $dataSystemstatisticsFinishedLektion = array();
                $dataSystemstatisticsLastVisitedPage = array();
                $dataSystemstatisticsTrainingProgress = array();
                if($includeSystemstatistics) {
                    $header = array_merge($header, $this->getSystemstatisticsHeader($data['systemstatistics']['trainings'], $data['systemstatistics']['statistics']));
                    if(in_array("startedLektion", $data['systemstatistics']['statistics'])){
                        $dataSystemstatisticsStartedLektion = Trainingssystem_Plugin_Database::getInstance()->TrainingseitenDao->getSystemstatisticStartedLektion($data['systemstatistics']['trainings'], $users);
                    }
                    if(in_array("finishedLektion", $data['systemstatistics']['statistics'])){
                        $dataSystemstatisticsFinishedLektion = Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getSystemstatisticFinishedLektion($data['systemstatistics']['trainings'], $users);
                    }
                    if(in_array("lastVisitedPage", $data['systemstatistics']['statistics'])){
                        $dataSystemstatisticsLastVisitedPage = Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getSystemstatisticLastVisitedPage($data['systemstatistics']['trainings'], $users);
                    }
                    if(in_array("trainingProgress", $data['systemstatistics']['statistics'])) {
                        $dataSystemstatisticsTrainingProgress = Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getSystemstatisticTrainingProgress($data['systemstatistics']['trainings'], $users);
                    }
                }
                // END HEADER ----------------------------------------------

                // BUILD DATA ----------------------------------------------
                foreach($users as $userid) {
                    // GENERAL
                    $csvdata[$userid] = array();
                    $csvdata[$userid][] = $userid;
                    $csvdata[$userid][] = isset($allusers[$userid]) ? $this->sanitizeDataField($allusers[$userid]->display_name) : self::$noval;
                    $csvdata[$userid][] = isset($allusers[$userid]) ? date("d.m.Y", strtotime($allusers[$userid]->user_registered)) : self::$noval;
                    
                    // STUDY-ID
                    if($include_studyid) {
                        $csvdata[$userid][] = isset($allusers[$userid]->studienid) && trim($allusers[$userid]->studienid) != "" ? $this->sanitizeDataField($allusers[$userid]->studienid) : self::$noval;
                    }
                    // STUDYGROUP-ID
                    if($include_studygroupid) {
                        $csvdata[$userid][] = isset($allusers[$userid]->studiengruppenid) && trim($allusers[$userid]->studiengruppenid) != "" ? $this->sanitizeDataField($allusers[$userid]->studiengruppenid) : self::$noval;
                    }

                    // COMPANY DATA
                    if($include_groupdata) {
                        if(isset($groupdata[$userid])) {
                            $csvdata[$userid][] = $this->sanitizeDataField(implode(self::$val_sep, array_keys($groupdata[$userid]['companies'])));
                            $csvdata[$userid][] = $this->sanitizeDataField(implode(self::$val_sep, $groupdata[$userid]['companies']));
                            $csvdata[$userid][] = $this->sanitizeDataField(implode(self::$val_sep, $groupdata[$userid]['groups']));
                        } else {
                            $csvdata[$userid][] = self::$noval;
                            $csvdata[$userid][] = self::$noval;
                            $csvdata[$userid][] = self::$noval;
                        }
                    }

                    // TSFORMS
                    $csvdata[$userid] = array_merge($csvdata[$userid], $this->getTsFormsData($userid, $tsformheader, $tsformdata));

                    // SYSTEMSTATISTICS
                    if($includeSystemstatistics) {
                        foreach($data['systemstatistics']['trainings'] as $training){
                            foreach($data['systemstatistics']['statistics'] as $statistic){
                                if($statistic == "startedLektion")
                                {
                                    $csvdata[$userid] = array_merge($csvdata[$userid], $this->getSystemstatisticsContent($userid, $training, "startedLektion", $dataSystemstatisticsStartedLektion));
                                }
                                else if($statistic == "finishedLektion")
                                {
                                    $csvdata[$userid] = array_merge($csvdata[$userid], $this->getSystemstatisticsContent($userid, $training, "finishedLektion", $dataSystemstatisticsFinishedLektion));
                                }
                                else if($statistic == "lastVisitedPage")
                                {
                                    $csvdata[$userid] = array_merge($csvdata[$userid], $this->getSystemstatisticsContent($userid, $training, "lastVisitedPage", $dataSystemstatisticsLastVisitedPage));
                                }
                                else if($statistic == "trainingProgress")
                                {
                                    $csvdata[$userid] = array_merge($csvdata[$userid], $this->getSystemstatisticsContent($userid, $training, "trainingProgress", $dataSystemstatisticsTrainingProgress));
                                }
    
                            }
                        }

                        
                    }
                }
                // END BUILD DATA ----------------------------------------------

                // TS FORMS MULTIPLE EXPORT ------------------------------------
                $zip_file = false;
                $zip_content = array();
                if(isset($data['tsformsmulti']) && sizeof($data['tsformsmulti']) > 0) {

                    $multiheader = Trainingssystem_Plugin_Database::getInstance()->Formfield->getDataExportData($data['tsformsmulti']);
                    $multidata = Trainingssystem_Plugin_Database::getInstance()->Formdata->getDataExportData($data['tsformsmulti'], $users, true);

                    if(sizeof($multiheader) > 0 && count($multidata) > 0) {
                        $zip_file = true;
                        $zip_content = $this->getTsFormsMultiFiles($users, $multiheader, $multidata);
                    }
                }
                // END TS FORMS MULTIPLE EXPORT --------------------------------

                // FINAL FILE CREATION
                if($zip_file) {

                    $zip = new ZipArchive;
                    $filename = "Datenexport-Paket-" . current_time("d-m-Y-H-i-s") . ".zip";
                    $tmp_dir = trailingslashit((wp_get_upload_dir()['basedir'])) . "trainings-ex-import/";
                    $zipfile = $tmp_dir . $filename;
    
                    if (is_dir($tmp_dir) && $zip->open($zipfile, ZipArchive::CREATE) === true) {
                        
                        $zip->addFromString("Datenexport-" . current_time("d-m-Y-H-i-s") . ".csv", $twig->render("data-export/data-export-csv.html", ["header" => $header, "data" => $csvdata, "sep" => self::$csv_sep]));
                        
                        foreach($zip_content as $path => $content) {
                            $zip->addFromString($path, $content);
                        }

                        if($zip->close()) {
                            header('Content-Type: application/zip; charset=utf-8');
                            header('Content-disposition: attachment; filename="' . $filename . '"');
                            header('Content-Length: ' . filesize($zipfile));
                            $handle = fopen($zipfile, 'rb');
                            $buffer = '';
                            while (!feof($handle)) {
                                $buffer = fread($handle, 4096);
                                echo $buffer;
                                ob_flush();
                                flush();
                            }
                            fclose($handle);
                        }
                        unlink($zipfile);
                    }

                } else {
                    header("Content-type: text/csv, charset=utf-8");
                    header("Content-disposition: attachment; filename=Datenexport-" . current_time("d-m-Y-H-i-s") . ".csv");
                    echo $twig->render("data-export/data-export-csv.html", ["header" => $header, "data" => $csvdata, "sep" => self::$csv_sep]);
                }
            }
        }
        wp_die();
    }

    /**
     * FRONTEND
     * 
     * Filter Forms by Training and Lektionen
     * 
     * @return Form-IDs seperated with ;
     */
    public function filterForms() {
        if(isset($_POST['ids']) && is_array($_POST['ids']) && isset($_POST['type']) && $_POST['type'] == "tsforms") {

            $ids = $_POST['ids'];
            $type = $_POST['type'];
            
            $code = "";

            foreach($ids as $trainingid => $lektionids) {

                $trainingpost = get_post($trainingid, OBJECT);

                $code .= $trainingpost->post_ecerpt . $trainingpost->post_content;

                foreach($lektionids as $lektionid) {

                    $lektionpost = get_post($lektionid, OBJECT);

                    $code .= $lektionpost->post_ecerpt . $lektionpost->post_content;

                    $seitenids = $this::getLektionseiten($lektionid);

                    foreach ($seitenids as $seitenid) {
                        $seitenpost = get_post($seitenid);

                        $code .= $seitenpost->post_ecerpt . $seitenpost->post_content;
                    }
                }
            }

            $regex = "/\[[\wöäüßÖÄÜ0-9-_?! :.,;\/{}()=\"'…\n\r]+\]?/";
            preg_match_all($regex, $code, $matches);
            $all_shortcodes_list = array();
            $s_index = 0;
            foreach ($matches[0] as $match) {
                if (!$this::startsWith($match, "[/")) { // no closing tags here
                    $all_shortcodes_list[$s_index]['original_shortcode'] = $match;
                    if (strpos($match, " ") !== false) {
                        $all_shortcodes_list[$s_index]['shortcode_name'] = substr($match, 1, strpos($match, " "));
                        $all_shortcodes_list[$s_index] = array_merge($all_shortcodes_list[$s_index], array_slice(shortcode_parse_atts(substr($match, 1, strlen($match) - 2)), 1));
                    } else {
                        $all_shortcodes_list[$s_index]['shortcode_name'] = substr($match, 1, strlen($match) - 2);
                    }
                    $s_index++;
                }
            }

            $found_ids = array();

            foreach ($all_shortcodes_list as $shortcode) {
                switch (trim($shortcode['shortcode_name'])) {
                    case "ts_forms":
                        if (isset($shortcode['id']) && $type == "tsforms" && !isset($found_ids[$shortcode['id']])) {
                            $found_ids[$shortcode['id']] = $shortcode['id'];
                        }
                        break;
                }
            }
            
            echo implode(';', array_values($found_ids));
        }
        wp_die();
    }

    /**
     * HELPER
     * 
     * Get all the Lektion's Seiten IDs as Array
     * 
     * @param Integer Lektion-ID
     * @return Int-Array with Page-IDs
     */
    private function getLektionseiten($lektionid)
    {
        global $wpdb;
        $dbprefix_seiten = $wpdb->prefix . 'md_trainingsmgr_tranings_seiten';

        $sql = $wpdb->prepare("SELECT `seiten_id` FROM $dbprefix_seiten WHERE lektion_id = %d ORDER BY seiten_index ASC", $lektionid);
        $ids = $wpdb->get_col($sql);

        return $ids;
    }

    /**
     * GENERAL
     * 
     * Return all users except vorlagenuser
     * 
     * @return Array of WP-User-Objects
     */
    private function getAllUsers() {
        $args = [ 'role__in' => ['subscriber' ], 'orderby' => 'display_name', 'order' => 'ASC' ];
        if(Trainingssystem_Plugin_Module_Berechtigung::getInstance()->accessAllowed('userlistall')) {
            $args = ['orderby' => 'display_name', 'order' => 'ASC'];
        }
        $args['meta_key'] = 'vorlagenuser';
        $args['meta_compare'] = "NOT EXISTS";

        return get_users( $args );
    }

    /**
     * TS FORMS
     * 
     * Returns the header for all selected TS Form IDs which can be merged into the csvheader
     * 
     * @param multi-dim-Array TS Form Fields
     * @param boolean clean header: only use form title as header; default false
     * 
     * @return String-Array to be merged with CSV-Headers
     */
    private function getTsFormsHeader($tsformheader, $cleanheader = false) {

        if(sizeof($tsformheader) == 0) return array();

        $ret = array();

        foreach($tsformheader as $tsformid => $fields) {
            foreach($fields as $field) {
                if(!in_array($field->getType(), self::$ignoreFieldTypesTsForms)) {
                    if($cleanheader) {
                        if(in_array($field->getType(), self::$multipleFieldTypesTsForms)) {
                            $length = 0;
                            if($field->getType() == "matrix") {
                                $length = sizeof($field->getAttributesJsonDecoded()["questions"]);
                            } else if($field->getType() == "scale") {
                                $length = sizeof($field->getAttributesJsonDecoded()["elements"]);
                            }
                            for($i = 1; $i <= $length; $i++) {
                                $ret[] = $this->sanitizeHeaderTitle(get_the_title($tsformid) . " " . $i, true);
                            }

                        } else {
                            $ret[] = $this->sanitizeHeaderTitle(get_the_title($tsformid), true);
                        }
                    } else {
                        $t = $tsformid . self::$headersep . $field->getFieldId();

                        $title = $this->sanitizeHeaderTitle(get_the_title($tsformid));

                        if(in_array($field->getType(), self::$multipleFieldTypesTsForms)) {
                            $length = 0;
                            if($field->getType() == "matrix") {
                                $length = sizeof($field->getAttributesJsonDecoded()["questions"]);
                            } else if($field->getType() == "scale") {
                                $length = sizeof($field->getAttributesJsonDecoded()["elements"]);
                            }
                            
                            for($i = 1; $i <= $length; $i++) {
                                $ret[] = $title . self::$headersep . $t . self::$headersep . $i;
                            }

                        } else {
                            $ret[] = $title . self::$headersep . $t; 
                        }
                    }
                }
            }
        }
        return $ret;
    }

    /**
     * TS FORMS
     * 
     * Returns the TS Form data for one user
     * 
     * @param Integer User-ID
     * @param multi-dim-Array TS Form Fields
     * @param multi-dim-Array TS Form Data
     * 
     * @return String-Array to be merged with csv data
     */
    private function getTsFormsData($userid, $tsformheader, $tsformdata) {
        if(sizeof($tsformheader) == 0) return array();

        $ret = array();

        foreach($tsformheader as $tsformid => $fields) {
            foreach($fields as $field) {
                if(!in_array($field->getType(), self::$ignoreFieldTypesTsForms)) {
                    
                    if(in_array($field->getType(), self::$multipleFieldTypesTsForms)) {
                        $length = 0;
                        if($field->getType() == "matrix") {
                            $length = sizeof($field->getAttributesJsonDecoded()["questions"]);
                        } else if($field->getType() == "scale") {
                            $length = sizeof($field->getAttributesJsonDecoded()["elements"]);
                        }
                        
                        if(isset($tsformdata[$userid][$tsformid][$field->getFieldId()]) && sizeof($tsformdata[$userid][$tsformid][$field->getFieldId()]) == 1 &&
                            $this->dataIsArray($tsformdata[$userid][$tsformid][$field->getFieldId()][0])) {

                            $data = json_decode($tsformdata[$userid][$tsformid][$field->getFieldId()][0], true);

                            if(sizeof($data) === $length) {
                                foreach($data as $d) {
                                    $ret[] = $this->sanitizeDataField($d);
                                }
                            } else {
                                for($i = 0; $i < $length; $i++) {
                                    $ret[] = self::$noval;
                                }
                            }

                        } else {
                            for($i = 0; $i < $length; $i++) {
                                $ret[] = self::$noval;
                            }
                        }
                        

                    } else {
                        if(isset($tsformdata[$userid][$tsformid][$field->getFieldId()])) {
                            $max_formgroup = 0;
                            foreach($tsformdata[$userid][$tsformid] as $values) {
                                foreach($values as $v_key => $v_val) {
                                    if($v_key > $max_formgroup) {
                                        $max_formgroup = $v_key;
                                    }
                                }
                            }
                            $tmp = array();
                            for ($i = 0; $i <= $max_formgroup; $i++) {
                                if (isset($tsformdata[$userid][$tsformid][$field->getFieldId()][$i]) &&
                                    $field->getType() === "input" &&
                                    ($field->getAttributesJsonDecoded()['type'] === "date" || $field->getAttributesJsonDecoded()['type'] === "datetime-local")) {

                                    if ($field->getAttributesJsonDecoded()['type'] === "date") {
                                        $tmp[] = $this->sanitizeDataField(date("d.m.Y", strtotime($tsformdata[$userid][$tsformid][$field->getFieldId()][$i])));
                                    } elseif ($field->getAttributesJsonDecoded()['type'] === "datetime-local") {
                                        $tmp[] = $this->sanitizeDataField(date("d.m.Y H:i:s", strtotime($tsformdata[$userid][$tsformid][$field->getFieldId()][$i])));
                                    }
                                } else {
                                    $tmp[] = isset($tsformdata[$userid][$tsformid][$field->getFieldId()][$i]) ? $this->sanitizeDataField($tsformdata[$userid][$tsformid][$field->getFieldId()][$i]) : self::$noval;
                                }
                            }
                            $ret[] = implode(self::$val_sep, $tmp);
                        } else {
                            $ret[] = self::$noval;
                        }
                    }
                    
                }
            }
        }
        return $ret;
    }

    /**
     * TS FORMS MULTI
     * 
     * Returns the multi-tsform data for multiple users as Array for storage into a zip-file
     * 
     * @param Array User-IDs
     * @param Array Header-Information: Multi-dim Array with TS Forms FormField-Objects
     * @param Array User-Data
     * 
     * @return Array with Key path of csv-file in ZIP-file and the content of the csv-filev as alue 
     */
    private function getTsFormsMultiFiles($userids, $multiheader, $multidata) {
        $ret = array();

        // Re-Index header with field-ID on 2nd level
        $header = array();
        foreach($multiheader as $formid => $fields) {
            foreach($fields as $field) {
                $header[$formid][$field->getFieldId()] = $field;
            }
        }

        $twig = Trainingssystem_Plugin_Twig::getInstance()->twig;

        foreach($userids as $userid) {
            foreach($multiheader as $formid => $fields) {
                if(isset($multidata[$userid][$formid])) {

                    $userheader = array("fieldname");
                    $userdata = array();

                    // Titel horizontal
                    foreach($multidata[$userid][$formid] as $fieldid => $data) {
                        if(!in_array($header[$formid][$fieldid]->getType(), self::$ignoreFieldTypesTsForms)) {
                            foreach($data as $date => $value) {
                                if(!isset($userheader[$this->sanitizeHeaderTitle($date)])) {
                                    $userheader[$this->sanitizeHeaderTitle($date)] = $this->sanitizeHeaderTitle($date);
                                }
                            }
                        }
                    }

                    // Titel vertikal + default Data noval
                    foreach($multidata[$userid][$formid] as $fieldid => $data) {
                        if(!in_array($header[$formid][$fieldid]->getType(), self::$ignoreFieldTypesTsForms)) {
                            $t = $formid . self::$headersep . $fieldid . self::$headersep;

                            $title = $this->sanitizeHeaderTitle(get_the_title($formid));

                            if(in_array($header[$formid][$fieldid]->getType(), self::$multipleFieldTypesTsForms)) {
                                $length = 0;
                                if($header[$formid][$fieldid]->getType() == "matrix") {
                                    $length = sizeof($header[$formid][$fieldid]->getAttributesJsonDecoded()["questions"]);
                                } else if($header[$formid][$fieldid]->getType() == "scale") {
                                    $length = sizeof($header[$formid][$fieldid]->getAttributesJsonDecoded()["elements"]);
                                }
                                
                                for($i = 1; $i <= $length; $i++) {
                                   $userdata[$fieldid . "-" . $i] = array($t . $i . self::$headersep . $title);
                                   
                                   for($j = 0; $j < (sizeof($userheader) -1); $j++) {
                                       $userdata[$fieldid . "-" . $i][] = self::$noval;
                                   }
                                }

                            } else {
                                $t .= $title;
                                $userdata[$fieldid] = array($t);
                                for($j = 0; $j < (sizeof($userheader) -1); $j++) {
                                    $userdata[$fieldid][] = self::$noval;
                                }
                            }
                        }
                    }

                    // Data
                    foreach($multidata[$userid][$formid] as $fieldid => $data) {
                        if(!in_array($header[$formid][$fieldid]->getType(), self::$ignoreFieldTypesTsForms)) {

                            $i = 1;
                            foreach($data as $date => $value) {
                                if(in_array($header[$formid][$fieldid]->getType(), self::$multipleFieldTypesTsForms)) {
                                    $length = 0;
                                    if($header[$formid][$fieldid]->getType() == "matrix") {
                                        $length = sizeof($header[$formid][$fieldid]->getAttributesJsonDecoded()["questions"]);
                                    } else if($header[$formid][$fieldid]->getType() == "scale") {
                                        $length = sizeof($header[$formid][$fieldid]->getAttributesJsonDecoded()["elements"]);
                                    }
                                    
                                    foreach($value as $v) {
                                        if($this->dataIsArray($v)) {
                
                                            $data = json_decode($v, true);
                
                                            if(sizeof($data) === $length) {
                                                $j = 1;
                                                foreach($data as $dindex => $d) {
                                                    if($userdata[$fieldid . "-" . $j][$i] == self::$noval) {
                                                        $userdata[$fieldid . "-" . $j][$i] = $this->sanitizeDataField($d);
                                                    } else {
                                                        $userdata[$fieldid . "-" . $j][$i] = $userdata[$fieldid . "-" . $j][$i] . self::$val_sep . $this->sanitizeDataField($d);
                                                    }
                                                    $j++;
                                                }
                                            }
                                        }
                                    }

                                } else {
                                    $max_formgroup = 0;
                                    foreach($multidata[$userid][$formid] as $field_values) {
                                        foreach($field_values[$date] as $v_key => $v_val) {
                                            if($v_key > $max_formgroup) {
                                                $max_formgroup = $v_key;
                                            }
                                        }
                                    }
                                    $tmp = array();
                                    for ($i_t = 0; $i_t <= $max_formgroup; $i_t++) {
                                        $tmp[] = isset($multidata[$userid][$formid][$fieldid][$date][$i_t]) ? $this->sanitizeDataField($multidata[$userid][$formid][$fieldid][$date][$i_t]) : self::$noval;
                                    }
                                    $userdata[$fieldid][$i] = implode(self::$val_sep, $tmp);
                                }
                                $i++;
                            }
                        }
                    }

                    $ret["form-" . $formid . "/user-" . $userid . ".csv"] = $twig->render("data-export/data-export-csv.html", ["header" => $userheader, "data" => $userdata, "sep" => self::$csv_sep]);;
                }
            }
        }
        return $ret;
    }
    
    /**
     * Systemstatistics
     * 
     * 
     */
    public function getSystemstatisticsHeader($trainings, $statistics){

		$header = array();

		foreach($trainings as $training){
			$thisTraining = Trainingssystem_Plugin_Database::getInstance()->TrainingDao->getTrainingById($training);
            if(!is_null($thisTraining)) {
                $thisTrainingLektionen = $thisTraining->getLektionsliste();
                foreach($statistics as $statistic){

                    if($statistic == "startedLektion")
                    {
                        foreach($thisTrainingLektionen as $lektion){
                            $header[] = $this->sanitizeHeaderTitle("Beginn ".$lektion->getTitle());
                        }
                    }
                    else if($statistic == "finishedLektion")
                    {
                        foreach($thisTrainingLektionen as $lektion){
                            $header[] = $this->sanitizeHeaderTitle("Ende ".$lektion->getTitle());
                        }
                    }
                    else if($statistic == "lastVisitedPage")
                    {
                        $header[] = $this->sanitizeHeaderTitle("Letzte besuchte Seite ".$thisTraining->getTitle());
                    }
                    else if($statistic == "trainingProgress")
                    {
                        $header[] = $this->sanitizeHeaderTitle("Trainingsfortschritt ".$thisTraining->getTitle());
                    }
                }
            } else {
                $this->ignoreTraining[] = $training;
            }
		}

		return $header;
	}


    /**
     * Systemstatistics
     * 
     * 
     */
    
    public function getSystemstatisticsContent($userid, $training, $statistic, $userdata){

		$result = array();

        if(!in_array($training, $this->ignoreTraining)) {
            if($statistic == "startedLektion")
            {
                $result = array_merge($result, $this->getSystemstatisticsStartedLektion($userid, $training, $userdata));
            }
            else if($statistic == "finishedLektion")
            {
                $result = array_merge($result, $this->getSystemstatisticsFinishedLektion($userid, $training, $userdata));
            }
            else if($statistic == "lastVisitedPage")
            {
                $result[] = $this->getSystemstatisticsLastVisitedPage($userid, $training, $userdata);
            }
            else if($statistic == "trainingProgress")
            {
                $result[] = $this->getSystemstatisticsTrainingProgress($userid, $training, $userdata);
            }
        }
			
		return $result;
	}
    

    /**
     * Systemstatistics
     * 
     * 
     */
    public function getSystemstatisticsStartedLektion($userid, $training, $userdata){
		$result = array();

        if(isset($userdata[$userid][$training]) && sizeof($userdata[$userid][$training]) > 0) {

            foreach($userdata[$userid][$training] as $lektion){

                if(is_null($lektion["startdate"]) || trim($lektion['startdate']) == ""){
                    $result[] = self::$noval;
                }
                else{
                    $result[] = $this->sanitizeDataField(date("d.m.Y", strtotime($lektion["startdate"])));
                }
            }

        } else {
            $result[] = self::$noval;
        }

        return $result;
		
	}

    /**
     * Systemstatistics
     * 
     * 
     */
	public function getSystemstatisticsFinishedLektion($userid, $training, $userdata){
		$result = array();
		
        if(isset($userdata[$userid][$training]) && sizeof($userdata[$userid][$training]) > 0) {

            foreach($userdata[$userid][$training] as $lektion){
                if(is_null($lektion["findate"]) || trim($lektion['findate']) == ""){
                    $result[] = self::$noval;
                }
                else{
                    $result[] = $this->sanitizeDataField($lektion["findate"]);
                }
            }

        } else {
            $result[] = self::$noval;
        }
        return $result;
	}

    /**
     * Systemstatistics
     * 
     * 
     */
	public function getSystemstatisticsLastVisitedPage($userid, $training, $userdata){

        if(isset($userdata[$userid][$training]) && sizeof($userdata[$userid][$training]) > 0){

            $lastPage = "";
            $maxLektionIndex = -1;
            foreach($userdata[$userid][$training] as $lektion){
                if($lektion["lektion_index"]> $maxLektionIndex){
                    $lastPage = $lektion["page"];
                    $maxLektionIndex = $lektion["lektion_index"];
                }
            }

            return $this->sanitizeDataField($lastPage);
        }
        else{
            return self::$noval;
        }
	}

    /**
     * Systemstatistics
     * 
     * Return the user's progress of a Training 
     */
	public function getSystemstatisticsTrainingProgress($userid, $training, $userdata){

        if(isset($userdata[$userid][$training]['progress'])){

            return $this->sanitizeDataField($userdata[$userid][$training]['progress'] . " %");
        }
        else{
            return self::$noval;
        }
	}

    /**
     * Sanitizes a header Title by removing everything except a-zA-Z0-9öäüÖÄÜß, 
     * remove all special delimiter chars and join everything together by the whitespace
     * 
     * @param String title
     * @param boolean cleanheader: _ allowed
     * 
     * @return String title cleaned
     */
    private function sanitizeHeaderTitle($title, $cleanheader = false) {
        if($cleanheader) {
            $dismiss = "/[^_A-Za-z0-9öäüÖÄÜß]/";
        } else {
            $dismiss = "/[^A-Za-z0-9öäüÖÄÜß]/";
        }
        $dismiss_special = array(self::$csv_sep, self::$val_sep, self::$noval, self::$headersep);

        $tmp = $title;
        $tmp = preg_replace($dismiss, " ", $tmp);
        if(!$cleanheader)
            $tmp = str_replace($dismiss_special, "", $tmp);
        $tmp = preg_replace("/\s+/", " ", $tmp);
        $tmp = str_replace(" ", self::$headerReplace, $tmp);
        if(strlen($tmp) > 0 && $this->startsWith($tmp, self::$headerReplace)) $tmp = substr($tmp, 1);
        if(strlen($tmp) > 0 && $this->endsWith($tmp, self::$headerReplace)) $tmp = substr($tmp, 0, -1);

        return $tmp;
    }

    /**
     * Sanitizes a data field by replacing new line, tab and csv delimiters with a single whitespace
     * 
     * @param String data
     * @return String data cleaned
     */
    private function sanitizeDataField($data) {
        $dismiss = array("\n", "\r", "\t", "=", "-", self::$csv_sep);
        if(self::$csv_sep == self::$val_sep) {
            $dismiss[] = self::$val_sep; // filter ; as values separator if the same as csv sep, else ignore it
        }

        $tmp = $data;
        $tmp = str_replace($dismiss, " ", $tmp);

        return $tmp;
    }

    /**
     * HELPER
     * 
     * Determines if a String $haystack starts with $needle
     */
    private function startsWith($haystack, $needle)
    {
        $length = strlen($needle);
        return (substr($haystack, 0, $length) === $needle);
    }

    /** 
     * HELPER
     * 
     * Determines if a String $haystack ends with $needle
     */
    private function endsWith($haystack, $needle)
    {
        $length = strlen($needle);
        if ($length == 0) {
            return true;
        }

        return (substr($haystack, -$length) === $needle);
    }

    /**
     * HELPER
     * 
     * Determines if a String is a valid JSON-Object
     */
    private function dataIsArray($data) {
        if(strpos($data, "[") === false && strpos($data, "{") === false) {
            return false;
        }

        json_decode($data);
        if(json_last_error() == JSON_ERROR_NONE) {
            return true;
        }
        return false;
    }
}