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

class DashboardModel extends CI_Model
{

    public function __construct()
    {
        parent::__construct();
        $this->load->database();
    }

    public function incomeByDate($last_date)
    {
        $sql = 'SELECT SUM(amount) as amount FROM ab_receipts WHERE Date(created_date)= "' . $last_date . '" ';
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function expenseByDate($last_date='',$status='')
    {
        $sql = 'SELECT SUM(amount) as amount FROM ab_expenses WHERE Date(created_date)= "' . $last_date . '" ';
        if (isset($status) && !empty($status)) {
            $sql .= 'AND status = "'.$status.'"';
        }
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function incomeByMonths($firstDate, $lastDate)
    {
        $sql = 'SELECT SUM(amount) as amount FROM ab_receipts WHERE Date(created_date) BETWEEN "' . $firstDate . '" AND "' . $lastDate . '" ';
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function expenseByMonths($firstDate = '', $lastDate = '',$status = '')
    {
        $sql = 'SELECT SUM(amount) as amount FROM ab_expenses WHERE Date(created_date) BETWEEN "' . $firstDate . '" AND "' . $lastDate . '" ';
        if (isset($status) && !empty($status)) {
            $sql .= 'AND status = "'.$status.'"';
        }
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function overduePayments()
    {
        $today = date('Y/m/d');
        $sql = 'SELECT * FROM ab_invoice_header WHERE Date(valid_until_date) < "' . $today . '" AND record_type = "invoice" AND status = "approved" AND (payment_status = "partially" OR payment_status = "not paid")';
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function getTopCustomers($limit="",$status='')
    {
        $limit = (!empty($limit)) ? $limit : 10;
        $sql = 'SELECT customer_id, customer_name, SUM(grossAmount) AS gross_amount, SUM(pending_amount) AS total_pending_amount  FROM ab_invoice_header WHERE record_type = "invoice" ';
        if (isset($status) && !empty($status)) {
            $sql .= " AND status = '".$status."'"; 
        }
        $sql .=' GROUP BY customer_id ORDER BY gross_amount DESC LIMIT '.$limit.' ';
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function getOutStading()
    {
        $limit = (!empty($limit)) ? $limit : 10;
        $sql = 'SELECT customer_id, customer_name, SUM(grossAmount) AS gross_amount, SUM(pending_amount) AS total_pending_amount  FROM ab_invoice_header WHERE record_type = "invoice" ';
        if (isset($status) && !empty($status)) {
            $sql .= " AND status = 'approved'"; 
        }
        $sql .=' GROUP BY customer_id ORDER BY gross_amount DESC';
        // $sql = "SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));";
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    //LEADS API MODEL
    public function getLeadsByCategory($category)
    {
        $sql = 'SELECT COUNT(customer_id) as amount FROM ab_customer WHERE stages = "' . $category . '" AND status="active" AND type="lead" ';
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function getLeadsBySources($category)
    {
        $sql = 'SELECT COUNT(customer_id) as amount FROM ab_customer WHERE lead_source = "' . $category . '" AND status="active" AND type="lead" ';
        $query = $this->db->query($sql);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    public function getUnattendedList($where = array())
    {
        $sql = "SELECT * FROM ab_customer WHERE last_activity_type IS NULL";
        if ((isset($where['startDate']) && !empty($where['startDate'])) && (isset($where['endDate']) && !empty($where['endDate']))) {
            $sql .= " AND Date(last_activity_date) BETWEEN " . $where['startDate'] . " AND " . $where['endDate'] . "";
        }
        $query = $this->db->query($sql);
        $sqlerror = $this->db->error();
        $this->errorlogs->checkDBError($sqlerror, 'SQL Error', dirname(__FILE__), __LINE__, __METHOD__);
        return $query->result_array();
    }
    public function getPendingFollowList()
    {
        $currentDate = date('y-m-d');
        $sql = "SELECT ab_tasks.*, cs.name AS customer_id, a.name AS assignee
        FROM ab_tasks
        LEFT JOIN ab_customer AS cs ON ab_tasks.customer_id = cs.customer_id
        LEFT JOIN ab_admin AS a ON ab_tasks.assignee = a.adminID
        WHERE (ab_tasks.start_date <=> ? OR ab_tasks.start_date IS NULL)
            AND (ab_tasks.due_date <= ? OR ab_tasks.due_date IS NOT NULL)
            AND (ab_tasks.task_status IN ('pending', 'In Progress'))";
        $query = $this->db->query($sql, array($currentDate, $currentDate));
        $sqlerror = $this->db->error();
        $this->errorlogs->checkDBError($sqlerror, 'SQL Error', dirname(__FILE__), __LINE__, __METHOD__);
        return $query->result_array();
    }
    public function getUpcomingFollowList()
    {
        $currentDate = date('y-m-d');
        $sql = "SELECT ab_tasks.*,cs.name AS customer_id, a.name AS assignee
        FROM ab_tasks
        LEFT JOIN ab_customer AS cs ON ab_tasks.customer_id = cs.customer_id
        LEFT JOIN ab_admin AS a ON ab_tasks.assignee = a.adminID
        WHERE ( start_date <=> ? OR start_date IS NULL)
            AND (due_date >= ? OR due_date IS NULL)
            AND (task_status IN ('pending', 'In Progress'))";
        $query = $this->db->query($sql, array($currentDate, $currentDate));
        $sqlerror = $this->db->error();
        $this->errorlogs->checkDBError($sqlerror, 'SQL Error', dirname(__FILE__), __LINE__, __METHOD__);
        return $query->result_array();
    }
    //TASK API MODEL
    public function getTasksCount($filters = [], $other = [])
    {
        $sql = 'SELECT COUNT(task_id) as amount FROM ab_tasks WHERE status="active" AND record_type="task"';
        $params = [];
        foreach ($filters as $key => $value) {
            $sql .= " AND {$key} = ?";
            $params[] = $value;
        }
        $query = $this->db->query($sql, $params);
        if (isset($other["resultType"]) && !empty($other["resultType"])) {
            $result = $query->result_array();
        } else {
            $result = $query->result();
        }
        return $result;
    }
    // HELPERS
    public function getCategoryBySlug($slug)
    {
        $sql = 'SELECT category_id, slug, categoryName,cat_color
        FROM ab_categories
        WHERE parent_id = (
            SELECT category_id
            FROM ab_categories
            WHERE slug = "' . $slug . '"
        ) AND status = "active" ORDER BY categories_index ASC';

        $query = $this->db->query($sql, array($slug));
        $sqlerror = $this->db->error();
        $this->errorlogs->checkDBError($sqlerror, 'SQL Error', dirname(__FILE__), __LINE__, __METHOD__);
        $result = $query->result();
        return $result;
    }
    public function getCategorySlug($slug)
    {
        $sql = 'SELECT category_id, slug, categoryName,cat_color
        FROM ab_categories
        WHERE slug = "' . $slug . '" AND status = "active" ';

        $query = $this->db->query($sql, array($slug));
        $sqlerror = $this->db->error();
        $this->errorlogs->checkDBError($sqlerror, 'SQL Error', dirname(__FILE__), __LINE__, __METHOD__);
        $result = $query->result();
        if (!empty($result)) {
            return $result[0]->category_id;
        }else{
            return 0 ;
        }
    }
    public function getFinanceModuleData($select = '', $table = '', $where = array(), $limit = '', $start = '', $join = array(), $other = array())	{
		if ($select == '') {
			$select = "*";
		}
		$whereStr = "";
		$limitstr = "";
		$groupBy = ''; 
		$freeTextSearch = '';
		if (isset($other['freeTextSearch']) && !empty($other['freeTextSearch'])) {
			$freeTextSearch = $other['freeTextSearch'];
		}
		foreach ($where as $key => $value) {
			if ($whereStr == "")
				$whereStr .= $key . " " . $value . " ";
			else
				$whereStr .= " AND " . $key . " " . $value . " ";
		}
		if (isset($other['whereOR']) && !empty($other['whereOR'])) {
			foreach ($other['whereOR'] as $key => $value) {
				if ($whereStr == ""){
					$whereStr .= $key . " " . $value . " ";
				}else{		
					if (!strpos($whereStr,'AND')) {
						$whereStr .= " AND " . $key . " " . $value . " ";
					}else{
						$whereStr .= " OR " . $key . " " . $value . " ";
					}
				}		
			}
		}
		// print_r($whereStr);exit;
		if (isset($other['find_in_set']) && !empty($other['find_in_set'])) {
			for ($i=0; $i < count($other['find_in_set']); $i++) { 
				$whereStrFindSet="";
				foreach ($other['find_in_set'][$i] as $key => $value) {
					if ($whereStrFindSet == "")
						$whereStrFindSet .= "FIND_IN_SET('".$value."',REPLACE(".$other['find_in_set_key'][$i].",' ','')) > 0";
					else
						$whereStrFindSet .= " ".$other['find_in_set_type'][$i]." FIND_IN_SET('".$value."',REPLACE(".$other['find_in_set_key'][$i].",' ','')) > 0 ";
				}
				if ($whereStr != ""){
					$whereStr .= "AND ( ";
				}else{
					$whereStr .= " ( ";
				}
				$whereStr .= $whereStrFindSet.")";
			}
		}
		// change for all record. For linking to other form need all records. so skip pagination.
		if ($start != '' && $limit != '') {
			$limitstr = "LIMIT " . $start . "," . $limit;
		} else {
			if (isset($limit) && !empty($limit)) {
				$limitstr = "LIMIT 0," . $limit;
			} else {
				$limitstr = "";
			}
		}
		if (trim($whereStr) != '') {
			$whereStr = " WHERE " . $whereStr;
		} else {
			$whereStr = "";
		}
		if (isset($other['whereIn']) && !empty($other['whereIn'])) {
			if (trim($whereStr) == "")
				$whereStr .= " WHERE " . $other['whereIn'] . " IN (" . $other['whereData'] . ") ";
			else
				$whereStr .= " AND " . $other['whereIn'] . " IN (" . $other['whereData'] . ") ";
		}
		if (isset($other['orderBy']) && !empty($other['orderBy'])) {
			$orderBy = "ORDER BY " . $other['orderBy'] . " " . $other['order'];
		} else {
			$orderBy = "";
		}
		if (isset($other['groupBy']) && !empty($other['groupBy'])) {
			$groupBy = "GROUP BY " . $other['groupBy'];
		} else {
			$groupBy = "";
		}
		$joinsql = '';
		if (isset($join) && !empty($join)) {
			foreach ($join as $key => $value) {
				if (isset($value['key1Alias']) && !empty($value['key1Alias'])) {
					$joinsql .= " " . $value['type'] . " " . $this->db->dbprefix . $value['table'] . " as " . $value['alias'] . " ON " . $value['key1Alias'] . "." . $value['key1'] . " = " . $value['alias'] . "." . $value['key2'];
				} else {
					$joinsql .= " " . $value['type'] . " " . $this->db->dbprefix . $value['table'] . " as " . $value['alias'] . " ON t." . $value['key1'] . " = " . $value['alias'] . "." . $value['key2'];
				}
			}
		} else {
			$joinsql = "";
		}
		if ($freeTextSearch != '') {
			$tableName = $this->db->dbprefix . $table;
			$fields = $this->db->field_data($tableName);
			$this->db->from($tableName);
			$searchStr = '';
			// FOR SELF TABLE FIELDS
			foreach ($fields as $field) {	
				$searchTerm = '%' . $this->db->escape_like_str($freeTextSearch) . '%' ;
				if ($searchStr == "") {
					if ($whereStr == '') {
						$searchStr .= ' WHERE t.'.$this->db->protect_identifiers($field->name) . " LIKE " . $this->db->escape($searchTerm);
					}else{
						$searchStr .= ' t.'.$this->db->protect_identifiers($field->name) . " LIKE " . $this->db->escape($searchTerm);
					}
				} else {
					$searchStr .= " OR t." . $this->db->protect_identifiers($field->name) . " LIKE " . $this->db->escape($searchTerm);
				}
			}
			// FOR JOINED TABLE FIELDS
			if (isset($join) && !empty($join)) {
				foreach ($join as $joinTable) {
					if ($searchStr != "") {
						$searchStr .= " OR ";
					}
					$searchStr .= $joinTable['alias']. '.' . $joinTable['column'] . " LIKE " . $this->db->escape($searchTerm);
				}
			}
			($whereStr == '') ? $whereStr .= $searchStr : $whereStr .= ' AND ('.$searchStr.') ';
			$sql = "SELECT " . $select . " FROM " . $this->db->dbprefix . "{$table} as t " . $joinsql . $whereStr . " " . $orderBy . " " . $limitstr;
			$query = $this->db->query($sql);
		}else{
			$sql = "SELECT " . $select . " FROM " . $this->db->dbprefix . "{$table} as t " . $joinsql . $whereStr . " " . $groupBy . " " . $orderBy ." ". $limitstr;
			$query = $this->db->query($sql);
		}
		$sqlerror = $this->db->error();
		$this->errorlogs->checkDBError($sqlerror,'SQL Error', dirname(__FILE__), __LINE__, __METHOD__);

		if (isset($other["resultType"]) && !empty($other["resultType"])) {
			$result = $query->result_array();
		} else {
			$result = $query->result();
		}
		return $result[0];
	}
}
