From 6534191d59521e1e5a88638b1c6a1c77f74dd12a Mon Sep 17 00:00:00 2001
From: Michael Weimann <mail@michael-weimann.eu>
Date: Thu, 31 Oct 2019 20:02:34 +0100
Subject: Migrate news to model class

---
 .../2019_10_15_000000_create_news_table.php        | 137 +++++++++++++++++++++
 includes/pages/admin_news.php                      |  43 +++----
 includes/pages/user_news.php                       |  85 ++++++-------
 src/Models/News/News.php                           |  30 +++++
 src/Models/User/User.php                           |  10 ++
 tests/Unit/Models/News/NewsTest.php                |  80 ++++++++++++
 tests/Unit/Models/User/UserTest.php                |  58 ++++++++-
 7 files changed, 362 insertions(+), 81 deletions(-)
 create mode 100644 db/migrations/2019_10_15_000000_create_news_table.php
 create mode 100644 src/Models/News/News.php
 create mode 100644 tests/Unit/Models/News/NewsTest.php

diff --git a/db/migrations/2019_10_15_000000_create_news_table.php b/db/migrations/2019_10_15_000000_create_news_table.php
new file mode 100644
index 00000000..d6b93265
--- /dev/null
+++ b/db/migrations/2019_10_15_000000_create_news_table.php
@@ -0,0 +1,137 @@
+<?php
+declare(strict_types=1);
+
+namespace Engelsystem\Migrations;
+
+use Carbon\Carbon;
+use Engelsystem\Database\Migration\Migration;
+use Illuminate\Database\Schema\Blueprint;
+use stdClass;
+
+/**
+ * This migration creates the news table and copies the existing News table records to the new one.
+ */
+class CreateNewsTable extends Migration
+{
+    use ChangesReferences, Reference;
+
+    /**
+     * Creates the news table, copies the data and drops the News table.
+     */
+    public function up(): void
+    {
+        $hasPreviousNewsTable = $this->schema->hasTable('News');
+
+        if ($hasPreviousNewsTable) {
+            // rename because some SQL DBMS handle identifiers case insensitive
+            $this->schema->rename('News', 'PreviousNews');
+        }
+
+        $this->createNewNewsTable();
+
+        if ($hasPreviousNewsTable) {
+            $this->copyPreviousToNewNewsTable();
+            $this->changeReferences(
+                'PreviousNews',
+                'ID',
+                'news',
+                'id',
+                'unsignedInteger'
+            );
+            $this->schema->drop('PreviousNews');
+        }
+    }
+
+    /**
+     * Recreates the previous News table, copies back the data and drops the new news table.
+     */
+    public function down(): void
+    {
+        // rename because some SQL DBMS handle identifiers case insensitive
+        $this->schema->rename('news', 'new_news');
+
+        $this->createPreviousNewsTable();
+        $this->copyNewToPreviousNewsTable();
+        $this->changeReferences(
+            'new_news',
+            'id',
+            'News',
+            'ID',
+            'unsignedInteger'
+        );
+        $this->schema->drop('new_news');
+    }
+
+    private function createNewNewsTable(): void
+    {
+        $this->schema->create('news', function (Blueprint $table) {
+            $table->increments('id');
+            $table->string('title', 150);
+            $table->text('text');
+            $table->boolean('is_meeting')->default(0);
+            $this->referencesUser($table, false);
+            $table->timestamps();
+        });
+    }
+
+    private function copyPreviousToNewNewsTable(): void
+    {
+        /** @var stdClass[] $previousNewsRecords */
+        $previousNewsRecords = $this->schema
+            ->getConnection()
+            ->table('PreviousNews')
+            ->get();
+
+        foreach ($previousNewsRecords as $previousNews) {
+            $date = Carbon::createFromTimestamp($previousNews->Datum);
+            $this->schema->getConnection()->table('news')->insert([
+                'id'         => $previousNews->ID,
+                'title'      => $previousNews->Betreff,
+                'text'       => $previousNews->Text,
+                'is_meeting' => $previousNews->Treffen,
+                'user_id'    => $previousNews->UID,
+                'created_at' => $date,
+                'updated_at' => $date,
+            ]);
+        }
+    }
+
+    private function createPreviousNewsTable(): void
+    {
+        $this->schema->create('News', function (Blueprint $table) {
+            $table->increments('ID');
+            $table->integer('Datum');
+            $table->string('Betreff', 150)
+                ->default('');
+            $table->text('Text');
+            $table->boolean('Treffen');
+            $table->unsignedInteger('UID');
+            $table->foreign('UID')
+                ->references('id')
+                ->on('users');
+        });
+    }
+
+    private function copyNewToPreviousNewsTable(): void
+    {
+        /** @var stdClass[] $newsRecords */
+        $newsRecords = $this->schema
+            ->getConnection()
+            ->table('new_news')
+            ->get();
+
+        foreach ($newsRecords as $newsRecord) {
+            $date = Carbon::createFromFormat('Y-m-d H:i:s', $newsRecord->created_at)
+                ->getTimestamp();
+
+            $this->schema->getConnection()->table('News')->insert([
+                'ID'      => $newsRecord->id,
+                'Datum'   => $date,
+                'Betreff' => $newsRecord->title,
+                'Text'    => $newsRecord->text,
+                'UID'     => $newsRecord->user_id,
+                'Treffen' => $newsRecord->is_meeting,
+            ]);
+        }
+    }
+}
diff --git a/includes/pages/admin_news.php b/includes/pages/admin_news.php
index 21245eb9..75d8291e 100644
--- a/includes/pages/admin_news.php
+++ b/includes/pages/admin_news.php
@@ -1,7 +1,6 @@
 <?php
 
