/**
* Get list of customers.
*/
public function executeGetCustomers()
{
$wpdb = $this->getWpdb();
$response = array('status' => 'ok', 'data' => array('customers' => array(), 'total' => 0, 'pages' => 0, 'active_page' => 0));
$page = intval($this->getParameter('page'));
$sort = in_array($this->getParameter('sort'), array('name', 'phone', 'email', 'notes', 'last_appointment', 'total_appointments', 'payments')) ? $this->getParameter('sort') : 'name';
$order = in_array($this->getParameter('order'), array('asc', 'desc')) ? $this->getParameter('order') : 'asc';
$filter = $wpdb->_real_escape($this->getParameter('filter'));
$items_per_page = 20;
$total = $wpdb->get_var('SELECT COUNT(*) FROM `ab_customer`');
$pages = ceil($total / $items_per_page);
if ($page < 1 || $page > $pages) {
$page = 1;
}
if ($total) {
$query = "SELECT `c`.*, MAX(`a`.`start_date`) AS `last_appointment`, COUNT(`a`.`id`) AS `total_appointments`, COALESCE(SUM(`p`.`total`),0) AS `payments`\n FROM `ab_customer` `c`\n LEFT JOIN `ab_customer_appointment` `ca` ON `ca`.`customer_id` = `c`.`id`\n LEFT JOIN `ab_appointment` `a` ON `a`.`id` = `ca`.`appointment_id`\n LEFT JOIN `ab_payment` `p` ON `p`.`appointment_id` = `a`.`id` and `p`.`customer_id` = `c`.`id`";
// WHERE
if ($filter !== '') {
$query .= " WHERE `c`.`name` LIKE '%{$filter}%' OR `c`.`phone` LIKE '%{$filter}%' OR `c`.`email` LIKE '%{$filter}%'";
}
// GROUP BY
$query .= ' GROUP BY `c`.`id`';
// ORDER BY
$query .= " ORDER BY {$sort} {$order}";
// LIMIT
$start = ($page - 1) * $items_per_page;
$query .= " LIMIT {$start}, {$items_per_page}";
$data = $wpdb->get_results($query);
array_walk($data, function ($row) {
$row->last_appointment = AB_CommonUtils::getFormattedDateTime($row->last_appointment);
$row->payments = AB_CommonUtils::formatPrice($row->payments);
});
// Populate response.
$response['data']['customers'] = $data;
$response['data']['total'] = $total;
$response['data']['pages'] = $pages;
$response['data']['active_page'] = $page;
}
echo json_encode($response);
exit(0);
}
