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

require_once('class/DB/DBObj.php');
require_once('app/Member/StatusHistory/StatusHistory.php');
require_once('app/Member/Grid/Grid.php');
require_once('Qs/Db.php');
/**
 * @method array getDExchangeAccessType4Select()
 */

class Member extends DBObj
{
    const NULL_FILTER_VALUE = 'none';

    /* next 3 user types can register to events */
    public static $hasRegistrationMncarIdTypes = array(1, 2, 3, 4); // (1 - Designated Realtor, 2 - Realtor, 3 - Secondary Member, 4 - Senior Member
    public static $hasRegistrationMncarLIdTypes = array(1); // broker
    public static $hasRegistrationMiscIdTypes = array(2, 4); // 2 - staff, 4 - support

    public static $hasMemberCostMncarIdTypes = array(1, 2, 3, 4, 7); // (1 - Designated Realtor, 2 - Realtor, 3 - Secondary Member, 4 - Senior Member, 7 - Designated Affiliate)
    public static $hasMemberCostMncarLIdTypes = array(1, 2, 3, 4); //(1 - Broker, 2 - Affiliate, 3 - EDC Affiliate, 4 - EDC EDAM Affiliate)
    public static $hasMemberCostMiscIdTypes = array(4); //4 - Support

    public static $findABrokerMncarIdTypes = array(1,2,3,4); // (1 - Designated Realtor, 2 - Realtor, 3 - Secondary Member, 4 - Senior Member
    public static $findABrokerMncarLIdTypes = array(1,2,3,4); //(1 - Broker, 2 - Affiliate, 3 - EDC Affiliate, 4 - EDC EDAM Affiliate)


    public static $typesAliases = array(
        'mncar'   => 'MNCAR',
        'mncar_l' => 'MNCAR-L',
        'misc'    => 'Miscellaneous',
    );

    public static $activePendingStatuses = array(1, 3); //1 - Active, 3 - Pending

    var $tableName = 'Member';
    var $itemName = 'Member';
    var $itemsName = 'Members';
    var $pass_field = 'password';
    var $filter = array();
    var $_urlParseFields = array('bio');
    var $_filterFields = array('Member.id', 'Member.nrds_id', "CONCAT(Member.first_name, ' ', Member.middle_initial, IF (Member.middle_initial = '' , '', '. '), Member.last_name)", 'Location.name');
    var $login_field = 'email';
    var $_allowedMncar2Bio = array(1, 2, 3, 4);
    var $_allowedMncarL2Bio = array(1, 2, 3, 4);
    var $_allowedMncar2BioCategories = array(1, 2, 3, 4);
    var $_allowedMncarL2BioCategories = array(1, 2, 3, 4);
    var $_allowedMncarL2TypeOfBusiness = array(2, 3, 4);


    function Member($id = null, $id_location = null, $id_company = null)
    {
        $this->DBObj($id);
        $this->id_location = intval($id_location);
        $this->id_company = intval($id_company);
    }

    function getAllowedMncar2BioCategories()
    {
        return $this->_allowedMncar2BioCategories;
    }

    function getAllowedMncarL2BioCategories()
    {
        return $this->_allowedMncarL2BioCategories;
    }

    function getAllowedMncarL2TypeOfBusiness()
    {
        return $this->_allowedMncarL2TypeOfBusiness;
    }

    function getAccessMap()
    {
        $list = get_object_vars($this);
        $map = array();
        foreach ($list as $name => $value) {
            if (0 === strpos($name, '_allowed')) {
                $map[substr($name, 1)] = $value;
            }
        }
        return $map;
    }

    function initFromForm(&$frm)
    {
        $files = array();
        $files['img']  = $frm->getElementValue('img');
        $data = $frm->exportValues();
        $this->arrayParseUrl2Tag($data);
        $memberData = $this->getMemberById($this->id);
        if (empty($memberData['birth_date'])) {
            $memberData['birth_date'] = '1902';
        }
        $data['birth_date']['Y'] = intval($memberData['birth_date']); // intval 2007-02-03 = 2007
        $data['birth_date'] = $this->initDateFromForm($data['birth_date']);
        foreach (array('mncar', 'mncar_l', 'misc') as $type) {
            $fieldDateJoined = $type . '_status_joined';
            $data[$fieldDateJoined] = $this->initDateFromForm($data[$fieldDateJoined]);
        }
        $data['id_specialty']        = $this->initCheckBoxListFromForm($data['id_specialty']);
        $data['id_specialty_info']   = $this->initCheckBoxListFromForm($data['id_specialty_info']);
        $data['id_line_of_work']     = $this->initCheckBoxListFromForm($data['id_line_of_work']);
        $data['id_area']             = $this->initCheckBoxListFromForm($data['id_area']);
        $data['id_area_info']        = $this->initCheckBoxListFromForm($data['id_area_info']);
        $data['id_email_type']       = $this->initCheckBoxListFromForm($data['id_email_type']);
        $data['id_email_group']      = $this->initCheckBoxListFromForm($data['id_email_group']);
        $data['type_of_business_id'] = $this->initCheckBoxListFromForm($data['type_of_business_id']);
        $this->_initProfilingUpdateDate($data);
        $nrdsId = ctype_digit((string)$data['nrds_id']) ? $data['nrds_id'] : 0;
        if (!$nrdsId) {
            unset($data['nrds_id']);
        }
        $this->_initBlockedMembers($data);
        $this->_initAcountTypeFromForm($data);
        $this->initFromArray($data, $files);
        return true;
    }

    protected function _initProfilingUpdateDate(&$data)
    {
        if (!array_key_exists('profile_update_date', $data)) {
            return;
        }
        $dateParts = array_filter($data['profile_update_date']);
        if (empty($dateParts)) {
            return;
        }
        $date = sprintf('%04d-%02d-%02d', $dateParts['Y'], $dateParts['M'], $dateParts['d']);
        $time = '00:00:00';
        if (array_key_exists('profile_update_time', $data)) {
            $timeParts = array_filter($data['profile_update_time']);
            if (!empty($timeParts)) {
                $_time = sprintf('%02d:%02d %s', $timeParts['g'], $timeParts['i'], $timeParts['A']);

                $timestamp = strtotime($_time);
                $time = date('H:i:s', $timestamp);
            }
        }
        $data['profile_update'] = $date . ' ' . $time;
    }

    protected function _initBlockedMembers(&$data)
    {
        $emails = Settings::parseEmails($data['specific_senders']);
        require_once('app/Settings/Settings.php');
        $data['id_blocked_member'] = array();
        if (empty($emails)) {
            return;
        }
        require_once('Qs/Db.php');
        $sql = 'SELECT `id` '
             . 'FROM `' . $this->db->tblMember . '` '
             . 'WHERE `email` IN (' . Qs_Db::quote($emails) . ')';
        $res = $this->db->queryCol($sql);
        $this->isDBError($res);
        $data['id_blocked_member'] = $res;
    }

    protected function _getSpecificEmails($id_member)
    {
        $sql = 'SELECT `m`.`email` '
             . 'FROM `' . $this->db->tblMember . '` AS `m` '
             . 'JOIN `' . $this->db->tblMember2BlockedMember . '` AS `m2bm` '
             . '    ON  `m2bm`.`id_parent` = ' . $this->db->quote($id_member, 'integer') . ' '
             . '    AND `m2bm`.`id_blocked_member` = `m`.`id` '
             . 'WHERE 1';
         $res = $this->db->queryCol($sql);
         $this->isDBError($res);
         return implode(', ', $res);
    }

    public function isEmailExists($email)
    {
        $sql = 'SELECT 1 '
             . 'FROM `' . $this->db->tblMember . '` '
             . 'WHERE `email` = ' . $this->db->quote($email, 'text') . ' '
             . 'LIMIT 1';
        $res = $this->db->queryOne($sql);
        $this->db->isDBError($res);
        return $res === '1';
    }

    protected function _initAcountTypeFromForm(&$data)
    {
        foreach (array('mncar', 'mncar_l', 'misc') as $type) {
            if (empty($data['id_' . $type . '_type'])) {
                $data['id_' . $type . '_status'] = 0;
            }
        }
        return $this;
    }

