members_model

<?php

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

/**
 * Modelo para la clase Miembros
 *
 * @author Reynier Pérez Mira
 * @package Members\Models
 */
class Members_m extends MY_Model {

    protected $_table = 'users';
    protected $_table_profiles = 'profiles';
    protected $_table_blog = 'cmvblog';
    protected $_table_event = 'event';
    protected $_table_friend = 'friend';
    protected $_table_friend_list = 'friend_list';
    protected $_table_privacy = 'privacy';
    protected $_table_groups = 'groups';

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

    // ------------------------------------------------------
    // Miembros
    // ------------------------------------------------------
    public function getAllMembers($params = array()) {
        $sql = "SELECT u.id, u.email, p.first_name, p.last_name, p.display_name, p.phone_1, p.phone_2 FROM " . $this->db->dbprefix($this->_table) . " u
                LEFT JOIN " . $this->db->dbprefix($this->_table_profiles) . " p ON (p.user_id = u.id)";

        if (isset($params['query_string'])) {
            $sql .= " WHERE (u.email LIKE '%" . $params['query_string'] . "%' OR u.username LIKE '%" . $params['query_string'] . "%')
            AND u.id != " . intval($this->current_user->id) . " AND u.active = 1";
        } else {
            $sql .= " WHERE u.id != " . intval($this->current_user->id) . " AND u.active = 1";
        }

        $sql .= " ORDER BY p.display_name DESC";

        if (isset($params ['limit']) && is_array($params ['limit']))
            $sql .= " LIMIT " . (int) $params ['limit'] [1] . ", " . (int) $params ['limit'] [0];
        elseif (isset($params ['limit']))
            $sql .= " LIMIT " . (int) $params ['limit'];

        return $this->db->query($sql)->result();
    }

    public function countAllMembers($params = array()) {
        $sql = "SELECT
            COUNT(*) AS num_rows
        FROM " . $this->db->dbprefix($this->_table) . " u
                LEFT JOIN " . $this->db->dbprefix($this->_table_profiles) . " p
            ON (p.user_id = u.id)";

        if (isset($params['query_string'])) {
            $sql .= " WHERE (u.email LIKE '%" . $params['query_string'] . "%' OR u.username LIKE '%" . $params['query_string'] . "%')
            AND u.id != " . intval($this->current_user->id) . " AND u.active = 1";
        } else {
            $sql .= " WHERE u.id != " . intval($this->current_user->id) . " AND u.active = 1";
        }

        return $this->db->query($sql)->row()->num_rows;
    }

    /**
     * Obtener los usuarios pertenecientes al grupo CAMARAS
     */
    public function getCamaras() {
        $sql = "SELECT
                    u.id,
                    p.first_name,
                    p.last_name,
                    p.display_name
                FROM " . $this->db->dbprefix($this->_table_profiles) . " p
                LEFT JOIN " . $this->db->dbprefix($this->_table) . " u
                    ON (p.user_id = u.id)
                WHERE (u.group_id = 4 OR u.group_id = 5) AND u.active = 1
                ORDER BY p.first_name ASC";

        return $this->db->query($sql)->result();
    }

    // ------------------------------------------------------
    // Busquedas
    // ------------------------------------------------------

    /**
     * Funcion que permite contar la cantidad de empresas, camaras, fedecamaras
     * regionales
     * y que es usada para paginar los resultados
     *
     * El campo users.group_id esta atado a valores especificos
     * 3: Empresas
     * 4: Camaras
     * 5: Fedecamaras Regionales
     *
     * @param $qs string
     * @return Arreglo de objetos
     */
    function count_companies($params = array()) {
        $sql = "SELECT
            COUNT(*) AS num_rows
        FROM " . $this->db->dbprefix($this->_table) . " u
                LEFT JOIN " . $this->db->dbprefix($this->_table_profiles) . " p
            ON (p.user_id = u.id)";

        if (isset($params['query_string'])) {
            $sql .= " WHERE (u.email LIKE '%" . $params['query_string'] . "%' OR u.username LIKE '%" . $params['query_string'] . "%')
            AND (u.group_id = 3 OR u.group_id = 4 OR u.group_id = 5)
            AND u.active = 1";
        } else {
            $sql .= " WHERE (u.group_id = 3 OR u.group_id = 4 OR u.group_id = 5) AND u.active = 1";
        }

        return $this->db->query($sql)->row()->num_rows;
    }

    /**
     * Funcion para obtener los resultados de la busqueda
     *
     * El campo users.group_id esta atado a valores especificos
     * 3: Empresas
     * 4: Camaras
     * 5: Fedecamaras Regionales
     *
     * @param $qs string
     * @return Arreglo de objetos
     */
    function get_companies($params = array()) {
        $sql = "SELECT u.id, u.email, p.first_name, p.last_name, p.display_name, p.phone_1, p.phone_2 FROM " . $this->db->dbprefix($this->_table) . " u
                LEFT JOIN " . $this->db->dbprefix($this->_table_profiles) . " p ON (p.user_id = u.id)";

        if (isset($params['query_string'])) {
            $sql .= " WHERE (u.email LIKE '%" . $params['query_string'] . "%' OR u.username LIKE '%" . $params['query_string'] . "%')
            AND (u.group_id = 3 OR u.group_id = 4 OR u.group_id = 5)
            AND u.active = 1";
        } else {
            $sql .= " WHERE (u.group_id = 3 OR u.group_id = 4 OR u.group_id = 5) AND u.active = 1";
        }

        $sql .= " ORDER BY p.display_name DESC";

        if (isset($params ['limit']) && is_array($params ['limit']))
            $sql .= " LIMIT " . (int) $params ['limit'] [1] . ", " . (int) $params ['limit'] [0];
        elseif (isset($params ['limit']))
            $sql .= " LIMIT " . (int) $params ['limit'];

        return $this->db->query($sql)->result();
    }

    function count_filter($params = array()) {
        $sql = "SELECT
            COUNT(*) AS num_rows
        FROM " . $this->db->dbprefix($this->_table) . " u
                LEFT JOIN " . $this->db->dbprefix($this->_table_profiles) . " p
            ON (p.user_id = u.id)";

        $sql .= " WHERE (u.email LIKE '%" . $params['query_string'] . "' OR u.username LIKE '%" . $params['query_string'] . "')
            AND (u.group_id = 3 OR u.group_id = 4 OR u.group_id = 5)
            AND u.active = 1";

        return $this->db->query($sql)->row()->num_rows;
    }

    function get_filter($params = array()) {
        $sql = "SELECT u.id, u.email, p.first_name, p.last_name, p.display_name, p.phone_1, p.phone_2 FROM " . $this->db->dbprefix($this->_table) . " u
                LEFT JOIN " . $this->db->dbprefix($this->_table_profiles) . " p ON (p.user_id = u.id)";

        $sql .= " WHERE (u.email LIKE '%" . $params['query_string'] . "' OR u.username LIKE '%" . $params['query_string'] . "')
            AND (u.group_id = 3 OR u.group_id = 4 OR u.group_id = 5)
            AND u.active = 1";

        $sql .= " ORDER BY p.display_name DESC";

        if (isset($params ['limit']) && is_array($params ['limit']))
            $sql .= " LIMIT " . (int) $params ['limit'] [1] . ", " . (int) $params ['limit'] [0];
        elseif (isset($params ['limit']))
            $sql .= " LIMIT " . (int) $params ['limit'];

        return $this->db->query($sql)->result();
    }

}