``` $query = Mission::select(DB::raw('IFNULL(user_mission_log.id, 0) as id'), 'mission.id as mission_id', 'mission.type as mission_type', 'mission.name as mission_name', 'description', DB::raw('IFNULL(current_process, 0) as current_process'), DB::raw('IFNULL(user_mission_log.total_process,mission.num) as total_process'), DB::raw(sprintf('IFNULL(user_mission_log.status, %d) as status', UserMissionLog::STATUS_UNFINISHED)), DB::raw(sprintf('IFNULL(user_mission_log.is_settle, %d) as is_settle', UserMissionLog::SETTLE_STATUS_NOT_YET)), DB::raw('IFNULL(mission_award.gold, 0) as gold'), DB::raw('IFNULL(mission_award.score, 0) as score'), 'mission.description') ->leftJoin('user_mission_log', function ($join) use ($userId) { $join->on('mission.id', '=', 'user_mission_log.mission_id') ->where('user_mission_log.user_id', '=', $userId) ->where(function ($where) { $where->where(function ($where) { $where->where('user_mission_log.create_time', '>=', Carbon::today()->toDateTimeString()) ->where('user_mission_log.create_time', '<', Carbon::today()->tomorrow()) ->where('mission.type', '=', Mission::TYPE_DAILY); }) ->orWhere('mission.type', '!=', Mission::TYPE_DAILY); }); }) ->leftJoin('mission_award', 'mission_award.mission_id', '=', 'mission.id') ->where('mission_award.is_del', '=', MissionAward::IS_NOT) ->where('mission.is_del', '=', Mission::IS_NOT) ->where('mission.status', '=', Mission::STATUS_OPEN) ->where(function ($where) { $where->whereIn( 'mission.id', UserMissionLog::select('mission_id') ->where('user_id', '=', 0) ->where('create_time', '>=', Carbon::today()->toDateTimeString()) ->where('create_time', '<', Carbon::today()->tomorrow()) ->get()->toArray() ) ->orWhere('mission.type', '!=', Mission::TYPE_DAILY); }) ->where(function ($where) { $where->where(function ($where) { $where->where('mission.type', '=', Mission::TYPE_ACT) ->where(function ($where) { $where->where('user_mission_log.is_settle', '=', UserMissionLog::SETTLE_NOT_YET) ->orWhere(function ($where) { $where->where('user_mission_log.is_settle', '=', UserMissionLog::SETTLE_DONE) ->where('user_mission_log.settle_time', '>=', Carbon::today()->toDateTimeString()) ->where('user_mission_log.settle_time', '<', Carbon::tomorrow()->toDateTimeString()); }); }); }) ->orWhere(function ($where) { $where->where('mission.start', '<=', Carbon::now()->toDateTimeString()) ->where('mission.end', '>', Carbon::now()->toDateTimeString()); }); }) ->where(function ($where) use ($type) { $type === null ?: $where->where('mission.type', '=', $type); }) ->where(function ($where) { $where->WhereRaw(sprintf( '( (mission.type = %d and ((user_mission_log.create_time >= "%s" and user_mission_log.create_time < "%s") or (user_mission_log.create_time is null) )) or (mission.type = %d) or (mission.type = %d and ( (user_mission_log.is_settle = %d or user_mission_log.is_settle is null) or (user_mission_log.create_time >= "%s" and user_mission_log.create_time < "%s") ) ) )', Mission::TYPE_DAILY, Carbon::today()->toDateTimeString(), Carbon::tomorrow()->toDateTimeString(), Mission::TYPE_ACT, Mission::TYPE_NEW, UserMissionLog::SETTLE_NOT_YET, Carbon::today()->toDateTimeString(), Carbon::tomorrow()->toDateTimeString() )); }) ->orderByRaw(sprintf('FIELD(mission.type, %d, %d, %d)', Mission::TYPE_ACT, Mission::TYPE_DAILY, Mission::TYPE_NEW)) ->orderByRaw('current_process/total_process desc') ->orderBy('mission.id', 'asc'); $total = $query->count(); $data = $query->offset($offset)->limit($limit)->get()->toArray(); ``` ``` // 这 $query = Order::query(); switch ($where['type']) { //基层医院姓名 case 1: // 这 $query->where('create_doctor_hospital', 'like', $where['name'] . "%"); break; //基层医生姓名 case 2: // 和1一样 break; case 3: $doctor_model=UserModel::select('doctoruid') ->where('doctorname', 'like', $where['name'] . "%"); // 这 $query->whereIn('check_doctor_uid', $doctor_model); break; } if (isset($where['start_time']) && isset($where['end_time'])) { $query->join(HDW_CHECKREPORT_ECG, $this->table . '.', '=', HDW_CHECKREPORT_ECG . '.id') ->select([ HDW_CHECKREPORT_ECG . '.check_time', HDW_CHECKREPORT_ECG . '.patient_name', HDW_CHECKREPORT_ECG . '.patient_mobile', HDW_CHECKREPORT_ECG . '.patient_idcard', $this->table . '.orderid', $this->table . '.pay_money' ]) ->whereBetween(HDW_ORDER . '.created_at', [ $where['start_time'], $where['end_time'] + 24 * 60 * 60 ]); } else { $query->join('doctor_user', $this->table . '.check_doctor_uid', '=', 'doctor_user.doctoruid') ->join(HDW_PRODUCT, $this->table . '.product_id', '=', HDW_PRODUCT . '.productid') ->select([ $this->table . '.orderid', $this->table . '.patient_name', $this->table . '.product_id', $this->table . '.create_doctor_uid', $this->table . '.pay_money', $this->table . '.create_doctor_hospital', $this->table . '.check_doctor_uid', $this->table . '.create_doctor_name', 'doctor_user.doctorname', HDW_PRODUCT . '.name' ]); } // 这 $order_data = $query->get(); ```