tableName = $wpdb->prefix . AmzFulfillment_Core::FULFILLMENT_TABLE; $this->createQuery = " CREATE TABLE `%s` ( `fulfillmentId` int NOT NULL AUTO_INCREMENT PRIMARY KEY, `orderId` int NOT NULL, `status` varchar(32) NOT NULL, `statusTime` datetime DEFAULT NULL, INDEX idx_orderId_status(orderId, status) )"; add_action('wp_ajax_amzFulfillmentFulfillments', array ($this, 'createJson')); } public function add($orderId, $status, $date) { global $wpdb; $query = "INSERT INTO %s (orderId, status, statusTime) VALUES(%d, '%s', '%s')"; $wpdb->query(sprintf($query, $this->tableName, $orderId, esc_sql($status), esc_sql($date))); } public function exist($orderId, $status = NULL) { global $wpdb; if($status === NULL) { $query = sprintf("SELECT fulfillmentId FROM `%s` WHERE `orderId` = %d LIMIT 1", $this->tableName, $orderId); } else { $query = sprintf("SELECT fulfillmentId FROM `%s` WHERE `orderId` = %d AND `status` = '%s' LIMIT 1", $this->tableName, $orderId, esc_sql($status)); } $results = $wpdb->get_results($query); if(count($results)) { return true; } else { return false; } } public function getByOrderId($orderId) { global $wpdb; $query = sprintf("SELECT * FROM `%s` WHERE `orderId`=%d ORDER BY `statusDate` ASC", $this->tableName, $orderId); return $wpdb->get_results($query); } public function getAll() { global $wpdb; $query = sprintf("SELECT * FROM `%s` ORDER BY `statusDate`,`orderId` DESC", $this->tableName); return $wpdb->get_results($query); } public function createJson() { global $wpdb; $request = $_POST; if(empty($request)) { return; } $columns = array ( array('db' => 'statusTime', 'dt' => 0), array('db' => 'orderId', 'dt' => 1), array('db' => 'status', 'dt' => 2) ); $properties = implode(",", SSP::pluck($columns, 'db')); $binding = array(); $where = SSP::filter($request, $columns, $binding); $order = SSP::order($request, $columns); $limit = SSP::limit($request, $columns); $query = sprintf("SELECT SQL_CALC_FOUND_ROWS %s FROM %s %s %s %s", $properties, $this->tableName, $where, $order, $limit); foreach($binding as $bind) { $value = $bind['val']; $value = str_replace('%', '', $value); $value = "'%" . esc_sql($value) . "%'"; $query = str_replace($bind['key'], $value, $query); } $results = $wpdb->get_results($query, 'ARRAY_A'); $resultsFilter = $wpdb->get_results("SELECT FOUND_ROWS() as filteredRows"); $recordsFiltered = intval($resultsFilter[0]->filteredRows); $recordsTotal = $recordsFiltered; $rows = SSP::data_output($columns, $results); for($i = 0; $i < count($rows); $i++) { $rows[$i][0] = AmzFulfillment_Core::instance()->getFormatedDate($rows[$i][0]); $orderId = intval($rows[$i][1]); $rows[$i][1] = sprintf('%d', $orderId, $orderId); $rows[$i][2] = ucfirst(strtolower($rows[$i][2])); } wp_send_json(array( "draw" => intval($request['draw']), "recordsTotal" => $recordsTotal, "recordsFiltered" => $recordsFiltered, "data" => $rows )); } }