依據前端傳入參數動態組合 $conditions 陣列:
public function getConditions($data) {
$this->loadModel('Cm.LoginHistory');
$conditions = [];
foreach ($data as $key => $val) {
$val = trim($val);
if ($key === 'start_date' && $val) {
$conditions['LoginHistory.logintime >='] = $val;
} elseif ($key === 'end_date' && $val) {
$conditions['LoginHistory.logouttime <='] = $val;
} elseif ($key === 'selectvsoftid' && $val) {
$conditions['LoginHistory.LOGINSUBSYSTEM LIKE'] = "%$val%";
} elseif ($key === 'username' && $val) {
$conditions['LoginHistory.USERNAME LIKE'] = "%$val%";
} elseif ($key === 'vdevicename' && $val) {
$conditions['LoginHistory.WINDOWNAME LIKE'] = "%$val%";
} elseif ($key === 'selectvsoftid' && $val) { // 注意:鍵名重複,實際應區分
$conditions['LoginHistory.PHDEVICENAME LIKE'] = "%$val%";
}
}
return $conditions;
}
$fields = [
'LoginHistory.windowname',
'LoginHistory.loginsubsystem',
'VirtualDevice.PHDEVICEID',
'LoginHistory.userid',
'VirtualDevice.SYSTYPEID',
'UserInfo.USERNAME',
'"SUM"(LoginHistory.LOGOUTTIME - LoginHistory.LOGINTIME) moument',
'DeviceInfo.DEVICENAME'
];
$group = [
'LoginHistory.windowname',
'LoginHistory.loginsubsystem',
'LoginHistory.userid',
'UserInfo.USERNAME',
'DeviceInfo.DEVICENAME',
'VirtualDevice.SYSTYPEID',
'VirtualDevice.PHDEVICEID'
];
$this->paginate = [
'conditions' => $conditions,
'fields' => $fields,
'group' => $group,
'limit' => 9,
'joins' => [
[
'alias' => 'UserInfo',
'table' => 'SMM_USERINFO',
'type' => 'LEFT',
'conditions' => 'LoginHistory.USERID = UserInfo.USERID'
],
[
'alias' => 'VirtualDevice',
'table' => 'ET_NMVIRTUALDEVICEINFO',
'type' => 'LEFT',
'conditions' => 'LoginHistory.WINDOWNAME = VirtualDevice.DEVICENAME'
],
[
'alias' => 'DeviceInfo',
'table' => 'ET_NMDEVICEINFO',
'type' => 'LEFT',
'conditions' => 'VirtualDevice.PHDEVICEID = DeviceInfo.DEVICEID'
]
]
];
$loginHistory = $this->paginate('LoginHistory');
$this->set('loginhistory', $loginHistory);
LEFT/INNER/RIGHT 關聯,conditions 使用 SQL 片段,欄位需加上 Model 別名避免歧義。"SUM"(...))。ONLY_FULL_GROUP_BY 模式會報錯。<table>
<tr><th>裝置</th><th>子系統</th><th>使用者</th><th>總時長(分)</th></tr>
<?php foreach ($loginhistory as $row): ?>
<tr>
<td><?= h($row['LoginHistory']['windowname']) ?></td>
<td><?= h($row['LoginHistory']['loginsubsystem']) ?></td>
<td><?= h($row['UserInfo']['USERNAME']) ?></td>
<td><?= h($row[0]['moument']) ?></td>
</tr>
<?php endforeach; ?>
</table>
<?= $this->Paginator->numbers() ?>
USERID、DEVICENAME、PHDEVICEID)有索引。conditions 使用 LIKE '%...%' 導致全表掃描,必要時用全文索引。