<?php
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Collabolator_model extends MY_Model {

    protected $table_name = 'tblcollaborator';
    protected $id_name = 'id';

    function __construct() {
        parent::__construct();
    }

    function getCollaborators($data) {
        $sql = "SELECT * FROM " . $this->table_name;
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%')";
            } else {
                $sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%') ";
            }
        }

        $order = "";
        if ($data['sorting_order'] != "sorting") {
            
                $sort = "DESC";
                if ($data['sorting_order'] == "sorting_asc")
                    $sort = "ASC";
                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
            
        }
       
        return $this->db->query($sql . " " . $order . " LIMIT " . $data['start'] . ", " . $data['perpage'] . " ")->result_array();
    }
    function getAllCollaboratorUser($from,$id=false)
    {
        $from = date("Y-m-d 00:00:00",$from); 
        $where="";
        if ($id)
        {
            $where = " AND tbluser.collaborator = $id ";
        }
        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id WHERE tbluser.created_time > '".$from."'".$where."";

        $result = $this->db->query($sql)->result_array();
        foreach ($result as $index=>$paidlog)
        {
            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
        }
        return $result;
    }
    function countCountCollaboratorUser($data)
    {
        $order = "";
        if ($data['sorting_order'] != "sorting") {
            
                $sort = "DESC";
                if ($data['sorting_order'] == "sorting_asc")
                    $sort = "ASC";
                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
            
        }
        $where="";
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $where.=" WHERE (tblcollaborator.cellphone LIKE '%" . $data['keyword'] . "%')";
            } else {
                $where.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%') ";
            }
        }
       // $from = date("Y-m-d 00:00:00",$from); 
        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time, COUNT(tbluser.collaborator) as total_user  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." GROUP BY tblcollaborator.id ".$order." ";
        
        $result = $this->db->query($sql)->result_array();
        

        return count($result);
    }
    function getCountCollaboratorUser($data)
    {
        $order = "";
        if ($data['sorting_order'] != "sorting") {
            
                $sort = "DESC";
                if ($data['sorting_order'] == "sorting_asc")
                    $sort = "ASC";
                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
            
        }
        $where="";
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $where.=" WHERE (tblcollaborator.cellphone LIKE '%" . $data['keyword'] . "%')";
            } else {
                $where.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%') ";
            }
        }
       // $from = date("Y-m-d 00:00:00",$from); 
        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time, COUNT(tbluser.collaborator) as total_user  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." GROUP BY tblcollaborator.id ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
        
        $result = $this->db->query($sql)->result_array();
        foreach ($result as $index=>$paidlog)
        {
            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
        }

        return $result;
    }
    
        function countCountCollaboratorUsers($data)
    {
        $order = "";
        if ($data['sorting_order'] != "sorting") {
            
                $sort = "DESC";
                if ($data['sorting_order'] == "sorting_asc")
                    $sort = "ASC";
                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
            
        }
        $where="";
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $where.=" WHERE (tbluser.cellphone LIKE '%" . $data['keyword'] . "%')";
            }
        }
       // $from = date("Y-m-d 00:00:00",$from); 
        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tbluser.cellphone as user_cellphone,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." AND tbluser.collaborator = ".$data['id']." ".$order."";
   
        $result = $this->db->query($sql)->result_array();
        foreach ($result as $index=>$paidlog)
        {
            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
        }

        return count($result);
    }
        function getCountCollaboratorUsers($data)
    {
        $order = "";
        if ($data['sorting_order'] != "sorting") {
            
                $sort = "DESC";
                if ($data['sorting_order'] == "sorting_asc")
                    $sort = "ASC";
                $order = "ORDER BY " . $data['sorting_field'] . " " . $sort;
            
        }
        $where="";
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $where.=" WHERE (tbluser.cellphone LIKE '%" . $data['keyword'] . "%')";
            } 
        }
       // $from = date("Y-m-d 00:00:00",$from); 
        $sql="SELECT *,tblcollaborator.created_time as collaborator_created_time,tbluser.cellphone as user_cellphone,tblcollaborator.cellphone as collaborator_cellphone, tbluser.created_time as user_created_time  FROM ".$this->table_name." INNER JOIN tbluser ON tbluser.collaborator = tblcollaborator.id ".$where." AND tbluser.collaborator = ".$data['id']." ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
        $result = $this->db->query($sql)->result_array();
        foreach ($result as $index=>$paidlog)
        {
            $result[$index]['time']=  strtotime($paidlog['user_created_time']);
        }

        return $result;
    }
    
    function isExist($data) {
        $sql = "SELECT COUNT(id) as total FROM " . $this->table_name . " WHERE " . $data['field'] . " = '" . $data['value'] . "'";
        if (isset($data['id']))
        {
            $id=$data['id'];
            $sql.=" AND id <> ".$id." LIMIT 1";
        }
        $result = $this->db->query($sql)->row_array();
        if ($result['total'] == 1) {
            return true;
        } else {
            return false;
        }
    }

    function countCollaborator($data) {
        $sql = "SELECT COUNT(id) as total FROM " . $this->table_name;
        if ($data['keyword']) {
            if ($data['search_field'] == "cellphone") {
                $sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%')";
            } else {
                $sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%') ";
            }
        }
        $result = $this->db->query($sql)->row_array();
        return $result['total']; 
    }
    function getCollaborator($id)
    {
        $sql="SELECT * FROM ".$this->table_name." WHERE id=".$id." LIMIT 1";
        $result = $this->db->query($sql)->row_array();
        return $result;
    }

}