refine member-export, fixes #3841

Closes #3841

Merge request studip/studip!2711

5 files changed, 163 insertions, 101 deletions

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 '';
+        }
+    }
 }