    function getData($field = false)
    {
        $value = parent::getData($field);
        if ($field == 'id_company' && empty($value) && isset($_REQUEST[$field])) {
            return $_REQUEST[$field];
        }
        if ($field == 'id_location' && empty($value) && isset($_REQUEST[$field])) {
            return $_REQUEST[$field];
        }
        if ($field == 'company_name' && empty($value)) {
            return 'No Company';
        }
        if ($field == 'location_name' && empty($value)) {
            return 'No Location';
        }
        return $value;
    }

    function initCheckBoxListFromForm($list)
    {
        if (is_array($list) && !empty($list)) {
            $value = current($list);
            if (is_array($value)) {
                foreach ($list as $k => &$v) {
                    $list[$k] = $this->initCheckBoxListFromForm($v);
                }
                return $list;
            } else {
                return array_filter($list, 'intval');
            }
        }
        return array();
    }

    function initDateFromForm($date)
    {
        if (intval($date['Y']) && intval($date['M']) && intval($date['d'])) {
            return sprintf('%04d-%02d-%02d', $date['Y'], $date['M'], $date['d']);
        }
        return null;
    }

    protected function _getWhatForExchangeAccessType()
    {
        $sql = "SELECT `title` FROM " . Qs_Db::getPair('DExchangeAccessType')
             . " WHERE `DExchangeAccessType`.`id` = `Member`.`id_exchange_access_type`
                 LIMIT 1";
        return "({$sql}) AS exchange_access_type ";
    }

    function _getWhatMemberType()
    {
        $sql = "
            DMncarMemberType.title as mncar_type,
            DMncarLMemberType.title as mncar_l_type,
            DMiscMemberType.title as misc_type";
        return $sql;
    }

    function _getWhatMemberStatus()
    {
        $sql = "
            IFNULL(`DMncarMemberStatus`.`title`, 'No Status') AS `mncar_status`,
            IFNULL(`DMncarLMemberStatus`.`title`, 'No Status') AS `mncar_l_status`,
            IFNULL(`DMiscMemberStatus`.`title`, 'No Status') AS `misc_status`";
        return $sql;
    }

