<?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 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, b.name,b.refer_from,c.join_fee, 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.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 id FROM `activation_reqst` WHERE user_id = '$user_id' AND status=1");

        $r= $query->result();

        if($r=null){
            $this->db->query("UPDATE `activation_reqst` SET `amount`=(SELECT join_fee FROM `join_fee_income`),`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 `amount`=(SELECT join_fee FROM `join_fee_income`),`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)
    {

        return $this->db->query("INSERT INTO `total_balance`(`user_id`) VALUES ('$user_id')");  

    }



    public function update_referer_refer_earn_balance($referer_refer_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`),`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)
    {

        $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`) 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 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)
    {

        $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`) 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 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)
    {

        $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`) 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 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)
    {

        $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`) 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 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 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.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 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;
    }

}