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

class Gestaoresultados_model extends CI_Model
{

    function __construct()
    {
        parent::__construct();
    }


    function get($table, $fields, $where = '', $perpage = 0, $start = 0, $one = false, $array = 'array')
    {

        $this->db->select($fields);
        $this->db->from($table);
        $this->db->limit($perpage, $start);
        $this->db->order_by('idAtestadoMedico', 'desc');
        $this->db->join('morador', 'atestadomedico.morador_id = morador.idMorador');
        //$this->db->join('item as imoveis', 'imoveis.item_id = tb003compromissos.item_id', "inner");


        if ($where) {
            $this->db->where($where);
        }

        $query = $this->db->get();

        $result = !$one ? $query->result() : $query->row();
        return $result;
    }

    function getById($id)
    {
        //$this->db->join('fotos','fotos.membro_id = membros.idMembro');
        $this->db->where('idMorador', $id);
        $this->db->limit(1);
        return $this->db->get('morador')->row();
    }


    function add($table, $data, $returnId = false)
    {
        $this->db->insert($table, $data);
        if ($this->db->affected_rows() == '1') {
            if ($returnId == true) {
                return $this->db->insert_id($table);
            }
            return TRUE;
        }

        return FALSE;
    }


    function edit($table, $data, $fieldID, $ID)
    {
        $this->db->where($fieldID, $ID);
        $this->db->update($table, $data);

        if ($this->db->affected_rows() >= 0) {
            return TRUE;
        }

        return FALSE;
    }

    function delete($table, $fieldID, $ID)
    {
        $this->db->where($fieldID, $ID);
        $this->db->delete($table);
        if ($this->db->affected_rows() == '1') {
            return TRUE;
        }

        return FALSE;
    }

    function count($table)
    {
        return $this->db->count_all($table);
    }


    function graficoMaioresDespesas()
    {
        $this->db->select('descricao, valor');
        $this->db->from('lancamentos');
        $this->db->where('month(data_vencimento) = MONTH(now())');
        $this->db->order_by('valor', 'desc');
        $this->db->limit(5);
        return $this->db->get()->result();
    }

    public function getGastosGeralPorCategoria($categoria)
    {
        $this->db->select('valor,data_vencimento, categoria, descricao');
        $this->db->where('categoria', $categoria);
        $this->db->from('lancamentos');
        $this->db->order_by('data_vencimento', 'desc');
        $this->db->order_by('descricao');
        $this->db->limit(6);
        return $this->db->get()->result();
    }

    public function getVendasValorPorVendedor($where){
        $P_Dia = date("Y-01-01");
        $U_Dia = date("Y-12-31");
        
        $ultimo_ano = 'DT_NOTA BETWEEN "'.$P_Dia.'" AND "'.$U_Dia.'"';
        $ultimos30_dias = 'DT_NOTA BETWEEN DATE_ADD(now(), INTERVAL -30 DAY) AND now()';
        $ultimos07_dias = 'DT_NOTA BETWEEN DATE_ADD(now(), INTERVAL -12 MONTH) AND now()';
        $diaAtual = 'DT_NOTA = CURDATE()';


        $this->db->select('NM_VENDEDOR, SUM(VL_PEDIDO) AS qtdeTipo');
		$this->db->where("EXTRACT(MONTH from `DT_NOTA`) = MONTH(CURDATE())");
//		$this->db->where("EXTRACT(YEAR from `DT_NOTA`) = YEAR(CURDATE())");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('NM_VENDEDOR');
        $this->db->order_by('NM_VENDEDOR', 'desc');
//        $this->db->limit(6);
        return $this->db->get()->result();
    }

    public function getVendasValorPorVendedorPorMes($mesConsulta){

        $this->db->select('NM_VENDEDOR, SUM(VL_PEDIDO) AS qtdeTipo');
        $this->db->where("EXTRACT(MONTH from `DT_NOTA`) = MONTH('".$mesConsulta."')");
		$this->db->where("EXTRACT(YEAR from `DT_NOTA`) = YEAR(CURDATE())");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('NM_VENDEDOR');
        $this->db->order_by('NM_VENDEDOR', 'desc');
        return $this->db->get()->result();
    }

