<?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") {
				if(strlen($data['daterange_start'])>0){
					$sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
				}else{
					$sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
				}
            } else {
				if(strlen($data['daterange_start'])>0){
					$sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
				}else{
					$sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
				}
            }
        }else{
			if(strlen($data['daterange_start'])>0){
				$sql.=" WHERE created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
			}
		}

        $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."";
		$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.cellphone 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'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
            } else {
                $where.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province 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." ";

		$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.cellphone ".$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'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
				
            } else {
                $where.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province 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'] . " ";
        
		$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.cellphone ".$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'] . "%' OR district LIKE '%".$data['keyword']."%' OR province 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."";
		if(strlen($data['daterange_start'])>0){
			$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.cellphone ".$where." AND (tbluser.created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."') AND tblcollaborator.id = ".$data['id']." ".$order."";
		}else{
			$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.cellphone ".$where." AND tblcollaborator.id = ".$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'] . "%' OR tblcollaborator.district LIKE '%".$data['keyword']."%' OR tblcollaborator.province 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'] . " ";
		if(strlen($data['daterange_start'])>0){
			$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.cellphone ".$where." AND (tbluser.created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."') AND tblcollaborator.id = ".$data['id']." ".$order." LIMIT " . $data['start'] . ", " . $data['perpage'] . " ";
		}else{
			$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.cellphone ".$where." AND tblcollaborator.id = ".$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") {
				if(strlen($data['daterange_start'])>0){
					$sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
				}else{
					$sql.=" WHERE (cellphone LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%')";
				}
            } else {
				if(strlen($data['daterange_start'])>0){
					$sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') AND (created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."')";
				}else{
					$sql.=" WHERE (full_name LIKE '%" . $data['keyword'] . "%' OR login_name LIKE '%" . $data['keyword'] . "%' OR district LIKE '%".$data['keyword']."%' OR province LIKE '%".$data['keyword']."%') ";
				}
            }
        }else{
			if(strlen($data['daterange_start'])>0){
				$sql.=" WHERE created_time BETWEEN '".$data['daterange_start']."' AND '".$data['daterange_end']."'";
			}
		}
        $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;
    }
    function getStatistics()
    {
      
       $day = date('w');
        $data = array();
        $last_month['start'] = strtotime(date("Y-m-d", strtotime(date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y"))))) . " 00:00:00");
        $last_month['end'] = strtotime(date("Y-m-d", strtotime(date("Y-m-d", mktime(0, 0, 0, date("m"), 0, date("Y"))))) . " 23:59:59");

        $last_week['start'] = strtotime(date("Y-m-d", strtotime(date('m-d-Y', strtotime('-'.$day.' days')))) . " 00:00:00");
        $last_week['end'] = strtotime(date("Y-m-d", strtotime(date('m-d-Y', strtotime('+'.(6-$day).' days')))) . " 23:59:59");

        $last_week['end'] = strtotime('last sunday') + (24 * 60 * 60 - 1);
        $last_week['start'] = ($last_week['end'] - 7 * 24 * 60 * 60 + 1);

        $last_year['start'] = strtotime((date("Y") - 1) . "-01-01 00:00:00");
        $last_year['end'] = strtotime((date("Y") - 1) . "-12-31 23:59:59");



        $last_quarter = $this->getLastquarter();
        $last_quarter['start'] = $last_quarter['start'];
        $last_quarter['end'] = $last_quarter['end'];


        $this->load->model('paidlog_model');
        $paidlogs = $this->paidlog_model->getAllPaidlogs($last_year['start']);

        $revenue['last_week'] = 0;
        $revenue['last_month'] = 0;
        $revenue['last_quarter'] = 0;
        $revenue['last_year'] = 0;

        $lastmonth = strtotime(date("Y-m-d", mktime(0, 0, 0, date("m")-1, 1, date("Y"))));
        $thismonth = strtotime(date('Y-m-01'));
        $charts['last_month'] = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0);
        $charts['this_month'] = array(0 => 0, 1 => 0, 2 => 0, 3 => 0, 4 => 0);
        
        $top['last_month']['user']=0;
        $top['last_month']['sms']=0;
        $top['last_month']['card']=0;
        $top['last_month']['collaborator']=0;
        $top['this_month']['user']=0;
        $top['this_month']['sms']=0;
        $top['this_month']['card']=0;
        $top['this_month']['collaborator']=0;
        
        foreach ($paidlogs as $paidlog) {

            
            if (($paidlog['time'] < $last_week['end']) && ($paidlog['time'] > $last_week['start'])) {
                
                $revenue['last_week']+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $last_month['end']) && ($paidlog['time'] > $last_month['start'])) {
           
                $revenue['last_month']+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $last_quarter['end']) && ($paidlog['time'] > $last_quarter['start'])) {
             
                $revenue['last_quarter']+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $last_year['end']) && ($paidlog['time'] > $last_year['start'])) {
              
                $revenue['last_year']+=$paidlog['amount'];
            }
           
            
            if (($paidlog['time'] < $lastmonth + (7 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth)) {
                $charts['last_month'][0]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $lastmonth + (14 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (7 * 24 * 60 * 60))) {
                $charts['last_month'][1]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $lastmonth + (21 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (14 * 24 * 60 * 60))) {
                $charts['last_month'][2]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $lastmonth + (28 * 24 * 60 * 60)) && ($paidlog['time'] > $lastmonth + (21 * 24 * 60 * 60))) {
                $charts['last_month'][3]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $thismonth) && ($paidlog['time'] > $lastmonth + (28 * 24 * 60 * 60))) {
                $charts['last_month'][4]+=$paidlog['amount'];
            }
            
            if (($paidlog['time'] < $thismonth) && ($paidlog['time'] > $lastmonth)) {
                $top['last_month']['user']++;
                if ($paidlog['paid_type']==1)
                {
                   $top['last_month']['sms']+=$paidlog['amount']; 
                }
                if ($paidlog['paid_type']==2)
                {
                    $top['last_month']['card']+=$paidlog['amount'];
                }
                if ($paidlog['collaborator']!="")
                {
                    $top['last_month']['collaborator']+=$paidlog['amount'];
                }
            }
            
            

            if (($paidlog['time'] < $thismonth + (7 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth)) {
              
                $charts['this_month'][0] += $paidlog['amount'];
            }
            if (($paidlog['time'] < $thismonth + (14 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (7 * 24 * 60 * 60))) {
                
                $charts['this_month'][1]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $thismonth + (21 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (14 * 24 * 60 * 60))) {
               
                $charts['this_month'][2]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $thismonth + (28 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (21 * 24 * 60 * 60))) {
                
                $charts['this_month'][3]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $thismonth + (31 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth + (28 * 24 * 60 * 60))) {
               
                $charts['this_month'][4]+=$paidlog['amount'];
            }
            if (($paidlog['time'] < $thismonth + (31 * 24 * 60 * 60)) && ($paidlog['time'] > $thismonth )) {
                $top['this_month']['user']++;
                if ($paidlog['paid_type']==1)
                {
                   $top['this_month']['sms']+=$paidlog['amount']; 
                }
                if ($paidlog['paid_type']==2)
                {
                    $top['this_month']['card']+=$paidlog['amount'];
                }
                if ($paidlog['collaborator']!="")
                {
                    $top['this_month']['collaborator']+=$paidlog['amount'];
                }
            }
        }
     
        $percent=array();
        foreach ($top['this_month'] as $index=>$value)
        {
            $percent[$index]['class']="down";
            $percent[$index]['display']="giảm";
            $percent[$index]['value']="n/a";
            if (($top['this_month'][$index]>0)&&($top['last_month'][$index]>0))
            {
            $percent[$index]['value']=round((($top['this_month'][$index] / $top['last_month'][$index])*100),1);
            if ($top['this_month'][$index] > $top['last_month'][$index])
            {
               $percent[$index]['class']="up"; 
               $percent[$index]['display']="tăng";
            }
            }
        }
        $data['percent']=$percent;
        $data['top']=$top;
        $max = 0;
        foreach ($charts['last_month'] as $index => $total) {
            if ($total > $max) {
                $max = $total;
            }
            $chart_last_month[] = array($index, $total);
        }
        foreach ($charts['this_month'] as $index => $total) {
            if ($total > $max) {
                $max = $total;
            }
            $chart_this_month[] = array($index, $total);
        }

        $data['revenue'] = $revenue;
        $data['max'] = $max;
        $chard[] = array("color" => "#e67e22", "label" => "Tháng hiện tại", "data" => $chart_this_month);
        $chard[] = array("color" => "#1abc9c", "label" => "Tháng trước", "data" => $chart_last_month);
         
        $data['chard'] = $chard;
        
        return $data;
    }
    public function getLastquarter() {
        $current_month = date('m');
        $current_year = date('Y');

        if ($current_month >= 1 && $current_month <= 3) {
            $start_date = strtotime('1-October-' . ($current_year - 1));  // timestamp or 1-October Last Year 12:00:00 AM
            $end_date = strtotime('1-Janauary-' . $current_year);  // // timestamp or 1-January  12:00:00 AM means end of 31 December Last year
        } else if ($current_month >= 4 && $current_month <= 6) {
            $start_date = strtotime('1-January-' . $current_year);  // timestamp or 1-Janauray 12:00:00 AM
            $end_date = strtotime('1-April-' . $current_year);  // timestamp or 1-April 12:00:00 AM means end of 31 March
        } else if ($current_month >= 7 && $current_month <= 9) {
            $start_date = strtotime('1-April-' . $current_year);  // timestamp or 1-April 12:00:00 AM
            $end_date = strtotime('1-July-' . $current_year);  // timestamp or 1-July 12:00:00 AM means end of 30 June
        } else if ($current_month >= 10 && $current_month <= 12) {
            $start_date = strtotime('1-July-' . $current_year);  // timestamp or 1-July 12:00:00 AM
            $end_date = strtotime('1-October-' . $current_year);  // timestamp or 1-October 12:00:00 AM means end of 30 September
        }
        return array("start" => $start_date, "end" => $end_date);
    }

}