baoshi-was/app/Services/common/QueryService.php

<?php

namespace App\Services\common;


use Carbon\Carbon;
use App\Traits\ServiceAppAop;
use Illuminate\Database\Eloquent\Builder;


class QueryService
{
    use ServiceAppAop;

    /**
     * parameter - query(sql) - special column description
     *
     * @param array $params
     * @param object $query
     * @param array $columnQueryRules
     * @param string $tableName
     * @return object
     */
    public function query(array $params,$query,array $columnQueryRules,$tableName = null)
    {
        if ($tableName) $tableName .= ".";
        foreach ($params as $param => $value){
            if ($param === 'paginate' || $param === 'page')continue;
            if (!$value || ($columnQueryRules[$param] ?? false))continue;
            $query = $query->where($tableName.$param,$value);
        }
        //rules: alias timeLimit startDate endDate like multi
        foreach ($columnQueryRules as $param => $rules){
            if (!($params[$param] ?? false) || !$params[$param])continue;
            $isExecute = true;
            $column = $param;
            foreach ($rules as $rule => $value){
                if ($rule === 'alias'){
                    $column = $value;
                    $isExecute = false;
                }
                if ($rule === 'timeLimit'){
                    $today=Carbon::now()->subDays($value);
                    $queryTemp=clone $query;
                    $queryTemp=$queryTemp->where($tableName.$column,'like','%'.$params[$param].'%')
                        ->where($tableName.'created_at','>',$today);
                    if($queryTemp->count()==0 || $queryTemp->first()[$column]==$params[$param]){
                        $query=$query->where($tableName.$column,"like",$params[$param]."%");
                    }else{
                        $query=$query->where($tableName.$column,'like','%'.$params[$param].'%')
                            ->where($tableName.'created_at','>',$today);
                    }
                    $isExecute = true;
                }
                if ($rule === 'startDate'){
                    $startDate = $params[$param].$value;
                    $query = $query->where($tableName.$column,'>=',$startDate);
                    $isExecute = true;
                }
                if ($rule === 'endDate'){
                    $endDate = $params[$param].$value;
                    $query = $query->where($tableName.$column,'<=',$endDate);
                    $isExecute = true;
                }
                if ($rule === 'like'){
                    $query = $query->where($tableName.$column,'like',$value.$params[$param].'%');
                    $isExecute = true;
                }
                if ($rule === 'multi'){
                    $query = $query->whereIn($tableName.$column,explode($value,$params[$param]));
                    $isExecute = true;
                }
                if ($rule === 'batch'){
                    if (strpos($value.$params[$param].$value, ',') || strpos($value.$params[$param].$value, '，') || strpos($value.$params[$param].$value, ' ')) {
                        $arr = array_filter(preg_split('/[,， ]+/is', $value.$params[$param].$value));
                        $query = $query->whereIn($tableName.$column, $arr);
                    } else {
                        $query = $query->where($tableName.$column,'like',$value.$params[$param].$value.'%');
                    }
                    $isExecute = true;
                }
            }
            if (!$isExecute) $query = $query->where($tableName.$column,$params[$param]);
        }
        return $query;
    }

    public function priceModelAuditOrRecoverQuery($isAudit, Builder $query, $ownerId, $ids, $notRelation = false)
    {
        $query = $query->whereNotNull("operation")->where("operation","!=","");
        if ($ownerId){
            if ($notRelation){
                if (is_array($ownerId))$query->whereIn("owner_id",$ownerId);
                else $query->where("owner_id",$ownerId);
            }else{
                $query->whereHas("owners",function ($query)use($ownerId){
                    /** @var Builder $query */
                    if (is_array($ownerId))$query->whereIn("id",$ownerId);
                    else $query->where("id",$ownerId);
                });
            }
        }
        if ($ids){
            if (is_array($ids))$query->whereIn("id",$ids);
            else $query->where("id",$ids);
        }
        $delete = [];
        $update = [];
        if ($isAudit){
            foreach ($query->get() as $model){
                if ($model->operation == "D"){
                    $delete[] = $model->id;
                    continue;
                }
                if ($model->operation == "U" && $model->target_id)$delete[] = $model->target_id;
                $update[] = $model->id;
            }
        }else{
            foreach ($query->get() as $model){
                if ($model->operation == "D"){
                    $update[] = $model->id;
                    continue;
                }
                $delete[] = $model->id;
            }
        }
        return ["delete"=>$delete,"update"=>$update];
    }
}