handz/fxm2/service/frontend/controllers/ArticleController.php

<?php
/**
 * Created by PhpStorm.
 * User: chenkuan
 * Date: 2017/11/9
 * Time: 上午10:39
 */

namespace frontend\controllers;

use common\helpers\PaginationHelper;
use common\helpers\ValidatorHelper;
use frontend\models\Article;
use frontend\models\ArticleCategory;
use Symfony\Component\Yaml\Tests\A;
use yii\data\Pagination;

/**
 * 注：article表的is_publication,is_recommend,is_top的数据类型是bit(1)，在线上不能按常规的判断方式判断，所以在这里先转换
 * 刚开始使用的是CONVERT函数，但是当select方法的参数是字符串时，select方法会根据逗号分隔成数据,CONVERT函数的两个参数之间有逗号，所以不能正确地转换成数据，解决办法是使用CAST函数或调用select方法时传入数组
 * 
 * CONVERT(is_top, SIGNED) AS is_top 第一个参数是字段，第二个是要转换的类型，AS取别名，Mysql函数有多个参数时，不要传字符串到select方法
 * CAST(is_top AS SIGNED) AS is_top 第一个AS是类型转换，第二个AS是取别名
 * 
 * @see \yii\db\Query::select()
 * @see https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
 * @see https://dev.mysql.com/doc/refman/5.6/en/cast-functions.html
 */
