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

class Projects_model extends CI_Model
{ 
    public function __construct()
	{
		parent::__construct();
    }

    function get_timesheet_by_id($id){
 
        $where = " WHERE t.id = ".$id;
        if(!$this->ion_auth->is_admin()){
            $where .= " AND t.user_id = ".$this->session->userdata('user_id');
        }

		$LEFT_JOIN = " LEFT JOIN users u ON u.id=t.user_id ";
		$LEFT_JOIN .= " LEFT JOIN projects p ON p.id=t.project_id ";
		$LEFT_JOIN .= " LEFT JOIN tasks ts ON ts.id=t.task_id ";

        $query = $this->db->query("SELECT t.*, CONCAT(u.first_name, ' ', u.last_name) as user, p.title as project_title, ts.title as task_title FROM timesheet t $LEFT_JOIN ".$where);
    
        $results = $query->result_array();  

        return $results;
    }
    
    function get_timesheet(){
 
        $offset = 0;$limit = 10;
        $sort = 't.id'; $order = 'ASC';
        $get = $this->input->get();
        if($this->ion_auth->is_admin()){
            if(isset($get['user_id']) && !empty($get['user_id'])){
                $where = " WHERE t.user_id = ".$get['user_id'];
            }else{
                $where = " WHERE t.id != '' ";
            }
        }else{
            $where = " WHERE t.user_id = ".$this->session->userdata('user_id');
        }
        if(isset($get['sort']))
            $sort = strip_tags($get['sort']);
        if(isset($get['offset']))
            $offset = strip_tags($get['offset']);
        if(isset($get['limit']))
            $limit = strip_tags($get['limit']);
        if(isset($get['order']))
            $order = strip_tags($get['order']);
        if(isset($get['search']) &&  !empty($get['search'])){
            $search = strip_tags($get['search']);
            $where .= " AND (t.id like '%".$search."%' OR u.first_name like '%".$search."%' OR u.last_name like '%".$search."%' OR t.starting_time like '%".$search."%' OR t.ending_time like '%".$search."%' OR p.title like '%".$search."%' OR ts.title like '%".$search."%')";
        }

        if(isset($get['project_id']) &&  !empty($get['project_id'])){
            $project_id = strip_tags($get['project_id']);
            $where .= " AND t.project_id = $project_id ";
        }

        if(isset($get['task_id']) &&  !empty($get['task_id'])){
            $task_id = strip_tags($get['task_id']);
            $where .= " AND t.task_id = $task_id ";
        }
    
		$LEFT_JOIN = " LEFT JOIN users u ON u.id=t.user_id ";
		$LEFT_JOIN .= " LEFT JOIN projects p ON p.id=t.project_id ";
		$LEFT_JOIN .= " LEFT JOIN tasks ts ON ts.id=t.task_id ";

        $query = $this->db->query("SELECT COUNT('t.id') as total FROM timesheet t $LEFT_JOIN ".$where);
    
        $res = $query->result_array();
        foreach($res as $row){
            $total = $row['total'];
        }
        
        $query = $this->db->query("SELECT t.*, CONCAT(u.first_name, ' ', u.last_name) as user, p.title as project_title, ts.title as task_title FROM timesheet t $LEFT_JOIN ".$where." ORDER BY ".$sort." ".$order." LIMIT ".$offset.", ".$limit);
    
        $results = $query->result_array();  
    
        $bulkData = array();
        $bulkData['total'] = $total;
        $rows = array();
        $tempRow = array();

        foreach ($results as $result) {
				$tempRow = $result;

                $tempRow['project_title'] = '<a href="'.base_url('projects/detail/'.$result['project_id']).'" target="_blank">'.$result['project_title'].'</a>';
                
                $tempRow['task_title'] = '<a href="'.base_url('projects/tasks/'.$result['project_id']).'" target="_blank">'.$result['task_title'].'</a>';
                $stop = '';
                if($result['ending_time'] && $result['starting_time']){
                    $datetime1 = new DateTime($result['ending_time']);
                    $datetime2 = new DateTime($result['starting_time']);
                    $interval = $datetime1->diff($datetime2);
                    $total_time = $interval->format('%d')>0?"<div><strong>Days:</strong> ".$interval->format('%d')."</div>":"";
                    $total_time .= $interval->format('%h')>0?"<div><strong>Hours:</strong> ".$interval->format('%h')."</div>":"";
                    $total_time .= $interval->format('%i')>0?"<div><strong>Minutes:</strong> ".$interval->format('%i')."</div>":"";
                    $tempRow['total_time'] = $total_time==""?"<strong>Hours:</strong> 0":$total_time;
                }else{
                    $tempRow['total_time'] = '<div class="text-danger">Running</div>';
                    $stop = '<a href="#" class="btn btn-icon btn-sm btn-warning stop_timesheet_timer" data-id="'.$result['id'].'" data-toggle="tooltip" title="Stop"><i class="fas fa-clock"></i></a>';
                }

                $tempRow['starting_time'] = format_date($result['starting_time'],system_date_format()." ".system_time_format());
                $tempRow['ending_time'] = format_date($result['ending_time'],system_date_format()." ".system_time_format());
                

                $tempRow['action'] = '<span class="d-flex"><a href="#" class="btn btn-icon btn-sm btn-primary mr-1 modal-edit-timesheet" data-edit="'.$result['id'].'" data-toggle="tooltip" title="Edit"><i class="fas fa-pen"></i></a><a href="#" class="btn btn-icon btn-sm btn-danger mr-1 delete_timesheet" data-id="'.$result['id'].'" data-toggle="tooltip" title="Delete"><i class="fas fa-trash"></i></a>'.$stop.'</span>';
                

                $rows[] = $tempRow;
        }

        $bulkData['rows'] = $rows;
        print_r(json_encode($bulkData));
    }