    public function getVendasValorDiariasPorMes($mesConsulta){


        $this->db->select("`DT_NOTA`  AS Periodo,
                                       SUM(tbl_002_notas.VL_PEDIDO) AS totalPreventiva,
                                       count(tbl_002_notas.ID_NOTA) AS totalCorretiva");
        $this->db->from('tbl_002_notas');

        $this->db->where("EXTRACT(MONTH from `DT_NOTA`) = MONTH('".$mesConsulta."')");
        $this->db->where("EXTRACT(YEAR from `DT_NOTA`) = YEAR(CURDATE())");

        $this->db->group_by('YEAR(tbl_002_notas.DT_NOTA), MONTH(tbl_002_notas.DT_NOTA), DAY(tbl_002_notas.DT_NOTA)');
        $this->db->order_by('YEAR(tbl_002_notas.DT_NOTA) ASC, MONTH(tbl_002_notas.DT_NOTA) ASC, DAY(tbl_002_notas.DT_NOTA) ASC');
//        $this->db->order_by('DAY( DT_NOTA)', 'asc');
        $this->db->limit(26);
//
        return $this->db->get()->result();


    }



    public function getProdutosMaisVendidos($where)
    {
        $this->db->select('NM_PRODUTO, count(NM_PRODUTO) AS qtdeTipo');
        $this->db->group_by('NM_PRODUTO');
        $this->db->where($where);
        $this->db->order_by('qtdeTipo', 'desc');
        $this->db->limit(10);
        return $this->db->get('tbl_002_notas')->result();
    }

    public function getPecasMaisUsadas()
    {
        $this->db->select('DS_DESCRICAO_PECA, count(DS_DESCRICAO_PECA) AS qtdeTipo');
        $this->db->join('tbl_012_pecas as pecas', 'pecas.ID_PECA = pecaspreventiva.PECA_ID', 'left');
        $this->db->group_by('DS_DESCRICAO_PECA');
        $this->db->order_by('qtdeTipo', 'desc');
        $this->db->limit(10);

        return $this->db->get('tbl_rel_003_pecas_por_preventiva as pecaspreventiva')->result();
    }

    public function getGastosMensalPorManutencoes()
    {
        $this->db->select("CONCAT( YEAR( DT_NOTA ), '-', MONTH( DT_NOTA ) ) AS Periodo, 
                                    SUM(VL_PEDIDO) AS totalPreventiva 
                                    ,count(ID_NOTA) AS totalCorretiva");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('YEAR( DT_NOTA ), MONTH( DT_NOTA )');
        $this->db->order_by('YEAR( DT_NOTA )', 'desc');
        $this->db->order_by('MONTH( DT_NOTA)', 'desc');
        $this->db->limit(12);
//
        return $this->db->get()->result();
    }
    
        public function getGastosMensalPorManutencoesAnoCorrente()
    {
        $this->db->select("CONCAT( YEAR( DT_NOTA ), '-', MONTH( DT_NOTA ) ) AS Periodo, 
                                    SUM(VL_PEDIDO) AS totalPreventiva 
                                    ,count(ID_NOTA) AS totalCorretiva");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('YEAR( DT_NOTA ), MONTH( DT_NOTA )');
        $this->db->order_by('YEAR( DT_NOTA )', 'desc');
        $this->db->order_by('MONTH( DT_NOTA)', 'desc');
        $this->db->where('YEAR(DT_NOTA) = YEAR(CURDATE())');
        $this->db->limit(12);
//
        return $this->db->get()->result();
    }
    
        public function getGastosMensalPorNotas($mesSelecionado = 2) {

        $this->db->select("
        EXTRACT(DAY from `DT_NOTA`) as Dia, 
        EXTRACT(MONTH from `DT_NOTA`) as Mes, 
        SUM(VL_PEDIDO) AS totalPreventiva 
        ,count(ID_NOTA) AS totalCorretiva"
		);
		
        $this->db->from('tbl_002_notas');
		
		$this->db->where("EXTRACT(MONTH from `DT_NOTA`) = MONTH(CURDATE())");
		$this->db->where("EXTRACT(YEAR from `DT_NOTA`) = YEAR(CURDATE())");
		
		
        $this->db->group_by("Dia");
		$this->db->group_by("Mes");
		$this->db->group_by("YEAR(`DT_NOTA`)");
		
		$this->db->order_by("Dia");
		$this->db->order_by("Mes");
		$this->db->order_by("YEAR(`DT_NOTA`)");
//
        return $this->db->get()->result();
    }

    public function getComparacaoVendasPorAnos($mesSelecionado = 2) {
        $this->db->select("
           EXTRACT(MONTH from `DT_NOTA`) as Mes,
           EXTRACT(YEAR from `DT_NOTA`)  as Ano,
           SUM(VL_PEDIDO)                AS totalPreventiva,
           count(ID_NOTA)                AS totalCorretiva
        "
        );

        $this->db->from('tbl_002_notas');

        $this->db->where("EXTRACT(YEAR from `DT_NOTA`) IN (2022, 2023)");


        $this->db->group_by("Mes");
        $this->db->group_by("Ano");

        $this->db->order_by("Mes");
        $this->db->order_by("Ano");
//
        return $this->db->get()->result();
    }



    public function getGastosDiarioPorManutencoes()
    {
        $this->db->select("CONCAT( DAY( DT_NOTA), '/', MONTH( CURDATE() )   ) AS Periodo, 
                                    SUM(VL_PEDIDO) AS totalPreventiva 
                                    ,count(ID_NOTA) AS totalCorretiva");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('MONTH( DT_NOTA ), DAY( DT_NOTA )');
        $this->db->order_by('MONTH( DT_NOTA )', 'asc');
        $this->db->order_by('DAY( DT_NOTA)', 'asc');
        $this->db->limit(26);
//
        return $this->db->get()->result();
    }

    public function getValorVendasPorPeriodo()
    {
        $this->db->select("CONCAT( YEAR( DT_NOTA ), '-', MONTH( DT_NOTA ) ) AS Periodo, 
                                    SUM(VL_PEDIDO) AS totalServicos");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('YEAR( DT_NOTA ), MONTH( DT_NOTA )');
        $this->db->order_by('YEAR( DT_NOTA )', 'asc');
        $this->db->order_by('MONTH( DT_NOTA)', 'asc');
        $this->db->limit(12);



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

    public function getVendasPorPeriodo()
    {
        $this->db->select("CONCAT( YEAR( DT_NOTA ), '-', MONTH( DT_NOTA ) ) AS Periodo, 
                                    count(ID_NOTA) AS totalPecas");
        $this->db->from('tbl_002_notas');
        $this->db->group_by('YEAR( DT_NOTA ), MONTH( DT_NOTA )');
        $this->db->order_by('YEAR( DT_NOTA )', 'asc');
        $this->db->order_by('MONTH( DT_NOTA)', 'asc');
        $this->db->limit(12);
        return $this->db->get()->result();
    }




    public function getLancamentosMaiorPorValor()
    {
        $this->db->select('descricao,valor');
        $this->db->from('lancamentos');

        $this->db->order_by('valor', 'desc');
        $this->db->limit(6);
        return $this->db->get()->result();
    }

    public function getMaioresNotOcorrencia($tabela)
    {
        $this->db->select('categoria, count(categoria) AS total');
        $this->db->from($tabela);
        $this->db->group_by('categoria');
        $this->db->order_by('total', 'desc');
        $this->db->limit(5);
        return $this->db->get()->result();
    }


}

/* End of file vendas_model.php */
/* Location: ./application/models/vendas_model.php */