    function _getWhat4Grid($opt = array())
    {
        $what = "
            {$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 name,
            'individual' as record_type,
            'Individual' as record_type_title,
            NRDS.added as nrds_id_date_joined,
            {$this->_getWhatMemberStatus()},
            {$this->_getWhatForExchangeAccessType()},
            IFNULL(DMCPEAccessType.title, 'N/A') as mcpe_access_type,
            Company.id as company_id,
            Company.name as company_name,
            Company.website as company_website,
            Location.id as location_id,
            Location.nrds_id as location_nrds_id,
            Location.name as location_name_original,
            IF(Location.name <> '', CONCAT(Location.name, IF(Location.physical_city <> '', ' (', ''), Location.physical_city, IF(Location.physical_city <> '', ') ', '')), CONCAT(Location.physical_address, ' - ', Location.physical_city, ', ', Location.physical_state, ' ', Location.physical_zip)) as location_name,
            {$this->_getWhatMemberType()},
            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.phone as location_phone,
            DMarket.title AS market
        ";
        return $what;
    }

    function _getJoinMemberStatus($alias = null)
    {
        if ($alias === null) {
            $alias = $this->tableName;
        }
        $sql = "
            LEFT JOIN {$this->db->tblDMemberStatus} as DMncarMemberStatus
                ON DMncarMemberStatus.id = {$alias}.id_mncar_status
            LEFT JOIN {$this->db->tblDMemberStatus} as DMncarLMemberStatus
                ON DMncarLMemberStatus.id = {$alias}.id_mncar_l_status
            LEFT JOIN {$this->db->tblDMemberStatus} as DMiscMemberStatus
                ON DMiscMemberStatus.id = {$alias}.id_misc_status
        ";
        return $sql;
    }

    function _getJoinMemberType($alias = null)
    {
        if ($alias === null) {
            $alias = $this->tableName;
        }
        $sql = "
            LEFT JOIN {$this->db->tblDMncarMemberType} as DMncarMemberType
                ON DMncarMemberType.id = {$alias}.id_mncar_type
            LEFT JOIN {$this->db->tblDMncarLMemberType} as DMncarLMemberType
                ON DMncarLMemberType.id = {$alias}.id_mncar_l_type
            LEFT JOIN {$this->db->tblDMiscMemberType} as DMiscMemberType
                ON DMiscMemberType.id = {$alias}.id_misc_type
        ";
        return $sql;
    }

    function _getJoin4Grid()
    {
        $join = "
            LEFT JOIN {$this->db->tblDMarket} as DMarket
                ON DMarket.id = {$this->tableName}.id_market
            " . $this->_getJoinMemberType() . "
            " . $this->_getJoinMemberStatus() . "
            LEFT JOIN {$this->db->tblDMCPEAccessType} as DMCPEAccessType
                ON DMCPEAccessType.id = {$this->tableName}.id_mcpe_access_type
            LEFT JOIN {$this->db->tblLocation} as Location
                ON Location.id = {$this->tableName}.id_location
            LEFT JOIN {$this->db->tblCompany} as Company
                ON Company.id = Location.id_company
            LEFT JOIN {$this->db->tblNRDS} as NRDS
                ON NRDS.id = {$this->tableName}.nrds_id
        ";

        if (!in_array($this->filter['id_specialty'], array('', 'all'))) {
            $join .= "
                LEFT JOIN {$this->tableNameDB}2Specialty as Member2Specialty
                    ON Member2Specialty.id_parent = {$this->tableName}.id
            ";
        }

        if (!in_array($this->filter['id_specialty_info'], array('', 'all'))) {
            $join .= "
                LEFT JOIN {$this->tableNameDB}2SpecialtyInfo as Member2SpecialtyInfo
                    ON Member2SpecialtyInfo.id_parent = {$this->tableName}.id
            ";
        }

        if (!in_array($this->filter['id_area'], array('', 'all'))) {
            $join .= "
                LEFT JOIN {$this->tableNameDB}2Area as Member2Area
                    ON Member2Area.id_parent = {$this->tableName}.id
            ";
        }

        if (!in_array($this->filter['id_area_info'], array('', 'all'))) {
            $join .= "
                LEFT JOIN {$this->tableNameDB}2AreaInfo as Member2AreaInfo
                    ON Member2AreaInfo.id_parent = {$this->tableName}.id
            ";
        }

        if (!in_array($this->filter['id_volunteer'], array('', 'all'))) {
            $join .= "
                LEFT JOIN {$this->tableNameDB}2Volunteer as Member2Volunteer
                    ON Member2Volunteer.id_parent = {$this->tableName}.id
            ";
        }

        return $join;
    }

    function _getOrder4Grid($opt=array())
    {
        if ($opt['order_by']=='last_name') $opt['order_by']='last_name, first_name';
        if ($opt['order_by']=='last_name DESC') $opt['order_by']='last_name DESC, first_name DESC';
        return parent::_getOrder4Grid($opt);
    }

    function _getGroup4Grid()
    {
        return "GROUP BY {$this->tableName}.id";
    }

    function getSpecialty4Select()
    {
        $sql = "
            SELECT id, title
            FROM {$this->db->tblDSpecialty}
            ORDER BY title
        ";
        return $this->db->queryAll($sql, 0, null, true);
    }

    function getRecordType4Select()
    {
        $sql = "
            SELECT id, title
            FROM {$this->db->tblDMemberRecordType}
            ORDER BY sorter
        ";
        return $this->db->queryAll($sql, 0, null, true);
    }

    function getState4Select()
    {
        $sql = "
            SELECT short as id, short as title
            FROM {$this->db->tblDState}
            ORDER BY title
        ";
        return $this->db->queryAll($sql, 0, null, true);
    }

    function getBrocker4Select()
    {
        $sql = "
            SELECT id as `key`, CONCAT(id, ' ', company, ' - ', address, ' - ', city, ', ', state, ' ', zip) as title
            FROM {$this->tableNameDB}
            WHERE id_type = 1
            ORDER BY title
        ";
        $list = $this->db->queryAll($sql, 0, null, true);
        return $list;
    }

    function getManagementCompany4Select()
    {
        $sql = "
            SELECT id as `key`, CONCAT(id, ' ', company, ' - ', address, ' - ', city, ', ', state, ' ', zip) as title
            FROM {$this->tableNameDB}
            WHERE id_type = 1
            ORDER BY title
        ";
        $list = $this->db->queryAll($sql, 0, null, true);
        return $list;
    }

    function getDMember4Select()
    {
        $sql = "
            SELECT id, title
            FROM {$this->db->tblDMember}
            ORDER BY sorter
        ";
        return $this->db->queryAll($sql, 0, null, true);
    }

    function getDMCPEAccessType4Select()
    {
        $sql = "
            SELECT id, title
            FROM {$this->db->tblDMCPEAccessType}
            ORDER BY sorter
        ";
        return $this->db->queryAll($sql, 0, null, true);
    }

    function getDAreaByType()
    {
        $sql = "
            SELECT
                DAreaType2Area.id_area_type,
                DAreaType.title as area_type_title,
                DAreaType2Area.id_area,
                DArea.title
            FROM {$this->db->tblDAreaType2Area} as DAreaType2Area
            JOIN {$this->db->tblDAreaType} as DAreaType
                ON  DAreaType.id = DAreaType2Area.id_area_type
            JOIN {$this->db->tblDArea} as DArea
                ON  DArea.id = DAreaType2Area.id_area
            ORDER BY DAreaType.sorter, DAreaType2Area.sorter
        ";
        $_list = $this->db->queryAll($sql);
        $this->_sqlError($_list);
        $list = array();
        foreach ($_list as $k=>$v) {
            $list[$v['id_area_type']]['id'] = $v['id_area_type'];
            $list[$v['id_area_type']]['title'] = $v['area_type_title'];
            $list[$v['id_area_type']]['items'][$v['id_area']] = $v['title'];
        }
        return $list;
    }

    function getCompanySize4Select()
    {
        $list = array('1-3', '4-9', '10-49', '50-99', '100+');
        return array_combine($list, $list);
    }

    function initFromDB()
    {
        if (!$this->id && $this->id_location) {
            $locationData = $this->getLocationById($this->id_location);
            $companyData = $this->getCompanyById($locationData['id_company']);
            $this->_data = array(
                'id_location'=>$this->id_location,
                'location_name'=>$locationData['title'],
                'id_company'=>$locationData['id_company'],
                'company_name'=>$companyData['name'],
            );
            return true;
        } else if (!$this->id && $this->id_company) {
            $companyData = $this->getCompanyById($this->id_company);
            $this->_data = array(
                'id_company'=>$this->id_company,
                'company_name'=>$companyData['name'],
            );
            return true;
        }
        $this->_data = $this->getFromDB($this->id);
        if (empty($this->_data)) {
            return false;
        }
        $this->_data['formatted_changed'] = date('F j\t\h, Y - g:i a', strtotime($this->_data['changed']));
        $nullDateList = array('status_date', 'mcpe_status_date', 'nrds_id_date_joined');
        foreach ($nullDateList as $k=>$v) {
            if (!empty($this->_data[$v])) {
                $this->_data[$v] = date('F j\t\h, Y - g:i a', strtotime($this->_data[$v]));
            }
        }
        $nrdsId = ctype_digit((string)$this->_data['nrds_id']) ? $this->_data['nrds_id'] : 0;
        if (!$nrdsId) {
            $this->_data['nrds_id'] = '';
        }
        if (!empty($this->_data['profile_update']) && false !== ($time = strtotime($this->_data['profile_update']))) {
            $this->_data['profile_update_date'] = date('Y-m-d', $time);
            $this->_data['profile_update_time'] = date('H:i:s', $time);
        }
        return true;
    }

    function getIdsFromDB($tableName, $DTableName, $field_name, $id_parent, $parentFieldName = 'id_parent')
    {
        $sql = "
                SELECT tblLink.{$field_name} as id, tblLink.{$field_name} as title
                FROM {$tableName} as tblLink
                JOIN {$DTableName} as Dictionary
                    ON tblLink.{$field_name} = Dictionary.id
                WHERE tblLink.{$parentFieldName} = ".$id_parent;
        $res = $this->db->queryAll($sql, 0, nnull, true);
        $this->_sqlError($res);
        return $res;
    }

    function getFromDB($id, $field = false)
    {
        $id = intval($id);
        $sql = "SELECT
                    {$this->tableName}.*,
                    IF(Location.name <> '', CONCAT(Location.name, IF(Location.physical_city <> '', ' (', ''), Location.physical_city, IF(Location.physical_city <> '', ') ', '')), CONCAT(Location.physical_address, ' - ', Location.physical_city, ', ', Location.physical_state, ' ', Location.physical_zip)) as location_name,
                    Company.id as id_company,
                    Company.name as company_name,
                    Location.id_pay_member AS location_id_pay_member,
                    IF(Location.id_pay_member = {$this->tableName}.id, 'y', 'n') AS billing_approve_pay_exchange,
                    NRDS.added as nrds_id_date_joined,
                    {$this->_getWhatMemberStatus()}
                FROM {$this->tableNameDB} AS {$this->tableName}
                {$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
                WHERE {$this->tableName}.id = $id";
        $res = $this->db->queryRow($sql);
        if (null === $res) {
            return $res;
        }
        $sql = 'SELECT `m`.`id` '
            . 'FROM `' . $this->db->tblMember2Member .'` AS `m2m` '
            . 'JOIN `' . $this->db->tblMember . '` AS `m` '
            . 'ON  `m2m`.`id_parent` = ' . $this->db->quote($id, 'integer') . ' '
            . 'AND `m2m`.`id_member` = `m`.`id` '
            . 'WHERE 1 '
            . 'AND `m`.`is_support_account` = "n"';
        $res['supported_accounts']=$this->db->queryCol($sql);
        if ($field === false) {
            $res['specific_senders'] = $this->_getSpecificEmails($id);
            $res['id_specialty'] = $this->getIdsFromDB($this->tableNameDB.'2Specialty', $this->db->tblDSpecialty, 'id_specialty', $id);
            $res['id_specialty_info'] = $this->getIdsFromDB($this->tableNameDB.'2SpecialtyInfo', $this->db->tblDSpecialty, 'id_specialty_info', $id);
            $res['id_area'] = $this->getIdsFromDB($this->tableNameDB.'2Area', $this->db->tblDArea, 'id_area', $id);
            $res['id_area_info'] = $this->getIdsFromDB($this->tableNameDB.'2AreaInfo', $this->db->tblDArea, 'id_area_info', $id);
            $res['id_line_of_work'] = $this->getIdsFromDB($this->tableNameDB.'2LineOfWork', $this->db->tblDLineOfWork, 'id_line_of_work', $id);
            $res['id_email_type'] = $this->getIdsFromDB($this->tableNameDB.'2EmailType', $this->db->tblDEmailType, 'id_email_type', $id);
            $res['id_email_group'] = $this->getIdsFromDB($this->tableNameDB.'2EmailGroup', $this->db->tblDEmailGroupType, 'group_id', $id, 'member_id');
            $res['type_of_business_id'] = $this->getIdsFromDB($this->tableNameDB.'2TypeOfBusiness', $this->db->tblDTypeOfBusiness, 'type_of_business_id', $id, 'member_id');
        }
        $this->arrayParseTag2Url(&$res);
        return $this->getSubElem($res, $field);
    }

    function getEmailAreaIdsFromDB($tableName, $id_parent)
    {
        $sql = "
            SELECT Member2EmailArea.id_area_type, Member2EmailArea.id_area
            FROM {$tableName} as Member2EmailArea
            JOIN {$this->db->tblDArea} as DArea
                ON DArea.id = Member2EmailArea.id_area
            JOIN {$this->db->tblDAreaType} as DAreaType
                ON DAreaType.id = Member2EmailArea.id_area_type
            JOIN {$this->db->tblDAreaType2Area} as DAreaType2Area
                ON  DAreaType2Area.id_area_type = Member2EmailArea.id_area_type
                AND DAreaType2Area.id_area = Member2EmailArea.id_area
            WHERE Member2EmailArea.id_parent = ".$this->db->quote($id_parent, 'integer')."
            ORDER BY DAreaType.sorter, DAreaType2Area.sorter
        ";
        $res = $this->db->queryAll($sql);
        $this->_sqlError($res);
        $list = array();
        foreach ($res as $row) {
            $list[$row['id_area_type']][$row['id_area']] = $row['id_area'];
        }
        return $list;
    }

    function insert($data = null, $files = null)
    {
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }
        $this->_initTable();
        $this->_data[$this->pass_field] = $this->_encryptPass($this->_data[$this->pass_field]);
        $data = $this->_data;
        unset($data['id']);
        $fields = $this->getSimpleFieldList();
        if (isset($fields['sorter'])) {
            $data['sorter'] = $this->getSorter();
        }
        $currDate = date('Y-m-d H:i:s');
        $data['status_date'] = $currDate;
        $data['mcpe_status_date'] = $currDate;
        $this->id = $this->table->insert($data);
        $this->updateIds($this->tableNameDB.'2BlockedMember', 'id_blocked_member', $this->_data['id_blocked_member']);
        $this->updateIds($this->tableNameDB.'2Specialty', 'id_specialty', $this->_data['id_specialty']);
        $this->updateIds($this->tableNameDB.'2SpecialtyInfo', 'id_specialty_info', $this->_data['id_specialty_info']);
        $this->updateIds($this->tableNameDB.'2Area', 'id_area', $this->_data['id_area']);
        $this->updateIds($this->tableNameDB.'2AreaInfo', 'id_area_info', $this->_data['id_area_info']);
        $this->updateIds($this->tableNameDB.'2LineOfWork', 'id_line_of_work', $this->_data['id_line_of_work']);
        $this->updateIds($this->tableNameDB.'2EmailType', 'id_email_type', $this->_data['id_email_type']);
        $this->updateIds($this->tableNameDB.'2EmailGroup', 'group_id', $this->_data['id_email_group'], 'member_id');
        $this->updateIds($this->tableNameDB.'2TypeOfBusiness', 'type_of_business_id', $this->_data['type_of_business_id'], 'member_id');
        $this->updateIds($this->db->tblMember2Member, 'id_member', $this->_data['supported_accounts']);
        $this->updateNrdsId($this->_data['nrds_id'], 'individual', $this->_data['id_company']);
        $this->handleFiles();
        return $this->id;
    }

    function updateEmailAreaIds($data)
    {
        $tableName = $this->tableNameDB.'2EmailArea';
        $this->deleteIds($tableName);
        if (!is_array($data) || empty($data)) {
            return $data;
        }
        $sql = '';
        foreach ($data as $id_area_type => $ids_area) {
            foreach ($ids_area as $id_area) {
                $sql .= "\n(".intval($this->id).', '.intval($id_area_type).', '.intval($id_area).'),';
            }
        }
        if (empty($sql)) {
            return false;
        }
        $sql = "INSERT INTO {$tableName} (`id_parent`, `id_area_type`, `id_area`) VALUES".rtrim($sql, ',').';';
        $res = $this->db->query($sql);
        $this->_sqlError($res);
        return $this;
    }

    function _setDateJoinedChanges($type, $data)
    {
        $insertArray = array(
            'memberId'        => $this->id,
            'accountType'     => $type,
            'oldTypeId'       => $data['oldType'],
            'newTypeId'       => $data['currentType'],
            'oldStatusId'     => $data['oldStatus'],
            'newStatusId'     => $data['currentStatus'],
            'newDateJoined'   => $data['newDateJoined'],
            'oldDateJoined'   => $data['oldDateJoined'],
        );
        $table = $this->getTable('StatusHistory');
        $table->insert($insertArray);
        return $this;
    }

    function update($data = null, $files = null)
    {
        if (empty($this->_data[$this->pass_field])){
            unset($this->_data[$this->pass_field]);
        } else {
            $this->_data[$this->pass_field] = $this->_encryptPass($this->_data[$this->pass_field]);
        }
        if (!is_null($data)) {
            $this->initFromArray($data, $files);
        }

        $availableStatuses = array(1, 3);
        if (
            !in_array($this->_data['id_mncar_status'], $availableStatuses)
            && !in_array($this->_data['id_mncar_l_status'], $availableStatuses)
            && !in_array($this->_data['id_misc_status'], $availableStatuses)
        ) {
            $this->_removeFromVolunteer();
        }

        if (!(1 == $this->_data['id_misc_status'] && 2 == $this->_data['id_misc_type'])) {
            $this->_removeFromLiaisonVolunteer();
        }

        $this->_initTable();

        $oldData = $this->db->queryRow("SELECT * FROM {$this->tableNameDB} WHERE id = ". $this->db->quote($this->id, 'integer') );
        $currDate = date('Y-m-d H:i:s');
        $tableMember2Member = new DBTable('Member2Member');
        foreach (array('mncar', 'mncar_l', 'misc') as $type) {
            $statusField = 'id_' . $type . '_status';
            if ($oldData[$statusField] != $this->_data[$statusField]) {
                $this->_data[$type . '_status_changed'] = $currDate;
                if ($this->_data[$statusField] != 1) {
                    $tableMember2Member->delete('id_member = ' . $this->db->quote($this->id, 'integer'));
                }
            }

            $accountType = 'id_' . $type . '_type';
            $dateJoinedField = $type . '_status_joined';
            if (!(empty($oldData[$statusField]) && empty($this->_data[$statusField]))) {
                if (intval($oldData[$statusField]) != intval($this->_data[$statusField])
                    || intval($oldData[$accountType]) != intval($this->_data[$accountType])
                    || strtotime($oldData[$dateJoinedField]) != strtotime($this->_data[$dateJoinedField])
                ) {
                    $this->_setDateJoinedChanges(
                        $type,
                        array(
                            'oldType' => $oldData['id_' . $type . '_type'],
                            'currentType' => $this->_data['id_' . $type . '_type'],
                            'oldStatus' => $oldData['id_' . $type . '_status'],
                            'currentStatus' => $this->_data['id_' . $type . '_status'],
                            'oldDateJoined' => $oldData[$dateJoinedField],
                            'newDateJoined' => $this->_data[$dateJoinedField],
                        )
                    );
                }
            }

        }
        if ($oldData['allow_mcpe_access'] != $this->_data['allow_mcpe_access']) {
            $this->_data['mcpe_status_date'] = $currDate;
        }
        if ($oldData['id_location'] != $this->_data['id_location']) {
            if ($this->db->qyueryOne('SELECT id_company FROM mn_Location WHERE id='.(int)$oldData['id_location']) !=
                    $this->db->qyueryOne('SELECT id_company FROM mn_Location WHERE id='.(int)$this->_data['id_location']))
            {
                $tableMember2Member->delete('id_member='. $this->db->quote($this->id, 'integer'));
            }
        }

        $this->table->update($this->_data, 'id = '. $this->db->quote($this->id, 'integer') );
        $this->updateIds($this->tableNameDB.'2BlockedMember', 'id_blocked_member', $this->_data['id_blocked_member']);
        $this->updateIds($this->tableNameDB.'2Specialty', 'id_specialty', $this->_data['id_specialty']);
        $this->updateIds($this->tableNameDB.'2SpecialtyInfo', 'id_specialty_info', $this->_data['id_specialty_info']);
        $this->updateIds($this->tableNameDB.'2Area', 'id_area', $this->_data['id_area']);
        $this->updateIds($this->tableNameDB.'2AreaInfo', 'id_area_info', $this->_data['id_area_info']);
        $this->updateIds($this->tableNameDB.'2LineOfWork', 'id_line_of_work', $this->_data['id_line_of_work']);
        $this->updateIds($this->tableNameDB.'2EmailType', 'id_email_type', $this->_data['id_email_type']);
        $this->updateIds($this->tableNameDB.'2EmailGroup', 'group_id', $this->_data['id_email_group'], 'member_id');
        $this->updateIds($this->tableNameDB.'2TypeOfBusiness', 'type_of_business_id', $this->_data['type_of_business_id'], 'member_id');
        $this->updateIds($this->db->tblMember2Member, 'id_member', $this->_data['supported_accounts']);
        $this->updateNrdsId($this->_data['nrds_id'], 'individual', $this->_data['id_company']);
        $this->handleFiles();
        return true;
    }

    protected function _removeFromVolunteer()
    {
        $this->deleteIds($this->tableNameDB . '2Volunteer');
        return $this;
    }

    protected function _removeFromLiaisonVolunteer()
    {
        $where = '`id_parent` = ' . $this->db->quote($this->id, 'integer')
            . ' AND `type` = ' . $this->db->quote(VolunteerMember::STAFF_LIAISON);
        $this->getTable('Member2Volunteer')->delete($where);
        return $this;
    }

    public function getVolunteerGroups()
    {
        $sql = "
            SELECT `Volunteer`.`title`, `Volunteer`.`type` AS `groupType`, `Member2Volunteer`.`type`
              FROM {$this->db->tblVolunteer} AS `Volunteer`
            JOIN {$this->db->tblMember2Volunteer} AS `Member2Volunteer`
              ON `Member2Volunteer`.`id_volunteer` = `Volunteer`.`id`
            WHERE `Member2Volunteer`.`id_parent` = " . $this->db->quote($this->id, 'integer')
        . "  ORDER BY `Volunteer`.`sorter`, `Volunteer`.`title`";
        return $this->db->queryAll($sql);
    }

    public function getVolunteerPositions()
    {
        return VolunteerMember::getInstance()->setId($this->id)->setParentId(null)->getAllPositions();
    }

    public function getStatusChanges()
    {
        return StatusHistory::getInstance($this->id)->getStatusChanges(array('limit' => 11));
    }

    public function updateRow($data)
    {
        $this->_initTable();
        $this->table->update($data, 'id = '. $this->db->quote($this->id, 'integer'));
    }

    function activate()
    {
        $this->_initTable();
        $this->table->update(
            array('email_verified' => 'y', 'info_verified' => 'y'),
            'id=' . $this->db->quote($this->id, 'integer')
        );
    }

    function updateLinks($tableName, $address)
    {
        $this->deleteLinks($tableName);
        if (empty($address)) {
            return false;
        }
        $sql = "INSERT INTO $tableName (`id_parent`, `address`, `sorter`) VALUES";
        foreach($address as $k=>$v) {
            $sql.="\n({$this->id}, ".$this->db->quote($address[$k]).", $k),";
        }
        $sql = rtrim($sql, ',').';';
        $res = $this->db->query($sql);
        $this->_sqlError($res);
    }

    function deleteLinks($tableName)
    {
        $this->db->query("DELETE FROM  $tableName WHERE id_parent = ".$this->db->quote($this->id));
    }

    function delete()
    {
        $this->_files = array(
            'img'  => array('del' => 1),
        );
        $this->deleteIds($this->tableNameDB.'2Specialty');
        $this->deleteIds($this->tableNameDB.'2Area');
        $this->deleteIds($this->tableNameDB.'2AreaInfo');
        $this->deleteIds($this->tableNameDB.'2EmailArea');
        $this->deleteIds($this->tableNameDB.'2EmailSpecialty');
        $this->deleteIds($this->tableNameDB.'2EmailType');
        $this->deleteIds($this->tableNameDB.'2Volunteer');
        parent::delete();
        return true;
    }

    function _encryptPass($psw)
    {
        return $psw;
    }

    function isUnique($field, $value, $record_type = '')
    {
        $sql = "SELECT COUNT(*) FROM {$this->tableNameDB}
            WHERE {$field} = ".$this->db->quote($value);
        if($record_type != '') {
            $sql .=  ' AND record_type = "'.$record_type.'"';
        }
        if ($this->id){
            $sql .=  ' AND id != '.$this->db->quote($this->id);
        }
        return !(bool)$this->db->queryOne($sql) ;
    }

    function getSalutation4Select()
    {
        $list = array('Mr', 'Mrs', 'Ms');
        return array_combine($list, $list);
    }

    function getCompany()
    {
        $sql = "SELECT name AS title, id AS value FROM {$this->db->tblCompany} ORDER BY name";
        return $this->db->queryAll($sql);
    }

    function getCompanyName($id_company)
    {
        $companyData = $this->getCompanyById((int)$id_company);
        if (isset($companyData['name'])) {
            return $companyData['name'];
        } else {
            return 'No Company';
        }
    }

    function getLocation($opt)
    {
        extract($opt);
        $sql = "
            SELECT
                IF(name <> '',CONCAT(name, ' (', physical_address, ' - ', physical_city, ', ', physical_state, ' ', physical_zip,')'), CONCAT(physical_address, ' - ', mailing_city, ', ', mailing_state, ' ', physical_zip)) AS title,
                id AS value
            FROM {$this->db->tblLocation}
            WHERE id_company = ".$this->db->quote($id_company, 'integer')."
            ORDER BY title
        ";
        return $this->db->queryAll($sql);
    }

    function getLocationName($id_location)
    {
        $locationData = $this->getLocationById((int)$id_location);
        if (isset($locationData['title'])) {
            return $locationData['title'];
        } else {
            return 'No Location';
        }
    }

    function getLocationById($id)
    {
        $sql = "
            SELECT id, IF(name <> '',CONCAT(name, ' (', physical_address, ' - ', physical_city, ', ', physical_state, ' ', physical_zip,')'), CONCAT(physical_address, ' - ', mailing_city, ', ', mailing_state, ' ', physical_zip)) as title
            FROM {$this->db->tblLocation}
            WHERE id = ".$this->db->quote($id, 'integer')."
        ";
        return $this->db->queryRow($sql, 0, null, true);
    }

    function getCompanyById($id)
    {
        $sql = "
            SELECT *
            FROM {$this->db->tblCompany}
            WHERE id = ".$this->db->quote($id, 'integer')."
        ";
        return $this->db->queryRow($sql, 0, null, true);
    }

    function _filterSqlParceValue($value, $type = null)
    {
        if (is_array($value)) {
            return ' IN ('.implode(', ', $value).')';
        }
        return ' = '.$this->db->quote($value, $type);
    }

    public function setFilter($filter)
    {
        if (is_string($filter)) {
            $this->_filterQuery = $filter;
        } else if (is_array($filter)) {
            if (isset($filter['query'])) {
                $this->_filterQuery = $filter['query'];
                unset($filter['query']);
                $this->_filter = $filter;
            }
        }
    }

    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;
        if ($filter['search_type'] != 'individual') {
            return false;
        }
        $filterByType = array();
        $filterByStatus = array();
        foreach ($filter as $field=>$value) {
            if ($value == "" || $value == 'all' || (is_array($value) && empty($value))) {
                continue;
            }
            switch($field) {
            case 'id_mncar_type':
            case 'id_mncar_l_type':
            case 'id_misc_type':
                $filterByType[] = "{$this->tableName}.{$field} " . $this->_filterSqlParceValue($value);
                break;
            case 'id_mncar_status':
            case 'id_mncar_l_status':
            case 'id_misc_status':
                $filterByStatus[] = "{$this->tableName}.{$field} " . $this->_filterSqlParceValue($value);
                break;
            case 'id_location':
            case 'id_mcpe_access_type':
            case 'id_market':
                $sql .= " AND {$this->tableName}.{$field} ".$this->_filterSqlParceValue($value);
                    break;
            case 'id_exchange_access_type':
                $sql .= $this->_getSqlConditionForExchangeAccessType($value);
                break;
            case 'id_company':
                $sql .= " AND Location.{$field} = ".intval($value);
                break;
            case 'send_focus_newsletter':
            case 'is_support_account':
            case 'allow_mcpe_access':
                if (in_array($value, array('y', 'n'))) {
                    $sql .= " AND {$this->tableName}.{$field} = '{$value}'";
                }
                break;
            case 'id_specialty':
                $sql .= " AND Member2Specialty.{$field} ".$this->_filterSqlParceValue($value);
                break;
            case 'id_area':
                $sql .= " AND Member2Area.{$field} ".$this->_filterSqlParceValue($value);
                break;
            case 'id_volunteer':
                $sql .= " AND Member2Volunteer.{$field} ".$this->_filterSqlParceValue($value);
                break;
            }
        }
        if (!empty($filterByType)) {
            $sql .= ' AND (' . implode(' OR ', $filterByType) . ')';
        }
        if (!empty($filterByStatus)) {
            $sql .= ' AND (' . implode(' OR ', $filterByStatus) . ')';
        }
        if (empty($sql)) {
            return false;
        }

        return $sql;
    }

