<?php
class ModelMemberMember extends Model {
	public function addMember($external_member_id, $first_name, $last_name,  $email, 
	$phone_no, $gender, $date_of_birth, $tier_setting_id ,$tier_expiry_date,
	$total_tier_achieved_purchase,  $status = 1) {
		$this->db->query("INSERT INTO `" . DB_PREFIX . "member` 
		SET `external_member_id` = '" . $this->db->escape($external_member_id) . "', 
		`first_name` = '" . $this->db->escape($first_name) . "', 
		`last_name` = '" . $this->db->escape($last_name) . "', 
		`email` = '" . $this->db->escape($email) . "', 
		`phone_no` = '" . $this->db->escape($phone_no) . "', 
		`gender` = '" . $this->db->escape($gender) . "', 
		`date_of_birth` = '" . $this->db->escape($date_of_birth) . "', 
		`tier_setting_id` = '" . (int)$tier_setting_id  . "',
		`tier_expiry_date` = '" . $this->db->escape($tier_expiry_date)  . "',
		`total_available_point` = 0,
		`total_tier_achieved_purchase` = '" . (float)$total_tier_achieved_purchase  . "',
		`created_by` = '" . $this->user->getUserName(). "',
		`created_date` = NOW() , 
		updated_by = '" . $this->user->getUserName() . "',
		updated_date = NOW() , 
		`status` = '" . (int)$status . "'");
		$member_id =  $this->db->getLastId();

		$this->insertMemberLog($member_id);
	}

	public function editMember($member_id, $external_member_id, $first_name, $last_name,  $email, 
	$phone_no, $gender, $date_of_birth, $tier_setting_id, $tier_expiry_date,  
	$total_tier_achieved_purchase, $status = 1) {
		$this->db->query("UPDATE `" . DB_PREFIX . "member` 
		SET `external_member_id` = '" . $this->db->escape($external_member_id) . "', 
		`first_name` = '" . $this->db->escape($first_name) . "', 
		`last_name` = '" . $this->db->escape($last_name) . "', 
		`email` = '" . $this->db->escape($email) . "', 
		`phone_no` = '" . $this->db->escape($phone_no) . "', 
		`gender` = '" . $this->db->escape($gender) . "', 
		`date_of_birth` = '" . $this->db->escape($date_of_birth) . "', 
		`tier_setting_id` = '" . (int)$tier_setting_id  . "',
		`tier_expiry_date` = '" . $this->db->escape($tier_expiry_date)  . "',
		`total_tier_achieved_purchase` = '" . (float)$total_tier_achieved_purchase  . "',
		`status` = '" . (int)$status . "',
		updated_by = '" . $this->user->getUserName() . "',
		updated_date = NOW() 
		WHERE member_id = '" . (int)$member_id . "'");
		$this->insertMemberLog($member_id);
	}

	public function deleteMember($member_id) {
		$this->db->query("DELETE FROM `" . DB_PREFIX . "member` WHERE `member_id` = '" . (int)$member_id . "'");
	}

	public function insertMemberLog($member_id) {
		$this->db->query("INSERT INTO `" . DB_PREFIX . "member_log` (
			external_member_id, first_name, last_name, email, phone_no, gender, date_of_birth,
			tier_setting_id, tier_expiry_date, total_available_point, total_tier_achieved_purchase, status, 
		    created_by, created_date) 
			SELECT external_member_id, first_name, last_name, email, phone_no, gender, date_of_birth,
			tier_setting_id, tier_expiry_date, total_available_point, total_tier_achieved_purchase, status,
		    updated_by, updated_date FROM `" . DB_PREFIX . "member` 
			WHERE member_id = '" . $member_id . "';");
	}
	
	public function getMember($member_id) {
		$query = $this->db->query("SELECT DISTINCT c.*, t.title AS tier_level FROM `" . DB_PREFIX . "member` c INNER JOIN  `" . DB_PREFIX . "tier_setting` t ON c.tier_setting_id = t.tier_setting_id  WHERE c.member_id = '" . (int)$member_id . "' LIMIT 1");
		return $query->row;
	}

	public function getMembers($data = array()) {
		$sql = "SELECT c.*, t.title AS tier_level FROM `" . DB_PREFIX . "member` c INNER JOIN `" . DB_PREFIX . "tier_setting` t ON c.tier_setting_id = t.tier_setting_id ";

		$implode = array();

		if (!empty($data['filter_name'])) {
			$implode[] = "CONCAT(c.first_name, ' ', c.last_name) LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
		}

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

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

		if (isset($data['filter_status']) && $data['filter_status'] !== '') {
			$implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
		}

		if (!empty($data['filter_date_added'])) {
			$implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

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

		$sort_data = array(
			'first_name',
			'last_name',
			'status',
			'created_date'
		);

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

		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 getTotalMembers($data) {
		$sql = "SELECT COUNT(*) AS total FROM `" . DB_PREFIX . "member` c";
		$implode = array();

		if (!empty($data['filter_name'])) {
			$implode[] = "CONCAT(c.first_name, ' ', c.last_name) LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
		}

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

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

		if (isset($data['filter_status']) && $data['filter_status'] !== '') {
			$implode[] = "c.status = '" . (int)$data['filter_status'] . "'";
		}

		if (!empty($data['filter_date_added'])) {
			$implode[] = "DATE(c.date_added) = DATE('" . $this->db->escape($data['filter_date_added']) . "')";
		}

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

		$query = $this->db->query($sql);
		return $query->row['total'];
	}

	public function getMemberByEmail($email){
		$query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "member` WHERE `email` = '" . $this->db->escape($email) . "' LIMIT 1");
		return $query->row;
	}

	public function getMemberByPhoneNo($phone_no){
		$query = $this->db->query("SELECT DISTINCT * FROM `" . DB_PREFIX . "member` WHERE `phone_no` = '" . $this->db->escape($phone_no) . "' LIMIT 1");
		return $query->row;
	}

	private function insertMemberRewardLog($member_reward_id) {
		$this->db->query("INSERT INTO `" . DB_PREFIX . "member_reward_log` (
			member_reward_id, member_id, reward_setting_id, value, used_point, expiry_date, redemption_date,
			is_redeemed, order_no, created_by, created_date) 
			SELECT member_reward_id, member_id, reward_setting_id, value, used_point, expiry_date, redemption_date,
			is_redeemed, order_no, updated_by, updated_date 
			FROM `" . DB_PREFIX . "member_reward` 
			WHERE member_reward_id = '" . $member_reward_id . "';");
	}

	private function updateMemberPoint($member_id,$used_point){
		$this->db->query("UPDATE `" . DB_PREFIX . "member` 
		    SET point = point - " . (int) $used_point . "
			WHERE member_id = '" . $member_id . "';");

		if ($this->db->count_affected() > 0)
		{
			$this->insertMemberLog($member_id);
			return true;
		}
		{
			return false;
		}
	}

	public function getMemberAvailableRewards($member_id){
		$query = $this->db->query("SELECT rs.*, 
		rsp.reward_promotion_setting_id, 
		rsp.point AS promotion_point
			FROM rl_member rl
			INNER JOIN rl_tier_setting ts1 ON rl.tier_setting_id = ts1.tier_setting_id 
			INNER JOIN rl_tier_setting ts2 ON ts1.achieve_tier_amount >= ts2.achieve_tier_amount 
			INNER JOIN rl_reward_setting rs ON rs.min_tier = ts2.tier_setting_id
			LEFT JOIN rl_reward_promotion_setting rsp ON rsp.reward_promotion_setting_id = 
					(SELECT rsp1.reward_promotion_setting_id 
					FROM rl_reward_promotion_setting AS rsp1 
					WHERE rsp1.reward_setting_id = rs.reward_setting_id AND rsp1.Status = 1
						AND DATE(rsp1.start_date) <= DATE(NOW()) AND DATE(rsp1.end_date) >= DATE(NOW()) 
					ORDER BY rsp1.reward_setting_id LIMIT 1) 
			WHERE  rs.status = 1 AND rs.assignment_type_id IN (1, 3) AND (rs.start_date IS NULL OR DATE(rs.start_date) <= DATE(NOW())) 
		AND (rs.end_date IS NULL OR DATE(rs.end_date) >= DATE(NOW())) 
		AND rl.member_id = '" . $member_id . "';");
		return $query->rows;
	}

	public function getSelectedRewardInfo($reward_setting_id)
	{
			$query = $this->db->query("SELECT rs.*, 
			rsp.reward_promotion_setting_id, 
			rsp.point AS promotion_point
				FROM rl_reward_setting rs 
				LEFT JOIN rl_reward_promotion_setting rsp ON rsp.reward_promotion_setting_id = 
						(SELECT rsp1.reward_promotion_setting_id 
						FROM rl_reward_promotion_setting AS rsp1 
						WHERE rsp1.reward_setting_id = rs.reward_setting_id AND rsp1.Status = 1
							AND DATE(rsp1.start_date) <= DATE(NOW()) AND DATE(rsp1.end_date) >= DATE(NOW()) 
						ORDER BY rsp1.reward_setting_id LIMIT 1) 
				WHERE  rs.status = 1 AND rs.assignment_type_id IN (1, 3) AND (rs.start_date IS NULL OR DATE(rs.start_date) <= DATE(NOW())) 
			AND (rs.end_date IS NULL OR DATE(rs.end_date) >= DATE(NOW())) 
			AND rs.reward_setting_id = '" . $reward_setting_id . "';");
			return $query->row;
		}
}