<?php

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

class Report_m extends CI_Model

{

    public function get_total_earned()
    {
        $query = $this->db->query("SELECT (SELECT IFNULL(Sum(amount),0) from refer_earned) as total_refer_earned,(SELECT IFNULL(SUM(amount),0) from refer_work_earned) as total_refer_work_earned,(SELECT IFNULL(SUM(amount),0) FROM work_earned) as total_work_earned,( SELECT IFNULL(SUM(amount),0) FROM other_earned) as total_other_earned, ((SELECT IFNULL(Sum(amount),0) from refer_earned)+(SELECT IFNULL(SUM(amount),0) from refer_work_earned)+(SELECT IFNULL(SUM(amount),0) FROM work_earned)+(SELECT IFNULL(SUM(amount),0) FROM other_earned)) as total_earned,(SELECT IFNULL(SUM(paid_total_amount),0) FROM `withdraw` WHERE status=1) as total_withdrawn");

        $r = $query->result();
        return $r[0];
    }

    public function get_all_members_total_earned()
    {
        $query = $this->db->query("SELECT
                                        a.id,
                                        a.name,
                                        a.picture,
                                        IFNULL(b.total_refer_earned,0) total_refer_earned,
                                        IFNULL(c.total_refer_work_earned,0) total_refer_work_earned,
                                        IFNULL(d.total_work_earned,0) total_work_earned,
                                        IFNULL(e.total_other_earned,0) total_other_earned,
                                        IFNULL(f.total_withdrawn,0) total_withdrawn
                                    FROM
                                        users_detail a
                                    LEFT JOIN(SELECT user_id, SUM(amount) AS total_refer_earned FROM refer_earned GROUP BY user_id) b
                                        ON a.id = b.user_id
                                    LEFT JOIN(SELECT user_id, SUM(amount) as total_refer_work_earned FROM refer_work_earned GROUP BY user_id) c
                                        on a.id = c.user_id
                                    LEFT JOIN(SELECT user_id, SUM(amount) AS total_work_earned FROM work_earned GROUP BY user_id) d
                                        on a.id = d.user_id
                                    LEFT JOIN(SELECT user_id, SUM(amount) AS total_other_earned FROM other_earned GROUP BY user_id) e
                                        on a.id = e.user_id
                                    LEFT JOIN(SELECT user_id,sum(paid_total_amount) as total_withdrawn FROM `withdraw` WHERE status=1 GROUP BY user_id) f
                                        on a.id = f.user_id
                                ");

        $r = $query->result();
        return $r;
    }


    public function get_refer_work_earned()
    {
        $query = $this->db->query("SELECT user_id,sum(amount) total_refer_work_earn FROM `refer_work_earned`  GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function get_refer_earned()
    {
        $query = $this->db->query("SELECT user_id,IFNULL(sum(amount),0) total_refer_earn FROM `refer_earned`  GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function get_work_earned()
    {
        $query = $this->db->query("SELECT user_id,IFNULL(sum(amount),0) total_work_earn FROM `work_earned`  GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function get_other_earned()
    {
        $query = $this->db->query("SELECT user_id,IFNULL(sum(amount),0) total_other_earn FROM `other_earned`  GROUP by user_id");

        $r = $query->result();
        return $r;
    }


    public function get_withdrawn()
    {
        $query = $this->db->query("SELECT user_id, IFNULL(SUM(paid_total_amount),0) as total_wid FROM `withdraw` WHERE status=1 GROUP BY user_id");

        $r = $query->result();
        return $r;
    }

    public function get_total_income()
    {
        $query = $this->db->query("SELECT IFNULL(sum(amount),0) as total_income FROM `activation_reqst` WHERE status=1");

        $r = $query->result();
        return $r[0];
    }

    public function get_all_user()
    {
        $query = $this->db->query("SELECT id,name,picture FROM users_detail where active_status = 1");

        $r = $query->result();
        return $r;
    }


    public function search_total_earned($from, $to)
    {
        $query = $this->db->query("SELECT
                (
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    refer_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) AS total_refer_earned,
            (
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    refer_work_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) AS total_refer_work_earned,
            (
                SELECT
                    IFNULL(SUM(amount),
                    0)
                FROM
                    work_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) AS total_work_earned,
            (
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    other_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) AS total_other_earned,
            (
                (
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    refer_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) +(
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    refer_work_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) +(
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    work_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            ) +(
                SELECT
                    IFNULL(SUM(amount),0)
                FROM
                    other_earned
                WHERE
                    created_at BETWEEN '$from' AND '$to'
            )
            ) AS total_earned,
            (
                SELECT
                    IFNULL(SUM(paid_total_amount),0)
                FROM
                    `withdraw`
                WHERE
            STATUS
                = 1 AND created_at BETWEEN '$from' AND '$to'
            ) AS total_withdrawn");

        $r = $query->result();
        return $r[0];
    }

    public function search_all_members_total_earned($from,$to)
    {
        $query = $this->db->query("SELECT
                                        a.id,
                                        a.name,
                                        a.picture,
                                        IFNULL(b.total_refer_earned,0) total_refer_earned,
                                        IFNULL(c.total_refer_work_earned,0) total_refer_work_earned,
                                        IFNULL(d.total_work_earned,0) total_work_earned,
                                        IFNULL(e.total_other_earned,0) total_other_earned,
                                        IFNULL(f.total_withdrawn,0) total_withdrawn
                                    FROM
                                        users_detail a
                                    LEFT JOIN(SELECT user_id, SUM(amount) AS total_refer_earned FROM refer_earned WHERE created_at BETWEEN '$from' and '$to' GROUP BY user_id) b
                                        ON a.id = b.user_id
                                    LEFT JOIN(SELECT user_id, SUM(amount) as total_refer_work_earned FROM refer_work_earned WHERE created_at BETWEEN '$from' and '$to' GROUP BY user_id) c
                                        on a.id = c.user_id
                                    LEFT JOIN(SELECT user_id, SUM(amount) AS total_work_earned FROM work_earned WHERE created_at BETWEEN '$from' and '$to' GROUP BY user_id) d
                                        on a.id = d.user_id
                                    LEFT JOIN(SELECT user_id, SUM(amount) AS total_other_earned FROM other_earned WHERE created_at BETWEEN '$from' and '$to' GROUP BY user_id) e
                                        on a.id = e.user_id
                                    LEFT JOIN(SELECT user_id,sum(paid_total_amount) as total_withdrawn FROM `withdraw` WHERE status=1 and created_at BETWEEN '$from' and '$to' GROUP BY user_id) f
                                        on a.id = f.user_id
                                ");

        $r = $query->result();
        return $r;
    }

    public function search_refer_work_earned($from, $to)
    {
        $query = $this->db->query("SELECT user_id,sum(amount) total_refer_work_earn FROM `refer_work_earned` WHERE created_at BETWEEN '$from' AND '$to' GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function search_refer_earned($from, $to)
    {
        $query = $this->db->query("SELECT user_id,IFNULL(sum(amount),0) total_refer_earn FROM `refer_earned` WHERE created_at BETWEEN '$from' AND '$to' GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function search_work_earned($from, $to)
    {
        $query = $this->db->query("SELECT user_id,IFNULL(sum(amount),0) total_work_earn FROM `work_earned`  WHERE created_at BETWEEN '$from' AND '$to'  GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function search_other_earned($from, $to)
    {
        $query = $this->db->query("SELECT user_id,IFNULL(sum(amount),0) total_other_earn FROM `other_earned` WHERE created_at BETWEEN '$from' AND '$to'  GROUP by user_id");

        $r = $query->result();
        return $r;
    }

    public function search_withdrawn($from, $to)
    {
        $query = $this->db->query("SELECT user_id, IFNULL(SUM(paid_total_amount),0) as total_wid FROM `withdraw` WHERE status=1 and created_at BETWEEN '$from' AND '$to' GROUP BY user_id");

        $r = $query->result();
        return $r;
    }

    public function search_total_income($from, $to)
    {
        $query = $this->db->query("SELECT IFNULL(sum(amount),0) as total_income FROM `activation_reqst` WHERE status=1 and update_date BETWEEN '$from' AND '$to'");

        $r = $query->result();
        return $r[0];
    }


    public function get_all_refer_bonus_earned($user_id)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,b.id as f_id,b.name as f_name,b.picture as f_picture ,c.id as t_id,c.amount,c.created_at FROM users_detail a,users_detail b, refer_earned c WHERE a.id=c.user_id AND b.id=c.from_user_id AND c.user_id = '$user_id' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }

    public function get_all_refer_work_earned($user_id)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,b.id as f_id,b.name as f_name,b.picture as f_picture ,c.id as t_id,c.amount,c.created_at FROM users_detail a,users_detail b, refer_work_earned c WHERE a.id=c.user_id AND b.id=c.from_user_id AND c.user_id = '$user_id' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
    public function get_all_work_earned($user_id)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,c.id as t_id,c.amount,c.created_at FROM users_detail a, work_earned c WHERE a.id=c.user_id AND c.user_id ='$user_id' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
    public function get_all_other_earned($user_id)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,c.id as t_id,c.amount,c.created_at FROM users_detail a, other_earned c WHERE a.id=c.user_id AND c.user_id ='$user_id' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
    public function get_all_withdraw($user_id)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,c.id as t_id,c.paid_total_amount as amount,c.created_at FROM users_detail a, withdraw c WHERE a.id=c.user_id AND c.user_id = '$user_id' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }

    public function search_all_refer_bonus_earned($user_id,$from,$to)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,b.id as f_id,b.name as f_name,b.picture as f_picture ,c.id as t_id,c.amount,c.created_at FROM users_detail a,users_detail b, refer_earned c WHERE a.id=c.user_id AND b.id=c.from_user_id AND c.user_id = '$user_id' AND created_at BETWEEN '$from' and '$to' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }

    public function search_all_refer_work_earned($user_id,$from,$to)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,b.id as f_id,b.name as f_name,b.picture as f_picture ,c.id as t_id,c.amount,c.created_at FROM users_detail a,users_detail b, refer_work_earned c WHERE a.id=c.user_id AND b.id=c.from_user_id AND c.user_id = '$user_id' AND created_at BETWEEN '$from' and '$to' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
    public function search_all_work_earned($user_id,$from,$to)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,c.id as t_id,c.amount,c.created_at FROM users_detail a, work_earned c WHERE a.id=c.user_id AND c.user_id ='$user_id' AND created_at BETWEEN '$from' and '$to' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
    public function search_all_other_earned($user_id,$from,$to)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,c.id as t_id,c.amount,c.created_at FROM users_detail a, other_earned c WHERE a.id=c.user_id AND c.user_id ='$user_id' AND created_at BETWEEN '$from' and '$to' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
    public function search_all_withdraw($user_id,$from,$to)
    {
        $query = $this->db->query("SELECT a.id,a.name,a.picture,c.id as t_id,c.paid_total_amount as amount,c.created_at FROM users_detail a, withdraw c WHERE a.id=c.user_id AND c.user_id = '$user_id' AND created_at BETWEEN '$from' and '$to' ORDER by c.created_at DESC");

        $r = $query->result();
        return $r;
    }
}