<?php

/**
 * Description of Project_Model
 *
 * @author NaYeM
 */
class Project_model extends MY_Model
{

	public $_table_name;
	public $_order_by;
	public $_primary_key;


	// get_project_kpi
	public function get_project_kpi()
	{

		$this->set_sql_mode();
		$statuses = $this->get_statuses();
		$project_end_date_sql = "COALESCE(STR_TO_DATE(tbl_project.end_date, '%Y-%m-%d'), STR_TO_DATE(tbl_project.end_date, '%d-%b-%Y'))";
		// total projects
		$this->db->select("COUNT(*) as total_projects", false);
		// overdue projects end_date should be format 13-Jan-2026 to Y-m-d
		$this->db->select("SUM(CASE WHEN {$project_end_date_sql} < CURDATE() AND tbl_project.project_status != 'completed' THEN 1 ELSE 0 END) AS overdue_projects", false);

		// active projects
		foreach ($statuses as $status) {
			$this->db->select("SUM(CASE WHEN tbl_project.project_status = '{$status['value']}' THEN 1 ELSE 0 END) as {$status['value']}_projects", false);
		}
		$this->db->from('tbl_project');
		$this->staff_query('tbl_project');

		return $this->db->get()->row();
	}

	public function get_dashboard_kpi($today = null)
	{
		$today = !empty($today) ? $today : date('Y-m-d');
		$stats = $this->get_project_kpi();
		if (empty($stats)) {
			$stats = (object) array();
		}

		$total_projects = !empty($stats->total_projects) ? (int) $stats->total_projects : 0;
		$completed_projects = !empty($stats->completed_projects) ? (int) $stats->completed_projects : 0;
		$stats->completion_pct = $total_projects > 0 ? round(($completed_projects / $total_projects) * 100) : 0;

		$this->db->from('tbl_task');
		$this->db->join('tbl_project', 'tbl_project.project_id = tbl_task.project_id', 'inner');
		$this->db->where('tbl_task.task_status !=', 'completed');
		$this->staff_query('tbl_project');
		$stats->active_tasks = (int) $this->db->count_all_results();

		$milestone_end_date_sql = "COALESCE(STR_TO_DATE(tbl_milestones.end_date, '%Y-%m-%d'), STR_TO_DATE(tbl_milestones.end_date, '%d-%b-%Y'))";
		$this->db->from('tbl_milestones');
		$this->db->join('tbl_project', 'tbl_project.project_id = tbl_milestones.project_id', 'inner');
		$this->db->where($milestone_end_date_sql . ' >= ' . $this->db->escape($today), null, false);
		$this->staff_query('tbl_project');
		$stats->upcoming_milestones = (int) $this->db->count_all_results();

		$this->db->select('tbl_milestones.milestone_name');
		$this->db->from('tbl_milestones');
		$this->db->join('tbl_project', 'tbl_project.project_id = tbl_milestones.project_id', 'inner');
		$this->db->where($milestone_end_date_sql . ' >= ' . $this->db->escape($today), null, false);
		$this->staff_query('tbl_project');
		$this->db->order_by($milestone_end_date_sql, 'ASC', false);
		$this->db->limit(1);
		$next_milestone = $this->db->get()->row();
		$stats->next_milestone_label = !empty($next_milestone->milestone_name) ? $next_milestone->milestone_name : null;

		return $stats;
	}

	// get_project
	public function get_project($id = null)
	{
		// get project by id with client info and project category 
		$this->db->select('tbl_project.*, tbl_client.name as client_name, tbl_client.email as client_email, tbl_customer_group.customer_group as category_name');
		$this->db->from('tbl_project');
		$this->db->join('tbl_client', 'tbl_client.client_id = tbl_project.client_id', 'left');
		$this->db->join('tbl_customer_group', 'tbl_customer_group.customer_group_id = tbl_project.category_id', 'left');
		$this->staff_query('tbl_project');
		if (!empty($id)) {
			$this->db->where('tbl_project.project_id', $id);
			$query = $this->db->get();
			return $query->row();
		} else {
			$query = $this->db->get();
			return $query->result();
		}
	}


	public function calculate_progress_by_tasks($id)
	{
		$project = $this->get($id);
		$total_project_tasks = total_rows('tbl_task', array(
			'project_id' => $id
		));
		$total_finished_tasks = total_rows('tbl_task', array(
			'project_id' => $id,
			'task_status' => 'completed'
		));
		$percent = 0;
		if ($total_finished_tasks >= floatval($total_project_tasks)) {
			$percent = 100;
		} else {
			if ($total_project_tasks !== 0) {
				$percent = number_format(($total_finished_tasks * 100) / $total_project_tasks, 2);
			}
		}
		return $percent;
	}

	function calculate_milestone_progress($milestones_id)
	{
		$cacheKey = 'milestone_progress_' . $milestones_id;
		$cached = $this->cache_get($cacheKey);
		if ($cached !== false) {
			return $cached;
		}

		$this->db->where('milestones_id', $milestones_id);
		$all_milestone_tasks = $this->db->get('tbl_task')->num_rows();

		if ($all_milestone_tasks > 0) {
			$complete_milestone_tasks = $this->db->where(array(
				'task_progress' => '100',
				'milestones_id' => $milestones_id
			))->get('tbl_task')->num_rows();
			$progress = round(($complete_milestone_tasks / $all_milestone_tasks) * 100);
		} else {
			$progress = 0;
		}

		$this->cache_save($cacheKey, $progress);
		return $progress;
	}

	function calculate_project($project_value, $project_id)
	{
		switch ($project_value) {
			case 'project_cost':
				return $this->total_project_cost($project_id);
				break;
			case 'project_hours':
				return $this->total_project_hours($project_id, true);
				break;
		}
	}

	function total_project_cost($project_id)
	{
		$project_info = $this->db->where('project_id', $project_id)->get('tbl_project')->row();
		$tasks_cost = $this->calculate_all_tasks_cost($project_id);
		$project_time = $this->calculate_total_task_time($project_id);
		$project_hours = $project_time / 3600;
		if (empty($project_info->hourly_rate)) {
			$project_info->hourly_rate = 0;
		}

		$project_cost = $project_hours * $project_info->hourly_rate;

		if ($project_info->billing_type == 'tasks_hours') {
			return $tasks_cost;
		}
		if ($project_info->billing_type == 'tasks_and_project_hours') {
			return $project_cost + $tasks_cost;
		}
		if ($project_info->billing_type == 'project_hours') {
			return $project_cost;
		} else {
			return $this->get_any_field('tbl_project', array('project_id' => $project_id), 'project_cost');
		}
	}

	public function calculate_all_tasks_cost($project_id)
	{
		$cacheKey = 'project_tasks_cost_' . $project_id;
		$cached = $this->cache_get($cacheKey);
		if ($cached !== false) {
			return $cached;
		}

		$all_tasks = $this->db->where('project_id', $project_id)->get('tbl_task')->result();
		$total_cost = 0;
		if (!empty($all_tasks)) {
			foreach ($all_tasks as $v_tasks) {
				if (!empty($v_tasks->billable) && $v_tasks->billable == 'Yes') {
					$task_time = $this->task_spent_time_by_id($v_tasks->task_id);
					$total_time = $task_time / 3600;

					$total_cost += $total_time * $v_tasks->hourly_rate;
				}
			}
		}
		$this->cache_save($cacheKey, $total_cost);
		return $total_cost;
	}