-use Engelsystem\Database\DB;
-use Engelsystem\Models\User\User;
+use Engelsystem\Models\News\News;
 
 /**
  * @return string
@@ -22,17 +21,17 @@ function admin_news()
         return error('Incomplete call, missing News ID.', true);
     }
 
-    $news = DB::selectOne('SELECT * FROM `News` WHERE `ID`=? LIMIT 1', [$news_id]);
+    $news = News::find($news_id);
     if (empty($news)) {
         return error('No News found.', true);
     }
 
     switch ($request->input('action')) {
         case 'edit':
-            $user_source = User::find($news['UID']);
+            $user_source = $news->user;
             if (
                 !auth()->can('admin_news_html')
-                && strip_tags($news['Text']) != $news['Text']
+                && strip_tags($news->text) != $news->text
             ) {
                 $html .= warning(
                     __('This message contains HTML. After saving the post some formatting will be lost!'),
@@ -42,11 +41,11 @@ function admin_news()
 
             $html .= form(
                 [
-                    form_info(__('Date'), date('Y-m-d H:i', $news['Datum'])),
+                    form_info(__('Date'), $news->created_at->format('Y-m-d H:i')),
                     form_info(__('Author'), User_Nick_render($user_source)),
-                    form_text('eBetreff', __('Subject'), $news['Betreff']),
-                    form_textarea('eText', __('Message'), $news['Text']),
-                    form_checkbox('eTreffen', __('Meeting'), $news['Treffen'] == 1, 1),
+                    form_text('eBetreff', __('Subject'), $news->title),
+                    form_textarea('eText', __('Message'), $news->text),
+                    form_checkbox('eTreffen', __('Meeting'), $news->is_meeting === true, 1),
                     form_submit('submit', __('Save'))
                 ],
                 page_link_to('admin_news', ['action' => 'save', 'id' => $news_id])
@@ -65,24 +64,10 @@ function admin_news()
                 $text = strip_tags($text);
             }
 
-            DB::update('
-                UPDATE `News` SET
-                    `Datum`=?,
-                    `Betreff`=?,
-                    `Text`=?,
-                    `UID`=?,
-                    `Treffen`=?
-                WHERE `ID`=?
-                ',
-                [
-                    time(),
-                    strip_tags($request->postData('eBetreff')),
-                    $text,
-                    $user->id,
-                    $request->has('eTreffen') ? 1 : 0,
-                    $news_id
-                ]
-            );
+            $news->title = strip_tags($request->postData('eBetreff'));
+            $news->text = $text;
+            $news->is_meeting = $request->has('eTreffen');
+            $news->save();
 
             engelsystem_log('News updated: ' . $request->postData('eBetreff'));
             success(__('News entry updated.'));
@@ -90,8 +75,8 @@ function admin_news()
             break;
 
         case 'delete':
-            DB::delete('DELETE FROM `News` WHERE `ID`=? LIMIT 1', [$news_id]);
-            engelsystem_log('News deleted: ' . $news['Betreff']);
+            $news->delete();
+            engelsystem_log('News deleted: ' . $news->title);
             success(__('News entry deleted.'));
             redirect(page_link_to('news'));
             break;
diff --git a/includes/pages/user_news.php b/includes/pages/user_news.php
index 39ce24a5..f67896da 100644
--- a/includes/pages/user_news.php
+++ b/includes/pages/user_news.php
@@ -1,6 +1,7 @@
 <?php
 
 use Engelsystem\Database\DB;
+use Engelsystem\Models\News\News;
 use Engelsystem\Models\User\User;
 
 /**
@@ -42,20 +43,17 @@ function user_meetings()
         $page = 0;
     }
 
-    $news = DB::select(sprintf('
-        SELECT *
-        FROM `News`
-        WHERE `Treffen`=1
-        ORDER BY `Datum`DESC
-        LIMIT %u, %u',
-        $page * $display_news,
-        $display_news
-    ));
+    $news = News::where('is_meeting', true)
+        ->orderBy('created_at', 'DESC')
+        ->limit($display_news)
+        ->offset($page * $display_news)
+        ->get();
+
     foreach ($news as $entry) {
         $html .= display_news($entry);
     }
 
-    $dis_rows = ceil(count(DB::select('SELECT `ID` FROM `News`')) / $display_news);
+    $dis_rows = ceil(News::where('is_meeting', true)->count() / $display_news);
     $html .= '<div class="text-center">' . '<ul class="pagination">';
     for ($i = 0; $i < $dis_rows; $i++) {
         if ($request->has('page') && $i == $request->input('page', 0)) {
@@ -75,28 +73,28 @@ function user_meetings()
 /**
  * Renders the text content of a news entry
  *
- * @param array $news
+ * @param News $news
  * @return string HTML
  */
