/**
* Get list of appointments.
*/
public function executeGetAppointments()
{
$response = array('appointments' => array(), 'total' => 0, 'pages' => 0, 'active_page' => 0);
$page = intval($this->getParameter('page'));
$sort = in_array($this->getParameter('sort'), array('staff_name', 'service_title', 'start_date', 'price')) ? $this->getParameter('sort') : 'start_date';
$order = in_array($this->getParameter('order'), array('asc', 'desc')) ? $this->getParameter('order') : 'asc';
$start_date = new DateTime($this->getParameter('date_start'));
$start_date = $start_date->format('Y-m-d H:i:s');
$end_date = new DateTime($this->getParameter('date_end'));
$end_date = $end_date->modify('+1 day')->format('Y-m-d H:i:s');
$items_per_page = 20;
$total = AB_Appointment::query()->whereBetween('start_date', $start_date, $end_date)->count();
$pages = ceil($total / $items_per_page);
if ($page < 1 || $page > $pages) {
$page = 1;
}
if ($total) {
$query = AB_CustomerAppointment::query('ca')->select('ca.id,
ca.number_of_persons,
ca.coupon_discount,
ca.coupon_deduction,
ca.appointment_id,
st.full_name AS staff_name,
s.title AS service_title,
s.duration AS service_duration,
c.name AS customer_name,
a.start_date,
ss.price')->leftJoin('AB_Appointment', 'a', 'a.id = ca.appointment_id')->leftJoin('AB_Service', 's', 's.id = a.service_id')->leftJoin('AB_Customer', 'c', 'c.id = ca.customer_id')->leftJoin('AB_Staff', 'st', 'st.id = a.staff_id')->leftJoin('AB_StaffService', 'ss', 'ss.staff_id = st.id AND ss.service_id = s.id')->whereBetween('a.start_date', $start_date, $end_date)->sortBy($sort)->order($order);
// LIMIT.
$start = ($page - 1) * $items_per_page;
$query->offset($start)->limit($items_per_page);
$rows = $query->fetchArray();
foreach ($rows as &$row) {
if ($row['coupon_discount'] or $row['coupon_deduction']) {
$coupon = new AB_Coupon();
$coupon->set('discount', $row['coupon_discount']);
$coupon->set('deduction', $row['coupon_deduction']);
$row['price'] = $coupon->apply($row['price']);
}
$row['price'] *= $row['number_of_persons'];
$row['price'] = AB_Utils::formatPrice($row['price']);
$row['start_date'] = AB_DateTimeUtils::formatDateTime($row['start_date']);
$row['service_duration'] = AB_Service::durationToString($row['service_duration']);
}
// Populate response.
$response['appointments'] = $rows;
$response['total'] = $total;
$response['pages'] = $pages;
$response['active_page'] = $page;
}
wp_send_json_success($response);
}
/**
* Get list of customers.
*/
public function executeGetCustomers()
{
$wpdb = $this->getWpdb();
$response = 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', 'wp_user')) ? $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 = AB_Customer::query()->count();
$pages = ceil($total / $items_per_page);
if ($page < 1 || $page > $pages) {
$page = 1;
}
if ($total) {
$query = AB_Customer::query('c')->select('c.*, MAX(a.start_date) AS last_appointment,
COUNT(a.id) AS total_appointments,
COALESCE(SUM(p.total),0) AS payments,
wpu.display_name AS wp_user')->leftJoin('AB_CustomerAppointment', 'ca', 'ca.customer_id = c.id')->leftJoin('AB_Appointment', 'a', 'a.id = ca.appointment_id')->leftJoin('AB_Payment', 'p', 'p.customer_appointment_id = ca.id')->tableJoin($wpdb->users, 'wpu', 'wpu.ID = c.wp_user_id');
// WHERE
if ($filter !== '') {
$query->whereLike('c.name', "%{$filter}%")->whereLike('c.phone', "%{$filter}%", 'OR')->whereLike('c.email', "%{$filter}%", 'OR');
}
$data = $query->groupBy('c.id')->sortBy($sort)->order($order)->limit($items_per_page)->offset(($page - 1) * $items_per_page)->fetchArray();
array_walk($data, function (&$row) {
if ($row['last_appointment']) {
$row['last_appointment'] = AB_DateTimeUtils::formatDateTime($row['last_appointment']);
}
$row['payments'] = AB_Utils::formatPrice($row['payments']);
});
// Populate response.
$response['customers'] = $data;
$response['total'] = $total;
$response['pages'] = $pages;
$response['active_page'] = $page;
}
wp_send_json_success($response);
}