<?php
if ( ! defined('BASEPATH')) exit('No direct script access allowed');


class Member_m extends CI_Model

{



	public function get_activation_his()
	{

		$query = $this->db->query("SELECT d.mobile_num,a.id, a.user_id, a.sender_num, a.transection_id, b.name,b.refer_from,a.amount as join_fee, a.reqst_date,a.update_date,CASE WHEN a.status=1 THEN 'Activated' WHEN a.status=2 then 'Pending' WHEN a.status=0 then 'Rejected' end as status FROM activation_reqst a, users_detail b,join_fee_income c,users_login d WHERE a.user_id = b.id AND a.user_id= d.user_id AND a.join_fee_income_id = c.id ORDER BY a.update_date DESC");

		return $query->result();

	}

	public function get_req_mem_for_activation()
	{
		$query = $this->db->query("SELECT d.mobile_num, a.id, a.user_id, a.sender_num, a.transection_id,a.join_fee_income_id,a.amount as fee_amount, b.name, b.refer_from, c.join_fee, c.method_name, c.mobile as paid_at, a.reqst_date FROM activation_reqst a, users_detail b,join_fee_income c,users_login d WHERE a.user_id = b.id AND a.user_id= d.user_id and a.join_fee_income_id = c.id and a.status=2");
		return $query->result();
	}



	public function setUserActive_and_updateActiveRqst($user_id, $id)

	{

		$this->db->query("UPDATE `users_detail` SET `active_status`= 1 ,`acc_active_date`= CURRENT_TIMESTAMP WHERE id='$user_id'");


		$query = $this->db->query("SELECT * FROM `activation_reqst` WHERE user_id = '$user_id' AND status=1");

		$r= $query->result();

		//if($r=null){
		if($r==null){
			$this->db->query("UPDATE `activation_reqst` SET `status`= 1 ,update_date= CURRENT_TIMESTAMP WHERE id ='$id'");
		}
		else{
			$this->db->query("DELETE from activation_reqst where user_id='$user_id' and status=1");
			$this->db->query("UPDATE `activation_reqst` SET `status`= 1 ,update_date= CURRENT_TIMESTAMP WHERE id ='$id'");
		}

	}



	public function set_refer_user_instant_refer_earn($refer_user_id, $user_id)
	{
		$query = $this->db->query("INSERT INTO `refer_earned`(`user_id`,`from_user_id`, `amount`) SELECT a.id,d.id, round(b.join_fee*b.referer_earn_percentage/100) as percent from users_detail a , join_fee_income b,users_detail d WHERE a.own_refer_id = '$refer_user_id' AND d.id = '$user_id' limit 1");
	}



	public function set_refer_user_always_earn($user_id, $refer_user_id)

	{

		return $query = $this->db->query("INSERT INTO `refer_network`(`user_id`, `referer_id`, `referer_reputation_lvl`, `referer_milestone_lvl`, `referer_total_refered_mem`, `referer_percentage`) SELECT (SELECT id FROM users_detail WHERE id = '$user_id') as user_id , (SELECT id FROM users_detail WHERE own_refer_id = '$refer_user_id') as referer_id, reputation_lvl,milestone_lvl,(SELECT COUNT(id) from users_detail WHERE refer_from = '$refer_user_id' and active_status=1) as total_refer,percentage FROM refer_earn_plan_distribution WHERE total_refer_member >= (SELECT COUNT(id) from users_detail WHERE refer_from = '$refer_user_id' and active_status=1) ORDER BY id ASC LIMIT 1");

	}



	public function set_balance($user_id)
	{
		$query = $this->db->query("SELECT * FROM `total_balance` WHERE user_id='$user_id'");
		$r= $query->result();
		if($r==null){
			return $this->db->query("INSERT INTO `total_balance`(`user_id`) VALUES ('$user_id')");
		}
		return true;
	}



	public function update_referer_refer_earn_balance($referer_refer_id,$refer_income_id)
	{

		return $this->db->query("UPDATE `total_balance` SET `refer_earn_amount`=`refer_earn_amount`+ (SELECT round(join_fee*referer_earn_percentage/100) as amnt FROM `join_fee_income` where id='$refer_income_id'),`updated_at`=CURRENT_TIMESTAMP WHERE user_id=(SELECT `id` FROM `users_detail` WHERE `own_refer_id`='$referer_refer_id')");

	}

