ball/app/adminapi/lists/master_worker/MasterWorkerServiceOrderLists.php

<?php
namespace app\adminapi\lists\master_worker;

use app\adminapi\lists\BaseAdminDataLists;
use app\common\lists\ListsSearchInterface;
use app\common\model\goods_category\GoodsCategory;
use app\common\model\master_worker\MasterWorker;
use app\common\model\master_worker_register\MasterWorkerRegister;
use app\common\model\works\ServiceWorkAllocateWorkerLog;
use think\facade\Db;
use think\facade\Log;


/**
 * MasterWorkerServiceOrder列表
 * Class MasterWorkerServiceOrderLists
 * @package app\adminapi\lists
 */
class MasterWorkerServiceOrderLists extends BaseAdminDataLists implements ListsSearchInterface
{

    public $sqlJoin = '';

    /**
     * @notes 设置搜索条件
     * @return \string[][]
     * @author likeadmin
     * @date 2024/11/15 17:21
     */
    public function setSearch(): array
    {
        return [];
    }

    public function queryWhere()
    {
        $where = [];
        if (isset($this->params['city']) && !empty($this->params['city'])) {
            $where[] = ['a.city','=' ,$this->params['city']];
        }
        if (isset($this->params['real_name']) && !empty($this->params['real_name'])) {
            $where[] = ['a.real_name','like' ,"%".$this->params['real_name']."%"];
        }
        if (isset($this->params['worker_number']) && !empty($this->params['worker_number'])) {
            $where[] = ['a.worker_number','like' ,"%".$this->params['worker_number']."%"];
        }
        if (isset($this->params['recruiting_behalf']) && !empty($this->params['recruiting_behalf'])) {
            $where[] = ['a.recruiting_behalf','like' ,"%".$this->params['recruiting_behalf']."%"];
        }
        if (isset($this->params['labels']) && !empty($this->params['labels'])) {
            $sqls = [];
            foreach ($this->params['labels'] as $item) {
                $sqls[] = "FIND_IN_SET({$item}, labels) > 0";
            }
            $query_sql = implode(' OR ', $sqls);
            $period_ids = MasterWorker::where('labels','<>', '')->whereRaw($query_sql)->column('id');
            $where[] = [ 'a.id','IN',$period_ids?:[0]];
        }
        if (isset($this->params['is_recruiting_behalf']) && !empty($this->params['is_recruiting_behalf'])) {
            if ($this->params['is_recruiting_behalf'] == 1)
                $where[] = ['a.recruiting_behalf','<>' ,""];
            else
                $where[] = ['a.recruiting_behalf','=' ,""];
        }
        if(isset($this->params['category_ids']) && !empty($this->params['category_ids'])){
            $sqls = [];
            $category_ids =[];
            foreach ($this->params['category_ids'] as $val){
                ($val = json_decode($val,true))?($category_ids[] = end($val)):($category_ids[] = $val);
            }
            foreach ($category_ids as $item) {
                $sqls[] = "FIND_IN_SET({$item}, category_ids) > 0";
            }
            $query_sql = implode(' OR ', $sqls);
            Log::write('query_sql:'.$query_sql);

            $period_ids = MasterWorker::where('category_ids','<>', '')->whereRaw($query_sql)->column('id');
            $where[] = [ 'a.id','IN',$period_ids?:[0]];
        }
        if (isset($this->params['mobile']) && !empty($this->params['mobile'])) {
            $where[] = ['a.mobile','like' ,"%".$this->params['mobile']."%"];
        }
        if (isset($this->params['cooperation']) && !empty($this->params['cooperation'])) {
            $where[] = ['a.cooperation','=' ,$this->params['cooperation']];
        }
        // ( ( b.finished_time >= 1743436800  AND b.finished_time < 1746028800 ) or ( b.create_time >= 1743436800  AND b.create_time < 1746028800) )
        if (!empty($this->params['start_time']) && !empty($this->params['end_time'])) {
            $this->sqlJoin = ' and ( ( b.finished_time >= '.strtotime($this->params['start_time']).'  AND b.finished_time < '.strtotime($this->params['end_time']).' ) or ( b.create_time >= '.strtotime($this->params['start_time']).'  AND b.create_time < '.strtotime($this->params['end_time']).') )';
        }
        //dd($this->sqlJoin);
        $where[] = ['a.audit_state','=' ,1];
        return $where;
    }
    public function queryWhereLog()
    {
        $where = [];
        if (!empty($this->params['start_time'])) {
            $where[0] = strtotime($this->params['start_time']);
        }
        if (!empty($this->params['end_time'])) {
            $where[1] = strtotime($this->params['end_time'])+86400;
        }
        //dd($where);
        return $where;
    }
    /**
     * 获取数据权限
     * $this->adminInfo['data_rules']
     * province city admin_id sale_group_id sale_id  property_head_id
     */
    public function queryDataWhere(){
        $where = [];
        $data_rules = $this->adminInfo['data_rules'];
        if (isset($data_rules['province']) && !empty($data_rules['province'])) {
            $where[] = ['a.province','in' ,$data_rules['province']];
            $where[] = ['b.province','in' ,$data_rules['province']];

        }
        if (isset($data_rules['city']) && !empty($data_rules['city'])) {
            $where[] = ['a.city','in' ,$data_rules['city']];
            $where[] = ['b.city','in' ,$data_rules['city']];
        }
        return $where;
    }
    /**
     * @notes 获取列表
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @author likeadmin
     * @date 2024/11/15 17:21
     */
    public function lists(): array
    {
        $queryWhere = $this->queryWhere();
        $queryWhereLog = $this->queryWhereLog();
        $lists = Db::name('master_worker')->alias('a')->field([
                'a.id','a.real_name','a.nickname','a.area_name','a.worker_number','a.recruiting_behalf','a.mobile','a.cooperation','a.category_ids','a.labels','a.remark',
                Db::raw("SUM( CASE WHEN b.work_status > 1 THEN 1 ELSE 0 END ) AS accepting_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN 1 ELSE 0 END) AS success_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 4 OR b.service_status = 5 THEN 1 ELSE 0 END) AS fail_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN b.work_amount ELSE 0 END) work_amount_sum"),
                //工程师结算金额：实际提成金额(含配件费+加单费)+质保金
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN (b.settlement_amount+b.earnest_money) ELSE 0 END) worker_settlement_sum"),
            ])
            ->leftJoin('service_work b', 'a.id = b.master_worker_id'.$this->sqlJoin)
            ->where($queryWhere)
            ->where($this->queryDataWhere())
            ->group('a.id')
            //->having('success_count>0 OR fail_count>0 OR work_amount_sum>0 OR worker_settlement_sum>0')
            ->order('a.id desc')
            ->limit($this->limitOffset, $this->limitLength)
            ->select()->toArray();
        $categoryData = GoodsCategory::select()->toArray();
        $ids = array_column($lists,'id');
        $masterWorkerRegisters = array_column(MasterWorkerRegister::with(['sale'])->whereIn('worker_id',$ids)->select()->toArray(), null,'worker_id');
        foreach ($lists as &$item) {
            $item['labels'] = $item['labels']?array_map(function ($item) { return intval($item); },explode(',',$item['labels'])):'';
            $item['category_name'] = $item['category_ids']?implode('、',array_column(get_parent_info($categoryData,explode(',',$item['category_ids'])),'name')):'';
            $item['allocate_num'] = ServiceWorkAllocateWorkerLog::where(['master_worker_id'=>$item['id']])
                                    ->where('type',0)
                                    ->whereBetweenTime('create_time',$queryWhereLog[0],$queryWhereLog[1])
                                    ->count();
            $item['register_info'] = $masterWorkerRegisters[$item['id']]??[];
        }
        return $lists;
    }


    /**
     * @notes 获取数量
     * @return int
     * @author likeadmin
     * @date 2024/11/15 17:21
     */
    public function count(): int
    {
        $queryWhere = $this->queryWhere();
        return Db::name('master_worker')->alias('a')->field([
                'a.id','a.real_name','a.nickname','a.worker_number','a.recruiting_behalf',
                Db::raw("SUM( CASE WHEN b.work_status > 1 THEN 1 ELSE 0 END ) AS accepting_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN 1 ELSE 0 END) AS success_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 4 OR b.service_status = 5 THEN 1 ELSE 0 END) AS fail_count"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN b.work_amount ELSE 0 END) work_amount_sum"),
                Db::raw("SUM(CASE WHEN b.service_status = 3 THEN (b.settlement_amount+b.earnest_money) ELSE 0 END) worker_settlement_sum"),
            ])
            ->leftJoin('service_work b', 'a.id = b.master_worker_id'.$this->sqlJoin)
            ->where($queryWhere)
            ->where($this->queryDataWhere())
            ->group('a.id')
            //->having('success_count>0 OR fail_count>0 OR work_amount_sum>0 OR worker_settlement_sum>0')
            ->count();
    }


    public function setExcelComplexFields(): array
    {
        $zh_cn_fields = [
            '工程师ID', '服务类别', '工程师编号', '工程师姓名', '工程师手机',
            '工程师备注', '工程师合作形式','代招人姓名', '派单数', '工程师接单数', '标签',
            '工程师成功单','工程师失败单','工程师成交金额','工程师提成金额'
        ];
        $data_fields = [
            'id', 'category_name', 'worker_number', 'real_name', 'mobile',
            'remark', function ($row) {
                return $row['cooperation'] == 0 ? '无' : ($row['cooperation'] == 1 ? '全日接单' : '自由接单');
            }, 'recruiting_behalf', 'allocate_num', 'all_count', function ($row) {
                if(!empty($row['labels'])){
                    $data_table_allLabels = array_column(\app\common\logic\TableDataLogic::allLabels(),'name','id');
                    foreach ($row['labels'] as &$val){
                        $val = $data_table_allLabels[$val];
                    }
                    $row['labels'] = implode('、', $row['labels']);
                }
                return $row['labels']?:'';
            },
            'success_count', 'fail_count', 'work_amount_sum', 'worker_settlement_sum'
        ];
        return [
            'zh_cn_fields' => $zh_cn_fields,
            'data_fields' => $data_fields
        ];
    }


}