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

<?php

namespace App\Services\common;


use Carbon\Carbon;
use Illuminate\Http\Request;

Class QueryService
{

    /**
     * parameter - query(sql) - special column description
     *
     * @param $queryParam ($request->all())
     * @param object $query
     * @param array $columnQueryRules
     * @param string $tableName
     * @return object
     */
    public function queryCondition($queryParam,$query,array $columnQueryRules,$tableName = null)
    {
        if ($tableName) $tableName .= ".";
        foreach ($queryParam as $param => $value){
            if ($param === 'paginate' || $param === 'page')continue;
            if (!$value || isset($columnQueryRules[$param]))continue;
            $query = $query->where($tableName.$param,$value);
        }
        //rules: alias timeLimit startDate endDate like multi
        foreach ($columnQueryRules as $param => $rules){
            $queryCondition=$queryParam[$param]??null;
            if (!$queryCondition)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','%'.$queryCondition.'%')
                        ->where($tableName.'created_at','>',$today);
                    if($queryTemp->count()==0 || $queryTemp->first()[$column]==$queryCondition){
                        $query=$query->where($tableName.$column,$queryCondition);
                    }else{
                        $query=$query->where($tableName.$column,'like','%'.$queryCondition.'%')
                            ->where($tableName.'created_at','>',$today);
                    }
                    $isExecute = true;
                }
                if ($rule === 'startDate'){
                    $startDate = $queryCondition.$value;
                    $query = $query->where($tableName.$column,'>=',$startDate);
                    $isExecute = true;
                }
                if ($rule === 'endDate'){
                    $endDate = $queryCondition.$value;
                    $query = $query->where($tableName.$column,'<=',$endDate);
                    $isExecute = true;
                }
                if ($rule === 'like'){
                    $query = $query->where($tableName.$column,'like',$value.$queryCondition.$value);
                    $isExecute = true;
                }
                if ($rule === 'multi'){
                    $query = $query->whereIn($tableName.$column,explode($value,$queryCondition));
                    $isExecute = true;
                }
            }
            if (!$isExecute) $query = $query->where($tableName.$column,$queryCondition);
        }
        return $query;
    }

/**
     * parameter - query(sql) - special column description
     *
     * @param Request $request
     * @param object $query
     * @param array $columnQueryRules
     * @param string $tableName
     * @return object
     */
    public function query(Request $request,$query,array $columnQueryRules,$tableName = null)
    {
        if ($tableName) $tableName .= ".";
        foreach ($request->input() as $param => $value){
            if ($param === 'paginate' || $param === 'page')continue;
            if (!$value || isset($columnQueryRules[$param]))continue;
            $query = $query->where($tableName.$param,$value);
        }
        //rules: alias timeLimit startDate endDate like multi
        foreach ($columnQueryRules as $param => $rules){
            if (!$request->input($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','%'.$request->input($param).'%')
                        ->where($tableName.'created_at','>',$today);
                    if($queryTemp->count()==0 || $queryTemp->first()[$column]==$request->input($param)){
                        $query=$query->where($tableName.$column,$request->input($param));
                    }else{
                        $query=$query->where($tableName.$column,'like','%'.$request->input($param).'%')
                            ->where($tableName.'created_at','>',$today);
                    }
                    $isExecute = true;
                }
                if ($rule === 'startDate'){
                    $startDate = $request->input($param).$value;
                    $query = $query->where($tableName.$column,'>=',$startDate);
                    $isExecute = true;
                }
                if ($rule === 'endDate'){
                    $endDate = $request->input($param).$value;
                    $query = $query->where($tableName.$column,'<=',$endDate);
                    $isExecute = true;
                }
                if ($rule === 'like'){
                    $query = $query->where($tableName.$column,'like',$value.$request->input($param).$value);
                    $isExecute = true;
                }
                if ($rule === 'multi'){
                    $query = $query->whereIn($tableName.$column,explode($value,$request->input($param)));
                    $isExecute = true;
                }
            }
            if (!$isExecute) $query = $query->where($tableName.$column,$request->input($param));
        }
        return $query;
    }

}