-function news_text($news)
+function news_text(News $news): string
 {
-    $text = ReplaceSmilies($news['Text']);
+    $text = ReplaceSmilies($news->text);
     $text = preg_replace("/\r\n\r\n/m", '<br><br>', $text);
     return $text;
 }
 
 /**
- * @param array $news
+ * @param News $news
  * @return string
  */
-function display_news($news)
+function display_news(News $news): string
 {
     global $page;
 
     $html = '';
-    $html .= '<div class="panel' . ($news['Treffen'] == 1 ? ' panel-info' : ' panel-default') . '">';
+    $html .= '<div class="panel' . ($news->is_meeting ? ' panel-info' : ' panel-default') . '">';
     $html .= '<div class="panel-heading">';
-    $html .= '<h3 class="panel-title">' . ($news['Treffen'] == 1 ? '[Meeting] ' : '') . ReplaceSmilies($news['Betreff']) . '</h3>';
+    $html .= '<h3 class="panel-title">' . ($news->is_meeting ? '[Meeting] ' : '') . ReplaceSmilies($news->title) . '</h3>';
     $html .= '</div>';
     $html .= '<div class="panel-body">' . news_text($news) . '</div>';
 
@@ -104,21 +102,21 @@ function display_news($news)
     if (auth()->can('admin_news')) {
         $html .= '<div class="pull-right">'
             . button_glyph(
-                page_link_to('admin_news', ['action' => 'edit', 'id' => $news['ID']]),
+                page_link_to('admin_news', ['action' => 'edit', 'id' => $news->id]),
                 'edit',
                 'btn-xs'
             )
             . '</div>';
     }
-    $html .= '<span class="glyphicon glyphicon-time"></span> ' . date('Y-m-d H:i', $news['Datum']) . '&emsp;';
+    $html .= '<span class="glyphicon glyphicon-time"></span> ' . $news->created_at->format('Y-m-d H:i') . '&emsp;';
 
-    $html .= User_Nick_render(User::find($news['UID']));
+    $html .= User_Nick_render(User::find($news->user_id));
     if ($page != 'news_comments') {
-        $html .= '&emsp;<a href="' . page_link_to('news_comments', ['nid' => $news['ID']]) . '">'
+        $html .= '&emsp;<a href="' . page_link_to('news_comments', ['nid' => $news->id]) . '">'
             . '<span class="glyphicon glyphicon-comment"></span> '
             . __('Comments') . ' &raquo;</a> '
             . '<span class="badge">'
-            . count(DB::select('SELECT `ID` FROM `NewsComments` WHERE `Refid`=?', [$news['ID']]))
+            . count(DB::select('SELECT `ID` FROM `NewsComments` WHERE `Refid`=?', [$news->id]))
             . '</span>';
     }
     $html .= '</div>';
@@ -135,13 +133,13 @@ function user_news_comments()
     $request = request();
 
     $html = '<div class="col-md-12"><h1>' . user_news_comments_title() . '</h1>';
+    $nid = $request->input('nid');
     if (
         $request->has('nid')
-        && preg_match('/^\d{1,}$/', $request->input('nid'))
-        && count(DB::select('SELECT `ID` FROM `News` WHERE `ID`=? LIMIT 1', [$request->input('nid')])) > 0
+        && preg_match('/^\d{1,}$/', $nid)
+        && News::where('id', $request->input('nid'))->count() > 0
     ) {
-        $nid = $request->input('nid');
-        $news = DB::selectOne('SELECT * FROM `News` WHERE `ID`=? LIMIT 1', [$nid]);
+        $news = News::find('id');
         if ($request->hasPostData('submit') && $request->has('text')) {
             $text = $request->input('text');
             DB::insert('
@@ -212,18 +210,12 @@ function user_news()
             $text = strip_tags($text);
         }
 
-        DB::insert('
-            INSERT INTO `News` (`Datum`, `Betreff`, `Text`, `UID`, `Treffen`)
-            VALUES (?, ?, ?, ?, ?)
-            ',
-            [
-                time(),
-                strip_tags($request->postData('betreff')),
-                $text,
-                $user->id,
-                $isMeeting,
-            ]
-        );
+        News::create([
+            'title'      => strip_tags($request->postData('betreff')),
+            'text'       => $text,
+            'user_id'    => $user->id,
+            'is_meeting' => !!$isMeeting,
+        ]);
 
         engelsystem_log('Created news: ' . $request->postData('betreff') . ', treffen: ' . $isMeeting);
         success(__('Entry saved.'));
@@ -236,20 +228,17 @@ function user_news()
         $page = 0;
     }
 
-    $news = DB::select(sprintf('
-            SELECT *
-            FROM `News`
-            ORDER BY `Datum`
-            DESC LIMIT %u, %u
-        ',
-        $page * $display_news,
-        $display_news
-    ));
+    $news = News::query()
+        ->orderBy('created_at', 'DESC')
+        ->limit($display_news)
+        ->offset($page * $display_news)
+        ->get();
+
     foreach ($news as $entry) {
         $html .= display_news($entry);
     }
 
-    $dis_rows = ceil(count(DB::select('SELECT `ID` FROM `News`')) / $display_news);
+    $dis_rows = ceil(News::query()->count() / $display_news);
     $html .= '<div class="text-center">' . '<ul class="pagination">';
     for ($i = 0; $i < $dis_rows; $i++) {
         if ($request->has('page') && $i == $request->input('page', 0)) {
diff --git a/src/Models/News/News.php b/src/Models/News/News.php
new file mode 100644
index 00000000..ae67752b
--- /dev/null
+++ b/src/Models/News/News.php
@@ -0,0 +1,30 @@
+<?php
+declare(strict_types=1);
+
+namespace Engelsystem\Models\News;
+
+use Engelsystem\Models\User\UsesUserModel;
+use Illuminate\Database\Eloquent\Model;
+
+/**
+ * This class represents a news item.
+ */
+class News extends Model
+{
+    use UsesUserModel;
+
+    protected $casts = [
+        'is_meeting' => 'boolean',
+    ];
+
+    protected $attributes = [
+        'is_meeting' => false,
+    ];
+
+    protected $fillable = [
+        'title',
+        'text',
+        'is_meeting',
+        'user_id',
+    ];
+}
diff --git a/src/Models/User/User.php b/src/Models/User/User.php
index af213c4e..cca96dbe 100644
--- a/src/Models/User/User.php
+++ b/src/Models/User/User.php
@@ -4,6 +4,8 @@ namespace Engelsystem\Models\User;
 
 use Carbon\Carbon;
 use Engelsystem\Models\BaseModel;
+use Engelsystem\Models\News\News;
+use Illuminate\Database\Eloquent\Relations\HasMany;
 use Illuminate\Database\Eloquent\Relations\HasOne;
 use Illuminate\Database\Query\Builder as QueryBuilder;
 
@@ -95,4 +97,12 @@ class User extends BaseModel
             ->hasOne(State::class)
             ->withDefault();
     }
+
+    /**
+     * @return HasMany
+     */
+    public function news(): HasMany
+    {
+        return $this->hasMany(News::class);
+    }
 }
diff --git a/tests/Unit/Models/News/NewsTest.php b/tests/Unit/Models/News/NewsTest.php
new file mode 100644
index 00000000..7309c0b0
--- /dev/null
+++ b/tests/Unit/Models/News/NewsTest.php
@@ -0,0 +1,80 @@
+<?php
+declare(strict_types=1);
+
+use Engelsystem\Models\News\News;
+use Engelsystem\Models\User\User;
+use Engelsystem\Test\Unit\HasDatabase;
+use Engelsystem\Test\Unit\TestCase;
+
+/**
+ * This class provides tests for the News model.
+ */
+class NewsTest extends TestCase
+{
+    use HasDatabase;
+
+    /**
+     * @var array
+     */
+    private $newsData;
+
+    /**
+     * @var User
+     */
+    private $user;
+
+    /**
+     * @return void
+     */
+    protected function setUp(): void
+    {
+        parent::setUp();
+        $this->initDatabase();
+
+        $this->user = User::make([
+            'name'     => 'lorem',
+            'password' => '',
+            'email'    => 'foo@bar.batz',
+            'api_key'  => '',
+        ]);
+        $this->user->save();
+
+        $this->newsData = [
+            'title'   => 'test title',
+            'text'    => 'test text',
+            'user_id' => $this->user->id
+        ];
+    }
+
+    /**
+     * Tests that creating a News item with default values works.
+     *
+     * @return void
+     */
+    public function testCreateDefault(): void
+    {
+        $news = News::create($this->newsData);
+
+        $this->assertSame(1, $news->id);
+        $this->assertSame($this->newsData['title'], $news->title);
+        $this->assertSame($this->newsData['text'], $news->text);
+        $this->assertFalse($news->is_meeting);
+    }
+
+    /**
+     * Tests that creating a News item with all fill values works.
+     *
+     * @return void
+     */
+    public function testCreate(): void
+    {
+        $news = News::create(
+            $this->newsData + ['is_meeting' => true,]
+        );
+
+        $this->assertSame(1, $news->id);
+        $this->assertSame($this->newsData['title'], $news->title);
+        $this->assertSame($this->newsData['text'], $news->text);
+        $this->assertTrue($news->is_meeting);
+    }
+}
diff --git a/tests/Unit/Models/User/UserTest.php b/tests/Unit/Models/User/UserTest.php
index b89f832b..96c2c1b7 100644
--- a/tests/Unit/Models/User/UserTest.php
+++ b/tests/Unit/Models/User/UserTest.php
@@ -3,6 +3,7 @@
 namespace Engelsystem\Test\Unit\Models;
 
 use DMS\PHPUnitExtensions\ArraySubset\ArraySubsetAsserts;
+use Engelsystem\Models\News\News;
 use Engelsystem\Models\User\Contact;
 use Engelsystem\Models\User\HasUserModel;
 use Engelsystem\Models\User\PersonalData;
@@ -25,6 +26,15 @@ class UserTest extends TestCase
         'api_key'  => '',
     ];
 
+    /**
+     * Prepare test
+     */
+    protected function setUp(): void
+    {
+        parent::setUp();
+        $this->initDatabase();
+    }
+
     /**
      * @return array
      */
@@ -93,11 +103,51 @@ class UserTest extends TestCase
     }
 
     /**
-     * Prepare test
+     * @covers User::news()
+     *
+     * @dataProvider hasManyRelationsProvider
+     *
+     * @param string $class Class name of the related models
+     * @param string $name Name of the accessor for the related models
+     * @param array  $data List of the related models
      */
-    protected function setUp(): void
+    public function testHasManyRelations(string $class, string $name, array $data): void
     {
-        parent::setUp();
-        $this->initDatabase();
+        $user = new User($this->data);
+        $user->save();
+
+        $relatedModelIds = [];
+
+        foreach ($data as $d) {
+            $stored = $class::create($d + ['user_id' => $user->id]);
+            $relatedModelIds[] = $stored->id;
+        }
+
+        $this->assertEquals($relatedModelIds, $user->{$name}->modelKeys());
+    }
+
+    /**
+     * @return array
+     */
+    public function hasManyRelationsProvider(): array
+    {
+        return [
+            'news' => [
+                News::class,
+                'news',
+                [
+                    [
+                        'title'      => 'Hey hoo',
+                        'text'       => 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit.',
+                        'is_meeting' => false,
+                    ],
+                    [
+                        'title'      => 'Huuhuuu',
+                        'text'       => 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit.',
+                        'is_meeting' => true,
+                    ],
+                ]
+            ]
+        ];
     }
 }
-- 
cgit v1.2.3-54-g00ecf