laravel model（查询）
黄元章
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;
}
}
类似资料：