<?php
class ModelMemberPoint extends Model {
	public function addMemberPoint($data) {
		$sql = "INSERT INTO `" . DB_PREFIX . "member_point` 
		SET member_id = '" . $this->db->escape($data['member_id']) . "',
		order_no = '" . $this->db->escape(trim($data['order_no'])) . "',
		regular_price_amount = '" . (float)($data['regular_price_amount']) . "',
		sales_price_amount = '" . (float)($data['sales_price_amount']). "',
		min_tier = '" . $this->db->escape($data['min_tier']) . "',
		expiry = '" . $this->db->escape($data['expiry']) . "',
		redemption_environment_id = '" . $this->db->escape($data['redemption_environment_id']) . "',
		image = '" . $this->db->escape($data['image']) . "',
		description = '" . $this->db->escape($data['description']) . "',
		term_and_condition = '" . $this->db->escape($data['term_and_condition']) . "',
		start_date = '" . $this->db->escape($data['start_date']) . "'";

		if (!empty($data['end_date'])) {
			$sql .= ",end_date = '" . $this->db->escape($data['end_date']) . "'";
		}

		$sql .= ",status = '" . $this->db->escape($data['status']) . "',created_by = '" . $this->session->data['username'] . "', created_date = NOW(), updated_by = '" . $this->session->data['username'] . "', updated_date = NOW();";

		$this->db->query($sql);

		$member_reward_id = $this->db->getLastId();
		//$this->insertRewardSettingLog($member_reward_id);

		return $member_reward_id;
	}

	public function editMemberPoint($member_point_id, $data) {
		$sql = "UPDATE `" . DB_PREFIX . "member_reward` SET reward_type_id = '" . $this->db->escape($data['reward_type_id']) . "',title = '" . $this->db->escape($data['title']) . "',point = '" . $this->db->escape($data['point']) . "',value = '" . $this->db->escape($data['value']) . "',min_tier = '" . $this->db->escape($data['min_tier']) . "',expiry = '" . $this->db->escape($data['expiry']) . "',redemption_environment_id = '" . $this->db->escape($data['redemption_environment_id']) . "',image = '" . $this->db->escape($data['image']) . "',description = '" . $this->db->escape($data['description']) . "',term_and_condition = '" . $this->db->escape($data['term_and_condition']) . "',start_date = '" . $this->db->escape($data['start_date']) . "'";

		if (!empty($data['end_date'])) {
			$sql .= ",end_date = '" . $this->db->escape($data['end_date']) . "'";
		} else {
			$sql .= ",end_date = NULL";
		}

		$sql .= ",status = '" . $this->db->escape($data['status']) . "',updated_by = '" . $this->session->data['username'] . "', updated_date = NOW() WHERE member_point_id = '" . (int)$member_point_id . "';";

		$this->db->query($sql);
        $this->insertRewardSettingLog($member_point_id);
	}

	public function deleteRewardSetting($member_point_id) {
		$this->db->query("DELETE FROM `" . DB_PREFIX . "member_reward` WHERE member_reward_id = '" . (int)$member_point_id . "'");
	}

	public function getMemberPoint ($member_point_id) {
		$query = $this->db->query("SELECT * FROM `" . DB_PREFIX . "member_point` mp  WHERE mp.member_point_id = '" . (int)$member_point_id . "';");
		return $query->row;
	}

	public function getActiveRewardSettingByTitle($title) {
		$query = $this->db->query("SELECT *, (SELECT rt.value FROM `" . DB_PREFIX . "reward_type` rt WHERE rs.reward_type_id = rt.reward_type_id) AS reward_type_name, (SELECT ts.title FROM `" . DB_PREFIX . "tier_setting` ts WHERE rs.min_tier = ts.tier_setting_id) AS min_tier_name, (SELECT re.value FROM `" . DB_PREFIX . "redemption_environment` re WHERE rs.redemption_environment_id = re.redemption_environment_id) AS redemption_environment_name FROM `" . DB_PREFIX . "member_reward` rs WHERE rs.title = '" . trim($title) . "' AND rs.Status = 1;");
		return $query->row;
	}

	public function getMemberPoints($data = array()) {
		$sql = "SELECT mr.*, m.phone_no, m.email
		FROM `" . DB_PREFIX . "member_point` mr 
		INNER JOIN `" . DB_PREFIX . "member` m ON mr.member_id = m.member_id ";

		$implode = array();

		if (!empty($data['filter_email'])) {
			$implode[] = "m.email LIKE '" . $this->db->escape($data['filter_email']) . "%'";
		}

		if (!empty($data['filter_phone_no'])) {
			$implode[] = "m.phone_no LIKE '" . $this->db->escape($data['filter_phone_no']) . "%'";
		}

		if ($implode) {
			$sql .= " WHERE " . implode(" AND ", $implode);
		}

		$sort_data = array(
			'email',
			'point',
			'available_point',
			'date_added'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY member_point_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'];
		}

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

		return $query->rows;
	}

	public function getTotalMemberPoints() {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "member_point`");
		return $query->row['total'];
	}

	public function insertRewardSettingLog($member_reward_id) {
		$this->db->query("INSERT INTO `" . DB_PREFIX . "member_reward_log` (member_reward_id, title, point, value, min_tier, expiry, redemption_environment_id, image, description, term_and_condition, start_date, end_date, status, created_by, created_date) SELECT member_reward_id, title, point, value, min_tier, expiry, redemption_environment_id, image, description, term_and_condition, start_date, end_date, status, updated_by as created_by, NOW() AS created_date FROM `" . DB_PREFIX . "member_reward` WHERE member_reward_id = '" . $member_reward_id . "';");
	}

	public function getRewardTypes() {
		$query = $this->db->query("SELECT reward_type_id, value AS reward_type_name FROM `" . DB_PREFIX . "reward_type`;");
		return $query->rows;
	}

	public function getActiveTierSettings() {
		$query = $this->db->query("SELECT tier_setting_id, title AS tier_setting_name FROM `" . DB_PREFIX . "tier_setting` WHERE Status = 1 ORDER BY achieve_tier_amount ASC;");
		return $query->rows;
	}

	public function getRedemptionEnvironments() {
		$query = $this->db->query("SELECT redemption_environment_id, value AS redemption_environment_name FROM `" . DB_PREFIX . "redemption_environment`;");
		return $query->rows;
	}

	public function getActiveRewardSettings() {
		$query = $this->db->query("SELECT rs.member_reward_id, rs.title AS member_reward_name, rs.point, rs.start_date, rs.end_date FROM `" . DB_PREFIX . "member_reward` rs WHERE rs.Status = 1 AND (rs.end_date IS NULL OR DATE(rs.end_date) >= DATE(NOW())) ORDER BY rs.title ASC;");
		return $query->rows;
	}
}