qasimBlog/app/Services/Interface/Database/Query/QueryDb.php

<?php

declare(strict_types=1);

namespace App\Services\Interface\Database\Query;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Request;
use App\Services\Interface\Database\CommonDb;

class QueryDb extends CommonDb
{
    public function __construct(array $interfaceInfo, array $assembleInfo, array $assembleColumns, array $requestData, array $columnIdToCodes)
    {
        parent::__construct($interfaceInfo, $assembleInfo, $assembleColumns, $requestData, $columnIdToCodes);
    }

    public function exec()
    {
        $search_type = $this->interfaceInfo['search_type'];

        if (1 === $search_type) { // 单条信息查询
            $result = $this->queryOne();
        } elseif (2 === $search_type) { // 分页查询
            $result = $this->queryByPaginator();
        } elseif (3 === $search_type) { // 列表查询
            $result = $this->queryAll();
        } elseif (4 === $search_type) { // 树型列表
            $result = $this->queryAll();
            if ($result) {
                $result = generateToTree($result);
            } else {
                $result = [];
            }
        } elseif (5 === $search_type) { // 条件总数
            $result = $this->queryCount();
        } elseif (6 === $search_type) { // sum
            $result = $this->querySum();
        } elseif (7 === $search_type) { // avg
            $result = $this->queryAvg();
        } elseif (8 === $search_type) { // max
            $result = $this->queryMax();
        } elseif (9 === $search_type) { // min
            $result = $this->queryMin();
        } else {
            abort(508, '接口配置[search_type]错误');
        }

        return $result;
    }

    /**
     * 单条信息查询.
     */
    public function queryOne(): array
    {
        if (empty($this->conditionSettingsInfo)) {
            abort(508, '参数错误，查询条件不能为空!');
        }

        list($find, $toArrColumns) = $this->queryEntity();
        if ($this->conditionSettingsInfo) {  // es查询
            foreach ($this->conditionSettingsInfo as $row) {
                if (1 == $row['type']) { // 普通查询
                    $this->execSearchCon($find, $row);
                } else { // 混合查询
                    $this->checkExpression('search_type', $this->requestData, $row['condition'], $find);
                }
            }
        }

        // 排序规则
        $this->sortFields($find);

//        DB::connection($this->getDbConnection())->enableQueryLog();#开启执行日志
        $result = $find->first();
//        Log::info($this->interfaceInfo['en_alias'] . " sql === ", DB::getQueryLog());

        // 需要转换为数组的字段
        if ($toArrColumns) {
            foreach ($toArrColumns as $column) {
                if ($result->{$column}) {
                    $result->{$column} = json_decode($result->{$column});
                } else {
                    $result->{$column} = [];
                }
            }
        }

        return $result ? get_object_vars($result) : [];
    }

    public function queryByPaginator(): array
    {
        $currentPage = Request::post('page', "1");
        $size = Request::post('size', "20");
        $size = (int)$size;

        DB::connection($this->getDbConnection())->enableQueryLog();#开启执行日志

        list($find, $toArrColumns) = $this->queryEntity();
        $total = $find->count();
        $list = $find->offset(($currentPage - 1) * $size)->limit($size)->get();

        Log::info($this->interfaceInfo['en_alias'] . " sql === ", DB::getQueryLog());

        // 需要转换为数组的字段
        if ($toArrColumns) {
            foreach ($list as $key => $item) {
                foreach ($toArrColumns as $column) {
                    if ($item->{$column}) {
                        $item->{$column} = json_decode($item->{$column});
                    } else {
                        $item->{$column} = [];
                    }
                }
            }
        }

        return ['list' => $list, 'total' => $total];
    }

    public function queryAll(): array
    {
        list($find, $toArrColumns) = $this->queryEntity();
        $list = $find->get();

        // 需要转换为数组的字段
        if ($toArrColumns) {
            foreach ($list as $key => $item) {
                foreach ($toArrColumns as $column) {
                    if ($item->{$column}) {
                        $item->{$column} = json_decode($item->{$column});
                    } else {
                        $item->{$column} = [];
                    }
                }
            }
        }

        return array_map('get_object_vars', $list->toArray());
    }

    public function queryCount(): int
    {
        list($find) = $this->queryEntity();

        return $find->count();
    }

    public function querySum(): int
    {
        $search_column_id = $this->interfaceSettingInfo['search_column_id'];
        $field = $this->columnIdToCodes[$search_column_id];

        list($find) = $this->queryEntity();

        return $find->sum($field);
    }

    public function queryAvg(): int
    {
        $search_column_id = $this->interfaceSettingInfo['search_column_id'];
        $field = $this->columnIdToCodes[$search_column_id];

        list($find) = $this->queryEntity();

        return $find->avg($field);
    }

    public function queryMax(): int
    {
        $search_column_id = $this->interfaceSettingInfo['search_column_id'];
        $field = $this->columnIdToCodes[$search_column_id];

        list($find) = $this->queryEntity();

        return $find->max($field);
    }

    public function queryMin(): int
    {
        $search_column_id = $this->interfaceSettingInfo['search_column_id'];
        $field = $this->columnIdToCodes[$search_column_id];

        list($find) = $this->queryEntity();

        return $find->min($field);
    }

    /**
     * 通过id 获取信息.
     */
    public function queryEntity()
    {
        $infoObj = DB::connection($this->getDbConnection())->table($this->table, 'A');

        // 获取需要显示的字段
        if ($selectColumns = $this->getSelectFields()) {
            $infoObj->select($selectColumns);
        }

        // 查询条件
        if ($this->conditionSettingsInfo) {
            foreach ($this->conditionSettingsInfo as $row) {
                if (1 == $row['type']) { // 普通查询
                    $this->execSearchCon($infoObj, $row);
                } else { // 混合查询
                    $this->checkExpression('search_type', $this->requestData, $row['condition'], $infoObj);
                }
            }
        }

        // 去除伪删除数据
        if (isset($this->requestData['is_delete'])) {
            $infoObj->where('is_delete', 0);
        }

        // 排序
        $this->sortFields($infoObj);

        $toArrColumns = []; // 需要json转为数组的字段

        return [$infoObj, $toArrColumns];
    }

    /**
     * 获取部分字段.
     *
     * @param $find
     */
    private function selectFields($find): void
    {
        $column_only_one = $this->interfaceResultInfo['column_only_one'] ?? false; // 是否直接返回单个字段
        $needFiledIds = $this->interfaceResultInfo['columns'];

        if ($column_only_one) {
            $find->select($this->columnIdToCodes[$needFiledIds]);
        } else {
            if ($needFiledIds) { // 将id,转为字段
                $showColumnsArr = [];
                foreach ($needFiledIds as $column_id) {
                    if (isset($this->columnIdToCodes[$column_id])) {
                        $showColumnsArr[] = $this->columnIdToCodes[$column_id];
                    }
                }
                $find->select($showColumnsArr);
            }
        }
    }

    /**
     * 排序规则.
     *
     * @param $find
     */
    public function sortFields($find): void
    {
        $sortSettings = $this->interfaceInfo['sort_settings'] ? json_decode($this->interfaceInfo['sort_settings'], true) : []; // 排序规则
        if ($sortSettings) {
            foreach ($sortSettings as $row) {
                $column = $this->columnIdToCodes[$row['column_id']];
                $value = $this->requestData[$row['paramDirection']];
                $value = strtolower($value);

                if (\in_array($value, ['asc', 'desc'], true)) {
                    $find->orderBy($column, $value);
                }
            }
        } else {
            $find->orderBy('id', 'desc');
        }
    }
}