test/app/Http/Services/TemplateLibraryApiService.php

<?php
/**
 * 模版站api服务类
 * @copyright 2021-浙江引擎力营销策划有限公司
 * @author Lc<sunshinecc1@163.com>
 * @since 2021-08-23
 */

namespace App\Http\Services;

use App\Http\Traits\TemplateLibraryTool;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\Builder;
use Illuminate\Contracts\Pagination\LengthAwarePaginator;
use Illuminate\Support\Collection;

class TemplateLibraryApiService
{
    use TemplateLibraryTool;

    /**
     * 获取页面\模版\变量关联列表
     * @param $siteId
     * @return array
     */
    public function getContentList($siteId)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentList = $connection->table('content')
            ->leftJoin('content_template', 'content.tpl_id', '=', 'content_template.id')
            ->whereIn('content.status', [1, 2])
            ->select('content.*', 'content_template.name')
            ->orderBy('content.parent_id', 'asc')
            ->orderBy('content.rank', 'asc')->get();

        $contentRelateVariableIds = $connection->table('content_template_relate')->get();

        foreach ($contentList as $value) {
            $value->variableIds = [];
            foreach ($contentRelateVariableIds as $vv) {
                if ($value->tpl_id == $vv->tid) {
                    $value->variableIds[] = $vv->vid;
                }
            }
        }

        $contentVariableList = $connection->table('content_template_var')->get();
        foreach ($contentList as $item) {
            $item->variableList = [];
            foreach ($item->variableIds as $vv) {
                foreach ($contentVariableList as $value) {
                    if ($vv == $value->id) {
                        $item->variableList[] = $value;
                    }
                }
            }
        }

        $contentList = json_decode($contentList->toJson(), true);
        $conditions = [];
        foreach ($contentList as $key => $item) {
            foreach ($item['variableList'] as $kk => $value) {
                $contentList[$key]['variableList'][$kk]['input_opts'] = explode("\r\n", $value['input_opts']) ?? [];
                $contentList[$key]['variableList'][$kk]['value'] = $value['input_value'] ?? '';
                $conditions[] = [
                    'key' => $value['name'],
                    'content_id' => $item['id'],
                ];
            }
        }

        $result = json_decode($connection->table('content_var')->get()->toJson(), true);
        $lists = [];
        foreach ($conditions as $item) {
            foreach ($result as $value) {
                if ($item['key'] == $value['key'] && $item['content_id'] == $value['content_id']) {
                    $key = $item['content_id'] . '--' . $item['key'];
                    $lists[$key] = $value['value'];
                }
            }
        }


        foreach ($contentList as $key => $item) {
            foreach ($item['variableList'] as $kk => $value) {
                $arrayKey = $item['id'] . '--' . $value['name'];
                $val = $lists[$arrayKey] ?? '';

                if (!empty($val)) {
                    $contentList[$key]['variableList'][$kk]['value'] = $val;

                    if ($value['input_type'] == 'checkbox' || $value['input_type'] == 'resource' || $value['input_type'] == 'file' || $value['input_type'] == 'image') {
                        $contentList[$key]['variableList'][$kk]['value'] = unserialize($val) ?? [];
                    }
                    if ($value['input_type'] == 'date') {
                        $contentList[$key]['variableList'][$kk]['value'] = date('Y-m-d', strtotime($val));
                    }
                }
            }
        }

        $templateList = $connection->table('content_template')->pluck('name', 'id');