    function get_comments($type = '',$from_id = '',$to_id = ''){

        $where = " WHERE m.type = '$type' AND m.to_id = $to_id ";
        $order = " ORDER BY m.created DESC ";

        $left_join = " LEFT JOIN users u ON m.from_id=u.id ";

        $query = $this->db->query("SELECT m.*,u.first_name,u.last_name,u.profile FROM messages m $left_join $where $order ");
    
        $comments = $query->result_array();
 
        $temp = [];

        foreach($comments as $key => $comment){
            $temp[$key] = $comment;
            $temp[$key]['can_delete'] = false;
            if($comment['from_id'] == $this->session->userdata('user_id')){
                $temp[$key]['can_delete'] = true;
            }
            if($this->ion_auth->is_admin()){
                $temp[$key]['can_delete'] = true;
            }
            $temp[$key]['created'] = format_date($comment['created'],system_date_format());
            $temp[$key]['profile'] = $comment['profile'];
            $temp[$key]['short_name'] = ucfirst(mb_substr($comment['first_name'], 0, 1, 'utf-8')).''.ucfirst(mb_substr($comment['last_name'], 0, 1, 'utf-8'));
        }
        $comments = $temp;
        if($comments){
            return $comments;
        }else{
            return false;
        }
    }

    function create_comment($data){
        if($this->db->insert('messages', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function task_status_update($task_id, $new_status){
        $this->db->set('status', $new_status);
        $this->db->where('id', $task_id);
        if($this->db->update('tasks'))
            return true;
        else
            return false;
    }

    function delete_task_files($file_id='',$task_id=''){
        if($file_id){
            $query = $this->db->query('SELECT * FROM media_files WHERE id='.$file_id.'');
            $data = $query->result_array();
            if(!empty($data)){
                if(unlink('assets/uploads/tasks/'.$data[0]['file_name'])){
                    $this->db->delete('media_files', array('id' => $file_id));
                }
            }
            return true;
        }elseif($task_id){
            $query = $this->db->query('SELECT * FROM media_files WHERE type="task" AND type_id='.$task_id.'');
            $datas = $query->result_array();
            if(!empty($datas)){
                foreach($datas as $data){
                    if(unlink('assets/uploads/tasks/'.$data['file_name'])){
                        $this->db->delete('media_files', array('id' => $data['id']));
                    }
                }
            }
            return true;
        }else{
            return false;
        }
        
    }
    function delete_project_files($file_id='',$project_id=''){
        if($file_id){
            $query = $this->db->query('SELECT * FROM media_files WHERE id='.$file_id.'');
            $data = $query->result_array();
            if(!empty($data)){
                if(unlink('assets/uploads/projects/'.$data[0]['file_name'])){
                    $this->db->delete('media_files', array('id' => $file_id));
                }
            }
            return true;
        }elseif($project_id){
            $query = $this->db->query('SELECT * FROM media_files WHERE type="project" AND type_id='.$project_id.'');
            $datas = $query->result_array();
            if(!empty($datas)){
                foreach($datas as $data){
                    if(unlink('assets/uploads/projects/'.$data['file_name'])){
                        $this->db->delete('media_files', array('id' => $data['id']));
                    }
                }
            }
            return true;
        }else{
            return false;
        }
        
    }

    function upload_files($data){
        if($this->db->insert('media_files', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function create_project($data){
        if($this->db->insert('projects', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function create_timesheet($data){
        if($this->db->insert('timesheet', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function create_task($data){
        if($this->db->insert('tasks', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function edit_task($task_id, $data){
        $this->db->where('id', $task_id);
        if($this->db->update('tasks', $data))
            return true;
        else
            return false;
    }

    function edit_timesheet($data, $timesheet_id = '', $task_id = '', $user_id = ''){
        if(!empty($timesheet_id)){
            $this->db->where('id', $timesheet_id);
        }
        if(!empty($task_id)){
            $this->db->where('task_id', $task_id);
        }
        if(!empty($user_id)){
            $this->db->where('user_id', $user_id);
        }
        if($this->db->update('timesheet', $data))
            return true;
        else
            return false;
    }

    function edit_project($project_id, $data){
        $this->db->where('id', $project_id);
        if($this->db->update('projects', $data))
            return true;
        else
            return false;
    }

    function create_project_users($data){
        if($this->db->insert('project_users', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function create_task_users($data){
        if($this->db->insert('task_users', $data))
            return $this->db->insert_id();
        else
            return false; 
    }

    function delete_project_users($project_id='',$user_id=''){

        if(empty($project_id) && empty($user_id)){
            return false;
        }

        if(!empty($project_id)){
            $this->db->where('project_id', $project_id);
        }
        if(!empty($user_id)){
            $this->db->where('user_id', $user_id);
        }
        $this->db->delete('project_users');
        return true;
    }

    function delete_task_comment($comment_id='',$user_id='',$type='',$to_id=''){

        if(empty($comment_id) && empty($user_id) && empty($type) && empty($to_id)){
            return false;
        }
        if(!empty($type)){
            $this->db->where('type', $type);
        }
        if(!empty($to_id)){
            $this->db->where('to_id', $to_id);
        }
        if(!empty($comment_id)){
            $this->db->where('id', $comment_id);
        }
        if(!empty($user_id)){
            $this->db->where('from_id', $user_id);
        }
        $this->db->delete('messages');
        return true;
    }

    function delete_task($task_id){
        $this->db->where('id', $task_id);
        if($this->db->delete('tasks'))
            return true;
        else
            return false;
    }
    
    function delete_timesheet($timesheet_id = '', $project_id = '', $task_id = '', $user_id = ''){
        if($timesheet_id){
            $this->db->where('id', $timesheet_id);
        }
        if($project_id){
            $this->db->where('project_id', $project_id);
        }
        if($task_id){
            $this->db->where('task_id', $task_id);
        }
        if($user_id){
            $this->db->where('user_id', $user_id);
        }

        if($timesheet_id = '' && $project_id = '' && $task_id = '' && $user_id = ''){
            return false;
        }

        if($this->db->delete('timesheet'))
            return true;
        else
            return false;
    }
    
    function delete_project($project_id){
        $this->db->where('id', $project_id);
        if($this->db->delete('projects'))
            return true;
        else
            return false;
    }

    function delete_task_users($task_id='',$user_id=''){

        if(empty($task_id) && empty($user_id)){
            return false;
        }

        if(!empty($task_id)){
            $this->db->where('task_id', $task_id);
        }
        if(!empty($user_id)){
            $this->db->where('user_id', $user_id);
        }
        $this->db->delete('task_users');
        return true;
    }

    function get_tasks_files($task_id = '',$user_id = ''){
        $where = "";
        $where .= (!empty($task_id) && is_numeric($task_id))?"AND type_id=$task_id":"";
        $where .= (!empty($user_id) && is_numeric($user_id))?"AND user_id=$user_id":"";
        $query = $this->db->query("SELECT * FROM media_files WHERE type='task' $where");
        $data = $query->result_array();
        if($data){
            return $data;
        }else{
            return false;
        }
    }

    function get_project_files($project_id = '',$user_id = ''){
        $where = "";
        $where .= (!empty($project_id) && is_numeric($project_id))?"AND type_id=$project_id":"";
        $where .= (!empty($user_id) && is_numeric($user_id))?"AND user_id=$user_id":"";
        $query = $this->db->query("SELECT * FROM media_files WHERE type='project' $where");
        $data = $query->result_array();
        if($data){
            return $data;
        }else{
            return false;
        }
    }

    function get_project_users($project_id = ''){
        $where = "";
        $where .= (!empty($project_id) && is_numeric($project_id))?"WHERE pu.project_id=$project_id":"";
        $left_join = "LEFT JOIN users u ON pu.user_id=u.id";
        $query = $this->db->query("SELECT u.id,u.email,u.first_name,u.last_name,u.profile FROM project_users pu $left_join $where GROUP BY pu.user_id");
        $data = $query->result_array();
        if($data){
            return $data;
        }else{
            return false;
        }
    }

    function get_task_users($task_id = ''){
        $where = "";
        $where .= (!empty($task_id) && is_numeric($task_id))?"WHERE pu.task_id=$task_id":"";
        $left_join = "LEFT JOIN users u ON pu.user_id=u.id";
        $query = $this->db->query("SELECT u.id,u.email,u.first_name,u.last_name,u.profile FROM task_users pu $left_join $where");
        $data = $query->result_array();
        if($data){
            return $data;
        }else{
            return false;
        }
    }

    function get_projects($user_id = '',$project_id = '',$limit='', $start='', $filter_type='', $filter=''){

        if(!empty($limit)){
            $where_limit = ' LIMIT '.$limit;
            if(!empty($start)){
                $where_limit .= ' OFFSET '.$start;
            }
        }else{
            $where_limit = '';
        }

        $where = "";
        $order = " ORDER BY p.created DESC ";

        if(!empty($filter_type) && !empty($filter) && is_numeric($filter) && $filter_type == 'status'){
            $where = "WHERE ps.id = $filter";
        }elseif(!empty($filter_type) && !empty($filter) && is_numeric($filter) && $filter_type == 'user'){
            $where = "WHERE pu.user_id = $filter";
        }elseif(!empty($filter_type) && !empty($filter) && is_numeric($filter) && $filter_type == 'client'){
            $where = "WHERE p.client_id = $filter";
        }elseif(!empty($filter_type) && !empty($filter) && $filter_type == 'sortby'){
            if($filter == 'old'){
                $order = " ORDER BY p.created ASC ";
            }elseif($filter == 'name'){
                $order = " ORDER BY p.title ASC ";
            }else{
                $order = " ORDER BY p.created DESC ";
            }
            
        }

        $where .= (!empty($project_id) && is_numeric($project_id) && empty($where))?"WHERE pu.project_id=$project_id":"";
        if(!empty($user_id) && is_numeric($user_id)){
            if($this->ion_auth->in_group(3)){
                $where .= (empty($where))?" WHERE p.client_id=$user_id ":" AND p.client_id=$user_id ";
            }else{
                $where .= (empty($where))?" WHERE pu.user_id=$user_id ":"";
            }
        }
        $left_join = " LEFT JOIN projects p ON pu.project_id=p.id ";
        $left_join .= " LEFT JOIN project_status ps ON p.status=ps.id ";
        $query = $this->db->query("SELECT p.*,ps.title AS project_status,ps.class AS project_class FROM project_users pu $left_join $where GROUP BY pu.project_id $order $where_limit");
    
        $projects = $query->result_array();

        $temp = [];

        foreach($projects as $key => $project){
            $temp[$key] = $project;
            
            if($temp[$key]['project_status'] == 'Not Started'){
                $temp[$key]['project_status'] = $this->lang->line('not_started')?$this->lang->line('not_started'):'Not Started';
            }elseif($temp[$key]['project_status'] == 'On Going'){
                $temp[$key]['project_status'] = $this->lang->line('on_going')?$this->lang->line('on_going'):'On Going';
            }elseif($temp[$key]['project_status'] == 'Finished'){
                $temp[$key]['project_status'] = $this->lang->line('finished')?$this->lang->line('finished'):'Finished';
            }

            $temp[$key]['starting_date'] = format_date($project['starting_date'],system_date_format());
            $temp[$key]['ending_date'] = format_date($project['ending_date'],system_date_format());
            $days_count = count_days_btw_two_dates(date("Y-m-d"),$project['ending_date']);
            $temp[$key]['days_count'] = $days_count['days'];
            $temp[$key]['days_status'] = $days_count['days_status'];
            $temp[$key]['total_tasks'] = get_count('id','tasks','project_id='.$project['id']);
            $temp[$key]['completed_tasks'] = get_count('id','tasks','status = 4 and project_id='.$project['id']);

            if($project['client_id']){
                $temp[$key]['project_client'] = $this->ion_auth->user($project['client_id'])->row();
                $temp[$key]['project_client']->company = company_details('company_name', $project['client_id']);
            }else{
                $temp[$key]['project_client'] = null;
            }

            $temp[$key]['project_users'] = $project_users = $this->get_project_users($project['id']);
            $temp[$key]['project_users_ids'] = '';
            if(!empty($project_users)){
                foreach($project_users as  $pkey => $project_user){
                    $tempid[$pkey] = $project_user['id'];
                }
                $temp[$key]['project_users_ids'] = implode(",",$tempid);
            }
            
        }
        $projects = $temp;
        if($projects){
            return $projects;
        }else{
            return false;
        }
    }

    function get_clients_projects($user_id){
        if($user_id){
            $query = $this->db->query("SELECT * FROM projects WHERE client_id=$user_id");
        }else{
            $query = $this->db->query("SELECT * FROM projects");
        }
        $projects = $query->result_array();
        if($projects){
            return $projects;
        }else{
            return false;
        }
    }

    function get_tasks($user_id = '',$task_id = '',$project_id = ''){

        $where = "";
        $order = " ORDER BY t.created DESC ";

        $where .= (!empty($task_id) && is_numeric($task_id) && empty($where))?"WHERE t.id=$task_id":"";

        if(!empty($user_id) && is_numeric($user_id)){
            if($this->ion_auth->in_group(3)){
                $where .= (empty($where))?" WHERE p.client_id=$user_id ":" AND p.client_id=$user_id ";
            }else{
                $where .= (empty($where))?" WHERE tu.user_id=$user_id ":"";
            }
        }
        
        if(!empty($project_id) && is_numeric($project_id) && empty($where)){
            $where .="WHERE p.id=$project_id";
        }elseif(!empty($project_id) && is_numeric($project_id) && !empty($where)){
            $where .=" AND p.id=$project_id";
        }
        
        $left_join = " LEFT JOIN tasks t ON tu.task_id=t.id ";
        $left_join .= " LEFT JOIN task_status ts ON t.status=ts.id ";
        $left_join .= " LEFT JOIN priorities tp ON t.priority=tp.id ";
        $left_join .= " LEFT JOIN projects p ON t.project_id=p.id ";
        $query = $this->db->query("SELECT t.*,ts.title AS task_status,ts.class AS task_class,tp.title AS task_priority,tp.class AS priority_class,p.title AS project_title FROM task_users tu $left_join $where GROUP BY tu.task_id $order ");
    
        $tasks = $query->result_array();

        $temp = [];

        foreach($tasks as $key => $task){
            $temp[$key] = $task;
            
            if(!$this->ion_auth->in_group(3)){
                $temp[$key]['can_see_time'] = true;
            }else{
                $temp[$key]['can_see_time'] = false;
            }

            if(check_my_timer($task['id'])){
                $temp[$key]['timer_running'] = true;
            }else{
                $temp[$key]['timer_running'] = false;
            }

            if($task['task_status'] == 'Todo'){
                $temp[$key]['task_status'] = $this->lang->line('todo')?$this->lang->line('todo'):'Todo';
            }elseif($task['task_status'] == 'In Progress'){
                $temp[$key]['task_status'] = $this->lang->line('in_progress')?$this->lang->line('in_progress'):'In Progress';
            }elseif($task['task_status'] == 'In Review'){
                $temp[$key]['task_status'] = $this->lang->line('in_review')?$this->lang->line('in_review'):'In Review';
            }elseif($task['task_status'] == 'Completed'){
                $temp[$key]['task_status'] = $this->lang->line('completed')?$this->lang->line('completed'):'Completed';
            }

            if($task['task_priority'] == 'Low'){
                $temp[$key]['task_priority'] = $this->lang->line('low')?$this->lang->line('low'):'Low';
            }elseif($task['task_priority'] == 'Medium'){
                $temp[$key]['task_priority'] = $this->lang->line('medium')?$this->lang->line('medium'):'Medium';
            }elseif($task['task_priority'] == 'High'){
                $temp[$key]['task_priority'] = $this->lang->line('high')?$this->lang->line('high'):'High';
            }
            
            $temp[$key]['due_date'] = format_date($task['due_date'],system_date_format());
            $days_count = count_days_btw_two_dates(date("Y-m-d"),$task['due_date']);
            $temp[$key]['days_count'] = $days_count['days'];
            $temp[$key]['days_status'] = $days_count['days_status'];
            $temp[$key]['task_users'] = $task_users = $this->get_task_users($task['id']);
            $temp[$key]['task_users_ids'] = '';
            if(!empty($task_users)){
                foreach($task_users as  $pkey => $task_user){
                    $tempid[$pkey] = $task_user['id'];
                }
                $temp[$key]['task_users_ids'] = implode(",",$tempid);
            }
            
        }
        $tasks = $temp;
        if($tasks){
            return $tasks;
        }else{
            return false;
        }
    }

}