<?php
define_class(__FILE__, 'FindMember');

require_once('app/Member/Member.php');

class FindMember extends Member
{
    var $_filterFields = array();

    function _filterSql()
    {
        require_once('class/DB/DBObj/Filter.php');
        $sql = DBObj_Filter::perseQr($this->db, $this->_filterFields, $this->_filterQuery);
        if (!$sql) {
            $sql = "";
        }
        $filter = &$this->filter;

        foreach ($filter as $field=>$value) {
            if ($value == "" || $value == 'all') {
                continue;
            }
            switch($field) {
            case 'first_name':
            case 'last_name':
                $sql .= " AND {$this->tableName}.{$field} LIKE '%".$this->db->escape($value, 'text')."%'";
                break;
            case 'company_name':
                $sql .= " AND Company.name LIKE '%".$this->db->escape($value, 'text')."%'";
                break;
            case 'id_specialty_info':
                $sql .= " AND Member2SpecialtyInfo.{$field} = ".intval($value);
                break;
            case 'id_area_info':
                $sql .= " AND Member2AreaInfo.{$field} = ".intval($value);
                break;
            }
        }
        if (empty($sql)) {
            return false;
        }

        return $sql;
    }

    function getFromDB($id, $field = false)
    {
        $id = intval($id);
        $sql = "SELECT
                    {$this->tableName}.*,
                    CONCAT({$this->tableName}.last_name, IF({$this->tableName}.last_name = '','', ', '), {$this->tableName}.first_name, IF ({$this->tableName}.middle_initial = '' , '', ' '), {$this->tableName}.middle_initial, IF ({$this->tableName}.middle_initial = '' , '', '. ')) as full_name,
                    IF(Location.name <> '',Location.name, CONCAT(Location.mailing_address, ' - ', Location.mailing_city, ', ', Location.mailing_state, ' ', Location.mailing_zip)) as location_name,
                    Location.phone as location_phone,
                    Location.physical_address as location_physical_address,
                    Location.physical_city as location_physical_city,
                    Location.physical_state as location_physical_state,
                    Location.physical_zip as location_physical_zip,
                    Location.mailing_address as location_mailing_address,
                    Location.mailing_city as location_mailing_city,
                    Location.mailing_state as location_mailing_state,
                    Location.mailing_zip as location_mailing_zip,
                    Company.id as id_company,
                    Company.name as company_name,
                    Company.website as company_website,
                    NRDS.added as nrds_id_date_joined,
                    {$this->_getWhatMemberType()},
                    {$this->_getWhatMemberStatus()},
                    IF ({$this->tableName}.allow_mcpe_access = 'y', 'Allowed', 'Membership Not Active') as mcpe_status
                FROM {$this->tableNameDB} AS {$this->tableName}
                {$this->_getJoinMemberType()}
                {$this->_getJoinMemberStatus()}
                LEFT JOIN {$this->db->tblLocation} as Location
                    ON Location.id = {$this->tableName}.id_location
                LEFT JOIN {$this->db->tblCompany} as Company
                    ON Location.id_company = Company.id
                LEFT JOIN {$this->db->tblNRDS} as NRDS
                    ON NRDS.id = {$this->tableName}.nrds_id AND `NRDS`.`record_type` = 'individual'
                WHERE {$this->tableName}.id = $id";
        $sql .= ' AND ' . Member::getAddonWhereForMemberFind($this->tableName);

        $res = $this->db->queryRow($sql);
        $this->_sqlError($res);
        if ($field === false && !empty($res)) {
            $res['specialty'] = $this->getSubListFromDB($this->tableNameDB.'2SpecialtyInfo', $this->db->tblDSpecialty, 'id_specialty_info', 'title', $id);
            $res['area'] = $this->getSubListFromDB($this->tableNameDB.'2AreaInfo', $this->db->tblDArea, 'id_area_info', 'title', $id);
        }
        $this->arrayParseTag2Url(&$res);
        return $this->getSubElem($res, $field);
    }

    function getSubListFromDB($tableName, $DTableName, $key_field_name, $title_field_name, $id_parent)
    {
        $sql = "
                SELECT tblLink.{$key_field_name} as id, Dictionary.{$title_field_name} as title
                FROM {$tableName} as tblLink
                JOIN {$DTableName} as Dictionary
                    ON tblLink.{$key_field_name} = Dictionary.id
                WHERE tblLink.id_parent = ".$id_parent;
        $res = $this->db->queryAll($sql, 0, null, true);
        $this->_sqlError($res);
        return $res;
    }

    function _getWhere4Grid($opt = array())
    {
        if (empty($opt['addonWhere'])) {
            $opt['addonWhere'] = '';
        }
        $opt['addonWhere'] .= ' AND ' . Member::getAddonWhereForMemberFind($this->tableName);
        return parent::_getWhere4Grid($opt);
    }

    function _getOrder4Grid($opt = array())
    {
        $order = "ORDER BY RAND()";
        return $order;
    }
}