	public function second_lvl_referer_percentage($referer_id,$id)
	{

		$this->db->query("INSERT INTO `refer_earned`(`user_id`, `from_user_id`, `amount`)  SELECT a.referer_id,b.id,(SELECT round(join_fee*2nd_lvl_percentage/100) as amnt FROM `join_fee_income` where id='$id') as amount from refer_network a, users_detail b WHERE a.user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id') AND b.own_refer_id = '$referer_id'");
		$this->db->query("UPDATE `total_balance` SET `refer_earn_amount`= `refer_earn_amount` + (SELECT round(join_fee*2nd_lvl_percentage/100) as amnt FROM `join_fee_income` where id='$id') WHERE user_id = (SELECT referer_id from refer_network  WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id'))");

	}

	public function third_lvl_referer_percentage($referer_id,$id)
	{

		$this->db->query("INSERT INTO `refer_earned`(`user_id`, `from_user_id`, `amount`)  SELECT a.referer_id,b.id,(SELECT round(join_fee*3rd_lvl_percentage/100) as amnt FROM `join_fee_income` where id='$id') as amount from refer_network a, users_detail b WHERE a.user_id = (SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id')) AND b.id =(SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id'))");
		$this->db->query("UPDATE `total_balance` SET `refer_earn_amount`= `refer_earn_amount` + (SELECT round(join_fee*3rd_lvl_percentage/100) as amnt FROM `join_fee_income` where id='$id') WHERE user_id = (SELECT referer_id from refer_network WHERE user_id=(SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id')))");

	}

	public function forth_lvl_referer_percentage($referer_id,$id)
	{

		$this->db->query("INSERT INTO `refer_earned`(`user_id`, `from_user_id`, `amount`)  SELECT a.referer_id,b.id,(SELECT round(join_fee*4th_lvl_percentage/100) as amnt FROM `join_fee_income`  where id='$id') as amount from refer_network a, users_detail b WHERE a.user_id = (SELECT referer_id from refer_network WHERE user_id=(SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id'))) AND b.id = (SELECT referer_id from refer_network WHERE user_id=(SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id')))");
		$this->db->query("UPDATE `total_balance` SET `refer_earn_amount`= `refer_earn_amount` + (SELECT round(join_fee*4th_lvl_percentage/100) as amnt FROM `join_fee_income`  where id='$id') WHERE user_id = (SELECT referer_id from refer_network WHERE user_id=(SELECT referer_id from refer_network WHERE user_id=(SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id'))))");

	}

	public function get_fifth_lvl_referer_id($referer_id)
	{

		$q = $this->db->query("SELECT referer_id from refer_network WHERE user_id =(SELECT referer_id from refer_network WHERE user_id = (SELECT referer_id from refer_network WHERE user_id=(SELECT referer_id from refer_network WHERE user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_id'))))");
		$r = $q->result();
		if (!$r == null) {
			return $r[0]->referer_id;
		} else {
			return null;
		}

	}

	public function fifth_lvl_referer_percentage($id,$referer_refer_id)
	{

		$this->db->query("INSERT INTO `refer_earned`(`user_id`, `from_user_id`, `amount`)  SELECT a.referer_id,b.id,(SELECT round(join_fee*5th_lvl_percentage/100) as amnt FROM `join_fee_income` where id='$referer_refer_id') as amount from refer_network a, users_detail b WHERE a.user_id = '$id' and b.id = '$id'");
		$this->db->query("UPDATE `total_balance` SET `refer_earn_amount`= `refer_earn_amount` + (SELECT round(join_fee*5th_lvl_percentage/100) as amnt FROM `join_fee_income`  where id='$referer_refer_id') WHERE user_id = '$id'");

	}



	public function delete_reqst_and_reset_status($user_id, $id)
	{
		$this->db->db_debug = false;

		$this->db->query("UPDATE `users_detail` SET `active_status`= 0 WHERE id='$user_id'");

		$this->db->query("UPDATE `activation_reqst` SET `status`= 0,update_date= CURRENT_TIMESTAMP WHERE id='$id'");

	}



	public function set_user_lvl($user_id)
	{
		return $this->db->query("INSERT INTO `user_lvl`(`user_id`,`reputation_lvl`,`milestone_lvl`,`total_refered`,`earn_percentage`) VALUES('$user_id',(SELECT reputation_lvl from refer_earn_plan_distribution WHERE id= 1),(SELECT milestone_lvl from refer_earn_plan_distribution WHERE id= 1),'0',(SELECT percentage from refer_earn_plan_distribution WHERE id= 1))");

	}



