/** * 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); }