OpenCart 🇺🇦

$sql = "
	SELECT `or`.order_recurring_id, `or`.order_id, `or`.`status`, `or`.`created`, `or`.profile_reference,
	  CONCAT(`o`.`firstname`, ' ', `o`.`lastname`) AS `customer`
	FROM `" . DB_PREFIX . "order_recurring` `or`
	JOIN `" . DB_PREFIX . "order` `o` USING(`order_id`)
	WHERE 1 = 1 ";

if (!empty($data['filter_order_recurring_id'])) {
	$sql .= " AND or.order_recurring_id = " . (int)$data['filter_order_recurring_id'];
}

if (!empty($data['filter_order_id'])) {
	$sql .= " AND or.order_id = " . (int)$data['filter_order_id'];
}

if (!empty($data['filter_payment_reference'])) {
	$sql .= " AND or.profile_reference LIKE '" . $this->db->escape($data['filter_payment_reference']) . "%'";
}

if (!empty($data['filter_customer'])) {
	$sql .= " AND CONCAT(o.firstname, ' ', o.lastname) LIKE '" . $this->db->escape($data['filter_customer']) . "%'";
}

if (!empty($data['filter_created'])) {
	$sql .= " AND DATE(or.created) = DATE('" . $this->db->escape($data['filter_created']) . "')";
}

if (!empty($data['filter_status'])) {
	$sql .= " AND or.status = " . (int)$data['filter_status'];
}

$sort_data = array(
	'or.order_recurring_id',
	'or.order_id',
	'or.profile_reference',
	'customer',
	'or.created',
	'or.status',
);

if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
	$sql .= " ORDER BY " . $data['sort'];
} else {
	$sql .= " ORDER BY or.order_recurring_id";
}

if (isset($data['order']) && ($data['order'] == 'DESC')) {
	$sql .= " DESC";
} else {
	$sql .= " ASC";
}

if (isset($data['start']) || isset($data['limit'])) {
	if ($data['start'] < 0) {
		$data['start'] = 0;
	}

	if ($data['limit'] < 1) {
		$data['limit'] = 20;
	}

	$sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
}

$profiles = array();

$results = $this->db->query($sql)->rows;

foreach ($results as $result) {
	$profiles[] = array(
		'order_recurring_id' => $result['order_recurring_id'],
		'order_id' => $result['order_id'],
		'status' => $this->getStatus($result['status']),
		'created' => $result['created'],
		'profile_reference' => $result['profile_reference'],
		'customer' => $result['customer'],
	);
}

return $profiles;