class ArticleController extends BaseController
{
    /**
     * 前台首页公告／动态／汇评列表获取接口
     * 1:Announcement
     * 2:Devisenmarkt
     * 5:ReviewAnalysis
     */
    public function actionGetArticleList()
    {
        $request = \Yii::$app->request;
        $articleCategory = $request->get('id');
        $type = $request->get('type', '');
        $isContainChildren = $request->get('isContainChildren', true);
        $pageNumber = $request->get('pageNumber', 1);
        $pageSize = $request->get('pageSize', 10);

        $offset = 0;
        if ($pageNumber > 1) {
            $offset = ($pageNumber - 1) * $pageSize;
        }

        $ac = ArticleCategory::findOne(['id' => $articleCategory]);

        $query = Article::find()->alias('a')
            ->leftJoin('bit_article_category ac', 'a.article_category_id = ac.id');
        if ($articleCategory != null) {
            if (strcasecmp($type, 'top') == 0) {
                if ($isContainChildren) {
                    $query->andWhere([
                        'a.is_publication' => 1,
                        'a.is_top' => 1,
                        ])->andWhere(['like', 'ac.path', $ac->path.'%', false])->orderBy([
                            'a.is_top' => SORT_DESC,
                            'a.create_date' => SORT_DESC,
                        ]);
                } else {
                    $query->andWhere([
                        'a.is_publication' => 1,
                        'a.is_top' => 1,
                        'a.article_category_id' => $articleCategory,
                    ])->orderBy([
                        'a.is_top' => SORT_DESC,
                        'a.create_date' => SORT_DESC,
                    ]);
                }
            } elseif (strcasecmp($type, 'recommend') == 0) {
                if ($isContainChildren) {
                    $query->andWhere([
                        'a.is_publication' => 1,
                        'a.is_recommend' => 1,
                    ])->andWhere(['like', 'ac.path', $ac->path.'%', false])->orderBy([
                        'a.is_top' => SORT_DESC,
                        'a.create_date' => SORT_DESC,
                    ]);
                } else {
                    $query->andWhere([
                        'a.is_publication' => 1,
                        'a.is_recommend' => 1,
                        'a.article_category_id' => $articleCategory,
                    ])->orderBy([
                        'a.is_top' => SORT_DESC,
                        'a.create_date' => SORT_DESC,
                    ]);
                }
            } elseif (strcasecmp($type, 'hot') == 0) {
                if ($isContainChildren) {
                    $query->andWhere([
                        'a.is_publication' => 1,
                    ])->andWhere(['like', 'ac.path', $ac->path.'%', false])->orderBy([
                        'a.hits' => SORT_DESC,
                        'a.create_date' => SORT_DESC,
                    ]);
                } else {
                    $query->andWhere([
                        'a.is_publication' => 1,
                        'a.article_category_id' => $articleCategory,
                    ])->orderBy([
                        'a.hits' => SORT_DESC,
                        'a.create_date' => SORT_DESC,
                    ]);
                }
            } elseif (strcasecmp($type, 'img') == 0) {
                if ($isContainChildren) {
                    $query->andWhere([
                        'a.is_publication' => 1,
                    ])->andWhere(['NOT', ['a.image_path' => null]])->andWhere(
                        ['like', 'ac.path', $ac->path.'%', false]
                    )->orderBy([
                        'a.create_date' => SORT_DESC,
                    ]);
                } else {
                    $query->andWhere([
                        'a.is_publication' => 1,
                        'a.article_category_id' => $articleCategory,
                    ])->andWhere(['NOT', ['a.image_path' => null]])->orderBy([
                        'a.hits' => SORT_DESC,
                        'a.create_date' => SORT_DESC,
                    ]);
                }
            } elseif ($isContainChildren) {
                $query->andWhere([
                    'a.is_publication' => 1,
                ])->andWhere(
                    ['like', 'ac.path', $ac->path.'%', false]
                )->orderBy([
                    'a.is_top' => SORT_DESC,
                    'a.create_date' => SORT_DESC,
                ]);
            } else {
                $query->andWhere([
                    'a.is_publication' => 1,
                    'a.article_category_id' => $articleCategory,
                ])->orderBy([
                    'a.is_top' => SORT_DESC,
                    'a.create_date' => SORT_DESC,
                ]);
            }
        } elseif (strcasecmp($type, 'top') == 0) {
            $query->andWhere([
                'a.is_publication' => 1,
                'a.is_top' => 1,
            ])->orderBy([
                'a.is_top' => SORT_DESC,
                'a.create_date' => SORT_DESC,
            ]);
        } elseif (strcasecmp($type, 'recommend') == 0) {
            $query->andWhere([
                'a.is_publication' => 1,
                'a.is_recommend' => 1,
            ])->orderBy([
                'a.is_top' => SORT_DESC,
                'a.create_date' => SORT_DESC,
            ]);
        } elseif (strcasecmp($type, 'hot') == 0) {
            $query->andWhere([
                'a.is_publication' => 1,
            ])->orderBy([
                'a.hits' => SORT_DESC,
                'a.create_date' => SORT_DESC,
            ]);
        } elseif (strcasecmp($type, 'img') == 0) {
            $query->andWhere([
                'a.is_publication' => 1,
            ])->andWhere(
                ['NOT', ['a.image_path' => null]]
            )->orderBy([
                'a.create_date' => SORT_DESC,
            ]);
        } else {
            $query->andWhere([
                'a.is_publication' => 1,
            ])->orderBy([
                'a.is_top' => SORT_DESC,
                'a.create_date' => SORT_DESC,
            ]);
        }

        $countQuery = clone $query;
        $count = (int)$countQuery->select('count(*)')->scalar();

        $query->select('a.id, CAST(is_publication AS SIGNED) AS is_publication, CAST(is_recommend AS SIGNED) AS is_recommend, CAST(is_top AS SIGNED) AS is_top,a.create_date,
            a.article_category_id,a.hits,a.image_path,a.title,a.link_url,
            a.min_content,a.content,ac.name,
            ac.path');

        $query->offset($offset)->limit($pageSize);

        $result['data'] = $query->asArray()->all();
        $result['totalCount'] = $count;
        return $this->outJson(1, $result);
    }

    /**
     * 文章详情
     */
    public function actionArticleDetail()
    {
        $result = [];
        $articleId = \Yii::$app->request->get('id');
        $model = Article::find()->alias('a')
            ->leftJoin(ArticleCategory::tableName().' ac', 'a.article_category_id = ac.id')
            ->select([
                'a.*',
                'CONVERT(a.is_publication, SIGNED) AS is_publication',
                'CONVERT(a.is_recommend, SIGNED) AS is_recommend',
                'CONVERT(a.is_top, SIGNED) AS is_top',
                'ac.name AS category_name'
            ])->where(['a.id' => $articleId]);

        $modelClone = clone $model;
        $article = $model->one();
        if ($article) {
            $article->hits += 1;
            $article->save();
            
            $result['article'] = $modelClone->asArray()->one();
            $result['nextArticle'] = $this->getNextArticle($article);
            $result['previousArticle'] = $this->getPreviousArticle($article);
            $result['relationArticle'] = $this->getRelationArticle($article);

            return $this->outJson(1, $result);
        } else {
            return $this->outJson(0, [], '获取文章详情失败');
        }
    }

    private function getNextArticle(Article $article)
    {
        $query = Article::find()->alias('a')
            ->select('a.id,CAST(is_publication AS SIGNED) AS is_publication, CAST(is_recommend AS SIGNED) AS is_recommend, CAST(is_top AS SIGNED) AS is_top,a.create_date,a.create_date,
            a.article_category_id,a.hits,a.image_path,a.title,a.link_url,
            a.min_content,ac.path,ac.language')
            ->leftJoin('bit_article_category ac', 'a.article_category_id = ac.id')
            ->andWhere(['a.article_category_id' => $article->article_category_id])
            ->andWhere(['ac.language' => 0])
            ->andWhere(['a.is_publication' => 1])
            ->andWhere(['>', 'a.create_date', $article->create_date])
            ->orderBy(['a.create_date' => SORT_ASC])
            ->limit(1);
        $rs = $query->asArray()->one();
        if (!empty($rs) && count($rs) > 0) {
            return $rs;
        }
        return null;
    }

    private function getPreviousArticle(Article $article)
    {
        $query = Article::find()->alias('a')
            ->select('a.id,CAST(is_publication AS SIGNED) AS is_publication, CAST(is_recommend AS SIGNED) AS is_recommend, CAST(is_top AS SIGNED) AS is_top,a.create_date,a.create_date,
            a.article_category_id,a.hits,a.image_path,a.title,a.link_url,
            a.min_content,
            ac.path,ac.language')
            ->leftJoin('bit_article_category ac', 'a.article_category_id = ac.id')
            ->andWhere(['a.article_category_id' => $article->article_category_id])
            ->andWhere(['ac.language' => 0])
            ->andWhere(['a.is_publication' => 1])
            ->andWhere(['<', 'a.create_date', $article->create_date])
            ->orderBy(['a.create_date' => SORT_DESC])
            ->limit(1);
        $rs = $query->asArray()->one();
        if (!empty($rs) && count($rs) > 0) {
            return $rs;
        }
        return null;
    }

    private function getRelationArticle(Article $article)
    {
        $ids = [];
        $next = $this->getNextArticle($article);
        $previous = $this->getPreviousArticle($article);
        if (!empty($next)) {
            $ids[] = $next['id'];
        }
        if (!empty($previous)) {
            $ids[] = $previous['id'];
        }
        $query = Article::find()->alias('a')
            ->select('a.id,CAST(is_publication AS SIGNED) AS is_publication, CAST(is_recommend AS SIGNED) AS is_recommend, CAST(is_top AS SIGNED) AS is_top,a.create_date,a.create_date,
            a.article_category_id,a.hits,a.image_path,a.title,a.link_url,
            a.min_content,
            ac.path,ac.language')
            ->leftJoin('bit_article_category ac', 'a.article_category_id = ac.id')
            ->andWhere(['a.article_category_id' => $article->article_category_id])
            ->andWhere(['<>', 'a.id', $article->id])
            ->andWhere(['not in', 'a.id', $ids])
            ->andWhere(['ac.language' => 0])
            ->andWhere(['a.is_publication' => 1])
            ->andWhere(['<', 'a.create_date', $article->create_date])
            ->orderBy(['a.create_date' => SORT_DESC])
            ->limit(4);
        $rs = $query->asArray()->all();
        if (!empty($rs) && count($rs) > 0) {
            return $rs;
        }
        return null;
    }

    /**
     * 获取不同分类的文章列表
     */
    public function actionArticleList()
    {
        $request = \Yii::$app->getRequest()->get();
        $data['id'] = (int)$request['id'];
        $data['pageNumber'] = isset($request['pageNumber']) ? (int)($request['pageNumber']) : 1;
        $data['pageSize'] = isset($request['pageSize']) ? (int)($request['pageSize']) : 20;
        $data['orderBy'] = isset($request['orderBy']) ? trim($request['orderBy']) : 'create_date';
        $data['order'] = isset($request['order']) ? trim($request['order']) : 'desc';
        $data['keyword'] = isset($request['keyword']) ? trim($request['keyword']) : '';
        $data['searchBy'] = isset($request['searchBy']) ? trim($request['searchBy']) : 'title';
        $data['is_publication'] = isset($request['is_publication']) ? intval($request['is_publication']) : null;

        $data = ValidatorHelper::validateData($data, [
            ['pageNumber', 'integer', 'min' => 1],
            ['pageSize', 'integer', 'min' => 1],
            ['orderBy', 'string'],
        ], $errors);

        if ($data == false) {
            return $this->outJson(0, '', $errors);
        }

        $query = Article::find()
            ->select(['*', 'CONVERT(is_publication, SIGNED) AS is_publication', 'CONVERT(is_recommend, SIGNED) AS is_recommend', 'CONVERT(is_top, SIGNED) AS is_top'])
            ->where(['article_category_id' => $data['id']])
            ->with('category');
        
        // 有逗号隔开的情况，比如is_top asc, create_date desc
        if (strpos($data['orderBy'], ',') !== false) {
            $query->orderBy($data['orderBy']);
        } else {
            $query->orderBy($data['orderBy'].' '.$data['order']);
        }
            
        // 按标题搜索
        if ($data['searchBy'] == 'title' && !empty($data['keyword'])) {
            $query->andFilterWhere(['like', 'title', $data['keyword']]);
        }
        
        // 是否发布
        if ($data['is_publication'] !== null) {
            $query->andFilterWhere(['=', 'is_publication', $data['is_publication']]);
        }
        
        $result = PaginationHelper::queryPage($query, $data['pageNumber'], $data['pageSize']);
        return $this->outJson(1, $result);
    }

    /**
     * 保存文章
     */
    public function actionArticleSave()
    {
        $request = \Yii::$app->getRequest()->post();
        $id = isset($request['id']) ? (int)$request['id'] : '';
        if ($id) {
            $model = Article::findOne(['id' => $id]);
            $model->modify_date = date('Y-m-d H:i:s', strtotime($request['modify_date']));
        } else {
            $model = new Article();
            $model->create_date = date('Y-m-d H:i:s', strtotime($request['create_date']));
            $model->up = 0;
            $model->down = 0;
            $model->image_path = isset($request['image_path']) ? $request['image_path'] : null;
        }
        $model->article_category_id = (int)$request['article_category_id'];
        $model->modifydatea = date('Y-m-d H:i:s', strtotime($request['modifydatea']));
        $model->createdatea = date('Y-m-d H:i:s', strtotime($request['createdatea']));
        $model->setAttributes($request);
        if ($model->save()) {
            return $this->outJson(1, $model->getAttributes());
        } else {
            return $this->outJson(0, [], $model->hasErrors());
        }
    }
    
    /**
     * 获取文章详情
     */
    public function actionAdminArticleDetail()
    {
        $articleId = \Yii::$app->request->get('id');
        $article = Article::find()->select(['*', 'CONVERT(is_publication, SIGNED) AS is_publication', 'CONVERT(is_recommend, SIGNED) AS is_recommend', 'CONVERT(is_top, SIGNED) AS is_top'])->with('category')->where(['id' => $articleId])->asArray()->one();
        return $this->outJson(1, $article);
    }

    /**
     * 删除文章(批量)
     */
    public function actionArticleDelete()
    {
        $ids = \Yii::$app->request->post('ids');

        $rs = Article::deleteAll(['in', 'id', $ids]);
        return $this->outJson(1, $rs);
    }
    
}