use SORM instead plain sql, closes #1491

Closes #1491

Merge request studip/studip!930

8 files changed, 65 insertions, 81 deletions

diff --git a/app/controllers/admin/additional.php b/app/controllers/admin/additional.php
index 8931809..eb98db8 100644
--- a/app/controllers/admin/additional.php
+++ b/app/controllers/admin/additional.php
@@ -51,8 +51,7 @@ class Admin_AdditionalController extends AuthenticatedController
             }
             // purge data
             if (Request::submitted('delete')) {
-                $stmt = DBManager::get()->prepare('DELETE FROM datafields_entries WHERE sec_range_id = ?');
-                $stmt->execute([$this->course->id]);
+                DatafieldEntryModel::deleteBySQL('sec_rage_id = ?', [$this->course->id]);
             }
 
             if ($this->course->store()) {
@@ -65,9 +64,7 @@ class Admin_AdditionalController extends AuthenticatedController
         }
 
         // Fetch data
-        $stmt = DBManager::get()->prepare('SELECT COUNT(*) FROM datafields_entries WHERE sec_range_id = ?');
-        $stmt->execute([$this->course->id]);
-        $this->count = $stmt->fetchColumn();
+        $this->count = DatafieldEntryModel::countBySql('sec_range_id = ?', [$this->course->id]);
         $this->list = AuxLockRule::findBySQL('1=1');
     }
 }
