<?php

  /*
   *  class Rets_Gjara_Winr_Migration
   *
   */

  class Rets_Gjara_Winr_Migration {

    //  configurables
    private $_cleanSlate = false;  //  start from scratch on all tables.  i.e., first truncate all WINR data tables.
    private $_resetAll = false;  //  if true, all timestamps comparisons ignored.  i.e., all records get updated
    private $_numCompareDays = 1;  //  number of days back to check RETS timestamps
    private $_classDebug = true;  //  show all database/query output (when script ran in browser)
    private $_mlsSystem = 'gjara';
    private $_retsClassIDS = array(
      'RESD' => 'property_resd',
      'LAND' => 'property_land',
      'CMML' => 'property_cmml',
      'MFAM' => 'property_mfam',
      'BUSOPPT' => 'property_busoppt',
      /*  'RRNTAL' => 'property_rrntal',  */
      'COMMLSE' => 'property_commlse',
      'Agent' => 'agent',
      'Office' => 'office'
    );
    const DEV_EMAIL = 'dennis.flexiss@gmail.com';
    const ADMIN_EMAIL = 'admin@flexiss.net';

    //  internals
    private $_db;
    private $_rs;
    private $_logID;
    private $_logMessage;
    private $_selectSQL;
    private $_insertSQL;
    private $_updateSQL;
    private $_deleteCwmlsHeaderDataSQL;
    private $_deleteCwmlsTrailerDataSQL;
    private $_deleteCwmlsAgentDataSQL;
    private $_deleteCwmlsOfficeDataSQL;
    private $_cwmlsHeadersTable;
    private $_cwmlsTrailersTables;
    private $_winrTrailersTable;
    private $_cwmlsAgentsTable;
    private $_cwmlsOfficesTable;
    private $_retsDataTables;
    private $_cwmlsAreasTable;
    private $_retsKey;
    private $_classSystemWhere;
    private $_currentRecordIDWhere;
    private $_retsClassID;
    private $_retsRecordIDValue;
    private $_retsRecordTimeStampValue;
    private $_retsListingTimeStampField;
    private $_retsAgentTimeStampField;
    private $_retsOfficeTimeStampField;
    private $_cwmlsRecordTable;
    private $_cwmlsRecordIDField;
    private $_cwmlsRecordMLSIDField;
    private $_cwmlsRecordListingIDField;
    private $_cwmlsRecordTimeStampField;
    private $_cwmlsRecordMLSIDValue;
    private $_cwmlsRecordListingIDValue;
    private $_cwmlsRecordTimeStampValue;
    private $_geoCodingURL;
    private $_geoCodingQuery;
    private $_sectionReportArray;
    private $_missingListingHeaderID;

    /*
     *
     *  function __construct()
     *
     *  class startup
     *
     *  @access public
     *  @var none
     *  @return none
     *
     */
    public function __construct() {
      $this->__classConfig();
      $this->__connectDB();
      $this->__processRequests();
    }

    /*
     *
     *  function _migrateRetsData()
     *
     *  move new RETS data into WINR system
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _migrateRetsData() {
      if ($this->_cleanSlate == true) {
        $this->_deleteWinrTrailerData();
        $this->_deleteWinrHeaderData();
        $this->_deleteWinrAgentData();
        $this->_deleteWinrOfficeData();
      }

      foreach ($this->_retsClassIDS as $classKey => $classValue) {
        $this->_retsClassID = $classKey;
        $this->_retsKey = $classValue;
        $this->_logMessage =  "\n" . date('H:i:s') . ' - Out Of Date Record Checks RETS/GJARA - Class ' . $this->_retsKey;
        $this->__logEntry('append', $this->_logMessage, 'gjara');
        //  1) setup rets check for any date stamps that are out
        if (!preg_match('/(agent|office)/i', strtolower($this->_retsClassID))) { /*  properties  */
          $this->_winrTrailersTable = $this->_getTrailersTable($classKey);
          $sql = "SELECT DISTINCT(t1.MST_MLS_NUMBER) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1 LEFT JOIN listing_headers t2 ON (t1.MST_MLS_NUMBER = t2.mls_no AND t2.mls_system = 'gjara') WHERE DATEDIFF(t1." . $this->_retsListingTimeStampField . ", FROM_UNIXTIME(t2.doa_ts)) > " . $this->_numCompareDays . ";";
        } else {
          if (strtolower($this->_retsClassID) == 'agent') {  //  agents
            $sql = "SELECT DISTINCT(t1.rets_agt_id) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1 LEFT JOIN listing_agents t2 ON (t1.rets_agt_id = t2.listing_agent_id AND t2.mls_system = 'gjara') WHERE DATEDIFF(t1." . $this->_retsAgentTimeStampField . ", FROM_UNIXTIME(t2.update_ts)) > " . $this->_numCompareDays . ";";
          } else {  //  offices
            $sql = "SELECT DISTINCT(t1.DO_OFFICE_ID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1  LEFT JOIN listing_agents t2 ON (t1.DO_OFFICE_ID = t2.listing_office_id AND t2.mls_system = 'gjara')  WHERE DATEDIFF(t1." . $this->_retsOfficeTimeStampField . ", FROM_UNIXTIME(t2.update_ts)) > " . $this->_numCompareDays . ";";
          }
        }
        $this->_runRetsQueries($sql);
        //  2) setup rets check for any new records
        $this->_logMessage =  "\n" . date('H:i:s') . ' - New Record Checks RETS/GJARA - Class ' . $this->_retsKey;
        $this->__logEntry('append', $this->_logMessage, 'gjara');
        //  1) setup rets check for any date stamps that are out
        if (!preg_match('/(agent)|(office)/i', strtolower($this->_retsClassID))) { /*  properties  */
          switch($this->_retsClassID) {
            case 'RESD':
              $classType = 'RESIDENTIAL';
              break;
            case 'LAND':
              $classType = 'LAND';
              break;
            case 'CMML':
              $classType = 'COMMERCIAL';
              break;
            case 'MFAM':
              $classType = 'MULTIFAMILY';
              break;
            case 'COMMLSE':
              $classType = 'LEASE';
              break;
          }
          $this->_winrTrailersTable = $this->_getTrailersTable($classKey);
          $sql = "SELECT * from " . sprintf($this->_retsDataTables, $this->_retsKey) . " where MST_MLS_NUMBER NOT IN (SELECT mls_no from " . $this->_cwmlsHeadersTable . " WHERE mls_system = 'gjara' AND class = '" . $classType . "');";
        } else {
          if (strtolower($this->_retsClassID) == 'agent') {  //  agents
            $sql = "SELECT * from " . sprintf($this->_retsDataTables, $this->_retsKey) . " where rets_agt_id NOT IN (SELECT listing_agent_id from listing_agents WHERE mls_system = 'gjara');";
          } else {  //  offices
            $sql = "SELECT * from " . sprintf($this->_retsDataTables, $this->_retsKey) . " where DO_OFFICE_ID NOT IN (SELECT listing_office_id from organizations WHERE mls_system = 'gjara');";
          }
        }
        $this->_runRetsQueries($sql);
      }
    }


    /***  HELPER FUNCTIONS  ***/


    /*
     *
     *  function _getTrailersTable()
     *
     *
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _getTrailersTable($classID) {
      switch($classID) {
        case 'RESD':
          $trailersTable = 'trailers_residential';
          break;
        case 'LAND':
          $trailersTable = 'trailers_land';
          break;
        case 'CMML': case 'BUSOPPT':
          $trailersTable = 'trailers_commercial';
          break;
        case 'MFAM':
          $trailersTable = 'trailers_multifamily';
          break;
        case 'COMMLSE':
          $trailersTable = 'trailers_lease';
          break;
/*
        case 'RRNTAL':
          $trailersTable = '?';
          break;
*/
      }
      return $trailersTable;
    }

    /*
     *
     *  function _runRetsQueries()
     *
     *
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _runRetsQueries($sql) {
      //  run rets query
      try {
        $this->_rs = $this->_db->Execute($sql);
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
      //  check returned rets records against cwmls records
      if ($this->_rs->RecordCount() > 0) {
        while (!$this->_rs->EOF) {
          $this->_setCurrrentRecordParams();
          if (!preg_match('/(agent|office)/i', strtolower($this->_retsClassID))) {  //  properties
            if ($this->_isRecordStored() == true) {
              //if ($this->_isRecordCurrent() == false) {  //  do updates
                $this->_updateRetsData($this->_cwmlsHeadersTable, $this->_stageHeaderData('update'));
                if ($this->_isTrailerStored() == true) {
                  $this->_updateRetsData($this->_winrTrailersTable, $this->_stageTrailerData('update'), true);
                } else {
                  $this->_insertRetsData($this->_winrTrailersTable, $this->_stageTrailerData('insert'), false, 'listing_header_id', $this->_missingListingHeaderID);
                }
              //}
            } else { //  do inserts
              $this->_insertRetsData($this->_cwmlsHeadersTable, $this->_stageHeaderData('insert'));
              $this->_insertRetsData($this->_winrTrailersTable, $this->_stageTrailerData('insert'), true, 'listing_header_id');
            }
          } else {  //  agents, offices
            if (strtolower($this->_retsClassID) == 'agent') {  //  agents
              if ($this->_isRecordStored() == true) {
                //if ($this->_isRecordCurrent() == false) {  //  do updates
                  $this->_updateRetsData($this->_cwmlsAgentsTable, $this->_stageAgentData('update'));
                //}
              } else { //  do inserts
                $this->_insertRetsData($this->_cwmlsAgentsTable, $this->_stageAgentData('insert'));
              }
            } else {  //  offices
              if ($this->_isRecordStored() == true) {
                //if ($this->_isRecordCurrent() == false) {  //  do updates
                  $this->_updateRetsData($this->_cwmlsOfficesTable, $this->_stageOfficeData('update'));
                //}
              } else { //  do inserts
                $this->_insertRetsData($this->_cwmlsOfficesTable, $this->_stageOfficeData('insert'));
              }
            }
          }
          $this->_rs->MoveNext();
        }
      }
    }

    /*
     *
     *  function _updateGeoCoding()
     *
     *  update geocoding status on mls records
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _updateGeoCoding() {
      $sqlSelect = <<<SQL
        SELECT listing_header_id,street_number,street_direction,street_name,city,state,zip
          FROM $this->_cwmlsHeadersTable
         WHERE (geocoding_status = 'N' OR geocoding_status = 'R')
         $this->_classSystemWhere
         LIMIT 1000;
SQL;
      $sqlUpdateSuccesses = <<<SQL
        UPDATE $this->_cwmlsHeadersTable
           SET
            accuracy_level='%s',
            latitude = '%s',
            longitude = '%s',
            geocoding_status = 'Y'
          WHERE listing_header_id = %s;
SQL;
      $sqlUpdateFailures = <<<SQL
        UPDATE $this->_cwmlsHeadersTable
           SET geocoding_status = 'U'
         WHERE listing_header_id = %s;
SQL;
      $this->_geoUpdateCount_200 = 0;
      $this->_geoUpdateCount_404 = 0;
      $rs = $this->_db->Execute($sqlSelect);
      if ($rs && $rs->RecordCount() > 0) {
        while (!$rs->EOF) {
          $listingHeaderID = $rs->fields['listing_header_id'];
          $streetAddress =
            trim($rs->fields['street_number']) .
            (trim($rs->fields['street_direction']) != ''
              ? ' ' . trim($rs->fields['street_direction'])
              : ''
            ) .
            ' ' . trim($rs->fields['street_name']) .
            ', ' . trim($rs->fields['city']) .
            ', ' . trim($rs->fields['state']) .
            ' ' . trim($rs->fields['zip']);
          $queryURL = $this->_geoCodingURL . sprintf($this->_geoCodingQuery, urlencode(strtolower($streetAddress)));
          if($queryData = file_get_contents($queryURL)) {
            $queryData = explode(',', $queryData);
            if(count($queryData) == 4) {
              if($queryData[0] == 200) {
                $querySuccess = true;
              } else {
                $querySuccess = false;
              }
            } else {
              $querySuccess = false;
            }
          } else {
            $querySuccess = false;
          }
          if($querySuccess) {  //  got geo coding
            try {
              $sql = sprintf($sqlUpdateSuccesses, $queryData[1],$queryData[2], $queryData[3],$listingHeaderID);
              if (!$this->_db->Execute($sql)) {
                $this->__exitOnError('db-sql', $sql);
              } else {
                $this->_updateMetrics('geo_coding', '200');
              }
            } catch (Exception $e) {
              $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
            }
          } else {  //  no geo coding
            try {
              $sql = sprintf($sqlUpdateFailures, $listingHeaderID);
              if (!$this->_db->Execute($sql)) {
                $this->__exitOnError('db-sql', $sql);
              } else {
                $this->_updateMetrics('geo_coding', '404');
              }
            } catch (Exception $e) {
              $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
            }
          }
          $rs->MoveNext();
        }
      }
    }

    /*
     *
     *  function _updateMetrics()
     *
     *  maintain update/insert info
     *    for reporting
     *
     *  @access private
     *  @var (string) $sectionKey
     *    section to update
     *  @var (array) $countType
     *    operation type insert/update
     *  @return none
     *
     */
    private function _updateMetrics($sectionKey, $countType) {
      if (array_key_exists($sectionKey, $this->_retsClassIDS)) { //  rets data
        $sectionTitle = ucwords(str_replace('_', ' ' , $this->_retsClassIDS[$sectionKey]));
        if (!is_array($this->_sectionReportArray)) {  //  array not started
          $this->_sectionReportArray = array();
          $this->_sectionReportArray[$sectionTitle] = array('update' => 0, 'insert' => 0);
        } else if (!array_key_exists($sectionTitle, $this->_sectionReportArray)) {  //  section not started
          $this->_sectionReportArray[$sectionTitle] = array('update' => 0, 'insert' => 0);
        }
        $this->_sectionReportArray[$sectionTitle][$countType]++;
      } else {  //  geo coding data
        $sectionTitle = ucwords(str_replace('_', ' ' , $sectionKey));
        if (!is_array($this->_sectionReportArray)) {  //  array not started
          $this->_sectionReportArray = array();
          $this->_sectionReportArray[$sectionTitle] = array('200' => 0, '404' => 0);
        } else if (!array_key_exists($sectionTitle, $this->_sectionReportArray)) {  //  section not started
          $this->_sectionReportArray[$sectionTitle] = array('200' => 0, '404' => 0);
        }
        $this->_sectionReportArray[$sectionTitle][$countType]++;
      }
    }

    /*
     *
     *  function _setCurrrentRecordParams()
     *
     *  load current (iterated) mls record params
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _setCurrrentRecordParams() {
      if(!preg_match('/(agent|office)/i', strtolower($this->_retsClassID))) {  //  properties
        $this->_cwmlsRecordTable = $this->_cwmlsHeadersTable;
        $this->_cwmlsRecordMLSIDField = 'mls_no';
        $this->_cwmlsRecordListingIDField = 'listing_header_id';
        $this->_cwmlsRecordTimeStampField = 'doa_ts';
        $this->_retsRecordIDValue = $this->_rs->fields['MST_MLS_NUMBER'];
        $this->_retsRecordTimeStampValue = (int) $this->_convertRetsDate($this->_rs->fields[$this->_retsListingTimeStampField]);
        $this->_currentRecordIDWhere = sprintf(" WHERE %s = '%s' AND mls_system = 'gjara'", $this->_cwmlsRecordMLSIDField, $this->_retsRecordIDValue);
      } else {  //  agents, offices
        if (strtolower($this->_retsClassID) == 'agent') {  //  agent
          $this->_cwmlsRecordTable = $this->_cwmlsAgentsTable;
          $this->_cwmlsRecordIDField = 'listing_agent_id';
          $this->_cwmlsRecordTimeStampField = 'update_ts';
          $this->_retsRecordIDValue = $this->_rs->fields['rets_agt_id'];
          $this->_retsRecordTimeStampValue = (int) $this->_convertRetsDate($this->_rs->fields[$this->_retsAgentTimeStampField]);
          $this->_currentRecordIDWhere = sprintf(" WHERE %s = '%s' AND mls_system = 'gjara'", $this->_cwmlsRecordIDField, $this->_retsRecordIDValue);
        } else {  //  office
          $this->_cwmlsRecordTable = $this->_cwmlsOfficesTable;
          $this->_cwmlsRecordIDField = 'listing_office_id';
          $this->_cwmlsRecordTimeStampField = 'update_ts';
          $this->_retsRecordIDValue = $this->_rs->fields['DO_OFFICE_ID'];
          $this->_retsRecordTimeStampValue = (int) $this->_convertRetsDate($this->_rs->fields[$this->_retsOfficeTimeStampField]);
          $this->_currentRecordIDWhere = sprintf(" WHERE %s = '%s' AND mls_system = 'gjara'", $this->_cwmlsRecordIDField, $this->_retsRecordIDValue);
        }
      }
    }

    /*
     *
     *  function _isRecordStored()
     *
     *  if record is currently saved
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _isRecordStored() {
      if(!preg_match('/(agent|office)/i', strtolower($this->_retsClassID))) {  //  properties
        $fields = $this->_cwmlsRecordListingIDField . ", " . $this->_cwmlsRecordTimeStampField;
      } else {  //  offices/agents
        $fields = $this->_cwmlsRecordTimeStampField;
      }
      $sql = sprintf("SELECT %s FROM %s%s;", $fields, $this->_cwmlsRecordTable, $this->_currentRecordIDWhere);
      $rs = $this->_db->Execute($sql);
      if ($rs && $rs->RecordCount() == 1) {
        if(!preg_match('/(agent|office)/i', strtolower($this->_retsClassID))) {  //  properties
          $this->_cwmlsRecordListingIDValue = $rs->fields[$this->_cwmlsRecordListingIDField];
        }
        $this->_cwmlsRecordTimeStampValue = $rs->fields[$this->_cwmlsRecordTimeStampField];
        return true;
      } else {
        return false;
      }
    }

    /*
     *
     *  function _isTrailerStored()
     *
     *  if trailer record is currently saved
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _isTrailerStored() {
      $sql = sprintf("SELECT %s FROM %s%s;", 'listing_header_id', $this->_cwmlsRecordTable, $this->_currentRecordIDWhere);
      $rs = $this->_db->Execute($sql);
      if ($rs && $rs->RecordCount() == 1) {
        $sql1 = sprintf("SELECT * FROM %s%s;", $this->_winrTrailersTable, ' WHERE listing_header_id = ' . $rs->fields['listing_header_id'] . '');
        $rs1 = $this->_db->Execute($sql1);
        if ($rs1 && $rs1->RecordCount() == 1) {
          return true;
        } else {
          $this->_missingListingHeaderID = $rs->fields['listing_header_id'];
          return false;
        }
      } else {
        return false;
      }
    }

    /*
     *
     *  function _isRecordCurrent()
     *
     *  if record time stamp is current or not
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _isRecordCurrent() {
      if ($this->_resetAll == true) return false;  //  i.e, update record
      if ($this->_cwmlsRecordTimeStampValue >= $this->_retsRecordTimeStampValue) {
        return true;
      } else {
        return false;
      }
    }

    /*
     *
     *  function _updateRetsData()
     *
     *  update RETS records in WINR system
     *
     *  @access private
     *  @var (string) $updateTable
     *    update table
     *  @var (array) $updateData
     *    associative data: fields -> values
     *  @var (string) $recordField
     *    anchor record field
     *  @var (variable) $recordID
     *    anchor record value
     *  @return none
     *
     */
    private function _updateRetsData($updateTable, $updateData, $isTrailerTable = false) {
      $updateFieldValueSets = array();
      foreach ($updateData as $updateField => $updateValue) {
        $fieldName = $updateField;
        if (is_numeric($updateValue) && !preg_match('/(x)/i', $updateValue)) {
          $fieldValue = $updateValue;
        } else {
          $fieldValue = "'" . $updateValue . "'";
        }
        $updateFieldValueSets[] = $fieldName . " = " . $fieldValue;
      }
      if ($isTrailerTable == false) {  //  not a trailer table
        $sql = sprintf($this->_updateSQL, $updateTable, implode(',', $updateFieldValueSets), $this->_currentRecordIDWhere);
      } else {
        $trailerWhere = sprintf(" WHERE listing_header_id = %s", $this->_cwmlsRecordListingIDValue);
        $sql = sprintf($this->_updateSQL, $updateTable, implode(',', $updateFieldValueSets), $trailerWhere);
      }
      try {
        if (!$this->_db->Execute($sql)) {
          $this->__exitOnError('db-sql', $sql);
        } else {
          $this->_updateMetrics($this->_retsClassID, 'update');
        }
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }

    /*
     *
     *  function _insertRetsData()
     *
     *  insert RETS records into WINR system
     *
     *  @access private
     *  @var (string) $insertTable
     *    insert table
     *  @var (array) $insertData
     *    associative data: fields -> values
     *  @var (boolean) $useLastInsertID [optional]
     *    t/f to add last inserted field name and its id
     *  @var (string) $insertIDName [optional]
     *    last inserted id field name
     *  @return none
     *
     */
    private function _insertRetsData($insertTable, $insertData, $useLastInsertID = false, $insertIDName = '', $missingInsertID = '') {
      $fieldNames = array();
      $fieldValues = array();
      if ($useLastInsertID == true) {
        $fieldNames[] = $insertIDName;
        $fieldValues[] = $this->_db->Insert_ID();
      } else if (!empty($missingInsertID)) {
        $fieldNames[] = $insertIDName;
        $fieldValues[] = $missingInsertID;
      }
      foreach ($insertData as $insertField => $insertValue) {
        $fieldNames[] = $insertField;
        if (is_numeric($insertValue) && !preg_match('/(x)/i', $insertValue)) {
          $fieldValues[] = $insertValue;
        } else {
          $fieldValues[] = "'" . $insertValue . "'";
        }
      }
      $sql = sprintf($this->_insertSQL, $insertTable, implode(',', $fieldNames), implode(',', $fieldValues));
      try {
        if (!$this->_db->Execute($sql)) {
          $this->__exitOnError('db-sql', $sql);
        } else {
          $this->_updateMetrics($this->_retsClassID, 'insert');
        }
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }


    private function _winrCleanup() {
      $goodListingHeaderIDs = <<<SQL
        SELECT t1.listing_header_id AS id
           FROM listing_headers t1, %s t2
          WHERE     t1.rets_class = '%s'
                AND t1.mls_system = 'gjara'
                AND t1.mls_no NOT LIKE '%s'
                AND t1.flexiss_status != 'oe'
                AND t1.mls_no = t2.MST_MLS_NUMBER
SQL;
      $badListingHeaderIDs = <<<SQL
        SELECT listing_header_id AS id
          FROM listing_headers
         WHERE listing_header_id NOT IN
                  (%s)
               AND rets_class = '%s'
               AND mls_system = 'gjara'
               AND mls_no NOT LIKE '%s'
               AND flexiss_status != 'oe'
SQL;

      $deleteTrailerHeaderIDs = <<<SQL
        DELETE
          FROM %s
         WHERE listing_header_id IN
                  (%s)
SQL;

      $deleteListingHeaderIDs = <<<SQL
        DELETE
          FROM listing_headers
         WHERE listing_header_id IN
                  (%s)
SQL;

      $retsTableTpl = 'rets_to_gjara_%s';
      //$this->__setDB('cwmls_db.config.php');
      foreach ($this->_retsClassIDS as $classID => $classTable) {
        $this->_classID = $classID;
        //  rets class pseudo-function
        switch($this->_classID) {
          case 'RESD':
            $trailersTable = 'trailers_residential';
            break;
          case 'LAND':
            $trailersTable = 'trailers_land';
            break;
          case 'CMML': case 'BUSOPPT':
            $trailersTable = 'trailers_commercial';
            break;
          case 'MFAM':
            $trailersTable = 'trailers_multifamily';
            break;
          case 'COMMLSE':
            $trailersTable = 'trailers_lease';
            break;
        }
        if ($trailersTable != '') {
          //  get good header ids
          $rs = $this->_db->Execute(sprintf($goodListingHeaderIDs, sprintf($retsTableTpl, $classTable), $this->_classID, 'oe%'));
          $goodIDs = array();
          if ($rs && $rs->RecordCount() > 0) {
            while (!$rs->EOF) {
              $goodIDs[] = $rs->fields['id'];
              $rs->MoveNext();
            }
          }
          //  get bad header ids
          $rs = $this->_db->Execute(sprintf($badListingHeaderIDs, implode(',', $goodIDs), $this->_classID, 'oe%'));
          $badIDs = array();
          if ($rs && $rs->RecordCount() > 0) {
            while (!$rs->EOF) {
              $badIDs[] = $rs->fields['id'];
              $rs->MoveNext();
            }
          }
          if (count($badIDs) > 0) {
            //  delete from trailer table
            $rs = $this->_db->Execute(sprintf($deleteTrailerHeaderIDs, $trailersTable, implode(',', $badIDs)));
            //  delete from header table
            $rs = $this->_db->Execute(sprintf($deleteListingHeaderIDs, implode(',', $badIDs)));
          }
        }
      }

    }


    /*
     *
     *  function _deleteWinrTrailerData()
     *
     *  delete trailer table data from WINR system
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _deleteWinrTrailerData() {
      try {
        foreach ($this->_retsClassIDS as $classKey => $classValue) {
          if (preg_match('/(property\_)/i', $classValue)) {
            $trailerTable = $this->_getTrailersTable($classKey);
            $sql = sprintf($this->_deleteCwmlsTrailerDataSQL, $trailerTable, $this->_cwmlsHeadersTable, $this->_mlsSystem, '%');
            $rs = $this->_db->Execute($sql);
          }
        }
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }

    /*
     *
     *  function _deleteWinrHeaderData()
     *
     *  delete header table data from WINR system
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _deleteWinrHeaderData() {
      try {
        $sql = sprintf($this->_deleteCwmlsHeaderDataSQL,$this->_mlsSystem, '%');
        $rs = $this->_db->Execute($sql);
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }

    /*
     *
     *  function _deleteWinrAgentData()
     *
     *  delete agent table data from WINR system
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _deleteWinrAgentData() {
      try {
        $sql = sprintf($this->_deleteCwmlsAgentDataSQL, $this->_mlsSystem);
        $rs = $this->_db->Execute($sql);
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }

    /*
     *
     *  function _deleteWinrOfficeData()
     *
     *  delete office/organization table data from WINR system
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _deleteWinrOfficeData() {
      try {
        $sql = sprintf($this->_deleteCwmlsOfficeDataSQL, $this->_mlsSystem);
        $rs = $this->_db->Execute($sql);
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }

    /*
     *
     *  function _stageHeaderData()
     *
     *  prepare/map RETS record data for WINR header table insert
     *
     *  @access private
     *  @var (string) $action
     *    stage for 'update' or 'insert' action
     *  @return array
     *
     */
    private function _stageHeaderData($action) {
      $rs = $this->_rs;
      //  items that need to be parsed first
      $addressNumber = addslashes($rs->fields['Street_Num']);
      $addressDirection = addslashes($rs->fields['Direction']);
      $addressStreetName = addslashes($rs->fields['Address']);
      $address = $addressNumber . ($addressDirection != '' ? ' ' . $addressDirection : '') . ' ' . $addressStreetName;
      $phone1 = $rs->fields['rets_la_phone1'];
      $areaMajor = $rs->fields['City'];//'Grand Junction';
      $areaMinor = addslashes(trim($rs->fields['MLS_Area']));
      if (strtolower($areaMajor) != strtolower($areaMinor)) {
        if ($areaMinor != '') {
          $this->_saveMinorArea($areaMajor, $areaMinor);  //  area major from board name
        } else {
          $areaMinor = 'Unknown';
        }
      }
      switch($this->_retsClassID) {
        case 'RESD':
          $propertyClass = 'RESIDENTIAL';
          break;
        case 'LAND':
          $propertyClass = 'LAND';
          break;
        case 'CMML':
          $propertyClass = 'COMMERCIAL';
          break;
        case 'BUSOPPT':
          $propertyClass = 'BUSOPPT';
          break;
        case 'MFAM':
          $propertyClass = 'MULTIFAMILY';
          break;
        case 'COMMLSE':
          $propertyClass = 'LEASE';
          break;
      }
      $headerData = array(
        'class' => $propertyClass,
        'type' => $rs->fields['Property_Type'],
        'area' => $rs->fields['MLS_Area'],
        'list_price' => $rs->fields['List_Price'],
        'street' => $address,
        'city' => $rs->fields['City'],
        'state' => $rs->fields['State'],
        'zip' => $rs->fields['ZipCode'],
        'status' => $rs->fields['Property_Status'],
        'water_supplier' => $rs->fields['Wtr_Co'],
        'sewer_supplier' => $rs->fields['Swr_Type'],
        'gas_supplier' => $rs->fields['Gas_Co'],
        'electric_supplier' => $rs->fields['Elec_Co'],
        'title_company' => $rs->fields['Title_Co'],
        'total_taxes' => $rs->fields['Taxes'],
        'remarks' => $rs->fields['Remarks'],
        'listing_agent_1' => addslashes($rs->fields['rets_la_first_name'] . ' ' . $rs->fields['rets_la_last_name']),
        'listing_agent_phone' => $phone1,
        'listing_office_1' => addslashes($rs->fields['rets_lo_name']),
        'zoning' => $rs->fields['Zoning'],
        'confidential_remarks' => $rs->fields['Agent_Remarks'],
        'mls_system' => $this->_mlsSystem,
        'board_name' => 'gjara',
        'listing_agent_id' => $rs->fields['rets_list_agt_id'],
        'listing_office_id' => $rs->fields['off_Number'],
        'street_number' => $addressNumber,
        'street_direction' => $addressDirection,
        'street_name' => $addressStreetName,
        'directions' => $rs->fields['Directions'],
        'doa_ts' => (int) $this->_convertRetsDate($rs->fields[$this->_retsListingTimeStampField]),
        'picture_count' => $rs->fields['rets_photo_count'],
        'rets_class' => $this->_retsClassID,
        'structure_type' => $this->_formatCommas($rs->fields['CF_A']),
        'major_area' => $areaMajor,
        'tax_schedule_number' => $rs->fields['off_Number'],
        'vtour' => (trim($rs->fields['Virtual_Tour']) != '' ? (!preg_match('/(http)/', trim($rs->fields['Virtual_Tour'])) ? 'http://' . $rs->fields['Virtual_Tour'] : $rs->fields['Virtual_Tour']) : ''),
        'tax_year' => $rs->fields['Tax_Yr']
      );
      //  on insert only...
      //  i.e. stuff not to update
      if ($action == 'insert') {
        $headerData = array_merge($headerData, array(
          'mls_no' => $rs->fields['MST_MLS_NUMBER'],
          'internal_listing_id' => $rs->fields['MST_MLS_NUMBER'],
          'flexiss_status' => '',
          'geocoding_status' => 'N',
          'longitude' => '',
          'latitude' => '',
          'accuracy_level' => ''
          ));
      }
      return $headerData;
    }

    /*
     *
     *  function _stageTrailerData()
     *
     *  prepare/map RETS record data for WINR trailer table insert
     *
     *  @access private
     *  @var (string) $action
     *    stage for 'update' or 'insert' action
     *  @return array
     *
     */
    private function _stageTrailerData($action) {
      $rs = $this->_rs;
      //  class specific trailers_* table values
      switch ($this->_retsClassID) {
        case 'RESD':  //  class RESD (trailers_residential)
          $trailerData = array(
            'subdivision_name' => $rs->fields['Subdivision'],
            'elementary_school' => $rs->fields['Elementary_School'],
            'middle_school' => $rs->fields['Middle_School'],
            'high_school' => $rs->fields['High_School'],
            'lot_dimensions' => $rs->fields['Lot_Dim'],
            'approximate_total_acres' => $rs->fields['Ttl_Acres'],
            'approximate_irrigated_acres' => $rs->fields['Apx_Irrig_Acres'],
            'irrigation_district' => $rs->fields['Irrig_Dist'],
            'irrigation_description' => $rs->fields['Irrig_Desc'],
            'area_main_sf' => $rs->fields['Apx_Main_SqFt'],
            'area_upper_sf' => $rs->fields['Apx_Uppr_SqFt'],
            'area_lower_sf' => $rs->fields['Apx_Lwr_SqFt'],
            'area_basement_sf' => $rs->fields['Apx_Unf_Bsmt_SqFt'],
            'area_fbasement_sf' => $rs->fields['Apx_Fin_Bsmt_SqFt'],
            'approximate_total_sf' => $rs->fields['Ttl_Apx_SqFt'],
            'year_built' => $rs->fields['Year_Blt'],
            'number_bedrooms' => $rs->fields['Bedroom'],
            'number_bathrooms' => $rs->fields['Num_Baths'],
            'bathrooms_description' => $rs->fields['Bath_Desc'],
            'dimensions_master_br' => $rs->fields['MBd_Size_1'],
            'dimensions_living_room' => $rs->fields['Lvg_Size_1'],
            'dimensions_br2' => $rs->fields['Bd2_Size_1'],
            'dimensions_dining_room' => $rs->fields['Dng_Size_1'],
            'dimensions_br3' => $rs->fields['Bd3_Size_1'],
            'dimensions_family_room' => $rs->fields['Fam_Size_1'],
            'dimensions_br4' => $rs->fields['Bd4_Size_1'],
            'dimensions_kitchen' => $rs->fields['Kit_Size_1'],
            'dimensions_office_den' => $rs->fields['Othr_Size_1'],
            'dimensions_laundry_utility' => $rs->fields['Lndry_Size_1'],
            'garage_capacity' => $rs->fields['Gar_Cap'],
            'garage_type' => $rs->fields['garage_type'],
            'hoa' => $rs->fields['CICx'],
            'hoa_dues' => $rs->fields['CIC_Dues'],
            'hoa_dues_frequency' => $rs->fields['CIC_Frequency'],
            'energy_rating' => $rs->fields['Energy_Rtg'],
            'energy_features' => $rs->fields['CF_W'],
            'landscaping_description' => $rs->fields['CF_S'],
            'patio_description' => $rs->fields['CF_T'],
            'year_remodeled' => $rs->fields['Year_Remodld'],
            'fireplace' => $this->_formatCommas($rs->fields['CF_M']),
            'heating' => $this->_formatCommas($rs->fields['CF_F']),
            'roof' => $this->_formatCommas($rs->fields['CF_D']),
            'construction_siding' => $this->_formatCommas($rs->fields['CF_C']),
            'foundation' => $this->_formatCommas($rs->fields['CF_E']),
            'flooring' => $this->_formatCommas($rs->fields['CF_N']),
            'heating_source' => $this->_formatCommas($rs->fields['CF_F']),
            'cooling' => $this->_formatCommas($rs->fields['CF_G']),
            'dom_water' => $this->_formatCommas($rs->fields['CF_H']),
            'appliances_included' => $this->_formatCommas($rs->fields['CF_K']),
            'interior_inclusions' => $this->_formatCommas($rs->fields['CF_Q']),
            'exterior_inclusions' => $this->_formatCommas($rs->fields['CF_R']),
            'fence_description' => $this->_formatCommas($rs->fields['CF_J']),
            'street_description_access' => $this->_formatCommas($rs->fields['CF_O']),
            'lot_size_acres' => $this->_formatCommas($rs->fields['Lot_Sz']),
            'sewer_description' => $this->_formatCommas($rs->fields['CF_I']),
            'lot_description' => $this->_formatCommas($rs->fields['CF_P']),
            'hoa_amenities' => $this->_formatCommas($rs->fields['CF_V']),
            'inclusions' => '',
            'extras' => '',
            'building_faces'=> '',
            'number_half_baths' => '',
            'lot_sf' => '',
            'lv_ht_sf' => '',
            'above_ground_sf' => '',
            'below_ground_sf' => '',
            'unfinished_sf' => '',
            'garage_sf' => '',
            'patio_deck_sf' => '',
            'number_floors' => '',
            'short_termable' => '',
            'furnished' => '',
            'under_construction' => ''
          );
          //  on insert only...
          //  i.e. stuff not to update
          if ($action == 'insert') {
            /*
            $trailerData = array_merge($trailerData, array(
              '' => $rs->fields['']
              ));
            */
          }
          break;
        case 'LAND':  //  class LAND (trailers_land)
          $trailerData = array(
            'subdivision_name' => $rs->fields['Subdivision'],
            'elementary_school' => $rs->fields['Elementary_School'],
            'middle_school' => $rs->fields['Middle_School'],
            'high_school' => $rs->fields['High_School'],
            'lot_dimensions' => $rs->fields['Lot_Dim'],
            'approximate_total_acres' => $rs->fields['Ttl_Acres'],
            'approximate_irrigated_acres' => $rs->fields['Apx_Irrig_Acres'],
            'irrigation_district' => $rs->fields['Irrig_Dist'],
            'curbs_gutters_in_paid' => $rs->fields['CF_M'],
            'special_assessment' => $rs->fields['Spcl_Assmt'],
            'earnest_money_holder' => $rs->fields['Earnst_Mny_Holder'],
            'possession' => $rs->fields['Possessn'],
            'hoa' => $rs->fields['CICx'],
            'hoa_dues_frequency' => $rs->fields['CIC_Frequency'],
            'hoa_dues' => $rs->fields['CIC_Dues'],
            'hoa_fees_include' => $this->_formatCommas($rs->fields['CF_L']),
            'lot_description' => $this->_formatCommas($rs->fields['CF_D']),
            'lot_improvements' => $this->_formatCommas($rs->fields['CF_J']),
            'reo_lender_owned' => $rs->fields['REOx'],
            'elevation' => $rs->fields['Elevation'],
            'irrigated_acreage_description' => $this->_formatCommas($rs->fields['Irrig_Desc']),
            'irrigation_water_description' => $this->_formatCommas($rs->fields['Irrig_Wtr']),
            'present_use' => $this->_formatCommas($rs->fields['Current_Use']),
            'street_description_access' => $this->_formatCommas($rs->fields['CF_E']),
            'fence_description' => $this->_formatCommas($rs->fields['CF_I']),
            'views_description' => $this->_formatCommas($rs->fields['LFD_VIEWS_178']),
            'possible_use' => $this->_formatCommas($rs->fields['CF_C']),
            'number_bedrooms' =>  $rs->fields['Bedroom'],
            'number_bathrooms' =>  $rs->fields['Num_Baths'],
            'lot_sf' => '',
            'improved_w_home' => '',
            'home_sf' => '',
            'home_condition' => '',
            'home_style' => '',
            'home_year_built' => '',
            'extras' => '',
            'hoa_amenities' => '',
            'allowable_uses' => ''
          );
          //  on insert only...
          //  i.e. stuff not to update
          if ($action == 'insert') {
            /*
            $trailerData = array_merge($trailerData, array(
              '' => $rs->fields['']
              ));
            */
          }
          break;
        case 'CMML': case 'BUSOPPT': //  class CMML|BUSOPPT (trailers_commercial)
          $trailerData = array(
            'loading_dock' => $rs->fields['Doc_Hi'],
            'railroad_siding' => $rs->fields['Rail_Spur'],
            'books_available' => $rs->fields['CF_V'],
            'sale_includes' => $this->_formatCommas($rs->fields['CF_S']),
            'lease_includes' => $this->_formatCommas($rs->fields['CF_T']),
            'year_built' => $rs->fields['Year_Blt'],
            'approximate_total_acres' => $rs->fields['Ttl_Acres'],
            'area_office_sf' => $rs->fields['Ofc_Ttl_SqFt'],
            'area_warehouse_sf' => $rs->fields['Whs_Ttl_SqFt'],
            'approximate_total_sf' => $rs->fields['Cmml_Ttl_Bldg_SqFt'],
            'lot_dimensions' => $rs->fields['Lot_Dim'],
            'air_condition' => $this->_formatCommas($rs->fields['CF_I']),
            'heat_type' => $this->_formatCommas($rs->fields['CF_H']),
            'amperage' => $this->_formatCommas($rs->fields['Amperage']),
            'volts' => $rs->fields['Volts'],
            'ceiling_height' => $rs->fields['Ceilg_Hgt'],
            'number_overhead_doors' => $rs->fields['Num_Ovrhd_Drs'],
            'ada_yn' => $rs->fields['ADA_Cmplnt'],
            'possession' => $rs->fields['Possessn'],
            'subdivision_or_location' => $rs->fields['Subdivision'],
            'year_remodeled' => $rs->fields['Year_Remodld'],
            'number_parking_spaces' => $rs->fields['Num_Off_Str_Prkg_Spcs'],
            'lease_type' => $rs->fields['CF_U'],
            'number_dock_hi_doors' => $rs->fields['Num_Doc_Hi_Drs'],
            'roof' => $this->_formatCommas($rs->fields['CF_F']),
            'subdivision_name' => $rs->fields['Subdivision'],
            'heating_source' => $this->_formatCommas($rs->fields['CF_G']),
            'heating_system' => $this->_formatCommas($rs->fields['CF_H']),
            'floor_type' => $this->_formatCommas($rs->fields['CF_E']),
            'hoa_includes' => $this->_formatCommas($rs->fields['LFD_HOAINCLUDES_73']),
            'hoa_dues' => $rs->fields['CIC_dues'],
            'hoa_frequency' => $rs->fields['CIC_Frequency'],
            'hoa' => $rs->fields['CICx'],
            'street_description_access' => $this->_formatCommas($rs->fields['CF_K']),
            'current_use' => $this->_formatCommas($rs->fields['Current_Use']),
            'building_construction' => '',
            'earnest_money_deposit' => '',
            'earnest_money_holder' => '',
            'year_started' => '',
            'description' => '',
            'lot_sf' => '',
            'special_assessment' => '',
            'number_water_meters' => '',
            'number_gas_meters' => '',
            'number_electric_meters' => '',
            'remaining_lease_months' => '',
            'inventory_value' => '',
            'ebitda' => '',
            'condominiumized' => '',
            'construction' =>  '',
            'expenses_include' => '',
            'green_features' => '',
            'amenities' => '',
            'extras' => '',
            'heating_cooling' => ''
          );
          //  on insert only...
          //  i.e. stuff not to update
          if ($action == 'insert') {
            /*
            $trailerData = array_merge($trailerData, array(
              '' => $rs->fields['']
              ));
            */
          }
          break;
        case 'MFAM':  //  class MFAM (trailers_multifamily)
          $trailerData = array(
            'subdivision_name' => $rs->fields['Subdivision'],
            'elementary_school' => $rs->fields['Elementary_School'],
            'middle_school' => $rs->fields['Middle_School'],
            'high_school' => $rs->fields['High_School'],
            'lot_dimension' => $rs->fields['Lot_Dim'],
            'approximate_total_acres' => $rs->fields['Ttl_Acres'],
            'irrigation_district' => $rs->fields['Irrig_Dist'],
            'approximate_total_sf' => $rs->fields['Ttl_Bldg_SqFt'],
            'year_built' => $rs->fields['Year_Blt'],
            'property_manager' => $rs->fields['Prop_Mgr'],
            'no_of_units' => $rs->fields['Ttl_Units'],
            'hoa' => $rs->fields['CICx'],
            'hoa_dues' => $rs->fields['CIC_Dues'],
            'hoa_dues_frequency' => $rs->fields['CIC_Frequency'],
            'approximate_irrigated_acres' => $rs->fields['Apx_Irrig_Acres'],
            'year_remodeled' => $rs->fields['Year_Remodld'],
            'number_water_meters' => $rs->fields['Sep_Wtr_Meters'],
            'number_gas_meters' => $rs->fields['Sep_Gas_Meters'],
            'number_electric_meters' => $rs->fields['Sep_Elec_Meters'],
            'interior_inclusions' => $this->_formatCommas($rs->fields['CF_N']),
            'appliances_included' => $this->_formatCommas($rs->fields['CF_K']),
            'flooring' => $this->_formatCommas($rs->fields['CF_M']),
            'heating_source' => $this->_formatCommas($rs->fields['CF_G']),
            'cooling' => $this->_formatCommas($rs->fields['CF_H']),
            'exterior_inclusions' => $this->_formatCommas($rs->fields['CF_Q']),
            'fireplaces_description' => $this->_formatCommas($rs->fields['CF_L']),
            'structure_type' => $this->_formatCommas($rs->fields['CF_C']),
            'construction_siding' => $this->_formatCommas($rs->fields['CF_D']),
            'foundation' => $this->_formatCommas($rs->fields['CF_F']),
            'roof' => $this->_formatCommas($rs->fields['CF_E']),
            'lot_size_acres' => $this->_formatCommas($rs->fields['Lot_Sz']),
            'street_description_access' => $this->_formatCommas($rs->fields['CF_O']),
            'hoa_includes' => $this->_formatCommas($rs->fields['CF_T']),
            'lot_description' => $this->_formatCommas($rs->fields['CF_P'])
          );
          //  on insert only...
          //  i.e. stuff not to update
          if ($action == 'insert') {
            /*
            $trailerData = array_merge($trailerData, array(
              '' => $rs->fields['']
              ));
            */
          }
          break;
        case 'COMMLSE':  //  //  class COMMLSE (trailers_lease)
          $trailerData = array(
            'lot_dimensions' => $rs->fields['Lot_Dim'],
            'year_built' => $rs->fields['Year_Blt'],
            'year_remodeled' => $rs->fields['Year_Remodld'],
            'limited_service' => $rs->fields['Lmtd_Svc'],
            'area_office_sf' => $rs->fields['Ofc_Ttl_SqFt'],
            'area_warehouse_sf' => $rs->fields['Whs_Ttl_SqFt'],
            'approximate_total_sf_comm' => $rs->fields['Ttl_Bldg_SqFt'],
            'number_floors' => $rs->fields['Num_Lvls'],
            'air_condition' => $rs->fields['CF_I'],
            'heat_type' => $rs->fields['CF_H '],
            'ada_yn' => $rs->fields['ADA_Cmplnt'],
            'amperage' => $rs->fields['Amperage'],
            'volts' => $rs->fields['Volts'],
            'ceiling_height' => $rs->fields['Ceilg_Hgt'],
            'number_dockhigh_doors' => $rs->fields['Num_Doc_Hi_Drs'],
            'number_overhead_doors' => $rs->fields['Num_Ovrhd_Drs'],
            'security_deposit_amount' => $rs->fields['Sec_Dep_Amt'],
            'possession' => $rs->fields['Possessn'],
            'irrigation_water' => $rs->fields['Irrig_Wtr'],
            'directions' => $rs->fields['Direction'],
            'subdivision_name' => $rs->fields['Subdivision'],
            'lease_type' => $rs->fields['CF_Q'],
            'number_parking_spaces' => $rs->fields['Num_Off_Str_Prkg_Spcs'],
            'building_use' => $this->_formatCommas($rs->fields['Current_Use']),
            'heating_source' => $this->_formatCommas($rs->fields['CF_G']),
            'heating_system' => $this->_formatCommas($rs->fields['CF_H']),
            'roof' => $this->_formatCommas($rs->fields['CF_F']),
            'floor' => $this->_formatCommas($rs->fields['CF_E']),
            'lot_description' => $this->_formatCommas($rs->fields['CF_M'])
          );
          //  stuff not to update
          if ($action == 'insert') {
            /*
            $trailerData[] = array('' => $rs->fields['']);
            */
          }
          break;
      }
      return $trailerData;
    }

    /*
     *
     *  function _stageAgentData()
     *
     *  prepare/map RETS record data for WINR agent table insert
     *
     *  @access private
     *  @var (string) $action
     *    stage for 'update' or 'insert' action
     *  @return array
     *
     */
    private function _stageAgentData($action) {
      $rs = $this->_rs;
      $phone1 = $rs->fields['DA_PHONE1'];
      $phone2 = $rs->fields['DA_PHONE2'];
      $boardName = 'gjara';
      $agentData = array(
        //'agent_designation' => $rs->fields['U_FreeFormDesig'], /* all together now */
        'listing_office_id' => $rs->fields['DA_DO'], /* not entirely sure here */
        'fname' => $rs->fields['DA_FIRST_NAME'],
        'lname' => $rs->fields['DA_LAST_NAME'],
        'mail_address' => $rs->fields['DA_MAIL_ADDRESS1'],
        'mail_city' => $rs->fields['DA_MAIL_CITY'],
        'mail_state' => $rs->fields['DA_MAIL_STATE'],
        'mail_zip' => $rs->fields['DA_MAIL_ZIP'],
        'phone1' => $phone1,
        'phone2' => $phone2,
        'email' => $rs->fields['DA_EMAIL'],
        'url' => $rs->fields['DA_URL'],
        'agent_id2' => $rs->fields['rets_agt_id'],
        'update_ts' => (int) $this->_convertRetsDate($rs->fields[$this->_retsAgentTimeStampField]),
        'nrds' => $rs->fields['DA_NERDS'],
        'agent_type' => $this->_convertAgentType($rs->fields['da_type']),
        'board_name' => $boardName
      );
      //  on insert only...
      //  i.e. stuff not to update
      if ($action == 'insert') {
        $agentData = array_merge($agentData, array(
          'listing_agent_id' => $rs->fields['rets_agt_id'],
          'mls_system' => $this->_mlsSystem,
          'designation_abr' => '', /* not used */
          'designation_ccim' => '', /* not used */
          'designation_cips' => '', /* not used */
          'designation_crb' => '', /* not used */
          'designation_crs' => '', /* not used */
          'designation_epro' => '', /* not used */
          'designation_ecobroker' => '', /* not used */
          'designation_gri' => '', /* not used */
          'designation_mre' => '' /* not used */
          ));
      }
      return $agentData;
    }

    /*
     *
     *  function _stageOfficeData()
     *
     *  prepare/map RETS record data for WINR office table insert
     *
     *  @access private
     *  @var (string) $action
     *    stage for 'update' or 'insert' action
     *  @return array
     *
     */
    private function _stageOfficeData($action) {
      $rs = $this->_rs;
      $phone1 = $rs->fields['DO_PHONE1'];
      $phone2 = $rs->fields['DO_PHONE2'];
      $boardName = 'gjara';
      $officeData = array(
        'branch_id' => $rs->fields['DO_OFFICE_ID'],
        'org_short_name' => $rs->fields['DO_NAME'],
        'org_name' => $rs->fields['DO_NAME'],
        'org_address' => $rs->fields['DO_MAIL_ADDRESS1'],
        'org_city' => $rs->fields['DO_MAIL_CITY'],
        'org_state' => $rs->fields['DO_MAIL_STATE'],
        'org_zip' => $rs->fields['DO_MAIL_ZIP'],
        'org_mail_address' => $rs->fields['DO_MAIL_ADDRESS1'],
        'org_mail_city' => $rs->fields['DO_MAIL_CITY'],
        'org_mail_state' => $rs->fields['DO_MAIL_STATE'],
        'org_mail_zip' => $rs->fields['DO_MAIL_ZIP'],
        'org_phone1' => $phone1,
        'org_phone2' => $phone2,
        'org_email' => $rs->fields['DO_EMAIL'],
        'org_url' => $rs->fields['DO_URL'],
        'update_ts' => (int) $this->_convertRetsDate($rs->fields[$this->_retsOfficeTimeStampField]),
        'board_name' => $boardName
      );
      //  on insert only...
      //  i.e. stuff not to update
      if ($action == 'insert') {
        $officeData = array_merge($officeData, array(
          'listing_office_id' => $rs->fields['DO_OFFICE_ID'],
          'mls_system' => $this->_mlsSystem
          ));
      }
      return $officeData;
    }

    /*
     *
     *  function _saveMinorArea()
     *
     *  check to add new minor area values
     *    to WINR areas table
     *
     *  @access private
     *  @var (string) $areaMajor
     *    major area criteria
     *  @var (string) $areaMinor
     *    minor area value
     *  @return none
     *
     */
    private function _saveMinorArea($areaMajor, $areaMinor) {
      if ($areaMajor == '' || $areaMajor == 'Unknown') return;
      try {
        $sql = sprintf($this->_selectSQL, $this->_cwmlsAreasTable, " WHERE areas_major LIKE '%" . $areaMajor . "%' AND areas_minor LIKE '%" . $areaMinor . "%'" . $this->_classSystemWhere);
        $rs = $this->_db->Execute($sql);
        if (!$rs || $rs->RecordCount() == 0) { //  no area info yet for this record, do insert
          $areaFields = "`areas_id`,`areas_major`,`areas_code`,`areas_minor`,`mls_system`,`board_name`";
          $areaValues = "NULL,'" . $areaMajor . "','','" . $areaMinor . "', '" . $this->_mlsSystem . "',''";
          $sql = sprintf($this->_insertSQL, $this->_cwmlsAreasTable, $areaFields, $areaValues);
          if (!$this->_db->Execute($sql)) $this->__exitOnError('db-sql', $sql);
        }
      } catch (Exception $e) {
        $this->__exitOnError('db-sql', $sql . '\n\nException:  ' . $e->getMessage());
      }
    }

    /*
     *
     *  function _formatCommas()
     *
     *  @access protected
     *  @var none
     *  @return none
     *
     */
    private function _formatCommas($input) {
      if (!preg_match('/(,)/', $input)) return $input;
      return implode(', ', explode(',', $input));
    }

    /*
     *
     *  function _convertRetsDate()
     *
     *  get converted MLS value from RETS record
     *
     *  @access private
     *  @var (string) $inValue
     *    value to convert
     *  @return integer
     *
     */
    private function _convertRetsDate($inValue) {
      // comes in as:  2009-07-24 19:59:00
      list($dateSplit, $timeSplit) = explode(' ', $inValue);
      $date = explode('-', $dateSplit);
      $time = explode(':', $timeSplit);
      return mktime((int) $time[0],(int) $time[1], $time[2],(int) $date[1],(int) $date[2],(int) $date[0]);
    }


    /*
     *
     *  function _convertAgentType()
     *
     *  get converted MLS value from RETS record
     *
     *  @access private
     *  @var (string) $inValue
     *    value to convert
     *  @return string
     *
     */
    private function _convertAgentType($inValue) {
      $inValue = strtolower(trim($inValue));
      switch(true) {
        case preg_match('/(appraiser)/i', strtolower(trim($inValue))):
          $outValue = '71';
          break;
        case preg_match('/(staff)|(assistant)/i', strtolower(trim($inValue))):
          $outValue = '72';
          break;
        case preg_match('/(agent)|(broker)/i', strtolower(trim($inValue))):
          $outValue = '73'; //  translate to 'broker'
          break;
        case preg_match('/(realtor)/i', strtolower(trim($inValue))):
          $outValue = '83'; //  translate to 'broker associate'
          break;
        default:
          $outValue = '99';
      }
      return $outValue;
    }

    /***  INTERNAL FUNCTIONS  ***/


    /*
     *
     *  function __classConfig()
     *
     *  class parameters setup/staging
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function __classConfig() {
      ini_set('display_errors', ($this->_classDebug ? 1 : 0));
      ini_set('memory_limit', -1);
      error_reporting(($this->_classDebug ? E_ALL ^ E_NOTICE : 0));
      set_time_limit(60 * 60);
      $geoCodingAPIKey = 'ABQIAAAA3mflrJEA2Lj1xt616unjgxQdpy4jhlTB5mlqMpK1QATM9eUVEhRwduxBu13bHVNXZVnXrsQ65nxX4A';
      $geoCodingOutput = 'csv';
      $this->_geoCodingURL = 'http://maps.google.com/maps/geo?&key=' . $geoCodingAPIKey . '&output=' . $geoCodingOutput;
      $this->_geoCodingQuery = '&q=%s';
      $this->_classSystemWhere = " AND mls_system = '" . $this->_mlsSystem . "'";
      $this->_cwmlsHeadersTable = 'listing_headers';
      $this->_cwmlsAgentsTable = 'listing_agents';
      $this->_cwmlsOfficesTable = 'organizations';
      $this->_cwmlsTrailersTables = 'trailers_%s';
      $this->_cwmlsAreasTable = 'areas';
      $this->_retsDataTables = 'rets_to_gjara_%s';
      $this->_retsListingTimeStampField = 'sys_Last_Modified';
      $this->_retsAgentTimeStampField = 'DA_MODIFIED';
      $this->_retsOfficeTimeStampField = 'DO_MODIFIED';
      $this->_selectSQL = "SELECT * from %s%s;";
      $this->_insertSQL = "INSERT INTO %s (%s) VALUES (%s);";
      $this->_updateSQL = "UPDATE %s SET %s%s;";
      $this->_deleteCwmlsTrailerDataSQL = <<<SQL
        DELETE FROM %s
         WHERE listing_header_id IN (
           SELECT DISTINCT(listing_header_id)
             FROM %s
            WHERE mls_system = '%s'
              AND (mls_no NOT LIKE 'oe%s' AND flexiss_status != 'oe')
          );
SQL;
      $this->_deleteCwmlsHeaderDataSQL = <<<SQL
        DELETE FROM $this->_cwmlsHeadersTable
         WHERE mls_system = '%s'
           AND mls_no NOT LIKE 'oe%s';
SQL;
      $this->_deleteCwmlsAgentDataSQL = <<<SQL
        DELETE FROM $this->_cwmlsAgentsTable
         WHERE mls_system = '%s'
           AND status != 'oe';
SQL;
      $this->_deleteCwmlsOfficeDataSQL = <<<SQL
        DELETE FROM $this->_cwmlsOfficesTable
         WHERE mls_system = '%s';
SQL;
    }

    /*
     *
     *  function __processRequests()
     *
     *  process primary class entry/exit functions
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function __processRequests() {
      $this->_logMessage = date('M d, Y  H:i:s') . ' - Starting RETS/GJARA Migration' . "\n";
      $this->__logEntry('start', $this->_logMessage, 'gjara');
      $this->_migrateRetsData();
      $this->_logMessage = "\n" . date('H:i:s') . ' - Cleaning up RETS/GJARA Data';
      $this->__logEntry('append', $this->_logMessage, 'gjara');
      $this->_winrCleanup();
      $this->_logMessage = "\n" . date('H:i:s') . ' - Updating RETS/GJARA Geo-Coding';
      $this->__logEntry('append', $this->_logMessage, 'gjara');
      $this->_updateGeoCoding();
      $this->_logMessage = "\n\n" . date('M d, Y  H:i:s') . ' - Ending RETS/GJARA Migration';
      $this->__logEntry('end', $this->_logMessage, 'gjara');
      $this->__exitOnComplete('RETS Migration Completed');
    }

    /*
     *
     *  function __connectDB()
     *
     *  via ADODB/LITE, make DB connection
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function __connectDB() {
      require_once '_adodb/cwmls_db.config.php';
      require_once '_adodb/adodb.inc.php';
      $ADODB_FETCH_MODE = 'ADODB_FETCH_BOTH';
      $this->_db = ADONewConnection('mysql');
      $this->_db->debug = $this->_classDebug;
      if(!$this->_db->Connect(DB_HOST, DB_USER, DB_PASS, DB_USE))
        $this->__exitOnError('db-conn');
    }

    /*
     *
     *  function __exitOnError()
     *
     *  error processing and email alerts
     *  script terminates here
     *
     *  @access private
     *  @var (string) $type
     *    pre-defined error label
     *  @var (string) $data [optional]
     *    any extra formatted data for email
     *  @return none
     *
     */
    private function __exitOnError($type, $data = '') {
      $email = $this->_classDebug == true ? self::DEV_EMAIL : self::ADMIN_EMAIL;
      $subject = 'GJARA RETS/WINR MIGRATION Script Error Occurred';
      switch ($type) {
        case 'db-conn':
          $message = 'Database connection failed.';
          @mail($email, $subject, $message);
          exit();
          break;
        case 'db-sql':
          $message = 'Database SQL failed.' . "\n\n" . $data;
          @mail($email, $subject, $message);
          exit();
          break;
        case 'query-empty':
          $message = 'Expected SQL query results were empty.' . "\n\n" . $data;
          @mail($email, $subject, $message);
          exit();
        case 'log-sql':
          $message = 'Log writing failure.' . "\n\n" . $data;
          @mail($email, $subject, $message);
          exit();
          break;
      }
    }

    /*
     *
     *  function __exitOnComplete()
     *
     *  completion processing and email alerts
     *  script terminates here
     *
     *  @access private
     *  @var (string) $classMsg
     *    class operation info executed
     *  @return none
     *
     */
    private function __exitOnComplete($classMsg) {
      $email = $this->_classDebug == true ? self::DEV_EMAIL : self::ADMIN_EMAIL;
      $subject = 'GJARA RETS/WINR MIGRATION Script Completed';
      @mail($email, $subject,
        $classMsg .
        print_r($this->_sectionReportArray, true));
      exit();
    }


    /*
     *
     *  function __logEntry()
     *
     *  loggin script activity
     *
     *  @access private
     *  @var (string) $state
     *
     *  @var (string) $logText
     *
     *  @var (string) $system
     *
     *  @return none
     *
     */
    private function __logEntry($state, $logText, $system) {
      $sqlSelect = "SELECT audit_text FROM `audits` WHERE `audit_id` = %s;";
      $sqlInsert = "INSERT INTO `audits` (%s) VALUES (%s);";
      $sqlUpdate = "UPDATE `audits` SET %s WHERE `audit_id` = %s;";
      if ($state == 'start') {
        $logEntry = time() . ",0,'" . $logText . "','" . $system . "'";
        $sql = sprintf($sqlInsert, 'ts_start,ts_end,audit_text,mls_system', $logEntry);
        if($this->_db->Execute($sql) === false) {
          //  operation failed
          $this->__exitOnError('log-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
        } else {
          $this->_logID = $this->_db->Insert_ID();
        }
      } else if ($state == 'append') {
        //  append to current text
        $this->_rs = $this->_db->Execute(sprintf($sqlSelect, $this->_logID));
        if ($this->_rs && $this->_rs->RecordCount() == 1) {
          $logText = $this->_rs->fields['audit_text'] . $logText;
          $logEntry = "audit_text = '" . $logText . "'";
          $sql = sprintf($sqlUpdate, $logEntry, $this->_logID);
          if($this->_db->Execute($sql) === false) {
            //  operation failed
            $this->__exitOnError('log-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
          }
        } else {
          //  operation failed
          $this->__exitOnError('log-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
        }
      } else {  // end
        //  complete current text
        $this->_rs = $this->_db->Execute(sprintf($sqlSelect, $this->_logID));
        if ($this->_rs && $this->_rs->RecordCount() == 1) {
          $logText = $this->_rs->fields['audit_text'] . $metrics . $logText;
          $logEntry = "ts_end = " . time() . ", audit_text = '" . $logText . "'";
          $sql = sprintf($sqlUpdate, $logEntry, $this->_logID);
          if($this->_db->Execute($sql) === false) {
            //  operation failed
            $this->__exitOnError('log-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
          }
        } else {
          //  operation failed
          $this->__exitOnError('log-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
        }
      }
    }

  }  //  end class

  //  self init
  $thisClass = new Rets_Gjara_Winr_Migration();