laravel model(查询)

时间:2025-02-23 07:34:17
namespace App\Models\Demand; use Illuminate\Database\Eloquent\Model; use Illuminate\Support\Facades\DB; class DemandModel extends Model { protected $table = 'demand'; /** * 依据条件获取项目 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionProject($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('project') ->where(function($query) use($condition) { if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['name']) && !empty($condition['name'])) { $query->where('project_name', '=', $condition['name']); } }) ->where(function($query) use($condition) { if (isset($condition['creater']) && !empty($condition['creater'])) { $query->where('project_creater', '=', $condition['creater']); } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取模块 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionModel($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('project_model') ->where(function($query) use($condition) { if (isset($condition['pm_id']) && !empty($condition['pm_id'])) { if (is_array($condition['pm_id'])) { $query->whereIn('pm_id', $condition['pm_id']); } else { $query->where('pm_id', '=', $condition['pm_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 根据条件获取需求 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionDemand($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { DB::connection()->enableQueryLog(); $query = self::query(); if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand_id', $condition['demand_id']); } else { $query->where('demand_id', '=', $condition['demand_id']); } } if (isset($condition['demand_name']) && !empty($condition['demand_name'])) { $query->where('demand_name', 'like', '%' . $condition['demand_name'] . '%'); } if (isset($condition['name']) && !empty($condition['name'])) { $query->where('demand_name', 'like', '%' . $condition['name']); } if (isset($condition['model']) && !empty($condition['model'])) { if (is_array($condition['model'])) { $query->whereIn('pm_id', $condition['model']); } else { $query->where('pm_id', '=', $condition['model']); } } if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } if (isset($condition['demand_level']) && $condition['demand_level'] !== '') { $query->where('demand_level', '=', $condition['demand_level']); } if (isset($condition['demand_type']) && $condition['demand_type'] !== '') { $query->where('demand_type', '=', $condition['demand_type']); } if (isset($condition['status'])) { if (is_array($condition['status']) && !empty($condition['status'])) { $query->whereIn('demand_status', $condition['status']); } else if ($condition['status'] !== '') { $query->where('demand_status', '=', $condition['status']); } } if (isset($condition['status_not'])) { if (is_array($condition['status_not']) && !empty($condition['status_not'])) { $query->whereNotIn('demand_status', $condition['status_not']); } else if ($condition['status_not'] !== '') { $query->where('demand_status', '!=', $condition['status_not']); } } if (isset($condition['status_less_than']) && $condition['status_less_than'] !== '') { $query->where('demand_status', '<=', $condition['status_less_than']); } if (isset($condition['demand_from']) && $condition['demand_from'] !== '') { $query->where('demand_from', '=', $condition['demand_from']); } if (isset($condition['demand_from_user']) && !empty($condition['demand_from_user'])) { $query->where('demand_from_user', '=', $condition['demand_from_user']); } if (isset($condition['demand_next_user']) && !empty($condition['demand_next_user'])) { $query->where(function($query) use($condition) { $query->where('demand_next_user', 'like', '%[' . $condition['demand_next_user'] . ']%')->orWhere('demand_next_user', $condition['demand_next_user']); }); } if (isset($condition['create']) && !empty($condition['create'])) { $query->where('demand_creater', '=', $condition['create']); } if (isset($condition['demand_create_date_start']) && !empty($condition['demand_create_date_start'])) { $query->where('demand_create_date', '>=', $condition['demand_create_date_start']); } if (isset($condition['demand_create_date_end']) && !empty($condition['demand_create_date_end'])) { $query->where('demand_create_date', '<=', $condition['demand_create_date_end']); } if (isset($condition['demand_last_modified_start']) && !empty($condition['demand_last_modified_start'])) { $query->where('demand_last_modified', '>=', $condition['demand_last_modified_start']); } if (isset($condition['demand_last_modified_end']) && !empty($condition['demand_last_modified_end'])) { $query->where('demand_last_modified', '<=', $condition['demand_last_modified_end']); } if (isset($condition['demand_dev_lead']) && !empty($condition['demand_dev_lead'])) { if (is_array($condition['demand_dev_lead'])) { $query->whereIn('demand_dev_lead', $condition['demand_dev_lead']); } else { $query->where('demand_dev_lead', '=', $condition['demand_dev_lead']); } } if (isset($condition['related_to_me']) && !empty($condition['related_to_me'])) { $query->where(function($query) use($condition) { $query->orWhere('demand_from_user', '=', $condition['related_to_me'])->orWhere('demand_next_user', 'like', '%[' . $condition['related_to_me'] . ']%')->orWhere('demand_next_user', $condition['related_to_me'])->orWhere('demand_creater', '=', $condition['related_to_me']); }); } if (isset($condition['demand_functional_satisfaction']) && $condition['demand_functional_satisfaction'] !== '') { $query->where('demand_functional_satisfaction', '=', $condition['demand_functional_satisfaction']); } if (isset($condition['demand_side_test_time_start']) && $condition['demand_side_test_time_start'] !== '') { $query->where('demand_side_test_time', '>=', $condition['demand_side_test_time_start']); } if (isset($condition['demand_side_test_time_end']) && $condition['demand_side_test_time_end'] !== '') { $query->where('demand_side_test_time', '<=', $condition['demand_side_test_time_end']); } if (isset($condition['demand_side_adopt_time_start']) && $condition['demand_side_adopt_time_start'] !== '') { $query->where('demand_side_adopt_time', '>=', $condition['demand_side_adopt_time_start']); } if (isset($condition['demand_side_adopt_time_end']) && $condition['demand_side_adopt_time_end'] !== '') { $query->where('demand_side_adopt_time', '<=', $condition['demand_side_adopt_time_end']); } if (isset($condition['demand_uptime_start']) && !empty($condition['demand_uptime_start'])) { $query->where('demand_uptime', '>=', $condition['demand_uptime_start']); } if (isset($condition['demand_uptime_end']) && !empty($condition['demand_uptime_end'])) { $query->where('demand_uptime', '<=', $condition['demand_uptime_end']); } if (isset($condition['is_score']) && $condition['is_score'] !== '') { $query->where('demand_functional_satisfaction', '=', $condition['is_score'])->where('demand_communication_satisfaction', '=', $condition['is_score']); } switch ($type) { case 'count(*)': $sql = $query->count(); break; case 'countBySum': $type = 'count(*) as all_demand, sum(demand_status = 1) as draft, sum(demand_status = 2) as waiting_time, sum(demand_status = 14) as to_be_decomposed, sum(demand_status = 3) as to_be_developed, sum(demand_status = 4) as in_development, sum(demand_status = 5) as to_be_tested, sum(demand_status = 6) as in_testing, sum(demand_status = 7) as suspend, sum(demand_status = 8) as test_completed, sum(demand_status = 9) as demander_acceptance, sum(demand_status = 10) as demand_side_approval, sum(demand_status = 11) as discard, sum(demand_status = 12) as stop, sum(demand_status = 13) as on_line_notification'; $sql = $query->get(DB::raw($type))->toArray(); break; case 'sum': $type = 'sum(demand_status != 11) as demand_total, sum(demand_status <= 9) as demand_in_progress'; $sql = $query->get(DB::raw($type))->toArray(); break; default: if (!empty($groupBy)) { $query->groupBy($groupBy); } if (!empty($orderBy)) { $query->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $query->offset($start)->limit($pageSize); } $sql = $query->get($type)->toArray(); break; } // print_r(DB::getQueryLog()); // die(); return $sql; } /** * 依据条件获取版本详情 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionVersionDetail($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('demand_version_detail') ->where(function($query) use($condition) { if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand_id', $condition['demand_id']); } else { $query->where('demand_id', '=', $condition['demand_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['pv_id']) && !empty($condition['pv_id'])) { if (is_array($condition['pv_id'])) { $query->whereIn('pv_id', $condition['pv_id']); } else { $query->where('pv_id', '=', $condition['pv_id']); } } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取版本 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionVersion($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('project_version') ->where(function($query) use($condition) { if (isset($condition['pv_id']) && !empty($condition['pv_id'])) { if (is_array($condition['pv_id'])) { $query->whereIn('pv_id', $condition['pv_id']); } else { $query->where('pv_id', '=', $condition['pv_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['pv_code']) && !empty($condition['pv_code'])) { $query->where('pv_code', '=', $condition['pv_code']); } }) ->where(function($query) use($condition) { if (isset($condition['status'])) { if (is_array($condition['status']) && !empty($condition['status'])) { $query->whereIn('pv_status', $condition['status']); } else if ($condition['status'] !== '') { $query->where('pv_status', '=', $condition['status']); } } }) ->where(function($query) use($condition) { if (isset($condition['status_not'])) { if (is_array($condition['status_not']) && !empty($condition['status_not'])) { $query->whereNotIn('pv_status', $condition['status_not']); } else if ($condition['status_not'] !== '') { $query->where('pv_status', '!=', $condition['status_not']); } } }) ->where(function($query) use($condition) { if (isset($condition['plan_release_start_date']) && !empty($condition['plan_release_start_date'])) { $query->where('pv_plan_date', '>=', $condition['plan_release_start_date']); } }) ->where(function($query) use($condition) { if (isset($condition['plan_release_end_date']) && !empty($condition['plan_release_end_date'])) { $query->where('pv_plan_date', '<=', $condition['plan_release_end_date']); } }) ->where(function($query) use($condition) { if (isset($condition['create_start_time']) && !empty($condition['create_start_time'])) { $query->where('pv_create_date', '>=', $condition['create_start_time']); } }) ->where(function($query) use($condition) { if (isset($condition['create_end_time']) && !empty($condition['create_end_time'])) { $query->where('pv_create_date', '<=', $condition['create_end_time']); } }) ->where(function($query) use($condition) { if (isset($condition['actual_release_start_date']) && !empty($condition['actual_release_start_date'])) { $query->where('pv_last_modified', '>=', $condition['actual_release_start_date']); } }) ->where(function($query) use($condition) { if (isset($condition['actual_release_end_date']) && !empty($condition['actual_release_end_date'])) { $query->where('pv_last_modified', '<=', $condition['actual_release_end_date']); } }); switch ($type) { case 'count(*)': $sql = $select->count(); break; case 'countBysum': $type = 'count(*) as total, sum(pv_status = 2) as published_version'; $sql = $select->get(DB::raw($type))->toArray(); break; default: if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); break; } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取任务 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionTask($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('task') ->where(function($query) use($condition) { if (isset($condition['task_id']) && !empty($condition['task_id'])) { if (is_array($condition['task_id'])) { $query->whereIn('task_id', $condition['task_id']); } else { $query->where('task_id', '=', $condition['task_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['task_name']) && !empty($condition['task_name'])) { $query->where('task_name', 'like', '%' . $condition['task_name'] . '%'); } }) ->where(function($query) use($condition) { if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand_id', $condition['demand_id']); } else { $query->where('demand_id', '=', $condition['demand_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['model']) && !empty($condition['model'])) { if (is_array($condition['model'])) { $query->whereIn('pm_id', $condition['model']); } else { $query->where('pm_id', '=', $condition['model']); } } }) ->where(function($query) use($condition) { if (isset($condition['task_level']) && $condition['task_level'] !== '') { $query->where('task_level', '=', $condition['task_level']); } }) ->where(function($query) use($condition) { if (isset($condition['status'])) { if (is_array($condition['status']) && !empty($condition['status'])) { $query->whereIn('task_status', $condition['status']); } else if ($condition['status'] !== '') { $query->where('task_status', '=', $condition['status']); } } }) ->where(function($query) use($condition) { if (isset($condition['status_not'])) { if (is_array($condition['status_not']) && !empty($condition['status_not'])) { $query->whereNotIn('task_status', $condition['status_not']); } else if ($condition['status_not'] !== '') { $query->where('task_status', '!=', $condition['status_not']); } } }) ->where(function($query) use($condition) { if (isset($condition['status_less_than']) && $condition['status_less_than'] !== '') { $query->where('task_status', '<=', $condition['status_less_than']); } }) ->where(function($query) use($condition) { if (isset($condition['create_date_start']) && !empty($condition['create_date_start'])) { $query->where('task_create_date', '>=', $condition['create_date_start']); } }) ->where(function($query) use($condition) { if (isset($condition['create_date_end']) && !empty($condition['create_date_end'])) { $query->where('task_create_date', '<=', $condition['create_date_end']); } }) ->where(function($query) use($condition) { if (isset($condition['task_next_user']) && !empty($condition['task_next_user'])) { $query->where('task_next_user', '=', $condition['task_next_user']); } }) ->where(function($query) use($condition) { if (isset($condition['is_inform']) && $condition['is_inform'] !== '') { $query->where('is_inform', '=', $condition['is_inform']); } }); switch ($type) { case 'count(*)': $sql = $select->count(); break; case 'sum': $type = 'sum(task_status <= 6) as total_task, sum(task_status <= 5) as under_way'; $sql = $select->get(DB::raw($type))->toArray(); break; case 'group_concat(task_coding_user,task_test_user)': $type = 'group_concat(task_coding_user) as task_coding_user, group_concat(task_test_user) as task_test_user'; $sql = $select->get(DB::raw($type))->toArray(); break; default: if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); break; } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取缺陷 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionDefect($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('task_bug') ->where(function($query) use($condition) { if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['model']) && !empty($condition['model'])) { if (is_array($condition['model'])) { $query->whereIn('pm_id', $condition['model']); } else { $query->where('pm_id', '=', $condition['model']); } } }) ->where(function($query) use($condition) { if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand_id', $condition['demand_id']); } else { $query->where('demand_id', '=', $condition['demand_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['task_id']) && !empty($condition['task_id'])) { if (is_array($condition['task_id'])) { $query->whereIn('task_id', $condition['task_id']); } else { $query->where('task_id', '=', $condition['task_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['pv_id']) && !empty($condition['pv_id'])) { if (is_array($condition['pv_id'])) { $query->whereIn('pv_id', $condition['pv_id']); } else { $query->where('pv_id', '=', $condition['pv_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['status'])) { if (is_array($condition['status']) && !empty($condition['status'])) { $query->whereIn('tb_status', $condition['status']); } else if ($condition['status'] !== '') { $query->where('tb_status', '=', $condition['status']); } } }) ->where(function($query) use($condition) { if (isset($condition['status_not'])) { if (is_array($condition['status_not']) && !empty($condition['status_not'])) { $query->whereNotIn('tb_status', $condition['status_not']); } else if ($condition['status_not'] !== '') { $query->where('tb_status', '!=', $condition['status_not']); } } }) ->where(function($query) use($condition) { if (isset($condition['status_less_than']) && $condition['status_less_than'] !== '') { $query->where('tb_status', '<=', $condition['status_less_than']); } }) ->where(function($query) use($condition) { if (isset($condition['tb_name']) && !empty($condition['tb_name'])) { $query->where('tb_name', 'like', '%' . $condition['tb_name'], '%'); } }) ->where(function($query) use($condition) { if (isset($condition['task_emergency_level']) && $condition['task_emergency_level'] !== '') { $query->where('task_emergency_level', '=', $condition['task_emergency_level']); } }) ->where(function($query) use($condition) { if (isset($condition['create_date_start']) && !empty($condition['create_date_start'])) { $query->where('tb_create_date', '>=', $condition['create_date_start']); } }) ->where(function($query) use($condition) { if (isset($condition['create_date_end']) && !empty($condition['create_date_end'])) { $query->where('tb_create_date', '<=', $condition['create_date_end']); } }) ->where(function($query) use($condition) { if (isset($condition['tb_next_user']) && !empty($condition['tb_next_user'])) { $query->where('tb_next_user', '=', $condition['tb_next_user']); } }); switch ($type) { case 'count(*)': $sql = $select->count(); break; case 'sum': $type = 'sum(tb_status <= 5) as total_defect, sum(tb_status <= 3) as to_be_solved'; $sql = $select->get(DB::raw($type))->toArray(); break; default: if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); break; } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取日志 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionLog($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('demand_log') ->where(function($query) use($condition) { if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand_id', $condition['demand_id']); } else { $query->where('demand_id', '=', $condition['demand_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['type'])) { if (is_array($condition['type']) && !empty($condition['type'])) { $query->whereNotIn('dl_type', $condition['type']); } else if ($condition['type'] !== '') { $query->where('dl_type', '!=', $condition['type']); } } }) ->where(function($query) use($condition) { if (isset($condition['dl_type'])) { if (is_array($condition['dl_type']) && !empty($condition['dl_type'])) { $query->whereIn('dl_type', $condition['dl_type']); } else if ($condition['dl_type'] !== '') { $query->where('dl_type', '=', $condition['dl_type']); } } }) ->where(function($query) use($condition) { if (isset($condition['task_id'])) { if (is_array($condition['task_id']) && !empty($condition['task_id'])) { $query->whereIn('task_id', $condition['task_id']); } else if ($condition['task_id'] !== '') { $query->where('task_id', '=', $condition['task_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['remark']) && !empty($condition['remark'])) { $query->where('dl_remark', 'like', '%' . $condition['remark'] . '%'); } }) ->where(function($query) use($condition) { if (isset($condition['task_id_not'])) { if (is_array($condition['task_id_not']) && !empty($condition['task_id_not'])) { $query->whereNotIn('task_id', $condition['task_id_not']); } else { $query->where('task_id', '!=', $condition['task_id_not']); } } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取项目权限 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionProjectAuthority($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('project_user') ->where(function($query) use($condition) { if (isset($condition['user_id']) && !empty($condition['user_id'])) { if (is_array($condition['user_id'])) { $query->whereIn('user_id', $condition['user_id']); } else { $query->where('user_id', '=', $condition['user_id']); } } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取配置 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionConfig($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('config') ->where(function($query) use($condition) { if (isset($condition['config_id']) && !empty($condition['config_id'])) { if (is_array($condition['config_id'])) { $query->whereIn('config_id', $condition['config_id']); } else { $query->where('config_id', '=', $condition['config_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['project_id']) && !empty($condition['project_id'])) { if (is_array($condition['project_id'])) { $query->whereIn('project_id', $condition['project_id']); } else { $query->where('project_id', '=', $condition['project_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['attribute']) && !empty($condition['attribute'])) { if (is_array($condition['attribute'])) { $query->whereIn('config_attribute', $condition['attribute']); } else { $query->where('config_attribute', '=', $condition['attribute']); } } }) ->where(function($query) use($condition) { if (isset($condition['like_attribute']) && !empty($condition['like_attribute'])) { $query->where('config_attribute', 'like', '%' . $condition['like_attribute']); } }) ->where(function($query) use($condition) { if (isset($condition['user_id']) && !empty($condition['user_id'])) { if (is_array($condition['user_id'])) { $query->whereIn('config_creater', $condition['user_id']); } else { $query->where('config_creater', '=', $condition['user_id']); } } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取需求版本详情左关联版本 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionDemandVersionDetailLeftJoinVersion($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('demand_version_detail') ->leftjoin('project_version', 'demand_version_detail.pv_id', '=', 'project_version.pv_id') ->where(function($query) use($condition) { if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand_id', $condition['demand_id']); } else { $query->where('demand_id', '=', $condition['demand_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['pv_id']) && !empty($condition['pv_id'])) { if (is_array($condition['pv_id'])) { $query->whereIn('pv_id', $condition['pv_id']); } else { $query->where('pv_id', '=', $condition['pv_id']); } } }); if ($type == 'count(*)') { $sql = $select->count(); } else { if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); } // die($select->select()->tosql()); return $sql; } /** * 依据条件获取需求左关联任务 * @param Array $condition 条件 * @param String || Array $type 结果集 * @param String $groupBy 聚合 * @param Array $orderBy 排序 * @param Integer $page 页 * @param Integer $pageSize 页数 * @return Integer || Array */ public static function getByConditionDemandLeftJoinTask($condition = array(), $type = '*', $groupBy = '', $orderBy = array(), $page = 0, $pageSize = 0) { $select = DB::table('demand') ->leftjoin('task', 'demand.demand_id', '=', 'task.demand_id') ->where(function($query) use($condition) { if (isset($condition['demand_id']) && !empty($condition['demand_id'])) { if (is_array($condition['demand_id'])) { $query->whereIn('demand.demand_id', $condition['demand_id']); } else { $query->where('demand.demand_id', '=', $condition['demand_id']); } } }) ->where(function($query) use($condition) { if (isset($condition['demand_name']) && !empty($condition['demand_name'])) { $query->where('demand_name', 'like', '%' . $condition['demand_name'] . '%'); } }) ->where(function($query) use($condition) { if (isset($condition['demand_from_user']) && !empty($condition['demand_from_user'])) { $query->where('demand_from_user', '=', $condition['demand_from_user']); } }) ->where(function($query) use($condition) { if (isset($condition['is_score']) && $condition['is_score'] !== '') { $query->where('demand_functional_satisfaction', '=', $condition['is_score'])->where('demand_communication_satisfaction', '=', $condition['is_score']); } }) ->where(function($query) use($condition) { if (isset($condition['demand_push_mail_frequency']) && $condition['demand_push_mail_frequency'] !== '') { $query->where('demand_push_mail_frequency', '=', $condition['demand_push_mail_frequency']); } }) ->where(function($query) use($condition) { if (isset($condition['status'])) { if (is_array($condition['status']) && !empty($condition['status'])) { $query->whereNotIn('demand_status', $condition['status']); } else if ($condition['status'] !== '') { $query->where('demand_status', '=', $condition['status']); } } }) ->where(function($query) use($condition) { if (isset($condition['status_not'])) { if (is_array($condition['status_not']) && !empty($condition['status_not'])) { $query->whereNotIn('demand_status', $condition['status_not']); } else if ($condition['status_not'] !== '') { $query->where('demand_status', '!=', $condition['status_not']); } } }) ->where(function($query) use($condition) { if (isset($condition['demand_dev_lead']) && !empty($condition['demand_dev_lead'])) { if (is_array($condition['demand_dev_lead'])) { $query->whereIn('demand_dev_lead', $condition['demand_dev_lead']); } else { $query->where('demand_dev_lead', '=', $condition['demand_dev_lead']); } } }) ->where(function($query) use($condition) { if (isset($condition['type_not']) && !empty($condition['type_not'])) { if (is_array($condition['type_not'])) { $query->whereNotIn('demand_type', $condition['type_not']); } else { $query->where('demand_type', '!=', $condition['type_not']); } } }) ->where(function($query) use($condition) { if (isset($condition['is_inform']) && $condition['is_inform'] !== '') { $query->where('is_inform', '=', $condition['is_inform']); } }) ->where(function($query) use($condition) { if (isset($condition['demand_type']) && $condition['demand_type'] !== '') { $query->where('demand_type', '=', $condition['demand_type']); } }) ->where(function($query) use($condition) { if (isset($condition['task_coding_user']) && $condition['task_coding_user'] !== '') { $query->where('task.task_coding_user', '=', $condition['task_coding_user']); } }) ->where(function($query) use($condition) { if (isset($condition['task_test_user']) && $condition['task_test_user'] !== '') { $query->where('task.task_test_user', '=', $condition['task_test_user']); } }) ->where(function($query) use($condition) { if (isset($condition['create']) && $condition['create'] !== '') { $query->where('demand_creater', '=', $condition['create']); } }) ->where(function($query) use($condition) { if (isset($condition['demand_uptime_start']) && !empty($condition['demand_uptime_start'])) { $query->where('demand_uptime', '>=', $condition['demand_uptime_start']); } }) ->where(function($query) use($condition) { if (isset($condition['demand_uptime_end']) && !empty($condition['demand_uptime_end'])) { $query->where('demand_uptime', '<=', $condition['demand_uptime_end']); } }); switch ($type) { case 'count(*)'; $sql = $select->count(); break; case 'group_concat(demand_id)'; if (!empty($groupBy)) { $select->groupBy($groupBy); } $type = 'group_concat(demand.demand_id) as demand_id, demand_from_user'; $sql = $select->get(DB::raw($type))->toArray(); break; case 'group_concat(task_coding_user,task_test_user)'; if (!empty($groupBy)) { $select->groupBy($groupBy); } $type = 'demand.demand_id, demand_name, demand_from_user, demand_creater, GROUP_CONCAT(task_coding_user) as task_coding_user, GROUP_CONCAT(task_test_user) as task_test_user'; $sql = $select->get(DB::raw($type))->toArray(); break; default: if (!empty($groupBy)) { $select->groupBy($groupBy); } if (!empty($orderBy)) { $select->orderBy($orderBy[0], $orderBy[1]); } if ($pageSize > 0 && $page > 0) { $start = ($page - 1) * $pageSize; $select->offset($start)->limit($pageSize); } $sql = $select->get($type)->toArray(); break; } //die($select->select()->tosql()); return $sql; } }