	public function update_referer_lvl($referer_refer_id)
	{

		$this->db->query("UPDATE

        `user_lvl`

    SET

        `reputation_lvl` = (SELECT reputation_lvl FROM refer_earn_plan_distribution WHERE total_refer_member >= (SELECT COUNT(id) FROM users_detail WHERE refer_from='$referer_refer_id' and active_status=1) ORDER BY id ASC limit 1),

        `milestone_lvl` =  (SELECT milestone_lvl FROM refer_earn_plan_distribution WHERE total_refer_member >= (SELECT COUNT(id) FROM users_detail WHERE refer_from='$referer_refer_id' and active_status=1) ORDER BY id ASC limit 1),

        `total_refered` = (SELECT COUNT(id) FROM users_detail WHERE refer_from='$referer_refer_id' and active_status=1),

        `earn_percentage` = (SELECT percentage FROM refer_earn_plan_distribution WHERE total_refer_member >= (SELECT COUNT(id) FROM users_detail WHERE refer_from='$referer_refer_id' and active_status=1) ORDER BY id ASC limit 1)

    WHERE

        user_id = (SELECT id FROM users_detail WHERE own_refer_id='$referer_refer_id')");

	}

	public function get_referer_total_refered($referer_refer_id)
	{
		$query = $this->db->query("SELECT COUNT(id) as total_refer FROM users_detail WHERE refer_from = '$referer_refer_id'");

		return $query->result();
	}

	public function get_pro_levels()
	{
		$query = $this->db->query("SELECT total_refer_member, percentage FROM refer_earn_plan_distribution WHERE reputation_lvl > 4");

		return $query->result();
	}

	public function set_referer_to_pro_level($referer_refer_id, $percentage)
	{
		$query = $this->db->query("UPDATE `refer_network` SET `referer_percentage`= '$percentage' WHERE referer_id = (SELECT id FROM users_detail WHERE own_refer_id = '$referer_refer_id' )");
	}

	public function get_all_members()
	{
		$query = $this->db->query("SELECT a.id, a.name, a.picture, (Case When a.active_status = 0 THEN 'Inactive' WHEN a.active_status = 1 THEN 'Active' WHEN a.active_status = 2 THEN 'Pending' WHEN a.active_status = 2 THEN 'Pending' WHEN a.active_status = 3 THEN 'Blocked' END) as status ,b.mobile_num,c.total_refered FROM  users_detail a, users_login b , user_lvl c WHERE a.id = b.user_id AND a.id = c.user_id");

		return $query->result();
	}

	public function get_active_members()
	{
		$query = $this->db->query("SELECT a.id, a.name, a.picture, (Case When a.active_status = 0 THEN 'Inactive' WHEN a.active_status = 1 THEN 'Active' WHEN a.active_status = 2 THEN 'Pending' END) as status ,b.mobile_num FROM  users_detail a, users_login b WHERE a.id = b.user_id AND a.active_status = 1");

		return $query->result();
	}

	public function get_inactive_members()
	{
		$query = $this->db->query("SELECT a.id, a.name, a.picture, (Case When a.active_status = 0 THEN 'Inactive' WHEN a.active_status = 1 THEN 'Active' WHEN a.active_status = 2 THEN 'Pending' END) as status ,b.mobile_num FROM  users_detail a, users_login b WHERE a.id = b.user_id AND a.active_status = 0");

		return $query->result();
	}

	public function get_blocked_members()
	{
		$query = $this->db->query("SELECT a.id, a.name, a.picture, (Case When a.active_status = 0 THEN 'Inactive' WHEN a.active_status = 1 THEN 'Active' WHEN a.active_status = 2 THEN 'Pending' WHEN a.active_status = 3 THEN 'Blocked' END) as status ,b.mobile_num FROM  users_detail a, users_login b WHERE a.id = b.user_id AND a.active_status = 3");

		return $query->result();
	}

	public function get_withdrawable_mem()
	{
		$query = $this->db->query("SELECT a.user_id,b.name,b.picture,a.refer_earn_amount,a.work_earn_amount,a.refer_work_earn_amount,a.other_earn_amount,a.refer_earn_amount+a.work_earn_amount+a.refer_work_earn_amount+a.other_earn_amount as total FROM `total_balance` a,users_detail b WHERE a.user_id=b.id HAVING total >500 ORDER BY `total` DESC");

		return $query->result();
	}

	public function get_withdrawable_mem_job()
	{
		$query = $this->db->query("SELECT a.user_id,b.name,b.picture,a.refer_earn_amount,a.work_earn_amount,a.refer_work_earn_amount,a.other_earn_amount,a.refer_earn_amount+a.work_earn_amount+a.refer_work_earn_amount+a.other_earn_amount as total FROM `total_balance` a,users_detail b WHERE a.user_id=b.id HAVING work_earn_amount >1000 ORDER BY `work_earn_amount` DESC");
		return $query->result();
	}

	public function get_withdrawable_mem_refer()
	{
		$query = $this->db->query("SELECT a.user_id,b.name,b.picture,a.refer_earn_amount,a.work_earn_amount,a.refer_work_earn_amount,a.other_earn_amount,a.refer_earn_amount+a.work_earn_amount+a.refer_work_earn_amount+a.other_earn_amount as total FROM `total_balance` a,users_detail b WHERE a.user_id=b.id HAVING refer_earn_amount+refer_work_earn_amount+other_earn_amount >1000 ORDER BY `refer_earn_amount` DESC");
		return $query->result();
	}

	public function get_withdrawable_tot_amount()
	{
		$query = $this->db->query("SELECT a.user_id,b.name,b.picture,a.refer_earn_amount,a.work_earn_amount,a.refer_work_earn_amount,a.other_earn_amount,sum(a.refer_earn_amount+a.work_earn_amount+a.refer_work_earn_amount+a.other_earn_amount) total FROM `total_balance` a,users_detail b WHERE a.user_id=b.id GROUP BY user_id HAVING total >500");

		return $query->result();
	}


	public function update_referer_last_refer_date($referer_refer_id)
	{
		$query = $this->db->query("UPDATE `users_login` SET `last_refer_date`= CURRENT_TIMESTAMP WHERE user_id=(SELECT id FROM users_detail WHERE own_refer_id='$referer_refer_id')");

	}


	public function block($user_id)
	{
		$query = $this->db->query("UPDATE `users_detail` SET  `active_status`= 3 WHERE id ='$user_id'");

	}
	public function unblock($user_id)
	{
		$q = $this->db->query("SELECT acc_active_date from `users_detail` WHERE id ='$user_id'");
		$r = $q->result();
		if($r[0]->acc_active_date==null){
			$this->db->query("UPDATE `users_detail` SET  `active_status`= 0 WHERE id ='$user_id'");
		}else{
			$this->db->query("UPDATE `users_detail` SET  `active_status`= 1 WHERE id ='$user_id'");
		}

	}


	public function set_bonus($user_id,$amnt)
	{

		$this->db->query("UPDATE `total_balance` SET `other_earn_amount`=`other_earn_amount`+ '$amnt' WHERE user_id = '$user_id'");
		$this->db->query("INSERT INTO `other_earned`(`user_id`, `amount`) VALUES ('$user_id','$amnt')");

	}

	public function set_cashback($user_id, $amnt, $reducet_type)
	{
		$reduce = $reducet_type == 'add' ? '+' : '-';
		$this->db->query("UPDATE `total_balance` SET `cashblnc`=`cashblnc` $reduce '$amnt' WHERE user_id = '$user_id'");
		$this->db->query("INSERT INTO `wallet_log`(`user_id`, `type`,`amount`) VALUES ('$user_id','$reducet_type','$amnt')");
	}

	public function get_pending_members()
	{
		$query = $this->db->query("SELECT a.id, a.name, a.picture, (Case When a.active_status = 0 THEN 'Inactive' WHEN a.active_status = 1 THEN 'Active' WHEN a.active_status = 2 THEN 'Pending' END) as status ,b.mobile_num FROM  users_detail a, users_login b WHERE a.id = b.user_id AND a.active_status = 2");

		return $query->result();
	}


	public function get_refer_network()
	{
		$query = $this->db->query("SELECT a.name,b.name as r_name,a.id,b.id as r_id,a.picture,b.picture as r_picture,c.referer_percentage from users_detail a,users_detail b,refer_network c WHERE a.id=c.user_id and b.id=c.referer_id");

		return $query->result();
	}

	public function remove_member($user_id)
	{
		$this->db->query("DELETE FROM `users_detail` WHERE id = '$user_id'");
		$this->db->query("DELETE FROM `users_login` WHERE `user_id` = '$user_id'");
		$this->db->query("DELETE FROM `activation_reqst` WHERE `user_id`= '$user_id'");

	}

	public function update_pass($user_id, $pass)
	{
		$query = $this->db->query("UPDATE `users_login` SET`password`='$pass' WHERE `user_id` ='$user_id'");
	}

	public function get_user_mobile($user_id)
	{
		$query = $this->db->query("SELECT a.`mobile_num`,b.name,b.activation_pin FROM `users_login` a, users_detail b WHERE b.id=a.user_id AND a.user_id='$user_id'");
		$r = $query->result();
		return $r[0];
	}


	public function update_profile_no_pic($name, $gender,$mail, $adress, $nid,  $user_id)
	{

		$qury = $this->db->query("UPDATE `users_detail` SET `name`='$name' ,`gender`='$gender' ,`adress`='$adress' ,`national_id`='$nid' WHERE id = '$user_id'");
		$qury = $this->db->query("UPDATE `users_login` SET `email`= '$mail' WHERE user_id='$user_id'");

	}
	public function update_profile_with_pic($name, $gender,$mail, $adress, $nid, $picture, $user_id)
	{

		$qury = $this->db->query("UPDATE `users_detail` SET `name`='$name' ,`gender`='$gender' ,`adress`='$adress' ,`national_id`='$nid' ,`picture`='$picture' WHERE id = '$user_id'");
		$qury = $this->db->query("UPDATE `users_login` SET `email`= '$mail' WHERE user_id='$user_id'");

	}

	public function get_refered_mem($user_id)
	{
		$query = $this->db->query("SELECT id,name,picture,(CASE WHEN active_status=0 THEN 'Inactive' WHEN active_status=1 then 'Active' WHEN active_status=2 THEN 'Pending' WHEN active_status=3 THEN 'Blocked' End) as status FROM `users_detail` WHERE refer_from = (SELECT own_refer_id FROM users_detail WHERE id ='$user_id')");

		return $query->result();
	}

	public function get_user_all_data($user_id)
	{
		$query = $this->db->query("SELECT
        a.id,
        a.name,
        a.gender,
        a.adress,
        a.activation_pin,
        a.national_id,
        a.picture,
        a.active_status,
        a.own_refer_id,
        a.acc_active_date,
        a.join_date,
        b.mobile_num,
        b.email,
        c.reputation_lvl,
        c.milestone_lvl,
        c.total_refered,
        d.total_refer_member,
        d.percentage,
        d.reputation_milestone_name,
        d.reputation_icon,
        d.milestone_icon,
        d.reputation_milestone_icon,
        e.refer_earn_amount,
        e.work_earn_amount,
        e.refer_work_earn_amount,
        e.other_earn_amount,
		e.marketing_earn,
		e.product_sell,
        (e.marketing_earn+e.product_sell+e.refer_earn_amount+e.work_earn_amount+e.refer_work_earn_amount+e.other_earn_amount) as total_bal
        
        FROM
        
        users_detail a,
        users_login b,
        user_lvl c,
        refer_earn_plan_distribution d,
        total_balance e
        
        WHERE 
        a.id = '$user_id' AND
        b.user_id = '$user_id' AND
        c.user_id = '$user_id' AND
        d.reputation_lvl = (SELECT reputation_lvl FROM user_lvl WHERE user_id = '$user_id') AND
        d.milestone_lvl = (SELECT milestone_lvl FROM user_lvl WHERE user_id = '$user_id') AND
        e.user_id = '$user_id'
    ");

		$rs = $query->result();

		if ($rs == null) {
			$query = $this->db->query("SELECT
            a.id,
            a.name,
            a.gender,
            a.adress,
            a.activation_pin,
            a.national_id,
            a.picture,
            a.active_status,
            a.own_refer_id,
            a.acc_active_date,
            a.join_date,
            b.mobile_num,
            b.email,
            (1) as reputation_lvl,
            (1) as milestone_lvl,
            (0) as total_refered,
            (0) as total_refer_memeber,
            (3) as percentage,
            ('Bronze Basic') as reputation_milestone_name,
            ('bronze.svg') as reputation_icon,
            ('1.svg') as milestone_icon,
            ('bronze_1.svg') as reputation_milestone_icon,
			(0) as marketing_earn,
			(0) as product_sell,
            (0) as refer_earn_amount,
            (0) as work_earn_amount,
            (0) as refer_work_earn_amount,
            (0) as other_earn_amount,
            (0) as total_bal
            FROM 
            
            users_detail a,
            users_login b
            
            WHERE 
            a.id = '$user_id' AND
            b.user_id = '$user_id'");
			$rs = $query->result();
		}


		return $rs;
	}

}
