/**
* @param RequestAbstract $request
* @return Route
* @throws RouterException
*/
protected function getRouterForRequest(RequestAbstract $request)
{
foreach ($this->routes as $route) {
if ($route->getMethod() == $request->getMethod()) {
if ($route->isMe($request->getPath())) {
return $route;
}
}
}
throw new RouterException('Route was not found!');
}
public function getCustomersListByPostRequest(RequestAbstract $request)
{
//SELECT
// c.id,
// c.fio,
// c.email,
// c.phone,
// c.balance,
// IFNULL(
// AVG(p.amount),
// 0
// ) AS avg_payment
//FROM
// customers AS c
// LEFT JOIN payments AS p ON c.id = p.customer_id
//WHERE
// c.fio LIKE "%%"
// AND (
// c.create_ts BETWEEN ""
// AND ""
// )
// AND c.balance > 0
// AND phone IS NULL
//GROUP BY
// c.id
//HAVING
// avg_payment BETWEEN 10
// AND 100
$query = $this->db->from('customers AS c')->leftJoin('payments AS p ON c.id = p.customer_id')->select('c.id')->select('c.fio')->select('c.email')->select('c.phone')->select('c.balance')->select('IFNULL(AVG(p.amount),0) AS avg_payment')->groupBy('c.id')->where('c.fio LIKE ?', '%' . $request->post('fio') . '%');
if ($request->post('hasPhone') == '1') {
$query = $query->where('c.phone IS NOT NULL');
}
//balance range
if (!empty($request->post('minBalance'))) {
$query = $query->where('c.balance >= ?', $request->post('minBalance'));
}
if (!empty($request->post('maxBalance'))) {
$query = $query->where('c.balance <= ?', $request->post('maxBalance'));
}
//average payment range
if (!empty($request->post('minAvgPayment'))) {
$query = $query->having('avg_payment >= ' . intval($request->post('minAvgPayment')));
}
if (!empty($request->post('maxAvgPayment'))) {
$query = $query->having('avg_payment <= ' . intval($request->post('maxAvgPayment')));
}
//registration date range
if (!empty($request->post('minDate'))) {
$query = $query->where('c.create_ts >= ?', $request->post('minDate'));
}
if (!empty($request->post('maxDate'))) {
$query = $query->where('c.create_ts <= ?', $request->post('maxDate'));
}
return $query->fetchAll();
}