diff --git a/app/controllers/course/overview.php b/app/controllers/course/overview.php
index bfff553..fa30b1c 100644
--- a/app/controllers/course/overview.php
+++ b/app/controllers/course/overview.php
@@ -96,13 +96,11 @@ class Course_OverviewController extends AuthenticatedController
                     foreach ((array) $rule['attributes'] as $val) {
                         if ($val == 1) {
                             // Es gibt also Zusatzangaben. Nun noch überprüfen ob der Nutzer diese Angaben schon gemacht hat...
-                            $query = "SELECT 1
-                                      FROM datafields
-                                      LEFT JOIN datafields_entries USING (datafield_id)
-                                      WHERE object_type = 'usersemdata' AND sec_range_id = ? AND range_id = ?";
-                            $statement = DBManager::get()->prepare($query);
-                            $statement->execute([$this->course_id, $GLOBALS['user']->id]);
-                            if (!$statement->fetchColumn()) {
+                            $count = DataField::countBySql("LEFT JOIN datafields_entries ON datafields_entries.datafield_id = datafields.datafield_id
+                                WHERE datafield_id.object_type = 'usersemdata' AND datafields_entries.sec_range_id = ? AND datafields_entries.range_id = ?",
+                                [$this->course_id, $GLOBALS['user']->id]
+                            );
+                            if (!$count) {
                                 $show = true;
                             }
                             break;
diff --git a/lib/classes/Context.php b/lib/classes/Context.php
index 09b3a1b..33e1097 100644
--- a/lib/classes/Context.php
+++ b/lib/classes/Context.php
@@ -242,14 +242,11 @@ class Context
                 && !match_route('dispatch.php/course/members/additional_input')
                 && !match_route('dispatch.php/course/change_view/*'))
             {
-                $query = "SELECT 1
-                          FROM datafields_entries
-                          WHERE range_id = ? AND sec_range_id = ?
-                          LIMIT 1";
-                $statement = DBManager::get()->prepare($query);
-                $statement->execute([$GLOBALS['user']->id, $course['Seminar_id']]);
-
-                if (!$statement->rowCount()) {
+                $count = DatafieldEntryModel::countBySql(
+                    'range_id = ? AND sec_range_id = ?',
+                    [$GLOBALS['user']->id, $course['Seminar_id']]
+                );
+                if (!$count) {
                     header('Location: ' . URLHelper::getURL('dispatch.php/course/members/additional_input'));
                     page_close();
                     die;
diff --git a/lib/classes/I18NStringDatafield.php b/lib/classes/I18NStringDatafield.php
index 4e78178..69c0053 100644
--- a/lib/classes/I18NStringDatafield.php
+++ b/lib/classes/I18NStringDatafield.php
@@ -52,22 +52,23 @@ class I18NStringDatafield extends I18NString
     public function storeTranslations()
     {
         if (is_array($this->lang)) {
-            $db = DBManager::get();
             $object_id = $this->metadata['object_id'];
             /* Replace translations */
-            $deleted = $db->execute("DELETE FROM `datafields_entries` "
-                . "WHERE `datafield_id` = ? "
-                . "AND `range_id` = ? "
-                . "AND `sec_range_id` = ? "
-                . "AND `lang` <> ''",
-                [$object_id[0], $object_id[1], $object_id[2]]);
-            $i18nSQL = $db->prepare("INSERT INTO `datafields_entries` "
-                    . "(`datafield_id`, `range_id`, `sec_range_id`, `content`, `lang`) "
-                    . "VALUES (?,?,?,?,?)");
+            DatafieldEntryModel::deleteBySQL(
+                "`datafield_id` = ? AND `range_id` = ? AND `sec_range_id` = ? AND `lang` <> ''",
+                [$object_id[0], $object_id[1], $object_id[2]]
+            );
             foreach ($this->lang as $lang => $value) {
                 if (mb_strlen($value)) {
-                    $i18nSQL->execute([$object_id[0], $object_id[1], $object_id[2],
-                        (string) $value, (string) $lang]);
+                    DataField::create(
+                        [
+                            'datafield_id' => $object_id[0],
+                            'range_id' => $object_id[1],
+                            'sec_range_id' => $object_id[2],
+                            'content' => (string) $value,
+                            'lang' => (string) $lang,
+                        ]
+                    );
                 }
             }
         }
@@ -85,18 +86,15 @@ class I18NStringDatafield extends I18NString
      */
     public static function load($object_id, $table = '', $field = '', $base = null)
     {
-        $db = DBManager::get();
         if (is_null($base)) {
-            $base = $db->fetchColumn("SELECT `content` "
-                    . "FROM `datafields_entries` "
-                    . "WHERE `datafield_id` = ? "
-                    . "AND `range_id` = ? "
-                    . "AND `sec_range_id` = ? "
-                    . "AND `lang` = ''", $object_id);
+            $base = DataField::findOneBySQL(
+                "`datafield_id` = ? AND `range_id` = ? AND `sec_range_id` = ? AND `lang` = ''",
+                $object_id
+            );
         }
         $table = null;
         $field = null;
-        return new self($base, self::fetchDataForField($object_id, $table, $field),
+        return new self($base->content, self::fetchDataForField($object_id, $table, $field),
                 compact('object_id', 'table', 'field'));
     }
 
@@ -110,13 +108,17 @@ class I18NStringDatafield extends I18NString
      */
     public static function fetchDataForField($object_id, $table, $field)
     {
-        $db = DBManager::get();
-        return $db->fetchPairs("SELECT `lang`, `content` "
-                . "FROM `datafields_entries` "
-                . "WHERE `datafield_id` = ? "
-                . "AND `range_id` = ? "
-                . "AND `sec_range_id` = ? "
-                . "AND `lang` <> ''", $object_id);
+        $result = [];
+        
+        DatafieldEntryModel::findEachBySQL(
+            function (DatafieldEntryModel $model) use (&$result) {
+                $result[$model->lang] = $model->content;
+           },
+            "`datafield_id` = ? AND `range_id` = ? AND `sec_range_id` = ? AND `lang` <> ''",
+            $object_id
+        );
+
+        return $result;
     }
 
     /**
diff --git a/lib/classes/admission/userfilter/DatafieldCondition.class.php b/lib/classes/admission/userfilter/DatafieldCondition.class.php
index 471b806..966fe8e 100644
--- a/lib/classes/admission/userfilter/DatafieldCondition.class.php
+++ b/lib/classes/admission/userfilter/DatafieldCondition.class.php
@@ -88,7 +88,6 @@ class DatafieldCondition extends UserFilterField
     public function getUsers($restrictions = [])
     {
         $db = DBManager::get();
-        $users = [];
         // Standard query getting the values without respecting other values.
         $select = "SELECT user_id FROM
                     auth_user_md5 LEFT JOIN
@@ -103,8 +102,8 @@ class DatafieldCondition extends UserFilterField
      * Gets the value for the given user that is relevant for this
      *
      * @param  String $userId User to check.
-     * @param  Array additional conditions that are required for check.
-     * @return The value(s) for this user.
+     * @param  Array $additional additional conditions that are required for check.
+     * @return array The value(s) for this user.
      */
     public function getUserValues($userId, $additional = null)
     {
diff --git a/lib/models/DataField.class.php b/lib/models/DataField.class.php
index d771eb2..b0b5c96 100644
--- a/lib/models/DataField.class.php
+++ b/lib/models/DataField.class.php
@@ -116,7 +116,7 @@ class DataField extends SimpleORMap implements PrivacyObject
      * Returns a list of all datatype classes with an id as key and a name as
      * value.
      *
-     * @return array() list of all datatype classes
+     * @return array list of all datatype classes
      */
     public static function getDataClass()
     {
@@ -136,8 +136,8 @@ class DataField extends SimpleORMap implements PrivacyObject
     /**
      * Return the mask for the given permission
      *
-     * @param    string     the name of the permission
-     * @return integer  the mask for the permission
+     * @param  string $perm the name of the permission
+     * @return integer the mask for the permission
      * @static
      */
     public static function permMask($perm)
@@ -148,7 +148,7 @@ class DataField extends SimpleORMap implements PrivacyObject
     /**
      * liefert String zu gegebener user_class-Maske
      *
-     * @param    integer    the user class mask
+     * @param integer $class the user class mask
      * @return string       a string consisting of a comma separated list of
      *                      permissions
      */
diff --git a/lib/models/Statusgruppen.php b/lib/models/Statusgruppen.php
index 7520c97..64623b2 100644
--- a/lib/models/Statusgruppen.php
+++ b/lib/models/Statusgruppen.php
@@ -648,11 +648,7 @@ class Statusgruppen extends SimpleORMap implements PrivacyObject
         }
 
         // Remove datafields
-        $query = "DELETE FROM datafields_entries
-                  WHERE range_id = ?";
-        $statement = DBManager::get()->prepare($query);
-        $statement->execute([$this->id]);
-
+        DatafieldEntryModel::deleteBySQL('range_id = ?', [$this->id]);
         $result += parent::delete();
 
         return $result;
diff --git a/lib/models/User.class.php b/lib/models/User.class.php
index 2f768c3..1fc2922 100644
--- a/lib/models/User.class.php
+++ b/lib/models/User.class.php
@@ -295,17 +295,14 @@ class User extends AuthUserMd5 implements Range, PrivacyObject
      */
     public static function findByDatafield($datafield_id, $value)
     {
-        $query = "SELECT range_id
-                  FROM datafields_entries
-                  WHERE datafield_id = :datafield_id
-                    AND content = :value";
-        $search = DBManager::get()->prepare($query);
-        $search->execute(compact('datafield_id', 'value'));
-        $users = [];
-        foreach ($search->fetchAll(PDO::FETCH_COLUMN) as $user_id) {
-            $users[] = new User($user_id);
-        }
-        return $users;
+        return User::findMany(
+            SimpleCollection::createFromArray(
+                DatafieldEntryModel::findBySQL(
+                    'datafield_id = :datafield_id AND content = :value',
+                    compact('datafield_id', 'value')
+                )
+            )->pluck('range_id')
+        );
     }
 
     public static function findDozentenByTermin_id($termin_id)
@@ -1091,20 +1088,18 @@ class User extends AuthUserMd5 implements Range, PrivacyObject
             // Generische Datenfelder zusammenführen (bestehende Einträge des
             // "neuen" Nutzers werden dabei nicht überschrieben)
             $old_user = User::find($old_id);
-
-            $query = "INSERT INTO datafields_entries
-                        (datafield_id, range_id, sec_range_id, content, mkdate, chdate)
-                      VALUES (:datafield_id, :range_id, :sec_range_id, :content,
-                              :mkdate, :chdate)
-                      ON DUPLICATE KEY
-                        UPDATE content = IF(content IN ('', 'default_value'), VALUES(content), content),
-                               chdate = UNIX_TIMESTAMP()";
-
-            $old_user->datafields->each(function ($field) use ($new_id, $query) {
+            $old_user->datafields->each(function ($field) use ($new_id) {
                 if (!$field->isNew() && $field->content !== null) {
                     $data = $field->toArray('datafield_id sec_range_id content mkdate chdate');
                     $data['range_id'] = $new_id;
-                    DBManager::get()->execute($query, $data);
+
+                    $datafield = DatafieldEntryModel::findOneBySQL('datafield_id = ?', [$data['datafield_id']]);
+                    if(!$datafield) {
+                        $datafield = DatafieldEntryModel::build($data);
+                    } else {
+                        $datafield->setData($data);
+                    }
+                    $datafield->store();
                 }
             });