    protected function _getSqlConditionForExchangeAccessType($value)
    {
        $sql = '';
        if (is_array($value) && !empty($value) && false !== ($key = array_search(Member::NULL_FILTER_VALUE, $value))) {
            $sql = " AND {$this->tableName}.`id_exchange_access_type` IS NULL";
            unset($value[$key]);
        }

        if (empty($value) && !empty($sql)){
            return $sql;

        }

        if ($sql) {
            $sql .= " OR ( {$this->tableName}.`id_exchange_access_type` "
                 . $this->_filterSqlParceValue($value) . ')';
        } else {
            $sql .= " AND {$this->tableName}.`id_exchange_access_type` " . $this->_filterSqlParceValue($value);
        }
        return $sql;
    }

    function getCompanyId()
    {
        $this->initFromDB();
        return $this->_data['id_company'];
    }

    function assignNrdsId($record_type)
    {
        if ($this->id) {
            $nrds_id = $this->getFromDB($this->id, 'nrds_id');
            $nrds_id = ctype_digit($nrds_id) ? $nrds_id : 0;
            if ($nrds_id) {
                return array('id'=>$nrds_id);
            }
        }
        $sql = "SELECT MAX(id) FROM {$this->db->tblNRDS} WHERE manualy_set = 'n' ";
        $max_nrds_id = $this->db->queryOne($sql);
        $data = array();
        if ($max_nrds_id == null ) {
            $data['id'] = NRDS_ID_START;
        } else {
            $data['id'] = $max_nrds_id + 1;
        }

        $companyId = (int)$this->getCompanyId();;

        $isNrdsApplicable = $this->isUniqueNrdsAtDictionary($data['id'])
            && $this->isNRDSUniqueApplied($data['id'])
            && $this->isFreeNrdsAtCompany($data['id'], $companyId);


        if (!$isNrdsApplicable) {
            do {
                $data['id']++;
                if ($data['id'] > NRDS_ID_END) {
                    return array('error'=>'No free NRDS ID');
                }
            } while(
                !($this->isUniqueNrdsAtDictionary($data['id'])
                && $this->isNRDSUniqueApplied($data['id'])
                && $this->isFreeNrdsAtCompany($data['id'], $companyId)
                )
            );
        }

        $data['id_owner'] = $this->id;
        $data['record_type'] = (string)$record_type;
        $data['manualy_set'] = 'n';
        $data['id_company'] = (int)$this->getCompanyId();
        $data['id_company'] = $data['id_company'] ? $data['id_company'] : null;

        require_once('class/DB/DBTable.php');

        $NRDS = new DBTable('NRDS');
        if ($this->isUniqueNrdsAtDictionary($data['id'], $record_type, $this->id)) {
            $NRDS->insert($data);
        } else {
            $where = 'id=' . S_db2::quoteBigInt($data['id']) . ' AND `record_type` = '
                . $this->db->quote($data['record_type']) . ' AND `id_owner` = ' . $this->db->quote($this->id, 'integer');
            $NRDS->update($data, $where );
        }
        if ($this->id) {
            $this->_initTable();
            $this->table->update(array('nrds_id'=>$data['id']), 'id='.$this->db->quote($this->id, 'integer'));
        }
        return $data;
    }

