123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275 |
- <?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');
- }
- }
- }
|