TP5中一个where使用多个条件
public function result()
{
checkLogin();
$userId = session('userId'); //获取session
//查询条件1
$type = input('post.type');
$value = input('post.value');
if ($type == '-1') { //全部
$type = "record.id";
$value = "not null";
} elseif ($type == 0) { //应用名称
$type = "record.app_name";
if (empty($value)){
$value = "not null";
}
} elseif ($type == 1) { //文件名称
$type = "record.apk_name";
if (empty($value)){
$value = "not null";
}
}
//查询条件2
$status = input('post.status');
if ($status== '-1') { //查询所有数据
$status = "not null";
$result = 'record.id';
}elseif($status == 0 ){ //测评中
$status = "null";
$result = 'record.result';
}elseif($status == 1 ){ //审核中
$status = "is not null";
$result = '(report.status = 0 or company.status = 0) and record.result is not null';
}elseif($status == 2 ){ //未通过审核
$status = "2";
$result = 'report.status';
}elseif($status == 3 ){ //待支付
$status = "0";
$result = 'report.status = 1 and company.status = 1 and order.status = 0';
}elseif($status == 4 ){ //已完成
$status = "1";
$result = 'report.status = 1 and company.status = 1 and order.status = 1';
}
$startTime = input('post.startTime');//
if (empty($startTime)) {
$startTime = '1970-01-01 00:00:00';
}
$endTime = input('post.endTime');//
if (empty($endTime)) {
$endTime = date("Y-m-d H:i:s", time());
}
//每页条数
$pageSize = input('post.pageSize');
//当前页
$pageNum = input('post.pageNo');
//总条数
$count = Db::field('record.id')
->table(['t_record' => 'record'])
->join(['t_user' => 'user'], "user.id = record.account_id", 'left')
->join(['t_report' => 'report'], "record.id = report.record_id", 'left')
->join(['t_order' => 'order'], "order.user_id = record.account_id and order.report_num = report.report_num", 'left')
->join(['t_company' => 'company'], "company.user_id = record.account_id", 'left')
->where($type, $value)
->where($result, $status)
->where('company.status','<>', 2)
->where('record.account_id', $userId)
->where('record.commit_time', '>= time', $startTime)
->where('record.commit_time', '<= time', $endTime)
->count();
//总页数
$pagecount = ceil($count / $pageSize);
//从哪个ID开始
$fromId = ($pageNum - 1) * $pageSize;
$result = Db::field('company.status companyStatus,record.id recordId,record.app_name appName,record.apk_name apkName,record.apk_ver apkVer,record.apk_size apkSize,record.commit_time commitTime,record.result,record.status recordStatus,report.status reportStatus,report.report_num reportNum,order.status orderStatus')
->table(['t_record' => 'record'])
->join(['t_user' => 'user'], "user.id = record.account_id", 'left')
->join(['t_report' => 'report'], "record.id = report.record_id", 'left')
->join(['t_order' => 'order'], "order.user_id = record.account_id and order.report_num = report.report_num", 'left')
->join(['t_company' => 'company'], "company.user_id = record.account_id", 'left')
->where($type, $value)
->where($result, $status)
->where('company.status','<>', 2)
->where('record.account_id', $userId)
->where('record.commit_time', '>= time', $startTime)
->where('record.commit_time', '<= time', $endTime)
->order('record.id desc')
->limit($fromId, $pageSize)
->select();
for ($i = 0; $i < count($result); $i++) {
//状态
if ($result[$i]['result'] == '' ){ //测评中
$result[$i]['status'] = '0';
}elseif ($result[$i]['result'] != '' and ($result[$i]['companyStatus'] == 0 or $result[$i]['reportStatus'] == 0)) 审核中
{
$result[$i]['status'] = '1';
}elseif ( $result[$i]['reportStatus'] == 2) 未通过审核
{
$result[$i]['status'] = '2';
}elseif ($result[$i]['companyStatus'] == 1 and $result[$i]['reportStatus'] == 1 and $result[$i]['orderStatus'] == 0) //待支付
{
$result[$i]['status'] = '3';
}elseif ($result[$i]['companyStatus'] == 1 and $result[$i]['reportStatus'] == 1 and $result[$i]['orderStatus'] == 1) //已完成
{
$result[$i]['status'] = '4';
}
}
$data['dataList'] = $result;
$data['pageNum'] = $pageNum;
$data['totalNumber'] = $count;
$data['totalPages'] = $pagecount;
if ($result) {
$data = str_ireplace('NULL','',$data); //替换null
return array("status" => '0', 'data' => $data);
} else {
return array("status" => '0', 'data' => $data);
}
}