        $list = [];
        foreach ($contentList as $value) {

            $last_names = array_column($value['variableList'], 'rank');
            array_multisort($last_names, SORT_ASC, $value['variableList']);

            $publishTime = '';
            if (!empty($value['publish_time'])) {
                $publishTime = date('Y-m-d', $value['publish_time']) ?? '';
            }

            $expiredTime = '';
            if (!empty($value['expired_time'])) {
                $expiredTime = date('Y-m-d', $value['expired_time']) ?? '';
            }

            $data = [
                'id' => $value['id'] ?? 0,
                'subtitle' => $value['subtitle'] ?? '',
                'parent_id' => $value['parent_id'] ?? 0,
                'name' => "{$value['title']}(#{$value['id']})",
                'title' => $value['title'] ?? '',
                'thumb' => $value['thumb'] ?? '',
                'summary' => $value['summary'] ?? '',
                'content_type' => $value['content_type'] ?? '',
                'menu_text' => $value['menu_text'] ?? '',
                'uri' => $value['uri'] ?? '',
                'alias' => $value['alias'] ?? '',
                'content' => $value['content'] ?? '',
                'tpl_id' => $value['tpl_id'] ?? 0,
                'tplName' => $templateList[$value['tpl_id']] ?? '',
                'css_id' => $value['css_id'] ?? 0,
                'cssName' => $templateList[$value['css_id']] ?? '',
                'deny_spider' => $value['deny_spider'] ?? '',
                'is_enabled' => $value['is_enabled'] ?? 0,
                'is_301' => $value['is_301'] ?? 0,
                'is_freeze_url' => $value['is_freeze_url'] ?? 0,
                'is_hidemenu' => $value['is_hidemenu'] ?? 0,
                'redirect' => $value['redirect'] ?? '',
                'status' => $value['status'],
                'publish_time' => $publishTime,
                'expired_time' => $expiredTime,
                'rank' => $value['rank'] ?? '',
                'seo_title' => $value['seo_title'] ?? '',
                'seo_keywords' => $value['seo_keywords'] ?? '',
                'seo_description' => $value['seo_description'] ?? '',
                'variable_list' => $value['variableList'],
            ];
            $list[] = $data;
        }