    public function isBlockedNrds($nrdsId)
    {
        require_once('app/NRDSUsage/NRDSUsage.php');
        $nrdsUsage = new NRDSUsage();
        return $nrdsUsage->isBlocked($nrdsId);
    }

    function assignManualyNrdsId($record_type, $nrds_id)
    {
        if ($this->id) {
            $_nrds_id = $this->getFromDB($this->id, 'nrds_id');
            $_nrds_id = ctype_digit((string)$_nrds_id) ? $_nrds_id : 0;
            if ($_nrds_id) {
                return array('id'=>$_nrds_id);
            }
        }
        if (!is_numeric($nrds_id)) {
            return array('error'=>'NRDS ID is in wrong format');
        }

        if (!$this->isNRDSUniqueApplied($nrds_id) || $this->isBlockedNrds($nrds_id)) {
            return array('error'=>'This NRDS ID is already in use');
        }

        $companyId = (int)$this->getCompanyId();
        $companyId = $companyId ? $companyId : null;

        if (!$this->isFreeNrdsAtCompany($nrds_id, $companyId)) {
            return array('error' => MemberGrid::MSG_ERR_NRDS_NOT_UNIQUE_AT_COMPANY);
        }

        $data = array();
        $data['id'] = $nrds_id;
        $data['id_owner'] = $this->id;
        $data['record_type'] = (string)$record_type;
        $data['manualy_set'] = 'y';
        $data['applied'] = 'n';
        $data['id_company'] = $companyId;
        require_once('class/DB/DBTable.php');
        $NRDS = new DBTable('NRDS');
        if ($this->isUniqueNrdsAtDictionary($nrds_id, $record_type, $this->id)) {
            $NRDS->insert($data);
        } else {
            $where = 'id=' . S_db2::quoteBigInt($data['id']) . ' AND `record_type` = '
                . $this->db->quote($data['record_type']) . ' AND `id_owner` = ' . $this->db->quote($this->id, 'integer');
            $NRDS->update($data, $where);
        }

        if ($this->id) {
            $this->_initTable();
            $this->table->update(array('nrds_id' => $data['id']), 'id=' . $this->db->quote($this->id, 'integer'));
        }

        return $data;
    }

