baoshi-was/app/Services/OrderIssuePerformanceService.php

<?php

namespace App\Services;

use App\OrderIssueProcessLog;
use App\Owner;
use App\Services\common\QueryService;
use App\User;
use App\Exports\Export;
use Carbon\Carbon;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Pagination\Paginator;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Facades\Excel;


class OrderIssuePerformanceService
{
    public function paginate(array $params)
    {
        $paginate = $params['paginate'] ?? 50;
        $page = $params['page'] ?? 1;
        $data = $this->queryAll($params);
        $collection = collect($data);
        $list = $collection->slice(($page - 1) * $paginate, $paginate)->all();
        $option = ['path' => Paginator::resolveCurrentPath()];
        $paginate = new LengthAwarePaginator($list, $collection->count(), $paginate, $page,$option );
        return $paginate;
    }

    public function queryAll($params)
    {
        $sql = $this->getSql($params);
        return $result = DB::select($sql);
    }

    public function  getSql($params){
        $sql =" select users.name as userName,owners.name as ownerName,count(case order_issue_process_logs.type WHEN  '创建' THEN 1 end ) as created, count(case order_issue_process_logs.type WHEN  '处理' THEN 1 end ) as processed, count(case order_issue_process_logs.type WHEN  '结束' THEN 1 end ) as end,count(1) as sumNumber ";
        $sql.=" from order_issue_process_logs inner join users on order_issue_process_logs.user_id = users.id inner join order_issues on order_issue_process_logs.order_issue_id = order_issues.id inner join orders on order_issues.order_id = orders.id inner join owners on orders.owner_id = owners.id ";
        $sql.=" where 1=1 ";
        if(isset($params['create_start'])){
            $sql.= " and order_issue_process_logs.created_at >= '".$params['create_start']." 00:00:00' ";
        }
        if(isset($params['create_end'])){
            $sql.= " and order_issue_process_logs.created_at <= '".$params['create_end']." 23:59:59' ";
        }
        if (isset($params['timeFrame'])) {
            $create_start ='';$create_end ='';
            if ($params['timeFrame'] == 'day') {
                $create_start = date('Y-m-d');
                $create_end = date('Y-m-d');
            } else if ($params['timeFrame'] == 'yesterday') {
                $create_start = date('Y-m-d', strtotime('-1 day'));
                $create_end = date('Y-m-d', strtotime('-1 day'));
            } else if ($params['timeFrame'] == 'week') {
                $date = date('Y-m-d', strtotime('-1 weeks', strtotime('Monday')));
                $create_start = $date;
                $create_end = date('Y-m-d');
            } else if ($params['timeFrame'] == 'month') {
                $create_start = date('Y-m-01', strtotime(date("Y-m-d")));
                $create_end = date('Y-m-d');
            }
            $sql.= " and order_issue_process_logs.created_at >= '".$create_start." 00:00:00' ";
            $sql.= " and order_issue_process_logs.created_at <= '".$create_end." 23:59:59' ";
        }
        $sql.= "group by users.name, owners.name;";
        return $sql;
    }
}