	public function get_project_participants($permission)
	{
		if (empty($permission) || $permission === 'all') {
			return array();
		}

		$decoded = json_decode($permission, true);
		if (!is_array($decoded)) {
			return array();
		}

		$user_ids = array_unique(array_filter(array_map('intval', array_keys($decoded))));
		if (empty($user_ids)) {
			return array();
		}

		return $this->db
			->select('tbl_users.user_id, tbl_users.role_id, tbl_account_details.fullname, tbl_account_details.avatar')
			->from('tbl_users')
			->join('tbl_account_details', 'tbl_account_details.user_id = tbl_users.user_id', 'left')
			->where_in('tbl_users.user_id', $user_ids)
			->order_by('tbl_account_details.fullname', 'ASC')
			->get()
			->result();
	}

	public function get_project_overview($project_details)
	{
		$project_id = is_object($project_details) ? (int) $project_details->project_id : (int) $project_details;
		if (empty($project_id)) {
			return array();
		}

		$task_counts = $this->db
			->select("
				COUNT(*) as total_task,
				SUM(CASE WHEN task_status = 'completed' THEN 1 ELSE 0 END) as completed_task
			", false)
			->where('project_id', $project_id)
			->get('tbl_task')
			->row();

		$expense_summary = $this->db
			->select("
				COUNT(*) as expense_rows,
				COALESCE(SUM(amount), 0) as total_expense,
				COALESCE(SUM(CASE WHEN billable = 'Yes' THEN amount ELSE 0 END), 0) as billable_expense,
				COALESCE(SUM(CASE WHEN billable = 'No' THEN amount ELSE 0 END), 0) as not_billable_expense
			", false)
			->where(array('project_id' => $project_id, 'type' => 'Expense'))
			->get('tbl_transactions')
			->row();

		$project_cost = (float) $this->calculate_project('project_cost', $project_id);
		$project_hours = $this->calculate_project('project_hours', $project_id);
		$tasks_hours = 0;
		if (!empty($project_details->billing_type) && in_array($project_details->billing_type, array('tasks_hours', 'tasks_and_project_hours', 'tasks_sub_tasks_and_project_hours'), true)) {
			$tasks_hours = $this->total_project_hours($project_id, '', true);
		}

		$tab_counts = $this->get_project_tab_counts($project_id);

		return array(
			'project_cost' => $project_cost,
			'project_hours' => $project_hours,
			'tasks_hours' => $tasks_hours,
			'total_task' => !empty($task_counts->total_task) ? (int) $task_counts->total_task : 0,
			'completed_task' => !empty($task_counts->completed_task) ? (int) $task_counts->completed_task : 0,
			'total_expense' => !empty($expense_summary->total_expense) ? (float) $expense_summary->total_expense : 0,
			'billable_expense' => !empty($expense_summary->billable_expense) ? (float) $expense_summary->billable_expense : 0,
			'not_billable_expense' => !empty($expense_summary->not_billable_expense) ? (float) $expense_summary->not_billable_expense : 0,
			'attachments_count' => isset($tab_counts['attachments']) ? (int) $tab_counts['attachments'] : 0,
			'comments_count' => isset($tab_counts['comments']) ? (int) $tab_counts['comments'] : 0,
		);
	}

	public function get_project_tab_counts($project_id)
	{
		$project_id = (int) $project_id;
		if (empty($project_id)) {
			return array();
		}
		$cache_key = 'project_tab_counts_' . $project_id;
		$cached_counts = function_exists('helper_cache_get') ? helper_cache_get($cache_key) : false;
		if ($cached_counts !== false) {
			return $cached_counts;
		}

		$counts = array(
			'comments' => (int) $this->db
				->where(array('module' => 'projects', 'module_field_id' => $project_id))
				->count_all_results('tbl_task_comment'),
			'attachments' => (int) $this->db
				->where(array('module' => 'projects', 'module_field_id' => $project_id))
				->count_all_results('tbl_attachments'),
			'expense' => 0,
			'deposit' => 0,
		);

		$transaction_counts = $this->db
			->select("
				SUM(CASE WHEN type = 'Expense' THEN 1 ELSE 0 END) as expense_count,
				SUM(CASE WHEN type = 'Income' THEN 1 ELSE 0 END) as deposit_count
			", false)
			->where('project_id', $project_id)
			->get('tbl_transactions')
			->row();

		if (!empty($transaction_counts)) {
			$counts['expense'] = !empty($transaction_counts->expense_count) ? (int) $transaction_counts->expense_count : 0;
			$counts['deposit'] = !empty($transaction_counts->deposit_count) ? (int) $transaction_counts->deposit_count : 0;
		}

		if (function_exists('helper_cache_save')) {
			helper_cache_save($cache_key, $counts, 60);
		}
		return $counts;
	}

	public function get_custom_table_values_map($form_id, array $record_ids)
	{
		$record_ids = array_unique(array_filter(array_map('intval', $record_ids)));
		if (empty($form_id) || empty($record_ids)) {
			return array();
		}

		$all_field = $this->db
			->where(array('form_id' => $form_id, 'status' => 'active', 'show_on_table' => 'on'))
			->get('tbl_custom_field')
			->result();
		$form = $this->db->where('form_id', $form_id)->get('tbl_form')->row();
		if (empty($form) || empty($form->tbl_name) || empty($form->table_id)) {
			return array();
		}

		$visible_fields = array();
		if (!empty($all_field)) {
			foreach ($all_field as $field) {
				$is_visible = false;
				if (!empty($field->visible_for_admin) && empty(client()) && !empty(admin())) {
					$is_visible = true;
				} elseif (!empty($field->visible_for_client) && !empty(client())) {
					$is_visible = true;
				} elseif (empty(client())) {
					$is_visible = true;
				}

				if ($is_visible) {
					$visible_fields[slug_it($field->field_label)] = $field->field_label;
				}
			}
		}

		if (empty($visible_fields)) {
			return array();
		}

		$select = array($form->table_id);
		foreach (array_keys($visible_fields) as $column) {
			$select[] = $form->tbl_name . '.' . $column;
		}

		$rows = $this->db
			->select(implode(',', $select), false)
			->where_in($form->table_id, $record_ids)
			->get($form->tbl_name)
			->result_array();

		$mapped = array();
		foreach ($rows as $row) {
			if (!isset($row[$form->table_id])) {
				continue;
			}
			$row_id = (int) $row[$form->table_id];
			foreach ($visible_fields as $column => $label) {
				$mapped[$row_id][$label] = isset($row[$column]) ? $row[$column] : null;
			}
		}

		return $mapped;
	}

	public function get_project_progress_map($projects)
	{
		if (empty($projects) || !is_array($projects)) {
			return array();
		}

		$progress_map = array();
		$task_progress_ids = array();
		$hour_progress_ids = array();

		foreach ($projects as $project) {
			if (empty($project) || empty($project->project_id)) {
				continue;
			}

			$project_id = (int) $project->project_id;
			if ($project->project_status == 'completed') {
				$progress_map[$project_id] = 100;
				continue;
			}

			if (empty($project->calculate_progress) || $project->calculate_progress == '0') {
				$progress = !empty($project->progress) ? (float) $project->progress : 0;
				$progress_map[$project_id] = max(0, min(100, $progress));
				continue;
			}

			if ($project->calculate_progress == 'through_project_hours') {
				$hour_progress_ids[] = $project_id;
				continue;
			}

			$task_progress_ids[] = $project_id;
		}

		$task_progress_ids = array_unique($task_progress_ids);
		if (!empty($task_progress_ids)) {
			$task_counts = $this->db
				->select("
					project_id,
					COUNT(*) as total_tasks,
					SUM(CASE WHEN task_status = 'completed' THEN 1 ELSE 0 END) as done_tasks
				", false)
				->where_in('project_id', $task_progress_ids)
				->group_by('project_id')
				->get('tbl_task')
				->result();

			foreach ($task_progress_ids as $project_id) {
				$progress_map[$project_id] = 0;
			}

			if (!empty($task_counts)) {
				foreach ($task_counts as $row) {
					$total_tasks = !empty($row->total_tasks) ? (int) $row->total_tasks : 0;
					$done_tasks = !empty($row->done_tasks) ? (int) $row->done_tasks : 0;
					$progress_map[(int) $row->project_id] = $total_tasks > 0 ? round(($done_tasks / $total_tasks) * 100) : 0;
				}
			}
		}

		$hour_progress_ids = array_unique($hour_progress_ids);
		if (!empty($hour_progress_ids)) {
			foreach ($hour_progress_ids as $project_id) {
				$progress_map[$project_id] = (float) $this->get_project_progress($project_id);
			}
		}

		return $progress_map;
	}

	public function get_project_tasks_with_meta($project_id, array $options = array())
	{
		$project_id = (int) $project_id;
		if (empty($project_id)) {
			return array();
		}

		$this->db
			->select('tbl_task.task_id, tbl_task.task_name, tbl_task.task_status, tbl_task.task_progress, tbl_task.milestones_id, tbl_task.task_start_date, tbl_task.due_date, tbl_task.permission, tbl_task.milestones_order')
			->select('COALESCE(task_comments.comment_count, 0) AS comment_count', false)
			->select('COALESCE(task_attachments.attachment_count, 0) AS attachment_count', false)
			->from('tbl_task')
			->join('(SELECT module_field_id, COUNT(*) AS comment_count FROM tbl_task_comment WHERE module="tasks" GROUP BY module_field_id) AS task_comments', 'task_comments.module_field_id = tbl_task.task_id', 'left')
			->join('(SELECT module_field_id, COUNT(*) AS attachment_count FROM tbl_attachments WHERE module="tasks" GROUP BY module_field_id) AS task_attachments', 'task_attachments.module_field_id = tbl_task.task_id', 'left')
			->where('tbl_task.project_id', $project_id);

		if (array_key_exists('milestone_id', $options)) {
			$milestone_id = (int) $options['milestone_id'];
			if ($milestone_id === 0) {
				$this->db->group_start();
				$this->db->where('tbl_task.milestones_id', null);
				$this->db->or_where('tbl_task.milestones_id', 0);
				$this->db->group_end();
			} else {
				$this->db->where('tbl_task.milestones_id', $milestone_id);
			}
		}

		$this->staff_query('tbl_task');
		$this->db->order_by('tbl_task.milestones_order', 'asc');
		$this->db->order_by('tbl_task.task_id', 'desc');

		if (isset($options['limit']) && (int) $options['limit'] > 0) {
			$limit = (int) $options['limit'];
			$offset = isset($options['offset']) ? max(0, (int) $options['offset']) : 0;
			$this->db->limit($limit, $offset);
		}

		return $this->db->get()->result();
	}

	public function get_project_milestones_data($project_details, $page_size = 6)
	{
		$project_id = is_object($project_details) ? (int) $project_details->project_id : (int) $project_details;
		if (empty($project_id)) {
			return array(
				'all_milestones_info' => array(),
				'milestone_progress_map' => array(),
				'kanban_columns' => array(),
				'kanban_items' => array(),
			);
		}

		$all_milestones_info = $this->db
			->where('project_id', $project_id)
			->order_by('milestones_id', 'asc')
			->get('tbl_milestones')
			->result();

		$count_rows = $this->db
			->select('IFNULL(tbl_task.milestones_id, 0) AS milestone_key, COUNT(*) AS total, SUM(CASE WHEN tbl_task.task_status = "completed" THEN 1 ELSE 0 END) AS completed', false)
			->from('tbl_task')
			->where('tbl_task.project_id', $project_id)
			->group_by('milestone_key');
		$this->staff_query('tbl_task');
		$count_rows = $this->db->get()->result();

		$milestone_task_counts = array();
		$milestone_progress_map = array();
		if (!empty($count_rows)) {
			foreach ($count_rows as $row) {
				$key = (string) $row->milestone_key;
				$total = !empty($row->total) ? (int) $row->total : 0;
				$completed = !empty($row->completed) ? (int) $row->completed : 0;
				$milestone_task_counts[$key] = $total;
				$milestone_progress_map[$key] = $total > 0 ? round(($completed / $total) * 100) : 0;
			}
		}

		$milestone_tones = array('slate', 'blue', 'emerald', 'rose', 'amber', 'indigo', 'teal');
		$kanban_columns = array(
			array(
				'id' => '0',
				'label' => lang('uncategorized'),
				'subtitle' => '',
				'tone' => 'slate',
				'total' => isset($milestone_task_counts['0']) ? $milestone_task_counts['0'] : 0,
			),
		);

		if (!empty($all_milestones_info)) {
			foreach ($all_milestones_info as $index => $milestone) {
				$subtitle_parts = array();
				if (!empty($milestone->start_date)) {
					$subtitle_parts[] = display_date($milestone->start_date);
				}
				if (!empty($milestone->end_date)) {
					$subtitle_parts[] = display_date($milestone->end_date);
				}
				$kanban_columns[] = array(
					'id' => (string) $milestone->milestones_id,
					'label' => $milestone->milestone_name,
					'subtitle' => !empty($subtitle_parts) ? implode(' • ', $subtitle_parts) : '',
					'tone' => $milestone_tones[$index % count($milestone_tones)],
					'total' => isset($milestone_task_counts[(string) $milestone->milestones_id]) ? $milestone_task_counts[(string) $milestone->milestones_id] : 0,
				);
				if (!isset($milestone_progress_map[(string) $milestone->milestones_id])) {
					$milestone_progress_map[(string) $milestone->milestones_id] = 0;
				}
			}
		}

		$tasks = $this->get_project_tasks_with_meta($project_id);
		$grouped_tasks = array();
		if (!empty($tasks)) {
			foreach ($tasks as $task) {
				$key = !empty($task->milestones_id) ? (string) $task->milestones_id : '0';
				if (!isset($grouped_tasks[$key])) {
					$grouped_tasks[$key] = array();
				}
				$grouped_tasks[$key][] = $task;
			}
		}

		$kanban_items = array();
		foreach ($kanban_columns as $column) {
			$key = (string) $column['id'];
			$column_tasks = isset($grouped_tasks[$key]) ? array_slice($grouped_tasks[$key], 0, $page_size) : array();
			foreach ($column_tasks as $task) {
				$kanban_items[] = $this->format_project_task_card($task, $key);
			}
		}

		return array(
			'all_milestones_info' => $all_milestones_info,
			'milestone_progress_map' => $milestone_progress_map,
			'kanban_columns' => $kanban_columns,
			'kanban_items' => $kanban_items,
		);
	}

	public function get_project_gantt_data($project_details)
	{
		$project_id = is_object($project_details) ? (int) $project_details->project_id : (int) $project_details;
		if (empty($project_id) || !is_object($project_details)) {
			return array('milestones' => array(), 'status' => array(), 'members' => array());
		}

		$tasks = $this->get_project_tasks_with_meta($project_id);
		$milestones = $this->db->where('project_id', $project_id)->get('tbl_milestones')->result();
		$milestones_map = array();
		if (!empty($milestones)) {
			foreach ($milestones as $milestone) {
				$milestones_map[(int) $milestone->milestones_id] = $milestone;
			}
		}

		$base_group = array(
			array(
				'name' => $project_details->project_name,
				'desc' => '',
				'values' => array(
					array(
						'label' => '',
						'from' => $project_details->start_date,
						'to' => $project_details->end_date,
						'customClass' => 'gantt-headerline',
					),
				),
			),
		);

		return array(
			'milestones' => $this->build_gantt_groups_by_milestone($tasks, $milestones_map, $base_group),
			'status' => $this->build_gantt_groups_by_status($tasks, $base_group, $project_details),
			'members' => $this->build_gantt_groups_by_member($tasks, $project_details, $base_group),
		);
	}

	public function get_project_calendar_events($project_details)
	{
		if (!is_object($project_details) || empty($project_details->project_id)) {
			return array();
		}

		$project_id = (int) $project_details->project_id;
		$events = array();
		$add_event = function ($title, $date, $color, $url, $type = '') use (&$events) {
			$normalized = $this->normalize_calendar_date($date);
			if (empty($normalized)) {
				return;
			}
			$events[] = array(
				'title' => clear_textarea_breaks($title),
				'start' => $normalized,
				'end' => $normalized,
				'color' => $color,
				'url' => $url,
				'type' => $type,
			);
		};

		if (config_item('invoice_on_calendar') == 'on') {
			$invoices = $this->db->select('invoices_id, reference_no, due_date')->where('project_id', $project_id)->get('tbl_invoices')->result();
			foreach ($invoices as $invoice) {
				$add_event($invoice->reference_no, $invoice->due_date, config_item('invoice_color'), base_url() . 'admin/invoice/manage_invoice/invoice_details/' . $invoice->invoices_id, 'invoice');
			}
		}

		if (config_item('estimate_on_calendar') == 'on') {
			$estimates = $this->db->select('estimates_id, reference_no, due_date')->where('project_id', $project_id)->get('tbl_estimates')->result();
			foreach ($estimates as $estimate) {
				$add_event($estimate->reference_no, $estimate->due_date, config_item('estimate_color'), base_url() . 'admin/estimates/create/estimates_details/' . $estimate->estimates_id, 'estimate');
			}
		}

		if (config_item('project_on_calendar') == 'on') {
			$add_event($project_details->project_name, $project_details->end_date, config_item('project_color'), base_url() . 'admin/projects/project_details/' . $project_id, 'project');
		}

		if (config_item('milestone_on_calendar') == 'on') {
			$milestones = $this->db->select('milestones_id, milestone_name, end_date')->where('project_id', $project_id)->get('tbl_milestones')->result();
			foreach ($milestones as $milestone) {
				$add_event($milestone->milestone_name, $milestone->end_date, config_item('milestone_color'), base_url() . 'admin/projects/project_details/' . $project_id . '/milestones', 'milestones');
			}
		}

		if (config_item('tasks_on_calendar') == 'on') {
			$tasks = $this->db->select('task_id, task_name, due_date')->where('project_id', $project_id)->get('tbl_task')->result();
			foreach ($tasks as $task) {
				$add_event($task->task_name, $task->due_date, config_item('tasks_color'), base_url() . 'admin/tasks/details/' . $task->task_id, 'tasks');
			}
		}

		if (config_item('bugs_on_calendar') == 'on') {
			$bugs = $this->db->select('bug_id, bug_title, created_time')->where('project_id', $project_id)->get('tbl_bug')->result();
			foreach ($bugs as $bug) {
				$add_event($bug->bug_title, $bug->created_time, config_item('bugs_color'), base_url() . 'admin/bugs/view_bug_details/' . $bug->bug_id, 'bugs');
			}
		}

		return $events;
	}

	public function get_project_tickets_data($project_id)
	{
		$project_id = (int) $project_id;
		if (empty($project_id)) {
			return array();
		}

		return $this->db
			->select('tbl_tickets.*, tbl_account_details.fullname as reporter_name, tbl_account_details.avatar as reporter_avatar, tbl_departments.deptname as department_name')
			->from('tbl_tickets')
			->join('tbl_account_details', 'tbl_account_details.user_id = tbl_tickets.reporter', 'left')
			->join('tbl_departments', 'tbl_departments.departments_id = tbl_tickets.departments_id', 'left')
			->where('tbl_tickets.project_id', $project_id)
			->order_by('tbl_tickets.tickets_id', 'DESC')
			->get()
			->result();
	}

	public function get_project_bugs_data($project_id)
	{
		$project_id = (int) $project_id;
		if (empty($project_id)) {
			return array();
		}

		return $this->db
			->select('tbl_bug.*, tbl_users.role_id as reporter_role_id, tbl_account_details.fullname as reporter_name')
			->from('tbl_bug')
			->join('tbl_users', 'tbl_users.user_id = tbl_bug.reporter', 'left')
			->join('tbl_account_details', 'tbl_account_details.user_id = tbl_bug.reporter', 'left')
			->where('tbl_bug.project_id', $project_id)
			->order_by('tbl_bug.bug_id', 'DESC')
			->get()
			->result();
	}

	public function get_project_expense_data($project_details, $filter_type = null, $category_id = null)
	{
		$project_id = is_object($project_details) ? (int) $project_details->project_id : (int) $project_details;
		if (empty($project_id)) {
			return array();
		}

		$summary = $this->db
			->select("
				COUNT(*) AS total_records,
				COALESCE(SUM(amount), 0) AS total_expense,
				COALESCE(SUM(CASE WHEN billable = 'Yes' THEN amount ELSE 0 END), 0) AS billable_expense,
				COALESCE(SUM(CASE WHEN billable = 'No' THEN amount ELSE 0 END), 0) AS not_billable_expense
			", false)
			->where(array('project_id' => $project_id, 'type' => 'Expense'))
			->get('tbl_transactions')
			->row();

		$this->db
			->select('tbl_transactions.*, tbl_expense_category.expense_category')
			->from('tbl_transactions')
			->join('tbl_expense_category', 'tbl_expense_category.expense_category_id = tbl_transactions.category_id', 'left')
			->where(array('tbl_transactions.project_id' => $project_id, 'tbl_transactions.type' => 'Expense'));
		if ($filter_type === 'category' && !empty($category_id)) {
			$this->db->where('tbl_transactions.category_id', (int) $category_id);
		}
		$this->db->order_by('tbl_transactions.transactions_id', 'DESC');
		$rows = $this->db->get()->result();

		$categories = $this->db->order_by('expense_category', 'ASC')->get('tbl_expense_category')->result();
		if (!empty($project_details->client_id)) {
			$currency = $this->client_currency_sambol($project_details->client_id);
		} else {
			$currency = $this->db->where('code', config_item('default_currency'))->get('tbl_currencies')->row();
		}

		return array(
			'rows' => $rows,
			'categories' => $categories,
			'currency' => $currency,
			'total_records' => !empty($summary->total_records) ? (int) $summary->total_records : 0,
			'total_expense' => !empty($summary->total_expense) ? (float) $summary->total_expense : 0,
			'billable_expense' => !empty($summary->billable_expense) ? (float) $summary->billable_expense : 0,
			'not_billable_expense' => !empty($summary->not_billable_expense) ? (float) $summary->not_billable_expense : 0,
		);
	}

	public function format_project_task_card($task, $status_key = null)
	{
		$progress = (int) $task->task_progress;
		if ($task->task_status === 'completed') {
			$progress = 100;
		}
		$progress = min(100, max(0, $progress));

		$subtitle_parts = array();
		if (!empty($task->task_start_date)) {
			$subtitle_parts[] = display_date($task->task_start_date);
		}
		if (!empty($task->due_date)) {
			$subtitle_parts[] = display_date($task->due_date);
		}

		return array(
			'id' => (int) $task->task_id,
			'title' => $task->task_name,
			'subtitle' => !empty($subtitle_parts) ? implode(' • ', $subtitle_parts) : '',
			'progress' => $progress,
			'status' => (string) $status_key,
			'status_label' => lang($task->task_status),
			'link' => base_url('admin/tasks/details/' . $task->task_id),
			'comments' => (int) $task->comment_count,
			'attachments' => (int) $task->attachment_count,
		);
	}

	protected function build_gantt_groups_by_milestone(array $tasks, array $milestones_map, array $base_group)
	{
		$grouped = array();
		foreach ($tasks as $task) {
			$key = !empty($task->milestones_id) ? (int) $task->milestones_id : 0;
			$grouped[$key][] = $task;
		}

		$rows = $base_group;
		foreach ($grouped as $milestone_id => $milestone_tasks) {
			$label = lang('uncategorized');
			$start_date = null;
			$end_date = null;
			$custom_class = '';
			if (!empty($milestone_id) && isset($milestones_map[$milestone_id])) {
				$label = $milestones_map[$milestone_id]->milestone_name;
				$start_date = $milestones_map[$milestone_id]->start_date;
				$end_date = $milestones_map[$milestone_id]->end_date;
				$custom_class = 'gantt-timeline';
			}
			$rows[] = $this->make_gantt_group($label, '', $start_date, $end_date, $custom_class);
			foreach ($milestone_tasks as $task) {
				$rows[] = $this->make_gantt_group('', $task->task_name, $this->resolve_task_start($task, $end_date), $this->resolve_task_end($task, $end_date), $this->get_gantt_task_class($task->task_status));
			}
		}

		return $rows;
	}

	protected function build_gantt_groups_by_status(array $tasks, array $base_group, $project_details)
	{
		$task_statuses = array('not_started', 'in_progress', 'completed', 'deferred', 'waiting_for_someone');
		$grouped = array();
		foreach ($task_statuses as $status) {
			$grouped[$status] = array();
		}
		foreach ($tasks as $task) {
			$status = !empty($task->task_status) ? $task->task_status : 'not_started';
			$grouped[$status][] = $task;
		}

		$rows = $base_group;
		foreach ($grouped as $status => $status_tasks) {
			if (empty($status_tasks)) {
				continue;
			}
			$rows[] = $this->make_gantt_group(lang($status), '', $project_details->start_date, $project_details->end_date, 'gantt-timeline');
			foreach ($status_tasks as $task) {
				$rows[] = $this->make_gantt_group('', $task->task_name, $this->resolve_task_start($task), $this->resolve_task_end($task), $this->get_gantt_task_class($task->task_status));
			}
		}

		return $rows;
	}

	protected function build_gantt_groups_by_member(array $tasks, $project_details, array $base_group)
	{
		$admin_users = $this->db->select('user_id')->where('role_id', 1)->get('tbl_users')->result();
		$admin_user_ids = array();
		if (!empty($admin_users)) {
			foreach ($admin_users as $user) {
				$admin_user_ids[] = (int) $user->user_id;
			}
		}

		$project_user_ids = $this->resolve_permission_user_ids($project_details->permission, '57', $admin_user_ids);
		if (empty($project_user_ids)) {
			return array();
		}

		$task_module_user_ids = $admin_user_ids;
		$task_permission_users = $this->all_permission_user('54');
		if (!empty($task_permission_users)) {
			foreach ($task_permission_users as $user) {
				if (!empty($user->user_id)) {
					$task_module_user_ids[] = (int) $user->user_id;
				}
			}
		}
		$task_module_user_ids = array_unique(array_filter($task_module_user_ids));

		$tasks_by_user = array();
		foreach ($tasks as $task) {
			$assigned_user_ids = $this->resolve_permission_user_ids($task->permission, null, $task_module_user_ids);
			foreach ($assigned_user_ids as $user_id) {
				$tasks_by_user[$user_id][] = $task;
			}
		}

		$user_rows = $this->db->select('user_id, fullname')->where_in('user_id', $project_user_ids)->get('tbl_account_details')->result();
		$user_names = array();
		if (!empty($user_rows)) {
			foreach ($user_rows as $user_row) {
				$user_names[(int) $user_row->user_id] = $user_row->fullname;
			}
		}

		$rows = $base_group;
		foreach ($project_user_ids as $user_id) {
			if (empty($tasks_by_user[$user_id])) {
				continue;
			}
			$label = isset($user_names[$user_id]) ? $user_names[$user_id] : lang('undefined_user');
			$rows[] = $this->make_gantt_group($label, '', $project_details->start_date, $project_details->end_date, 'gantt-timeline');
			foreach ($tasks_by_user[$user_id] as $task) {
				$rows[] = $this->make_gantt_group('', $task->task_name, $this->resolve_task_start($task), $this->resolve_task_end($task), $this->get_gantt_task_class($task->task_status));
			}
		}

		return count($rows) > 1 ? $rows : array();
	}

	protected function resolve_permission_user_ids($permission, $menu_id = null, array $default_all_ids = array())
	{
		if ($permission === 'all') {
			$user_ids = $default_all_ids;
			if (!empty($menu_id)) {
				$permission_users = $this->all_permission_user($menu_id);
				if (!empty($permission_users)) {
					foreach ($permission_users as $user) {
						if (!empty($user->user_id)) {
							$user_ids[] = (int) $user->user_id;
						}
					}
				}
			}
			return array_unique(array_filter(array_map('intval', $user_ids)));
		}

		$decoded = !empty($permission) ? json_decode($permission, true) : array();
		if (!is_array($decoded)) {
			return array();
		}
		return array_unique(array_filter(array_map('intval', array_keys($decoded))));
	}

	protected function make_gantt_group($name, $label, $from, $to, $custom_class)
	{
		return array(
			'name' => $name,
			'desc' => $label,
			'values' => array(
				array(
					'label' => $label,
					'from' => $from,
					'to' => $to,
					'customClass' => $custom_class,
				),
			),
		);
	}

	protected function resolve_task_start($task, $fallback = null)
	{
		return !empty($task->task_start_date) ? $task->task_start_date : $fallback;
	}

	protected function resolve_task_end($task, $fallback = null)
	{
		return !empty($task->due_date) ? $task->due_date : $fallback;
	}

	protected function get_gantt_task_class($status)
	{
		if ($status == 'completed') {
			return 'ganttGrey';
		}
		if ($status == 'in_progress') {
			return 'ganttin_progress';
		}
		if ($status == 'not_started') {
			return 'gantt_not_started';
		}
		if ($status == 'deferred') {
			return 'gantt_deferred';
		}
		return 'ganttin_progress';
	}

	protected function normalize_calendar_date($value)
	{
		if (empty($value)) {
			return null;
		}
		$timestamp = safe_strtotime($value);
		if (empty($timestamp)) {
			return null;
		}
		return date('Y-m-d', $timestamp);
	}

	function total_project_hours($project_id, $second = null, $task = null)
	{
		$project_info = $this->db->where('project_id', $project_id)->get('tbl_project')->row();
		$project_time = $this->calculate_total_task_time($project_id);
		$all_tasks = $this->db->where('project_id', $project_id)->get('tbl_task')->result();

		$task_time = 0;
		if (!empty($all_tasks)) {
			foreach ($all_tasks as $v_tasks) {
				if (!empty($v_tasks->billable) && $v_tasks->billable == 'Yes') {
					$task_time += $this->task_spent_time_by_id($v_tasks->task_id);
				}
			}
		}
		$c_logged_time = 0;
		if ($project_info->billing_type == 'project_hours') {
			$c_logged_time = $project_time / 3600;
		}
		if ($project_info->billing_type == 'tasks_hours') {
			$c_logged_time = $task_time / 3600;
		}
		if ($project_info->billing_type == 'tasks_and_project_hours') {
			$c_logged_time = ($task_time + $project_time) / 3600;
		}
		if (!empty($task)) {
			return $logged_time = $task_time;
		}
		if (!empty($second)) {
			$logged_time = $project_time;
		} else {
			$logged_time = $c_logged_time;
		}

		return $logged_time;
	}

	function calculate_total_task_time($project_id)
	{
		$cacheKey = 'project_total_task_time_' . $project_id;
		$cached = $this->cache_get($cacheKey);
		if ($cached !== false) {
			return $cached;
		}

		$total_time = "SELECT start_time,end_time,project_id,
		end_time - start_time time_spent FROM tbl_tasks_timer WHERE project_id = '$project_id'";
		$result = $this->db->query($total_time)->result();

		$time_spent = array();
		foreach ($result as $time) {
			if ($time->start_time != 0 && $time->end_time != 0) {
				$time_spent[] = $time->time_spent;
			}
		}
		if (is_array($time_spent)) {
			$sum = array_sum($time_spent);
		} else {
			$sum = 0;
		}
		$this->cache_save($cacheKey, $sum);
		return $sum;
	}

	function task_spent_time_by_staff($task_id, $user_id)
	{
		$result = $this->db->select("start_time,end_time,project_id,(end_time - start_time) as time_spent")
			->from('tbl_tasks_timer')
			->where(array('task_id' => $task_id, 'user_id' => $user_id))
			->get()
			->result();
		$time_spent = array();
		foreach ($result as $time) {
			if ($time->start_time != 0 && $time->end_time != 0) {
				$time_spent[] = $time->time_spent;
			}
		}
		if (is_array($time_spent)) {
			return array_sum($time_spent);
		} else {
			return 0;
		}
	}

	function project_hours($project_id)
	{
		$task_time = $this->get_sum('tbl_tasks', 'logged_time', array('project' => $project_id));
		$project_time = $this->get_sum('tbl_project', 'time_logged', array('project_id' => $project_id));
		$logged_time = ($task_time + $project_time) / 3600;
		return $logged_time;
	}


	function get_project_progress($id)
	{
		$cacheKey = 'project_progress_' . $id;
		$cached = $this->cache_get($cacheKey);
		if ($cached !== false) {
			return $cached;
		}

		$project_info = $this->check_by(array('project_id' => $id), 'tbl_project');
		if ($project_info->project_status == 'completed') {
			$progress = 100;
		} else {
			if (!empty($project_info->calculate_progress) && $project_info->calculate_progress != '0') {
				if ($project_info->calculate_progress == 'through_project_hours') {
					$estimate_hours = $project_info->estimate_hours;
					$percentage = $this->get_estime_time($estimate_hours);
					if ($percentage != 0) {
						$logged_hour = $this->calculate_project('project_hours', $project_info->project_id);
						if ($percentage != 0) {
							$progress = round(($logged_hour / $percentage) * 100);
						}
					}
				} else {
					$done_task = (int) $this->db->where(array('project_id' => $id, 'task_status' => 'completed'))->count_all_results('tbl_task');
					$total_tasks = (int) $this->db->where(array('project_id' => $id))->count_all_results('tbl_task');
					if ($total_tasks != 0) {
						$progress = round(($done_task / $total_tasks) * 100);
					}
				}
			} else {
				$progress = $project_info->progress;
			}
			if (empty($progress)) {
				$progress = 0;
			} else {
				if ($progress > 100) {
					$progress = 100;
				}
			}
		}

		$this->cache_save($cacheKey, $progress);
		return $progress;
	}

	function set_progress($id)
	{
		$project_info = $this->check_by(array('project_id' => $id), 'tbl_project');

		if (!empty($project_info->calculate_progress) && $project_info->calculate_progress != '0') {
			if ($project_info->calculate_progress == 'through_project_hours') {
				$estimate_hours = $project_info->estimate_hours;
				$percentage = $this->get_estime_time($estimate_hours);
				$logged_hour = $this->calculate_project('project_hours', $project_info->project_id);
			if ($percentage != 0) {
				$progress = round(($logged_hour / $percentage) * 100);
			}
		} else {
			$done_task = (int) $this->db->where(array('project_id' => $id, 'task_status' => 'completed'))->count_all_results('tbl_task');
			$total_tasks = (int) $this->db->where(array('project_id' => $id))->count_all_results('tbl_task');
			if (empty($total_tasks) || empty($done_task)) {
				$progress = 0;
			} else {
				$progress = round(($done_task / $total_tasks) * 100);
				}

				if ($progress > 100) {
					$progress = 100;
				}
			}
		} else {
			$progress = $project_info->progress;
		}
		if (empty($progress)) {
			$progress = 0;
		}
		$p_data = array(
			'progress' => $progress,
		);
		$this->_table_name = "tbl_project"; //table name
		$this->_primary_key = "project_id";
		$this->save($p_data, $id);
	}

	public function get_leads($search_by, $id)
	{
		$all_leads_info = array_reverse($this->get_permission('tbl_leads'));
		if (!empty($search_by) && !empty($id)) {
			$leads = array();
			if (!empty($all_leads_info)) {
				foreach ($all_leads_info as $v_leads) {
					if ($search_by == 'by_status') {
						if ($v_leads->lead_status_id == $id) {
							array_push($leads, $v_leads);
						}
					} else if ($search_by == 'by_source') {
						if ($v_leads->lead_source_id == $id) {
							array_push($leads, $v_leads);
						}
					}
				}
			}
		} else {
			$leads = $all_leads_info;
		}
		return $leads;
	}

	public function make_all_items($project_id, $items_name = null, $tasks = null, $expense = null, $json = null)
	{
		$all_items = array();
		$project_info = $this->check_by(array('project_id' => $project_id), 'tbl_project');
		$project_hours = $this->calculate_project('project_hours', $project_id);
		$project_cost = $this->calculate_project('project_cost', $project_id);
			$project_hours_value = (float) $project_hours;
			$p_hours = floor($project_hours_value);
			$p_minutes = floor(($project_hours_value - $p_hours) * 60);
		if ($p_hours < 9) {
			$p_hours = '0' . $p_hours;
		}
		if ($p_minutes < 9) {
			$p_minutes = '0' . $p_minutes;
		}
		if (!empty($items_name)) {
			if ($items_name == 'single_line') {
				$items['items_id'] = 0;
				$items['saved_items_id'] = 'msp_' . $project_id;
				$items['item_name'] = $project_info->project_name;
				$description = $project_info->project_name . ' - ' . $p_hours . ' : ' . $p_minutes . ' ' . lang('hours') . '&nbsp' . ' ' . '&nbsp';;

				if (!empty($tasks)) {
					foreach ($tasks as $task_id) {
						$all_tasks = $this->db->where('task_id', $task_id)->get('tbl_task')->result();
						if (!empty($all_tasks)) {
							foreach ($all_tasks as $task) {
								$t_seconds = $this->items_model->task_spent_time_by_id($task->task_id);
								$t_init = $t_seconds;
								$t_hours = intdiv((int)$t_init, 3600);
								$t_minutes = intdiv((int)$t_init, 60) % 60;

								if ($t_hours < 9) {
									$t_hours = '0' . $t_hours;
								}
								if ($t_minutes < 9) {
									$t_minutes = '0' . $t_minutes;
								}
								$description .= $task->task_name . ' - ' . $t_hours . ' : ' . $t_minutes . ' ' . lang('hours') . '&nbsp' . ' ' . '&nbsp';;
							}
						}
					}
				};
				$items['item_desc'] = $description;
				$items['quantity'] = 1;
				$items['unit'] = null;
				$items['unit_cost'] = $project_cost;
				$items['rate'] = $project_cost;
				$all_items[] = (object)$items;
			}

			$total_timer = $this->db->where(array('project_id' => $project_info->project_id))->get('tbl_tasks_timer')->result();
			if ($items_name == 'project_timer' || $items_name == 'all_timesheet_individually') {
				if (!empty($total_timer)) {
					foreach ($total_timer as $t_key => $v_timer) {
						$p_timer_info = $this->db->where(array('project_id' => $v_timer->project_id))->get('tbl_project')->row();
						if (!empty($p_timer_info)) {
							$user_info = $this->db->where(array('user_id' => $v_timer->user_id))->get('tbl_users')->row();
							$total_seconds = $v_timer->end_time - $v_timer->start_time;
							$init = $total_seconds;
							$pp_hours = intdiv((int)$init, 3600);
							$pp_minutes = intdiv((int)$init, 60) % 60;
							$qty = round($init / 3600, 2);

							$items['items_id'] = 0;
							$items['saved_items_id'] = 'mti' . $t_key;
							$items['item_name'] = $project_info->project_name . ' ' . lang('by') . ' - ' . $user_info->username;

							if ($pp_hours < 9) {
								$pp_hours = '0' . $pp_hours;
							}
							if ($pp_minutes < 9) {
								$pp_minutes = '0' . $pp_minutes;
							}
							$description = lang('start_date') . ' : ' . display_date($v_timer->start_time) . ' ' . display_time($v_timer->start_time, true) . '&nbsp';
							$description .= lang('end_date') . ' : ' . display_date($v_timer->end_time) . ' ' . display_time($v_timer->end_time, true) . '&nbsp';
							$description .= lang('logged_time') . ' : ' . $pp_hours . ' : ' . $pp_minutes . ' ' . lang('hours') . ' ' . lang('by') . ' - ' . $user_info->username;

							$items['item_desc'] = $description;
							$items['quantity'] = $qty;
							$items['unit'] = null;
							$items['unit_cost'] = $project_info->hourly_rate;
							$items['rate'] = $project_info->hourly_rate;
							$all_items[] = (object)$items;
						}
					}
				}
			}
			if ($items_name == 'all_timesheet_individually') {
				if (!empty($tasks)) {
					foreach ($tasks as $task_id) {
						$total_tasks_timer = $this->db->where(array('task_id' => $task_id))->get('tbl_tasks_timer')->result();

						if (!empty($total_tasks_timer)) {
							foreach ($total_tasks_timer as $t_key => $v_task_timer) {
								$tasks_timer_info = $this->db->where(array('task_id' => $v_task_timer->task_id))->get('tbl_task')->row();
								$user_info = $this->db->where(array('user_id' => $v_task_timer->user_id))->get('tbl_users')->row();
								$tasks_total_seconds = $v_task_timer->end_time - $v_task_timer->start_time;
								$tasks_init = $tasks_total_seconds;
								$tt_hours = intdiv((int)$tasks_init, 3600);
								$tt_minutes = intdiv((int)$tasks_init, 60) % 60;
								$qty = round($tasks_init / 3600, 2);

								$items['items_id'] = 0;
								$items['saved_items_id'] = 'mts' . $t_key;
								$items['item_name'] = $project_info->project_name . ' - ' . $tasks_timer_info->task_name;

								if ($tt_hours < 9) {
									$tt_hours = '0' . $tt_hours;
								}
								if ($tt_minutes < 9) {
									$tt_minutes = '0' . $tt_minutes;
								}
								$description = lang('start_date') . ' : ' . display_date($v_task_timer->start_time) . ' ' . display_time($v_task_timer->start_time, true) . '&nbsp';
								$description .= lang('end_date') . ' : ' . display_date($v_task_timer->end_time) . ' ' . display_time($v_task_timer->end_time, true) . '&nbsp';
								$description .= lang('logged_time') . ' : ' . $tt_hours . ' : ' . $tt_minutes . ' ' . lang('hours') . ' ' . lang('by') . ' - ' . $user_info->username;

								$items['item_desc'] = $description;
								$items['quantity'] = $qty;
								$items['unit'] = null;
								$items['unit_cost'] = $project_info->hourly_rate;
								$items['rate'] = $project_info->hourly_rate;
								$items['task_id'] = $task_id;

								$all_items[] = (object)$items;
							}
						}
					}
				}
			}
		}
		if ($items_name != 'all_timesheet_individually') {
			if (!empty($tasks)) {
				foreach ($tasks as $task_id) {
					$all_tasks = $this->db->where('task_id', $task_id)->get('tbl_task')->result();
					if (!empty($all_tasks)) {
						foreach ($all_tasks as $key => $task) {
							$total_seconds = $this->items_model->task_spent_time_by_id($task->task_id);
							$init = $total_seconds;
							$hours = intdiv((int)$init, 3600);
							$minutes = intdiv((int)$init, 60) % 60;
							$seconds = (int)$init % 60;
							$qty = round($init / 3600, 2);
							$items['items_id'] = 0;
							$items['saved_items_id'] = 'mts' . $key;

							$items['item_name'] = $project_info->project_name . ' - ' . $task->task_name;

							if ($hours < 9) {
								$hours = '0' . $hours;
							}
							if ($minutes < 9) {
								$minutes = '0' . $minutes;
							}
							$description = $hours . ' : ' . $minutes . ' ' . lang('hours');;

							$items['item_desc'] = $description;
							$items['quantity'] = $qty;
							$items['unit'] = null;
							$items['unit_cost'] = $task->hourly_rate;
							$items['rate'] = $task->hourly_rate;
							$items['task_id'] = $task_id;

							$all_items[] = (object)$items;
						}
					}
				}
			}
		}
		if (!empty($expense)) {
			foreach ($expense as $key => $expense_id) {
				$expense_info = $this->db->where('transactions_id', $expense_id)->get('tbl_transactions')->row();
				$category_info = $this->db->where('expense_category_id', $expense_info->category_id)->get('tbl_expense_category')->row();
				if (!empty($category_info)) {
					$category = $category_info->expense_category;
				} else {
					$category = 'Undefined Category';
				}

				$items['saved_items_id'] = 'mex' . $key;
				$items['items_id'] = 0;
				$items['item_name'] = '[ ' . lang('expense') . ' ] - ' . $category . ' - ' . $expense_info->name;;
				if (!empty($category_info->description)) {
					$description = '[ ' . lang('projects') . ' ] - ' . $project_info->project_name . ' - ' . $category_info->description;
				} else {
					$description = '[ ' . lang('projects') . ' ] - ' . $project_info->project_name;
				}
				$items['item_desc'] = $description;
				$items['quantity'] = 1;
				$items['unit'] = null;
				$items['unit_cost'] = $expense_info->amount;
				$items['rate'] = $expense_info->amount;

				$all_items[] = (object)$items;
			}
		}

		if (!empty($json)) {
			return json_encode($all_items);
		}

		return $all_items;
	}


	public function get_itemlist($group_id = null)
	{
		if (!empty($group_id)) {
			$result =  get_order_by('tbl_saved_items', array('customer_group_id' => $group_id), 'order', true);
		} else {
			$result = get_order_by('tbl_saved_items', array('status' => 'active'), 'order', true);
		}
		return $result;
	}

	public function get_goal_tracking($id)
	{
		$all_goal_tracking = array_reverse($this->get_permission('tbl_goal_tracking'));
		$goal_tracking = array();
		if (!empty($id)) {
			foreach ($all_goal_tracking as $v_goal_tracking) {
				if ($v_goal_tracking->goal_type_id == $id) {
					array_push($goal_tracking, $v_goal_tracking);
				}
			}
		} else {
			$goal_tracking = $all_goal_tracking;
		}
		return $goal_tracking;
	}


	public function generate_projects_number()
	{
		// Start number fallback
		$startNo = (int) config_item('projects_start_no');
		if ($startNo <= 0) {
			$startNo = 1;
		}

		$lastNumber = 0;

		// Get last project_no
		$row = $this->db
			->select('project_no')
			->from('tbl_project')
			->order_by('project_id', 'DESC')
			->limit(1)
			->get()
			->row();

		if ($row && !empty($row->project_no)) {

			// Extract numeric part from the end of project_no
			preg_match('/(\d+)$/', $row->project_no, $matches);

			if (!empty($matches[1])) {
				$lastNumber = (int) $matches[1];
			}
		}

		// Calculate next number
		$nextNumber = max($lastNumber + 1, $startNo);

		// Ensure number does not already exist
		$nextNumber = $this->project_no_exists($nextNumber);

		// Pad number (0001, 0002 ...)
		$numberPadded = str_pad($nextNumber, 4, '0', STR_PAD_LEFT);

		// Apply format if defined
		if (!empty(config_item('projects_number_format'))) {

			$format = config_item('projects_number_format');

			$replacements = [
				'[yyyy]'   => date('Y'),
				'[yy]'     => date('y'),
				'[mm]'     => date('M'),
				'[m]'      => date('m'),
				'[dd]'     => date('d'),
				'[number]' => $numberPadded,
			];

			// Prefix support
			if (!empty(config_item('projects_prefix'))) {
				$replacements['[' . config_item('projects_prefix') . ']']
					= config_item('projects_prefix');
			}

			$projectNo = str_replace(
				array_keys($replacements),
				array_values($replacements),
				$format
			);
		} else {
			// No format, just number
			$projectNo = $numberPadded;
		}

		return $projectNo;
	}

	public function project_no_exists($next_number)
	{
		$enext_number = sprintf('%04d', $next_number);
		if (!empty(config_item('projects_number_format'))) {
			$projects_format = config_item('projects_number_format');
			$projects_prefix = str_replace("[" . config_item('projects_prefix') . "]", config_item('projects_prefix'), $projects_format);
			$yyyy = str_replace("[yyyy]", date('Y'), $projects_prefix);
			$yy = str_replace("[yy]", date('y'), $yyyy);
			$mm = str_replace("[mm]", date('M'), $yy);
			$m = str_replace("[m]", date('m'), $mm);
			$dd = str_replace("[dd]", date('d'), $m);
			$enext_number = str_replace("[number]", $next_number, $dd);
		}
		$records = $this->db->where('project_no', $enext_number)->get('tbl_project')->num_rows();
		if ($records > 0) {
			return $this->project_no_exists($next_number + 1);
		} else {
			return $next_number;
		}
	}


	function calculate_to($invoice_value, $invoice_id)
	{
		switch ($invoice_value) {
			case 'invoice_cost':
				return $this->get_invoice_cost($invoice_id);
				break;
			case 'tax':
				return $this->get_invoice_tax_amount($invoice_id);
				break;
			case 'discount':
				return $this->get_invoice_discount($invoice_id);
				break;
			case 'paid_amount':
				return $this->get_invoice_paid_amount($invoice_id);
				break;
			case 'invoice_due':
				return $this->get_invoice_due_amount($invoice_id);
				break;
			case 'total':
				return $this->get_invoice_total_amount($invoice_id);
				break;
		}
	}
	function transfer_calculate_to($value, $transfer_item_id)
	{
		switch ($value) {
			case 'transfer_cost':
				return $this->get_transfer_cost($transfer_item_id);
				break;
			case 'tax':
				return $this->get_transfer_tax_amount($transfer_item_id);
				break;
			case 'discount':
				return $this->get_transfer_discount($transfer_item_id);
				break;
			case 'paid_amount':
				return $this->get_transfer_paid_amount($transfer_item_id);
				break;
			case 'purchase_due':
				return $this->get_transfer_due_amount($transfer_item_id);
				break;
			case 'total':
				return $this->get_transfer_total_amount($transfer_item_id);
				break;
		}
	}
	function get_transfer_cost($transfer_item_id)
	{
		$this->db->select_sum('total_cost');
		$this->db->where('transfer_item_id', $transfer_item_id);
		$this->db->from('tbl_transfer_itemlist');
		$query_result = $this->db->get();
		$cost = $query_result->row();
		if (!empty($cost->total_cost)) {
			$result = $cost->total_cost;
		} else {
			$result = '0';
		}
		return $result;
	}
	public function get_transfer_total_amount($transfer_item_id)
	{

		$purchase_info = $this->check_by(array('transfer_item_id' => $transfer_item_id), 'tbl_transfer_itemlist');
		$tax = $this->get_transfer_tax_amount($transfer_item_id);
		$purchase_cost = $this->get_transfer_cost($transfer_item_id);
		//        $payment_made = $this->get_purchase_paid_amount($purchase_id);

		$total_amount = $purchase_cost + $tax;
		if ($total_amount <= 0) {
			$total_amount = 0;
		}
		return $total_amount;
	}
	public function get_transfer_tax_amount($transfer_item_id)
	{
		$purchase_info = $this->check_by(array('transfer_item_id' => $transfer_item_id), 'tbl_transfer_itemlist');
		if (!empty($purchase_info->total_tax)) {
			$tax_info = json_decode($purchase_info->total_tax);
		}
		$tax = 0;
		if (!empty($tax_info)) {
			$total_tax = $tax_info->total_tax;
			if (!empty($total_tax)) {
				foreach ($total_tax as $t_key => $v_tax_info) {
					$tax += $v_tax_info;
				}
			}
		}
		return $tax;
	}

	function ordered_items_by_id($id, $json = null)
	{
		$rows = $this->db->where('transfer_item_id', $id)->order_by('order', 'asc')->get('tbl_transfer_itemlist')->result();
		if (!empty($json)) {
			if (!empty($rows)) {
				foreach ($rows as $row) {
					$row->items_id = $row->transfer_itemList_id;
					$row->qty = $row->quantity;
					$row->rate = $row->unit_cost;
					$row->cost_price = $row->unit_cost;
					$row->new_itmes_id = $row->saved_items_id;
					$row->taxname = json_decode($row->item_tax_name);
					$pr[] = $row;
				}
				return json_encode($pr);
			}
		} else {
			return $rows;
		}
	}

	public function get_statuses()
	{
		$statuses = array(
			array(
				'id' => 1,
				'value' => 'started',
				'name' => lang('started'),
				'class' => 'bg-slate-100 text-slate-700',
				'order' => 1,
			),
			array(
				'id' => 2,
				'value' => 'in_progress',
				'name' => lang('in_progress'),
				'class' => 'bg-blue-100 text-blue-700',
				'order' => 2,
			),
			array(
				'id' => 3,
				'value' => 'completed',
				'name' => lang('completed'),
				'class' => 'bg-emerald-100 text-emerald-700',
				'order' => 3,
			),
			array(
				'id' => 4,
				'value' => 'cancel',
				'name' => lang('cancel'),
				'class' => 'bg-rose-100 text-rose-700',
				'order' => 4,
			),
			array(
				'id' => 5,
				'value' => 'on_hold',
				'name' => lang('on_hold'),
				'class' => 'bg-amber-100 text-amber-700',
				'order' => 5,
			)
		);
		return apply_filters('project_statuses', $statuses);
	}
}