    public function isUniqueNrdsAtDictionary($nrdsId, $recordType = 'individual', $idOwner = null)
    {
        $quotedNrds = S_db2::quoteBigInt($nrdsId);
        $sql = "
            SELECT 1
            FROM `{$this->db->tblNRDS}`
            WHERE `id` = {$quotedNrds}
        ";
        
        if ($recordType) {
            $sql .= ' AND  `record_type` IN ("' . implode('", "', (array) $recordType) . '") ';
        }

        if (null !== $idOwner) {
            $sql .= "AND id_owner <> " . $this->db->quote($idOwner, 'integer');
        }
        $sql .= ' LIMIT 1';

        $res = $this->db->queryOne($sql);
        $this->isDBError($res);
        return '1' !== $res;
    }

    /**
     * check if members or locations from this company have the same nrds (if have - nrds is not unique)
     * @param int $nrdsId
     * @param int $companyId
     * @return bool
     */
    function isFreeNrdsAtCompany($nrdsId, $companyId)
    {
        $quotedNrdsId = S_db2::quoteBigInt($nrdsId);
        if (!$companyId) {
            return true;
        }
        $quotedCompanyId = $this->db->quote($companyId, 'integer');
        $sql = "SELECT 1 FROM {$this->db->tblNRDS} WHERE `id_company` = {$quotedCompanyId} AND `id` = {$quotedNrdsId}";

        if ($this->id) {
            $sql .= ' AND `id_owner` <> ' . $this->db->quote($this->id, 'integer');
        }

        $res = $this->db->queryOne($sql);
        $this->isDBError($res);
        return !((bool) $res);

    }

    function isNRDSUniqueApplied($nrds_id, $id_owner = null, $record_type = 'individual')
    {
        $quotedNrds = S_db2::quoteBigInt($nrds_id);
        $quotedIdOwner = $this->db->quote($id_owner, 'integer');
        $sql = "
            SELECT `id`
            FROM `{$this->db->tblNRDS}`
            WHERE `id` = {$quotedNrds}
                AND `applied` = 'y' AND `record_type` = '" . $record_type . "'
        ";

        if (null !== $id_owner) {
            $sql .= "AND id_owner <> ".$this->db->quote($id_owner, 'integer');
        }

        if ('individual' == $record_type ) {
            $sql .= "
                UNION
                SELECT `id`
                FROM `{$this->db->tblMember}`
                WHERE `nrds_Id` = {$quotedNrds}";
            if (null != $id_owner) {
                $sql .= ' AND `id` != ' . $quotedIdOwner;
            }
        }

        if ('location' == $record_type) {
            $sql .= "
                UNION
                SELECT `id`
                FROM `{$this->db->tblLocation}`
                WHERE `nrds_Id` = {$quotedNrds}
            ";
            if (null != $id_owner) {
                $sql .= ' AND `id` != ' . $quotedIdOwner;
            }
        }

        $row = $this->db->queryAll($sql);
        $this->isDBError($row);

        if (empty($row)) {
            return true;
        }
        return false;
    }