        ksort($list);
        return list_to_tree($list, 'id', 'parent_id', 'children');
    }

    public function getWebSitePage($siteId)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentList = $connection->table('content')
            ->leftJoin('content_template', 'content.tpl_id', '=', 'content_template.id')
            ->whereIn('content.status', [1, 2])
            ->select('content.*', 'content_template.name')
            ->orderBy('content.parent_id', 'asc')
            ->orderBy('content.rank', 'asc')->get()->toJson();
        $contentList = json_decode($contentList, true);
        $templateList = $connection->table('content_template')->pluck('name', 'id');

        foreach ($contentList as $value) {

            $publishTime = '';
            if (!empty($value['publish_time'])) {
                $publishTime = date('Y-m-d', $value['publish_time']) ?? '';
            }

            $expiredTime = '';
            if (!empty($value['expired_time'])) {
                $expiredTime = date('Y-m-d', $value['expired_time']) ?? '';
            }

            $data = [
                'id' => $value['id'] ?? 0,
                'subtitle' => $value['subtitle'] ?? '',
                'parent_id' => $value['parent_id'] ?? 0,
                'name' => "{$value['title']}(#{$value['id']})",
                'title' => $value['title'] ?? '',
                'thumb' => $value['thumb'] ?? '',
                'summary' => $value['summary'] ?? '',
                'content_type' => $value['content_type'] ?? '',
                'menu_text' => $value['menu_text'] ?? '',
                'uri' => $value['uri'] ?? '',
                'alias' => $value['alias'] ?? '',
                'content' => $value['content'] ?? '',
                'tpl_id' => $value['tpl_id'] ?? 0,
                'tplName' => $templateList[$value['tpl_id']] ?? '',
                'css_id' => $value['css_id'] ?? 0,
                'cssName' => $templateList[$value['css_id']] ?? '',
                'deny_spider' => $value['deny_spider'] ?? '',
                'is_enabled' => $value['is_enabled'] ?? 0,
                'is_301' => $value['is_301'] ?? 0,
                'is_freeze_url' => $value['is_freeze_url'] ?? 0,
                'is_hidemenu' => $value['is_hidemenu'] ?? 0,
                'redirect' => $value['redirect'] ?? '',
                'status' => $value['status'],
                'publish_time' => $publishTime,
                'expired_time' => $expiredTime,
                'rank' => $value['rank'] ?? '',
                'seo_title' => $value['seo_title'] ?? '',
                'seo_keywords' => $value['seo_keywords'] ?? '',
                'seo_description' => $value['seo_description'] ?? '',
            ];
            $list[] = $data;
        }

        ksort($list);
        return list_to_tree($list, 'id', 'parent_id', 'children');
    }


    /**
     * 获取所有页面列表
     * @param $siteId int 项目id
     * @param $tplId int 模版id
     * @param $notTplId int 排除的模版id
     * @return array|Builder|Collection|mixed
     */
    public function getWebsitePageList($siteId, $tplId, $notTplId)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentList = $connection->table('content');
        if (!empty($tplId)) {
            $contentList->where('tpl_id', $tplId);
        }
        if (!empty($notTplId)) {
            $notTplId = explode(',', $notTplId);
            $contentList->whereNotIn('tpl_id', $notTplId);
        }

        $contentList = $contentList
            ->whereIn('status', [1, 2])
            ->orderBy('parent_id', 'asc')
            ->orderBy('rank', 'asc')->get();
        $contentList = json_decode($contentList->toJson(), true);

        $templateList = $connection->table('content_template')->pluck('name', 'id');
        foreach ($contentList as $key => $item) {
            $contentList[$key]['tplName'] = $templateList[$item['tpl_id']] ?? '';
            $contentList[$key]['cssName'] = $templateList[$item['css_id']] ?? '';
        }

        //return list_to_tree($contentList, 'id', 'parent_id', 'children');
        return $this->listToTreeRecursive($contentList, 0, 'id', 'parent_id', 'children');
    }


    /**
     * 根据页面id获取模版列表
     * @param $siteId
     * @param $pageId
     * @param $uri
     * @return Model|Builder|object|null
     */
    public function getWebsitePageDetailsByUri($siteId, $pageId, $uri)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentInfo = $connection
            ->table('content')
            ->whereIn('status', [1, 2]);
        if (!empty($pageId)) {
            $contentInfo = $contentInfo->where('id', $pageId);
        }
        if (!empty($uri)) {
            $contentInfo = $contentInfo->where('uri', $uri);
        }
        $contentInfo = $contentInfo->first();
        if (!empty($contentInfo)) {
            $contentInfo->cssName = $connection->table('content_template')->where('id', $contentInfo->css_id)->value('name') ?? '';
            $contentInfo->tplName = $connection->table('content_template')->where('id', $contentInfo->tpl_id)->value('name') ?? '';
            $contentRelateVariableIds = $connection->table('content_template_relate')->get();

            $contentInfo->variableIds = [];
            foreach ($contentRelateVariableIds as $vv) {
                if ($contentInfo->tpl_id == $vv->tid) {
                    $contentInfo->variableIds[] = $vv->vid;
                }
            }

            $contentVariableList = $connection->table('content_template_var')->get();
            $contentInfo->variableList = [];
            foreach ($contentInfo->variableIds as $vv) {
                foreach ($contentVariableList as $value) {
                    if ($vv == $value->id) {
                        $contentInfo->variableList[] = $value;
                    }
                }
            }

            $contentInfo = \GuzzleHttp\json_decode(json_encode($contentInfo), true);

            foreach ($contentInfo['variableList'] as $kk => $value) {

                $contentInfo['variableList'][$kk]['input_opts'] = explode("\r\n", $value['input_opts']) ?? [];
                $contentInfo['variableList'][$kk]['value'] = $value['input_value'] ?? '';

                $result = $connection
                    ->table('content_var')
                    ->where('key', $value['name'])
                    ->where('content_id', $contentInfo['id'])->value('value');

                if (!empty($result)) {
                    $contentInfo['variableList'][$kk]['value'] = $result;

                    if ($value['input_type'] == 'checkbox' || $value['input_type'] == 'resource' || $value['input_type'] == 'file' || $value['input_type'] == 'image') {
                        $contentInfo['variableList'][$kk]['value'] = unserialize($result) ?? [];
                    }
                    if ($value['input_type'] == 'date') {
                        $contentInfo['variableList'][$kk]['value'] = date('Y-m-d', strtotime($result));
                    }
                }
            }
        }

        return $contentInfo;
    }


    /**
     * 根据页面Id获取页面的子页面列表
     * @param $siteId
     * @param $parentId
     * @param $tplId
     * @param $notTplId
     * @param $sortBy
     * @param $all
     * @param $pageSize
     * @return LengthAwarePaginator|Builder
     */
    public function getChildWebsitePageListByPageId($siteId, $parentId, $tplId, $notTplId, $sortBy, $all, $pageSize)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentList = $connection->table('content')->whereIn('status', [1, 2]);

        //是否筛选pid下所有的
        if ($all) {
            $parentPageList = $connection->table('content')->where('parent_id', $parentId)->get();
            $childPageList = $this->recursion($parentPageList, $siteId);
            $pageIds = array_column($childPageList, 'id');
            $contentList->whereIn('id', $pageIds);
        }
        if (!empty($parentId) && !$all) {
            $contentList->where('parent_id', $parentId);
        }
        if (!empty($notTplId)) {
            $notTplId = explode(',', $notTplId);
            $contentList->whereNotIn('tpl_id', $notTplId);
        }
        if (!empty($tplId)) {
            $contentList->where('tpl_id', $tplId);
        }
        //排序方式
        if ($sortBy == 'random') {
            $filed = ['tpl_id', 'title', 'parent_id', 'alias'];
            $filedKey = array_rand($filed);

            $sort = ['desc', 'asc'];
            $sortKey = array_rand($sort, 1);

            $contentList->orderBy($filed[$filedKey], $sort[$sortKey]);
        }

        if ($sortBy == true) {
            $contentList->orderBy('publish_time', 'desc');
        }
        if ($sortBy == false) {
            $contentList->orderBy('id', 'asc');
        }

        $contentList = $contentList->paginate($pageSize);

        $contentRelateVariableIds = $connection->table('content_template_relate')->get();
        $contentVariableList = $connection->table('content_template_var')->get();

        foreach ($contentList as $value) {

            $value->cssName = $connection->table('content_template')->where('id', $value->css_id)->value('name') ?? '';
            $value->tplName = $connection->table('content_template')->where('id', $value->tpl_id)->value('name') ?? '';

            $value->variableIds = [];
            foreach ($contentRelateVariableIds as $vv) {
                if ($value->tpl_id == $vv->tid) {
                    $value->variableIds[] = $vv->vid;
                }
            }

            $value->variableList = [];
            foreach ($value->variableIds as $vv) {
                foreach ($contentVariableList as $vvv) {
                    if ($vv == $vvv->id) {
                        $value->variableList[] = $vvv;
                    }
                }
            }

            $value = \GuzzleHttp\json_decode(json_encode($value), true);
            foreach ($value['variableList'] as $kk => $vv) {

                $value['variableList'][$kk]['input_opts'] = explode("\r\n", $vv['input_opts']) ?? [];
                $value['variableList'][$kk]['value'] = $vv['input_value'] ?? '';

                $result = $connection
                    ->table('content_var')
                    ->where('key', $vv['name'])
                    ->where('content_id', $vv['id'])->value('value');

                if (!empty($result)) {
                    $value['variableList'][$kk]['value'] = $result;

                    if ($vv['input_type'] == 'checkbox' || $vv['input_type'] == 'resource' || $vv['input_type'] == 'file' || $vv['input_type'] == 'image') {
                        $value['variableList'][$kk]['value'] = unserialize($result) ?? [];
                    }
                    if ($vv['input_type'] == 'date') {
                        $value['variableList'][$kk]['value'] = date('Y-m-d', strtotime($result));
                    }
                }
            }
        }

        return $contentList;
    }


    /**
     * 搜索
     * @param $siteId
     * @param $keyword
     * @param $pageSize
     * @param $tplId
     * @param $notTplId
     * @return LengthAwarePaginator|Builder
     */
    public function search($siteId, $keyword, $pageSize, $tplId, $notTplId)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentList = $connection->table('content')
            ->whereIn('status', [1, 2])
            ->where('deny_spider', 0);
        if (!empty($keyword)) {
            $contentList->where('title', 'like', '%' . $keyword . '%');
        }
        if (!empty($tplId)) {
            $contentList->where('tpl_id', $tplId);
        }
        if (!empty($notTplId)) {
            $notTplId = explode(',', $notTplId);
            $contentList->whereNotIn('tpl_id', $notTplId);
        }
        $contentList = $contentList->paginate($pageSize);

        if (!empty($contentList)) {
            $templateList = $connection->table('content_template')->pluck('name', 'id');

            foreach ($contentList as $item) {
                $item->cssName = $templateList[$item->css_id] ?? '';
                $item->tplName = $templateList[$item->tpl_id] ?? '';
            }
        }
        return $contentList;
    }

    /**
     * 广告列表
     * @param $siteId
     * @return Collection
     */
    public function getAdvertiseList($siteId)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentList = $connection->table('advert')->get();
        $advertElementList = $connection->table('advert_element')->get();
        foreach ($contentList as $value) {
            $value->advertElementList = [];
            foreach ($advertElementList as $item) {
                if ($value->id == $item->advert_id) {
                    $value->advertElementList[] = $item;
                }
            }
        }
        return $contentList;
    }


    /**
     * 获取语言环境
     * @param $siteId
     * @return Model|Builder|object|null
     */
    public function getLanguage($siteId)
    {
        $connection = DB::connection($this->connection($siteId));
        $contentInfo = $connection->table('language')
            ->where('code', 'en')
            ->select('code', 'name', 'chinese')
            ->where('is_enabled', 1)->first();
        return $contentInfo;
    }


    /**
     * 提交表单
     * @param $request
     * @return bool
     */
    public function formSubmission($request)
    {
        $connection = DB::connection($this->connection($request['siteId']));
        $content = '';
        if (!empty($request['list'])) {
            $content = json_encode($request['list']);
        }
        $update = [
            'sender_name' => $request['name'],
            'sender_email' => $request['email'],
            'content' => $content,
            'user_id' => 0,
            'sender_uid' => 0,
            'title' => '',
            'client_ip' => 0,
            'is_read' => 0,
            'is_delete' => 0,
            'mail_time' => 0,
            'mail_response' => 0,
            'create_time' => time(),
        ];
        $contentInfo = $connection->table('user_msg')->insert($update);
        return $contentInfo;
    }


    /**
     * 数据库连接信息
     * @param $siteId
     * @return mixed
     */
    public function connection($siteId)
    {
        $site = DB::connection('template')
            ->table('template_library_project_config')
            ->where('site_id', $siteId)->first();
        $config = [
            'connection_name' => sprintf('connection_name_%s', $site->site_id),
            'host' => $site->ip,
            'port' => '3306',
            'database' => $site->database,
            'username' => $site->username,
            'password' => $site->password,
        ];
        config_connection($config);
        return $config['connection_name'];
    }


    /**
     * 根据父级id查找子级数据(二维数组)
     * @param $array
     * @param $siteId
     * @return array
     */
    public function recursion($array, $siteId)
    {
        $output = [];
        foreach ($array as $value) {

            if (!empty($value->id)) {
                $list = DB::connection($this->connection($siteId))
                    ->table('content')
                    ->where('parent_id', $value->id)->get();
                $output [] = $value;
                $output = array_merge($output, $this->recursion($list, $siteId));
            }

        }
        return $output;
    }

    /**
     * 获取项目环境
     * @param $siteId
     * @return array
     */
    public function getSettingList($siteId)
    {
        $siteInfo = DB::connection('template')
            ->table('template_library_project_config')
            ->where('site_id', $siteId)
            ->select('id', 'cn_title', 'en_title', 'site_logo', 'domain', 'brand')->first();
        $siteInfo->id = $siteId;
        $setting = $this->getLanguage($siteId);
        return [$siteInfo, $setting];
    }

    /**
     * 采用递归将数据列表转换成树
     * @param array $dataArr 数据列表
     * @param int $rootId 根节点ID
     * @param string $pkName 主键名
     * @param string $pIdName 父节点id名
     * @param string $childName 子节点名称
     * @return array
     */
    public function listToTreeRecursive($dataArr, $rootId = 0, $pkName = 'id', $pIdName = 'parent_id', $childName = 'children')
    {
        $arr = [];
        foreach ($dataArr as $sorData) {
            if ($sorData[$pIdName] == $rootId) {
                $children = $this->listToTreeRecursive($dataArr, $sorData[$pkName]);
                if ($children) {
                    $sorData[$childName] = $children;
                }
                $arr[] = $sorData;
            }
        }

        return $arr;
    }
}