refine member-export, fixes #3841

Closes #3841

Merge request studip/studip!2711

10 files changed, 253 insertions, 157 deletions

diff --git a/app/controllers/admin/user.php b/app/controllers/admin/user.php
index 7c6ea9a..70dfdf1 100644
--- a/app/controllers/admin/user.php
+++ b/app/controllers/admin/user.php
@@ -1666,16 +1666,25 @@ class Admin_UserController extends AuthenticatedController
         mkdir($tmp_folder);
 
         $courses = Course::findMany(Request::optionArray('courses'));
-
+        $header = [
+            _('Status'),
+            _('Anrede'),
+            _('Titel'),
+            _('Vorname'),
+            _('Nachname'),
+            _('Titel nachgestellt'),
+            _('Benutzername'),
+            _('Adresse'),
+            _('Telefonnr.'),
+            _('E-Mail'),
+            _('Anmeldedatum'),
+            _('Matrikelnummer'),
+            _('Studiengänge'),
+            _('Position'),
+        ];
         foreach ($courses as $course) {
-            $header = ['Status', 'Anrede', 'Titel', 'Vorname', 'Nachname', 'Titel nachgestellt', 'Benutzername', 'Adresse', 'Telefonnr.',
-                'E-Mail', 'Anmeldedatum', 'Matrikelnummer', 'Studiengänge'];
-            $members = CourseMember::getMemberDataByCourse($course->seminar_id);
 
-            foreach ($members as &$member) {
-                $member['Anmeldedatum'] = $member['Anmeldedatum'] ? date('d.m.Y', $member['Anmeldedatum']) : _('unbekannt');
-                unset($member['user_id']);
-            }
+            $members = $course->getMembersData();
 
             $filename = FileManager::cleanFileName('Teilnehmendenexport ' . $course->Name . '.' . $export_format);
             $filepath = $tmp_folder . '/'. $filename;
diff --git a/app/controllers/course/members.php b/app/controllers/course/members.php
index bf07221..3eae083 100644
--- a/app/controllers/course/members.php
+++ b/app/controllers/course/members.php
@@ -1676,7 +1676,16 @@ class Course_MembersController extends AuthenticatedController
 
                 if (count($this->awaiting) > 0) {
                     $widget->addLink(
-                        _('Warteliste CSV-Datei exportieren'),
+                        _('Warteliste als Excel-Datei exportieren'),
+                        URLHelper::getURL('dispatch.php/course/members/export', [
+                            'course_id' => $this->course_id,
+                            'format'    => 'xlsx',
+                            'status'    => $this->waiting_type,
+                        ]),
+                        Icon::create('export')
+                    );
+                    $widget->addLink(
+                        _('Warteliste als CSV-Datei exportieren'),
                         URLHelper::getURL('dispatch.php/course/members/export', [
                             'course_id' => $this->course_id,
                             'format'    => 'csv',
@@ -1738,7 +1747,6 @@ class Course_MembersController extends AuthenticatedController
         if ($export_format !== 'csv' && $export_format !== 'xlsx') {
             throw new Exception('Wrong format');
         }
-
         $header = [
             _('Status'),
             _('Anrede'),
@@ -1752,23 +1760,18 @@ class Course_MembersController extends AuthenticatedController
             _('E-Mail'),
             _('Anmeldedatum'),
             _('Matrikelnummer'),
+            _('Studiengänge'),
+            _('Position'),
         ];
 
         if (in_array($status, ['awaiting', 'claiming'])) {
-            $header[] = _('Position');
             $filename = _('Wartelistenexport');
         } else {
             $filename = _('Teilnehmendenexport');
         }
 
-        $header[] = _('Studiengänge');
-
-        $members = CourseMember::getMemberDataByCourse($this->course_id, $status);
-
-        foreach ($members as &$member) {
-            $member['Anmeldedatum'] = $member['Anmeldedatum'] ? date("d.m.Y", $member['Anmeldedatum']) : _("unbekannt");
-            unset($member['user_id']);
-        }
+        $course = Course::findCurrent();
+        $members = $course->getMembersData($status);
 
         $filename = $filename . ' ' . $this->course_title . '.' . $export_format;
 
diff --git a/app/controllers/course/statusgroups.php b/app/controllers/course/statusgroups.php
index d2921a7..c15733d 100644
--- a/app/controllers/course/statusgroups.php
+++ b/app/controllers/course/statusgroups.php
@@ -265,7 +265,7 @@ class Course_StatusgroupsController extends AuthenticatedController
                 );
 
                 $export->addLink(
-                    _('Als .csv exportieren'),
+                    _('Als CSV-Datei exportieren'),
                     URLHelper::getURL('dispatch.php/course/statusgroups/export', [
                         'course_id' => $this->course_id,
                         'format' => 'csv',
@@ -311,57 +311,54 @@ class Course_StatusgroupsController extends AuthenticatedController
             throw new Exception('Wrong format');
         }
 
-        if (Request::get('institute_id')) {
-            $institute_id = Request::get('institute_id');
-        }
+        $course = Course::findCurrent();
 
-        $header = [_('Gruppe'), _('Titel'), _('Vorname'), _('Nachname'), _('Titel nachgestellt'), _('Nutzername'),
-            _('Privatadresse'), _('Privatnr.'), _('E-Mail'), _('Anmeldedatum'), _('Studiengänge')];
+        $header = [
+            _('Gruppe'),
+            _('geschlecht'),
+            _('Titel'),
+            _('Vorname'),
+            _('Nachname'),
+            _('Titel nachgestellt'),
+            _('Nutzername'),
+            _('Privatadresse'),
+            _('Privatnr.'),
+            _('E-Mail'),
+            _('Anmeldedatum'),
+            _('Matrikelnummer'),
+            _('Studiengänge')
+        ];
 
-        $temp = [];
 
         $groups = Statusgruppen::findBySeminar_id($this->course_id);
+        $result = [];
         if ($groups) {
+            $assigned_with_group = [];
             foreach ($groups as $group) {
-                foreach ($group['members'] as $mem) {
-                    $member_data = CourseMember::getMemberDataByCourse($this->course_id, '', $mem['user_id']);
-
-                    foreach ($member_data as &$mem_data) {
-                        $mem_data['Anmeldedatum'] = $mem_data['Anmeldedatum'] ? date('d.m.Y', $mem_data['Anmeldedatum']) : _('unbekannt');
-
-                        if (!isset($temp[$group['name']])) {
-                            $temp[$group['name']] = [];
-                        }
-                        $temp[$group['name']][$mem_data['user_id']] = [
-                            'Gruppe'               => $group['name'],
-                            'Titel'                => $mem_data['title_front'],
-                            'Vorname'              => $mem_data['Vorname'],
-                            'Nachname'             => $mem_data['Nachname'],
-                            'Titel nachgestellt'   => $mem_data['title_rear'],
-                            'Nutzername'           => $mem_data['title_front'],
-                            'Privatadresse'        => $mem_data['privadr'],
-                            'Privatnr.'            => $mem_data['privatnr'],
-                            'E-Mail'               => $mem_data['Email'],
-                            'Anmeldedatum'         => $mem_data['Anmeldedatum'],
-                            'Studiengänge'         => $mem_data['studiengaenge'],
-                        ];
+                foreach ($group->members as $member) {
+                    if (!in_array($member->user_id, $assigned_with_group)) {
+                        $assigned_with_group[] = $member->user_id;
                     }
+                    $result[$member->user_id] = $member->getExportData();
                 }
             }
-        }
+            $members = $course->members->filter(function($group_member) use ($assigned_with_group) {
+                    return !in_array($group_member->user_id, $assigned_with_group);
+                })->orderBy('position');
+
+            foreach ($members as $member) {
+                $data = ['gruppe' => _('keiner Funktion oder Gruppe zugeordnet')]  + $member->getExportData();
 
-        $statusgroup_data = [];
+                unset($data['status']);
+                unset($data['position']);
 
-        foreach ($temp as $group => $group_members) {
-            foreach ($group_members as $member) {
-                $statusgroup_data[] = $member;
+                $result[$member->user_id] = $data;
             }
-
         }
 
         $filename = FileManager::cleanFileName(_('Gruppenliste') . ' ' . $this->course_title . '.' . $export_format);
 
-        $this->render_spreadsheet($header, $statusgroup_data, $export_format, $filename);
+        $this->render_spreadsheet($header, $result, $export_format, $filename);
     }
 
     /**
diff --git a/app/controllers/institute/members.php b/app/controllers/institute/members.php
index 1d49012..4158478 100644
--- a/app/controllers/institute/members.php
+++ b/app/controllers/institute/members.php
@@ -654,7 +654,7 @@ class Institute_MembersController extends AuthenticatedController
                 Icon::create('export')
             );
             $widget->addLink(
-                _('Als .csv exportieren'),
+                _('Als CSV-Datei exportieren'),
                 URLHelper::getURL('dispatch.php/institute/members/export', [
                     'institute_id' => $this->institute->id,
                     'format' => 'csv',
diff --git a/db/migrations/5.5.27_remove_old_export_config_entries.php b/db/migrations/5.5.27_remove_old_export_config_entries.php
new file mode 100644
index 0000000..c870ce6
--- /dev/null
+++ b/db/migrations/5.5.27_remove_old_export_config_entries.php
@@ -0,0 +1,25 @@
+<?php
+
+final class RemoveOldExportConfigEntries extends Migration
+{
+    public function description()
+    {
+        return 'Removes remnants of the old export';
+    }
+
+    public function up()
+    {
+        DBManager::get()->execute("DELETE FROM `config` WHERE `field` = 'XSLT_ENABLE'");
+        DBManager::get()->execute("DELETE FROM `config` WHERE `field` = 'FOP_ENABLE'");
+    }
+
+    public function down()
+    {
+        DBManager::get()->execute("INSERT INTO `config` (`field`, `value`, `type`, `range`, `section`, `mkdate`, `chdate`, `description`)
+            VALUES('XSLT_ENABLE', '1', 'boolean', 'global', 'global', 1510849314, 1510849314, 'Soll Export mit XSLT angeschaltet sein?')"
+        );
+        DBManager::get()->execute("INSERT INTO `config` (`field`, `value`, `type`, `range`, `section`, `mkdate`, `chdate`, `description`)
+            VALUES('FOP_ENABLE', '1', 'boolean', 'global', 'global', 1510849314, 1510849314, 'Soll Export mit FOP erlaubt sein?')"
+        );
+    }
+}
diff --git a/lib/models/AdmissionApplication.class.php b/lib/models/AdmissionApplication.class.php
index d1f2cba..02514ec 100644
--- a/lib/models/AdmissionApplication.class.php
+++ b/lib/models/AdmissionApplication.class.php
@@ -263,4 +263,34 @@ class AdmissionApplication extends SimpleORMap implements PrivacyObject
             }
         }
     }
+
+
+    /**
+     * Prepare data for member export
+     * @return array
+     */
+    public function getExportData(): array
+    {
+        $user = $this->user;
+        $studycourse = [];
+        $user->studycourses->map(function($sc) use (&$studycourse) {
+            $studycourse[]= $sc->studycourse->name .  ',' . $sc->degree->name . ',' . $sc->semester;
+        });
+        return [
+            'status' => $this->status,
+            'salutation' => $user->salutation,
+            'Titel' => $user->title_front,
+            'Vorname' => $this->vorname,
+            'Nachname' => $this->nachname,
+            'Titel2' => $user->title_rear,
+            'username' => $this->username,
+            'privadr' => $user->privadr,
+            'privatnr' => $user->privatnr,
+            'Email' => $this->email,
+            'Anmeldedatum' => date('d.m.Y H:i:s', $this->mkdate),
+            'Matrikelnummer' => $user->matriculation_number,
+            'studiengaenge' => implode(';', $studycourse),
+            'position' => $this->position,
+        ];
+    }
 }
diff --git a/lib/models/Course.class.php b/lib/models/Course.class.php
index 1f60930..59f9b82 100644
--- a/lib/models/Course.class.php
+++ b/lib/models/Course.class.php
@@ -1117,4 +1117,57 @@ class Course extends SimpleORMap implements Range, PrivacyObject, StudipItem, Fe
         //that have autor permissions or higher.
         return $GLOBALS['perm']->have_studip_perm('autor', $this->id, $user_id);
     }
+
+    /**
+     * Get user information for all users in this course
+     *
+     */
+    public function getMembersData(?string $status = ''): array
+    {
+        $result = [];
+
+        if (!$status) {
+            foreach ($this->members->orderBy('position, nachname') as $member) {
+                $result[$member->user_id] = $member->getExportData();
+            }
+            foreach ($this->admission_applicants->findBy('status', 'accepted')->orderBy('position') as $member) {
+                $result[$member->user_id] = $member->getExportData();
+            }
+        } elseif ($status === 'awaiting') {
+            foreach ($this->admission_applicants->findBy('status', $status)->orderBy('position') as $member) {
+                $result[$member->user_id] = $member->getExportData();
+            }
+        } elseif ($status === 'claiming') {
+            $cs = CourseSet::getSetForCourse($this->id);
+            if (is_object($cs) && !$cs->hasAlgorithmRun()) {
+                $claiming_users = User::findFullMany(array_keys(AdmissionPriority::getPrioritiesByCourse($cs->getId(), $this->id)), 'ORDER BY nachname');
+                foreach ($claiming_users as $claiming_user) {
+                        $studycourse = [];
+                        $claiming_user->studycourses->map(function($sc) use (&$studycourse) {
+                            $studycourse[]= $sc->studycourse->name .  ',' . $sc->degree->name . ',' . $sc->semester;
+                        });
+                        $export_data = [
+                            'status' => $status,
+                            'salutation' => $claiming_user->salutation,
+                            'Titel' => $claiming_user->title_front,
+                            'Vorname' => $claiming_user->vorname,
+                            'Nachname' => $claiming_user->nachname,
+                            'Titel2' => $claiming_user->title_rear,
+                            'username' => $claiming_user->username,
+                            'privadr' => $claiming_user->privadr,
+                            'privatnr' => $claiming_user->privatnr,
+                            'Email' => $claiming_user->email,
+                            'Anmeldedatum' => '',
+                            'Matrikelnummer' => $claiming_user->matriculation_number,
+                            'studiengaenge' => implode(';', $studycourse),
+                            'position' => 0,
+                        ];
+                    $result[$claiming_user->user_id] = $export_data;
+                }
+            }
+        }
+
+        return $result;
+    }
+
 }
diff --git a/lib/models/CourseMember.class.php b/lib/models/CourseMember.class.php
index ed133a9..cd7555f 100644
--- a/lib/models/CourseMember.class.php
+++ b/lib/models/CourseMember.class.php
@@ -438,108 +438,28 @@ class CourseMember extends SimpleORMap implements PrivacyObject
         return true;
     }
 
-    /**
-     * Get user information for all users in this course
-     *
-     */
-    public static function getMemberDataByCourse(string $seminar_id, ?string $status = '', ?string $user_id = null): array
+    public function getExportData(): array
     {
-        $query = "SELECT `datafield_id`
-                  FROM `datafields`
-                  WHERE `name`= 'Matrikelnummer'
-                    AND `object_type` = 'user'";
-        $datafield_id = DBManager::get()->fetchColumn($query);
-
-        $user_condition = '';
-        $parameters = [
-            ':seminar_id' => $seminar_id,
-            ':datafield_id' => $datafield_id,
+        $user = $this->user;
+        $studycourse = [];
+        $user->studycourses->map(function($sc) use (&$studycourse) {
+            $studycourse[]= $sc->studycourse->name .  ',' . $sc->degree->name . ',' . $sc->semester;
+        });
+        return [
+            'status' => $this->status,
+            'salutation' => $user->salutation,
+            'Titel' => $user->title_front,
+            'Vorname' => $this->vorname,
+            'Nachname' => $this->nachname,
+            'Titel2' => $user->title_rear,
+            'username' => $this->username,
+            'privadr' => $user->privadr,
+            'privatnr' => $user->privatnr,
+            'Email' => $this->email,
+            'Anmeldedatum' => date('d.m.Y H:i:s', $this->mkdate),
+            'Matrikelnummer' => $this->user->matriculation_number,
+            'studiengaenge' => implode(';', $studycourse),
+            'position' => $this->position,
         ];
-        if ($user_id !== null) {
-            $user_condition = " AND su.`user_id` = :user_id";
-            $parameters[':user_id'] = $user_id;
-        }
-
-
-        if (in_array($status, ['awaiting', 'claiming'])) {
-            $query  = "SELECT su.`user_id`,
-                           su.`status`,
-                           ui.`geschlecht`,
-                           ui.`title_front` AS Titel,
-                           aum.`Vorname`,
-                           aum.`Nachname`,
-                           ui.`title_rear` AS Titel2,
-                           aum.`username`,
-                           ui.`privadr`,
-                           ui.`privatnr`,
-                           aum.`Email`,
-                           ui.`mkdate` AS Anmeldedatum,
-                           IFNULL(aum.`matriculation_number`, dfe.`content`) AS Matrikelnummer,
-                           su.position AS admission_position
-                       FROM admission_seminar_user AS su
-                       LEFT JOIN user_info AS ui USING (user_id)
-                       LEFT JOIN auth_user_md5 AS aum USING (user_id)
-                       LEFT JOIN `datafields_entries` AS dfe
-                         ON dfe.`range_id` = su.`user_id`
-                           AND dfe.`datafield_id` = :datafield_id
-                       WHERE seminar_id = :seminar_id AND su.status = :status
-                           {$user_condition}
-                       GROUP BY aum.user_id
-                       ORDER BY Nachname, Vorname";
-            $parameters[':status'] = $status;
-        }  else {
-            $query = "SELECT su.`user_id`,
-                             su.`status`,
-                             ui.`geschlecht`,
-                             ui.`title_front` AS Titel,
-                             aum.`Vorname`,
-                             aum.`Nachname`,
-                             ui.`title_rear` AS Titel2,
-                             aum.`username`,
-                             ui.`privadr`,
-                             ui.`privatnr`,
-                             aum.`Email`,
-                             ui.`mkdate` AS Anmeldedatum,
-                             IFNULL(aum.`matriculation_number`, dfe.`content`) AS Matrikelnummer
-                      FROM `seminar_user` AS su
-                      JOIN `auth_user_md5` AS aum USING (`user_id`)
-                      LEFT JOIN `user_info` AS ui USING (`user_id`)
-                      LEFT JOIN `datafields_entries` AS dfe
-                        ON dfe.`range_id` = su.`user_id`
-                          AND dfe.`datafield_id` = :datafield_id
-                      WHERE `Seminar_id` = :seminar_id
-                        {$user_condition}
-                      GROUP BY su.`user_id`
-                      ORDER BY `status` DESC, `Nachname`, `Vorname`";
-        }
-
-        return DBManager::get()->fetchAll(
-            $query,
-            $parameters,
-            function (array $row): array {
-                switch ($row['geschlecht']) {
-                    case '0':
-                        $row['geschlecht'] = _('unbekannt');
-                        break;
-                    case '1':
-                        $row['geschlecht'] = _('Herr');
-                        break;
-                    case '2':
-                        $row['geschlecht'] = _('Frau');
-                        break;
-                    case '3':
-                        $row['geschlecht'] = _('divers');
-                        break;
-                }
-
-                $query = "SELECT GROUP_CONCAT(CONCAT(f.`name`, ', ' , a.`name`, ', ', us.`semester`) SEPARATOR '; ')
-                          FROM `user_studiengang` AS us
-                          JOIN `fach` AS f USING (`fach_id`)
-                          JOIN abschluss AS a USING (`abschluss_id`)
-                          WHERE us.`user_id` = :user_id";
-                $row['studiengaenge'] = DBManager::get()->fetchColumn($query, [':user_id' => $row['user_id']]);
-                return $row;
-            }
-        );
     }
 }
diff --git a/lib/models/StatusgruppeUser.php b/lib/models/StatusgruppeUser.php
index a36dcdc..228568b 100644
--- a/lib/models/StatusgruppeUser.php
+++ b/lib/models/StatusgruppeUser.php
@@ -167,4 +167,27 @@ class StatusgruppeUser extends SimpleORMap implements PrivacyObject
         }
     }
 
+    public function getExportData(): array
+    {
+        $user = $this->user;
+        $studycourse = [];
+        $user->studycourses->map(function($sc) use (&$studycourse) {
+            $studycourse[]= $sc->studycourse->name .  ',' . $sc->degree->name . ',' . $sc->semester;
+        });
+        return [
+            'gruppe' => (string)$this->group->name,
+            'salutation' => $user->salutation,
+            'Titel' => $user->title_front,
+            'Vorname' => $this->vorname,
+            'Nachname' => $this->nachname,
+            'Titel2' => $user->title_rear,
+            'username' => $this->username,
+            'privadr' => $user->privadr,
+            'privatnr' => $user->privatnr,
+            'Email' => $this->email,
+            'Anmeldedatum' => date('d.m.Y H:i:s', $this->mkdate),
+            'Matrikelnummer' => $user->matriculation_number,
+            'studiengaenge' => implode(';', $studycourse),
+        ];
+    }
 }
diff --git a/lib/models/User.class.php b/lib/models/User.class.php
index 79de864..056b48a 100644
--- a/lib/models/User.class.php
+++ b/lib/models/User.class.php
@@ -209,6 +209,8 @@ class User extends AuthUserMd5 implements Range, PrivacyObject, Studip\Calendar\
         $config['additional_fields']['config']['get'] = function ($user) {
             return UserConfig::get($user->id);
         };
+        $config['additional_fields']['gender']['get'] = 'getGender';
+        $config['additional_fields']['salutation']['get'] = 'getSalutation';
 
         $config['registered_callbacks']['after_delete'][] = 'cbRemoveFeedback';
         $config['registered_callbacks']['after_delete'][] = 'cbRemoveForumVisits';
@@ -1585,4 +1587,38 @@ class User extends AuthUserMd5 implements Range, PrivacyObject, Studip\Calendar\
             ['this_user_id' => $this->id, 'other_user_id' => $user_id]
         ) > 0;
     }
+
+    /**
+     * Delivers the gender as text
+     * @return string
+     */
+    public function getGender(): string
+    {
+        switch ($this->geschlecht) {
+            case 1:
+                return _('männlich');
+            case 2:
+                return _('weiblich');
+            case 3:
+                return _('divers');
+            default:
+                return _('unbekannt');
+        }
+    }
+
+    /**
+     * Delivers the gender as text
+     * @return string
+     */
+    public function getSalutation(): string
+    {
+        switch ($this->geschlecht) {
+            case 1:
+                return _('Herr');
+            case 2:
+                return _('Frau');
+            default:
+                return '';
+        }
+    }
 }