    function clearNrdsId($recordType = 'individual')
    {
        if ($this->id) {
            $this->_initTable();
            $_nrds_id = (string)$this->getFromDB($this->id, 'nrds_id');
            require_once('class/DB/DBTable.php');
            $NRDS = new DBTable('NRDS');
            $where = '`id` = ' . S_db2::quoteBigInt($_nrds_id)
                . ' AND  `record_type` = ' . $this->db->quote($recordType)
                . ' AND `id_owner` = ' . $this->db->quote($this->id, 'integer');
            $NRDS->delete($where);

            $this->table->update(array('nrds_id' => NULL), 'id='.$this->db->quote($this->id, 'integer'));
            return array('id' => '');
        }
        return array('error'=>'Invalid Individual ID');
    }

    function updateNrdsId($nrds_id, $recordType = 'individual', $companyId)
    {
        $sql = "SELECT * FROM {$this->db->tblNRDS} WHERE id = " . S_db2::quoteBigInt($nrds_id) . " AND `record_type` = "
            . $this->db->quote($recordType);
        $addonWhere = '';
        if ($this->id) {
            $addonWhere .= ' AND `id_owner` = ' . $this->db->quote($this->id, 'integer');
        }
        $sql .= $addonWhere;
        $data = $this->db->queryRow($sql);
        if (is_array($data) && !empty($data)) {
            $updateData = array();
            if (!intval($data['id_owner'])) {
                $updateData['id_owner'] = intval($this->id);
            }
            if ($data['applied'] == 'n') {
                $updateData['applied'] = 'y';
            }
            $updateData['id_company'] = (int)$companyId;
            if (!empty($updateData)) {
                require_once('class/DB/DBTable.php');
                $NRDS = new DBTable('NRDS');
                $NRDS->update($updateData, 'id=' . S_db2::quoteBigInt($data['id']) . $addonWhere);
            }
        }
    }

