<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * Memento admin_model model
 *
 * This class handles admin_model management related functionality
 *
 * @package		Admin
 * @subpackage	admin_model
 * @author		propertyjar
 * @link		#
 */

class reports_model extends CI_Model 
{
	function __construct()
	{
		parent::__construct();
		$this->load->database();
	}
	
	function save($save)
	{
		$this->db->insert('acts',$save);
	}
	
	function get_earning_by_month()
	{
	$y= date("Y");
	$m= date("m");
	$d=@date('t', mktime(0,0,0,$m,1,$y));
	
				   $this->db->where('date >=',date("Y-m-d", strtotime("-".$d." days")));
				   $this->db->group_by('date', 'ASC');
				   $this->db->select('date');
				   $this->db->select_sum('amount');
			return $this->db->get('fees')->result();
	}
	
	function get_earning_by_week()
	{
	
				   $this->db->where('date >=',date("Y-m-d", strtotime("- 7 days")));
				   $this->db->group_by('date', 'ASC');
				   $this->db->select('date');
				   $this->db->select_sum('amount');
			return $this->db->get('fees')->result();
	}
	
	function get_earning_by_year()
	{
	
				   $this->db->group_by('YEAR(date)');
				   $this->db->select('date');
				   $this->db->select_sum('amount');
			return $this->db->get('fees')->result();
	}
	
	function get_earning_by_client()
	{
	
				   $this->db->group_by('C.client_id');
				   $this->db->select('date,U.name');
				   $this->db->select_sum('amount');
				   $this->db->join('cases C', 'C.id = F.case_id', 'LEFT');
				   $this->db->join('users U', 'U.id = C.client_id', 'LEFT');
			return $this->db->get('fees F')->result();
	}
	
	


    // Gráfico financeiro por mês
    function get_financeiro_by_month($client_id = null)
    {
        $sql = "SELECT 
            DATE_FORMAT(vencimento, '%m/%Y') AS mes_label,
            SUM(CASE WHEN tipo='receber' AND status='pago' THEN valor ELSE 0 END) AS recebido,
            SUM(CASE WHEN tipo='pagar' AND status='pago' THEN valor ELSE 0 END) AS pago,
            SUM(CASE WHEN tipo='receber' AND status='pendente' THEN valor ELSE 0 END) AS a_receber
            FROM fin_lancamentos
            WHERE vencimento >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)".($client_id ? " AND client_id=".(int)$client_id : "")."
            GROUP BY DATE_FORMAT(vencimento, '%Y-%m')
            ORDER BY MIN(vencimento) ASC";
        return $this->db->query($sql)->result();
    }

    // Financeiro por cliente
    function get_financeiro_by_client()
    {
        $sql = "SELECT u.name, 
            SUM(CASE WHEN l.tipo='receber' AND l.status='pago' THEN l.valor ELSE 0 END) AS recebido,
            SUM(CASE WHEN l.tipo='receber' AND l.status='pendente' THEN l.valor ELSE 0 END) AS pendente
            FROM fin_lancamentos l
            LEFT JOIN users u ON u.id = l.client_id
            WHERE l.client_id IS NOT NULL
            GROUP BY l.client_id
            ORDER BY recebido DESC
            LIMIT 10";
        return $this->db->query($sql)->result();
    }

}