    function _getWhere4DBLogin($user)
    {

        $sql = " WHERE {$this->login_field} = ".$this->db->quote($user['login'])
                ." AND {$this->pass_field}  = ".$this->db->quote($this->_encryptPass($user['password']))
                ." AND (
                    (id_mncar_type > 0 AND id_mncar_status IN (1, 3))
                    OR (id_mncar_l_type > 0 AND id_mncar_l_status IN (1, 3))
                    OR (id_misc_type > 0 AND id_misc_status IN (1, 3))
                  )
                ";
        return $sql;
    }

    function DBLogin($user)
    {
        $sql = "SELECT id FROM {$this->tableNameDB} ".$this->_getWhere4DBLogin($user);
        $id = $this->db->queryOne($sql);
        if (!MDB2::isError($id) && $id != 0) {
            $userOut = $this->getFromDB($id);
            $userOut[$this->pass_field] = $user[$this->pass_field];
            $userOut['id_history'] = $this->updateHistory($id);
        }else {
            $userOut = array();
        }

        return $userOut;
    }

    function updateHistory($id)
    {
        require_once('app/Member/MemberAuth.php');
        $id_history = (int)MemberAuth::getSessionData('id_history');
        require_once('class/DB/DBTable.php');
        $History = new DBTable($this->tableName.'History');
        if ($id_history) {
            $data = array(
                'last_activity_date'=>date('Y-m-d H:i:s'),
            );
            $History->update($data, 'id='.$this->db->quote($id_history));
            return $id_history;
        } else {
            $data = array(
                'id_individual' => $id,
                'login_date'=>date('Y-m-d H:i:s'),
                'last_activity_date'=>date('Y-m-d H:i:s'),
                'ip'=>$_SERVER['REMOTE_ADDR'],
                'id_session'=>Session::getId()
            );
            return $History->insert($data);
        }
    }

    function getMemberByEmail($email)
    {
        $sql = "
            SELECT
                {$this->tableName}.*,
                {$this->_getWhatMemberStatus()}
            FROM {$this->tableNameDB} as {$this->tableName}
            {$this->_getJoinMemberStatus()}
            WHERE email = ".$this->db->quote($email);
        $data = $this->db->queryRow($sql);
        $this->id = @$data['id'];
        return $data;
    }

    function getMemberByAccessCode($value)
    {
        $sql = "
            SELECT
                {$this->tableName}.*,
                {$this->_getWhatMemberStatus()}
            FROM {$this->tableNameDB} as {$this->tableName}
            {$this->_getJoinMemberStatus()}
            WHERE {$this->tableName}.access_code = ".$this->db->quote($value, 'text');
        $data = $this->db->queryRow($sql);
        $this->id = @$data['id'];
        return $data;
    }

    function updateAccessCode($id, $access_code, $field = 'access_code')
    {
        $this->_initTable();
        $this->table->update(array($field=>$access_code, 'access_code_changed'=>date('Y-m-d H:i:s')), 'id='.$this->db->quote($id, 'integer'));
    }

    function getAlphabet($ipp = 20, $where = 'where 1')
    {
        $ipp = (int)$ipp;
        $where = (string)$where;
        $alph = array();

        $sql = "select left(last_name, 1) as letter, count(*) as cnt
                from {$this->tableNameDB} as {$this->tableName}
                LEFT JOIN {$this->db->tblLocation} as Location ON Location.id = {$this->tableName}.id_location
                LEFT JOIN {$this->db->tblCompany} as Company ON Company.id = Location.id_company
                {$where}
                group by letter";
        $res = $this->db->queryAll($sql, null, MDB_FETCHMODE_DEFAULT, true);

        if (is_array($res) && !empty($res)) {
            $page = 0;
            $total = 0;
            foreach ($res as $letter => $count) {
                $alph[$letter] = $page;
                $total += $count;
                $page = floor($total / $ipp);
            }
        }
        return $alph;
    }

    function setAutoLogin($id_member)
    {
        $this->_initTable();
        $this->table->update(array('auto_login'=>'y'), 'id='.intval($id_member));
    }

    function validStatus($member)
    {
        $statuses = array();
        if ($member['id_mncar_type'] > 0) {
            $statuses[] = $member['id_mncar_status'];
        }
        if ($member['id_mncar_l_type'] > 0) {
            $statuses[] = $member['id_mncar_l_status'];
        }
        if ($member['id_misc_type'] > 0) {
            $statuses[] = $member['id_misc_status'];
        }
        $statuses = array_unique($statuses);
        if (false === array_search(1, $statuses) && false === array_search(3, $statuses)) {
            return ' Your account is not active in our system. Please <a href="' . BASE_URL . '/' .
                    SiteMap::getAliasByItem('HTMLBlock/Show', 'contact') . '">contact us</a> for more information.';
        }
        return true;
    }

    function validAgreement($member)
    {
        if ($member['is_accepted_legal_agreement'] != 'y') {
            return ' Your have not accept our legal agreement yet.';
        }
        return true;
    }

    function validActivated($member)
    {
        if ($member['email_verified'] == 'n' || $member['info_verified'] == 'n') {
            return 'Based on the email address you entered, our records indicate that you have not yet activated your '
                .'account according to the MNCAR user agreement.  To activate your account, you must verify  your email address.  '
                .'Please <a href="' . BASE_URL . '/' . SiteMap::getAliasByItemType('Member/Activation/Email').'?email='.urlencode($member['email']) . '">click here</a> '
                .'and we will send you instructions for account activation to your inbox.';
        }
        return true;
    }

    function getWhatTitledPhone($field_name)
    {
        $sql = "IF({$this->tableName}.{$field_name}_title <> '' AND {$this->tableName}.{$field_name} <> '', CONCAT(IF({$this->tableName}.{$field_name}_title = ' ', 'Other', {$this->tableName}.{$field_name}_title), ' ', {$this->tableName}.{$field_name}), {$this->tableName}.{$field_name}) as {$field_name}_full";
        return $sql;
    }

    function getRes4ExportPurchasable()
    {
        $sql = "
            SELECT
                {$this->tableName}.*,
                CONCAT({$this->tableName}.first_name, ' ', {$this->tableName}.middle_initial, IF ({$this->tableName}.middle_initial = '' , '', '. '), {$this->tableName}.last_name) as name,
                {$this->_getWhatMemberType()},
                {$this->_getWhatMemberStatus()},
                Company.name as company_name,
                Company.website as company_website,
                Location.name as location_name_original,
                IF(Location.name <> '', CONCAT(Location.name, IF(Location.physical_city <> '', ' (', ''), Location.physical_city, IF(Location.physical_city <> '', ') ', '')), CONCAT(Location.physical_address, ' - ', Location.physical_city, ', ', Location.physical_state, ' ', Location.physical_zip)) as location_name,
                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.phone as location_phone
            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  Company.id = Location.id_company
            LEFT JOIN {$this->tableNameDB}2EmailType as Member2EmailType
                ON  Member2EmailType.id_parent = {$this->tableName}.id
            LEFT JOIN {$this->tableNameDB}2EmailGroup as Member2EmailGroup
                ON  Member2EmailGroup.member_id = {$this->tableName}.id
            WHERE 1
                AND {$this->tableName}.id_mcpe_access_type IN (1, 2)
                AND (
                    {$this->tableName}.id_mncar_status IN (1, 3)
                    OR {$this->tableName}.id_mncar_l_status IN (1, 3)
                    OR {$this->tableName}.id_misc_status IN (1, 3)
                )
                AND {$this->tableName}.allow_mcpe_access = 'y'

                AND (Member2EmailType.id_parent IS NOT NULL OR Member2EmailGroup.member_id IS NOT NULL)
            GROUP BY {$this->tableName}.id
            ORDER BY {$this->tableName}.last_name
        ";
        $res = $this->db->query($sql);
        $this->_sqlError($res);
        return $res;
    }

    function getAllSupportedAccount()
    {
        $sql="SELECT DISTINCT {$this->tableName}.id, CONCAT(first_name, ' ', last_name)
            FROM {$this->tableNameDB} AS {$this->tableName}
                LEFT JOIN {$this->db->tblLocation} as Location ON {$this->tableName}.id_location=Location.id
                LEFT JOIN {$this->db->tblMember2Member} as Member2Member ON {$this->tableName}.id=Member2Member.id_member
            WHERE 1
                AND {$this->tableName}.is_support_account = 'n'
                AND (
                    (
                        {$this->tableName}.is_support_account != 'y'
                        AND Location.id_company = " . (int)$this->getFromDB($this->id, 'id_company')."
                        AND Location.id_company > 0
                        AND (
                            ({$this->tableName}.id_mncar_type > 0 AND {$this->tableName}.id_mncar_status IN (1, 3))
                            OR ({$this->tableName}.id_mncar_l_type > 0 AND {$this->tableName}.id_mncar_l_status IN (1, 3))
                            OR ({$this->tableName}.id_misc_type > 0 AND {$this->tableName}.id_misc_status IN (1, 3))
                        )
                    )
                    OR Member2Member.id_parent=".$this->id."
                    OR {$this->tableName}.id=".$this->id."
                )
            ORDER BY {$this->tableName}.last_name, {$this->tableName}.first_name";
        return ($this->db->queryAll($sql, 0, null, true));
    }

    public function generateAccessCode($id_member, $field)
    {
        do {
            $access_code = md5(rand(1, 1000) . 'mncar' . time() . rand(1, 1000) . $id_member);
            $sql = 'SELECT 1 FROM `' . $this->tableNameDB . '` '
                 . 'WHERE `' . $field . '` = ' . $this->db->quote($access_code, 'text') . ' '
                 . 'LIMIT 1';
            $res = $this->db->queryOne($sql);
            $this->isDBError($res);
        } while('1' == $res);
        $this->_initTable();
        $this->table->update(array($field => $access_code), 'id=' . intval($id_member));
        return $access_code;
    }

    public function clearAccessCode($field, $access_code)
    {
        $sql = 'UPDATE `' . $this->tableNameDB . '` '
             . 'SET `' . $field . '` = NULL '
             . 'WHERE 1 '
             . 'AND `' . $field . '` = ' . $this->db->quote($access_code, 'text') . ' '
             . 'LIMIT 1';
        $res = $this->db->queryRow($sql);
        $this->isDBError($res);
        return true;
    }

    public function isValidAccessCode($nrds_id, $field, $access_code)
    {
        $sql = 'SELECT * FROM `' . $this->tableNameDB . '` '
             . 'WHERE 1 '
             . 'AND `nrds_id` = ' . S_db2::quoteBigInt($nrds_id) . ' '
             . 'AND `' . $field . '` = ' . $this->db->quote($access_code, 'text') . ' '
             . 'LIMIT 1';
        $res = $this->db->queryRow($sql);
        $this->isDBError($res);
        return (empty($res)) ? false : $res;
    }

    public function getDMemberStatus4Select($type = null, $id_type = null)
    {
        $sql = 'SELECT `s`.`id`, `s`.`title` '
             . 'FROM `' . $this->db->tblDMemberStatus . '` AS `s` ';
        if (null !== $type) {
            $sql .= 'JOIN `' . $this->db->tblDMemberStatus2Type . '` AS `s2t` '
                  . 'ON  `s2t`.`type` = ' . $this->db->quote($type, 'text') . ' ';
            if (null !== $id_type) {
                $sql .= 'AND `s2t`.`id_type` = ' . $this->db->quote($id_type, 'integer') . ' ';
            }
            $sql .= 'AND `s2t`.`id_status` = `s`.`id` ';
        }
        $sql .= 'ORDER BY `s`.`sorter`';
        $res = $this->db->queryAll($sql, 0, null, true);
        $this->isDBError($res);
        return $res;
    }

    public static function hasStatus($member, $id_status)
    {
        if (is_array($id_status)) {
            foreach ($id_status as $id) {
                if (Member::_hasStatus($member, $id)) {
                    return true;
                }
            }
            return false;
        }
        return Member::_hasStatus($member, $id_status);
    }

    protected static function _hasStatus($member, $id_status)
    {
        return ($member['id_mncar_type'] > 0 && $member['id_mncar_status'] == $id_status)
            || ($member['id_mncar_l_type'] > 0 && $member['id_mncar_l_status'] == $id_status)
            || ($member['id_misc_type'] > 0 && $member['id_misc_status'] == $id_status);
    }

    public static function isActive($member)
    {
        return Member::hasStatus($member, array(1, 3));
    }

    public static function hasMemberCost($member)
    {
        if (in_array($member['id_mncar_type'], self::$hasMemberCostMncarIdTypes)
            || in_array($member['id_mncar_l_type'], self::$hasMemberCostMncarLIdTypes)
            || in_array($member['id_misc_type'], self::$hasMemberCostMiscIdTypes)
        ) {
            return true;
        }
        return false;
    }

    public static function hasEventRegistration($member)
    {
        return in_array($member['id_mncar_type'], self::$hasRegistrationMncarIdTypes)
            || in_array($member['id_mncar_l_type'], self::$hasRegistrationMncarLIdTypes)
            || in_array($member['id_misc_type'], self::$hasRegistrationMiscIdTypes);
    }

    public static function isMncar($member)
    {
        return $member['id_mncar_type'] > 0;
    }

    public static function getAddonWhereForMemberFind($tableName)
    {
        return "(
                (
                    {$tableName}.id_mncar_type IN (" . implode(', ', self::$findABrokerMncarIdTypes) . ")
                    AND {$tableName}.id_mncar_status IN (" . implode(', ', self::$activePendingStatuses) . ")
                )
                OR
                (
                    {$tableName}.id_mncar_l_type IN (" . implode(', ', self::$findABrokerMncarLIdTypes) . ")
                    AND {$tableName}.id_mncar_l_status IN (" . implode(', ', self::$activePendingStatuses) . ")
                )
            )";
    }

    public static function isMemberAvailableForFindABroker($member)
    {
        if (self::_isMncarTypeAvailableForFindABroker($member) || self::_isMncarLTypeAvailableForFindABroker($member)) {
            return true;
        }
        return false;
    }

    protected static function _isMncarTypeAvailableForFindABroker($member)
    {
        return (
            in_array($member['id_mncar_type'], self::$findABrokerMncarIdTypes)
            && in_array($member['id_mncar_status'], self::$activePendingStatuses)
        );
    }

    protected static function _isMncarLTypeAvailableForFindABroker($member)
    {
        return (
            in_array($member['id_mncar_l_type'], self::$findABrokerMncarLIdTypes)
            && in_array($member['id_mncar_l_status'], self::$activePendingStatuses)
        );
    }
}