<?php

  /*
   *  class Winr_Cren_Rets
   *
   *  wrapper class for the phrets API to communicate with RETS servers
   *
   *  this class is configured to "self init", so just calling it via a url
   *  with the appropriate "load" parameter will kick off the __construct() function
   *
   *  allowed request parameters:
   *  http://idx.flexiss.net/phrets/class.winr_cren_rets-1.0.0.php?load=(re_1|ld_2|ci_3|mf_4|fr_5|ls_6|agent|office)
   *
   *  @author Dennis Williams <dennis@flexiss.net>
   *  @copyright © Jan 2010 - Flexiss Digital Design
   *  @version 1.0.0
   *
   *  external requirements:
   *
   *  phRETS class api/documentation
   *    ./class.phrets.php
   *    http://www.troda.com/projects/phrets/docs.php

   *  adodb-lite db class/documentation
   *    ./_adodb/adodb.inc.php
   *    http://adodblite.sourceforge.net/
   *
   */
  require_once('FirePHPCore/fb.php');
  class Winr_Cren_Rets {

    private $_rets;
    private $_db;
    private $_rs;

    private $_logID;
    private $_logMessage;
    private $_classDebug = false;
    private $_insertCount = 0;
    private $_updateCount = 0;
    private $_imageScaleCount = 0;
    private $_deletePropCount = 0;
    private $_deleteImageCount = 0;
    private $_photoCount = 0;
    private $_retsClassID = '';
    private $_retsClassType = '';
    private $_retsClassIDS = array(
      'RE_1' => 'property_residential',
      'LD_2' => 'property_land',
      'CI_3' => 'property_commercial',
      'MF_4' => 'property_multifamily',
      'FR_5' => 'property_farm_ranch',
      'LS_6' => 'property_lease',
      'TF_7' => 'property_timeshare_fractional',
      'AGENT' => 'agent',
      'OFFICE' => 'office'
    );  //  left out for now...('tf_7' => 'property_timeshare_fractional')
    private $_retsClassName = '';
    //  this should eventually come from a config file, or something?
    //  these fields need to be added to the data and translation tables eventually
    private $_fieldsToSkipRegEx = array();

    const DEV_EMAIL = 'dennis.flexiss@gmail.com';
    const ADMIN_EMAIL = 'dennis.flexiss@gmail.com';
    const RETS_SRV_VER = 'RETS/1.5';
    const RETS_SRV_URL = 'http://cren.rets.fnismls.com/rets/fnisrets.aspx/CREN/login';
    const RETS_SRV_USER = 'winner';
    const RETS_SRV_PASS = 'fl3x1ss';

    const LISTING_FIELD_ID = 'L_ListingID';
    const AGENT_FIELD_ID = 'U_AgentID';
    const OFFICE_FIELD_ID = 'O_OfficeID';
    const LISTING_TIME_STAMP_FIELD = 'L_UpdateDate';
    const MLS_NUM_FIELD = 'L_ListingID';  //  same as listind ID in this system
    const AGENT_TIME_STAMP_FIELD = 'U_UpdateDate';
    const OFFICE_TIME_STAMP_FIELD = 'O_UpdateDate';
    const LISTING_SEARCH_FIELD = 'L_ListingID'; /*'L_HotSheetDate';*/
    const AGENT_SEARCH_FIELD = 'U_user_is_active';
    const OFFICE_SEARCH_FIELD = 'O_UpdateDate';
    const LISTING_SEARCH_VALUE = '0-1000000';
    const AGENT_SEARCH_VALUE = '1';
    const OFFICE_SEARCH_VALUE = '1990-01-01+';

    const RETS_CREN_IMG_TABLE = "`rets_to_cren_img_import`";
    const LISTING_PHOTO_TIME_STAMP_FIELD = 'L_Last_Photo_updt';
    const PHOTO_SMALL_KEY = 'Photo';
    //const PHOTO_LARGE_KEY = 'HiRes';  //  not needed?
    const RESIDENTIAL_LISTING_PHOTO_PATH = '/home/flexiss/public_html/idx/cren/rets/property_images';
    const RESIDENTIAL_LISTING_PHOTO_BASE_URL = 'http://idx.flexiss.net/cren/rets/property_images';
    const OFFICE_LISTING_PHOTO_PATH = '/home/flexiss/public_html/idx/cren/rets/office_images';
    const OFFICE_LISTING_PHOTO_BASE_URL = 'http://idx.flexiss.net/idx/cren/office_images';
    const AGENT_LISTING_PHOTO_PATH = '/home/flexiss/public_html/idx/cren/rets/agent_images';
    const AGENT_LISTING_PHOTO_BASE_URL = 'http://idx.flexiss.net/cren/rets/agent_images';

    /*
     *
     *  function __construct()
     *
     *  internal var _classDebug determines verbosity
     *  executes function _processRequest()
     *
     *  @access public
     *  @var none
     *  @return none
     *
     */
    public function __construct() {
      ini_set('display_errors', ($this->_classDebug ? 1 : 0));
      error_reporting(($this->_classDebug ? E_ALL ^ E_NOTICE : 0));
      set_time_limit(60*60);
      $this->_connectDB();
      $this->_processRequest();
    }

    /*
     *
     *  function _loadDataClass()
     *
     *  by value of internal var _retsClassType,
     *  retrieve apprropriate class data from
     *  RETS server and load/synch with local DB
     *
     *  auto-connect local DB
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _loadDataClass() {
      if (!eregi('(AGENT|OFFICE)', $this->_retsClassType)) {  //  property
        $recordFieldID = self::LISTING_FIELD_ID;
        $timeStampField = self::LISTING_TIME_STAMP_FIELD;
      } else {  //  office/agent
        if (eregi('(OFFICE)', $this->_retsClassType)) {  //  office
          $recordFieldID = self::OFFICE_FIELD_ID;
          $timeStampField = self::OFFICE_TIME_STAMP_FIELD;
        } else {  // agent
          $recordFieldID = self::AGENT_FIELD_ID;
          $timeStampField = self::AGENT_TIME_STAMP_FIELD;
        }
      }
      $sqlTable = "`rets_to_cren_" . $this->_retsClassName . "`";
      $sqlInsert = "INSERT INTO " . $sqlTable . " (%s) VALUES (%s);";
      $sqlUpdate = "UPDATE " . $sqlTable . " SET %s WHERE `" . $recordFieldID . "` = '%s';";
      $sqlTruncate = "TRUNCATE TABLE " . $sqlTable . ";";

      $recordID = ''; $timeStamp = ''; $status = '';
      $fieldBlackList = implode('|', $this->_fieldsToSkipRegEx);

      if (!eregi('(AGENT|OFFICE)', $this->_retsClassID)) {  //  property search param
        $retsSearchParam = self::LISTING_SEARCH_FIELD . '=' . self::LISTING_SEARCH_VALUE;
        if($this->_db->Execute($sqlTruncate) === false) {
          //  truncate failed
          $this->_exitOnError('db-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
        } else {
          //echo $sqlTruncate;
          //exit();
        }
      } else if (eregi('(AGENT)', $this->_retsClassID)) {  //  agent search param
        $this->_retsClassType = ucwords($this->_retsClassType);
        $this->_retsClassID = $this->_retsClassType;
        $retsSearchParam = self::AGENT_SEARCH_FIELD . '=' . self::AGENT_SEARCH_VALUE;
      } else {  //  office search param
        $this->_retsClassType = ucwords($this->_retsClassType);
        $this->_retsClassID = $this->_retsClassType;
        $retsSearchParam = self::OFFICE_SEARCH_FIELD . '=' . self::OFFICE_SEARCH_VALUE;
      }
      $recordLimit = 250;
      $recordOffset = 0;
      $bFinished = false;
      $i = 0;
      while ($bFinished == false) {
        $i++;
        //echo 'search count = ' . count($searchResults) . ' AND offset = ' . $recordOffset . '<br />';
        try {
          $search = $this->_rets->Search($this->_retsClassType, $this->_retsClassID, '(' . $retsSearchParam . ')', array('Limit' => $recordLimit, 'Offset' => $recordOffset));
fb($this->_retsClassType, 'search class type');
fb($this->_retsClassID, 'search class id');
fb($retsSearchParam, 'search query');
fb($search, 'search results');
          if (is_array($search)) $this->_loadRetsRecords($search);
        } catch (Exception $e) {
          $bFinished = false;
          $this->_exitOnError('rets-search', $sql . '\n\nException:  ' . $e->getMessage());
        }
        $recordOffset = (($recordLimit * $i) + 1);
        if($this->_rets->IsMaxrowsReached() == false) $bFinished = true;
      }

    }

    function _loadRetsRecords($searchResults) {
      if (!eregi('(AGENT|OFFICE)', $this->_retsClassType)) {  //  property
        $recordFieldID = self::LISTING_FIELD_ID;
        $timeStampField = self::LISTING_TIME_STAMP_FIELD;
        $imageTimeStampField = self::LISTING_PHOTO_TIME_STAMP_FIELD;
      } else {  //  office/agent
        if (eregi('(OFFICE)', $this->_retsClassType)) {  //  office
          $recordFieldID = self::OFFICE_FIELD_ID;
          $timeStampField = self::OFFICE_TIME_STAMP_FIELD;
        } else {  // agent
          $recordFieldID = self::AGENT_FIELD_ID;
          $timeStampField = self::AGENT_TIME_STAMP_FIELD;
        }
      }
      $recordID = ''; $timeStamp = ''; $status = '';
      $sqlTable = "`rets_to_cren_" . $this->_retsClassName . "`";
      $sqlInsert = "INSERT INTO " . $sqlTable . " (%s) VALUES (%s);";
      $sqlUpdate = "UPDATE " . $sqlTable . " SET %s WHERE `" . $recordFieldID . "` = '%s';";
      $sqlTruncate = "TRUNCATE TABLE " . $sqlTable . ";";

      //  step thru all rets records
      foreach ($searchResults as $listing => $listingIndex) {

        //  reset loop vars
        $updateRecord = false;
        $newTimeStamp = false;
        $newRecord = false;
        $sqlInsertFields = array();
        $sqlInsertValues = array();
        $sqlUpdates = array();
        //  load values for this rets record
        foreach ($listingIndex as $listingKey => $listingValue) {
          //if (!eregi('(' . $fieldBlackList . ')', strtolower($listingKey))) {
            if ($listingKey == $recordFieldID) $recordID = $listingIndex[$listingKey];
            if ($listingKey == $timeStampField) $timeStamp = $listingIndex[$listingKey];
            if ($listingKey == $imageTimeStampField) $imageTimeStamp = $listingIndex[$listingKey];
            //if ($listingKey == self::LISTING_SEARCH_FIELD) $status = $listingIndex[$listingKey];
            $sqlInsertFields[] = "`" . $listingKey . "`";
            $sqlInsertValues[] = "'" . htmlentities(addslashes($listingIndex[$listingKey]), ENT_QUOTES) . "'";
            $sqlUpdates[] = "`" . $listingKey . "` = '" . htmlentities(addslashes($listingIndex[$listingKey]), ENT_QUOTES) . "'";
          //}
        }
        if (!empty($recordID)) {
          //  check listing id existence, and/or changed timestamp
          $sql = "SELECT `" . $timeStampField . "` FROM " . $sqlTable . " WHERE `" . $recordFieldID . "` = '%s';";
          $this->_rs = $this->_db->Execute(sprintf($sql, $recordID));
          if ($this->_rs && $this->_rs->RecordCount() == 0) {
            $newRecord = true;
          } else {  //  check to update
            if ($this->_convertRetsDate($this->_rs->Fields($timeStampField)) < $this->_convertRetsDate($timeStamp)) {
              $updateRecord = true;
            }
            //  also check the image time stamp on properties
            if ($updateRecord == false && !eregi('(AGENT|OFFICE)', $this->_retsClassType)) {  //  property
              if ($this->_convertRetsDate($this->_rs->Fields($imageTimeStampField)) < $this->_convertRetsDate($imageTimeStamp)) {
                $updateRecord = true;
              }
            }
          }
          if ($newRecord) {  //  non-existent (new) record
            //  do insert
            $sql = sprintf($sqlInsert, implode(',', $sqlInsertFields), implode(',', $sqlInsertValues));
            if($this->_db->Execute($sql) === false) {
              //  insert failed
              $this->_exitOnError('db-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
            } else {
              $this->_insertCount++;
            }
          } else {  //  existing (modified) record, do update
            if ($updateRecord == true) {
              $sql = sprintf($sqlUpdate, implode(',', $sqlUpdates), $recordID);
              if($this->_db->Execute($sql) === false) {
                //  update failed;
                $this->_exitOnError('db-sql', $this->_db->ErrorMsg() . "\n\n" . $sql);
              } else {
                $this->_updateCount++;
              }
            }
          }
        }

      }  //  end for each
    }


    /*
     *
     *  function _connectDB()
     *
     *  via ADODB/LITE, make DB connection
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _connectDB() {
      require_once '_adodb/config.inc.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 _connectRETS()
     *
     *  establish connection to RETS server
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _connectRETS() {
      require_once 'class.phrets-1.0rc1.php';
      $this->_rets = new phRETS;
      $this->_rets->AddHeader('Accept', '*/*');
      $this->_rets->AddHeader('RETS-Version', self::RETS_SRV_VER);
      $this->_rets->AddHeader('User-Agent', 'PHRETS/1.0');
      $this->_rets->SetParam('cookie_file', 'phrets_cookies.txt');
      $this->_rets->SetParam("force_basic_authentication", TRUE);
      //$this->_rets->SetParam("compression_enabled", true);
      $this->_rets->SetParam('debug_mode', $this->_classDebug); // ends up in rets_debug.txt
      if ($this->_rets->Connect(self::RETS_SRV_URL, self::RETS_SRV_USER, self::RETS_SRV_PASS) == false)
        $this->_exitOnError('rets-conn');
    }

    /*
     *
     *  function _processRequest()
     *
     *  processing $_REQUEST parameter
     *  auto-connect RETS server and
     *  auto-execute function, _loadDataClass()
     *
     *  all requests except (AGENT|OFFICE)
     *
     *  @access private
     *  @var none
     *  @return none
     *
     */
    private function _processRequest() {
      /*  requests by class IDs
        http://idx.flexiss.net/phrets/class.winr_cren_rets-1.0.0.php?load=(re_1|ld_2|ci_3|mf_4|fr_5|ls_6|agent|office)
      */
      if (!empty($_REQUEST)) {
        if (!array_key_exists(strtoupper($_REQUEST['load']), $this->_retsClassIDS)) {
          $this->_exitOnError('req-type', strtoupper($_REQUEST['load']));
        } else {
          $this->_retsClassID = strtoupper($_REQUEST['load']);
          $this->_retsClassName = strtolower($this->_retsClassIDS[strtoupper($_REQUEST['load'])]);
          $this->_retsClassType = (!eregi('(AGENT|OFFICE)', $this->_retsClassID)) ? 'Property' : strtoupper($this->_retsClassID);
          $this->_logMessage = date('M d, Y  H:i:s') . ' - Starting RETS/Cren - Class ' . $this->_retsClassName;
          //$this->_logEntry('start', $this->_logMessage, 'cren');
        }
        $this->_logMessage = "\n\n" . date('H:i:s') . ' - connecting RETS...';
        //$this->_logEntry('append', $this->_logMessage, 'cren');
        $this->_connectRETS();
        $this->_logMessage = "\n" . date('H:i:s') . ' - processing data...';
        //$this->_logEntry('append', $this->_logMessage, 'cren');
        $this->_loadDataClass();
        if (!preg_match('/(AGENT|OFFICE)/', $this->_retsClassID)) {
          $this->_logMessage = "\n" . date('H:i:s') . ' - processing images...';
          $this->_logMessage .= "\n" . date('H:i:s') . ' - disconnecting RETS...';
          $this->_rets->Disconnect();
          $this->_exitOnComplete('Class ' . $this->_retsClassID . ' Data w/Images Import');
        } else {  //  agent/office
          $this->_rets->Disconnect();
          $this->_exitOnComplete('Class ' . $this->_retsClassID . ' Data Import');
        }

      } else {
        $this->_exitOnError('req-miss');
      }
    }

    /*
     *
     *  function _exitOnError()
     *
     *  error processing and email alerts
     *  script terminates here
     *
     *  @access private
     *  @var (string) $type
     *    pre-defined error label
     *  @var [(string) $data]
     *    any extra formatted data for email
     *  @return none
     *
     */
    private function _exitOnError($type, $data = '') {
      $message = '';
      switch ($type) {
        case 'db-conn':
          $message = 'Database connection failed.';
          break;
        case 'db-sql':
          $message = 'Database SQL failed.' . "\n\n" . $data;
          break;
        case 'req-miss':
          $message = 'Request paramater missing.';
          break;
        case 'req-type':
          $message = 'Request paramater incorrect.' . "\n\n" . $data;
          break;
        case 'rets-conn':
          $message = 'RETS connection failed.';
          break;
        case 'io-perm':
          $message = 'File permission denied.' . "\n\n" . $data;
          break;
        case 'object-error':
          $message = 'Error getting object.' . "\n\n" . $data;
          break;
        case 'rets-search':
          $message = 'RETS search failed/stopped.' . "\n\n" . $data;
          break;
        case 'rets-images':
          $message = 'RETS image transfer failure.' . "\n\n" . $data;
          break;
        case 'log-sql':
          $message = 'Log writing failure.' . "\n\n" . $data;
          break;
      }
      $metrics =
        "\n\nUpdate Count: " . $this->_updateCount .
        "\nInsert Count: " . $this->_insertCount .
        "\nPhoto Count: " . $this->_photoCount .
        "\nImages Scaled Count: " . $this->_imageScaleCount .
        "\nProperty Delete Count: " . $this->_deletePropCount .
        "\nImage Delete Count: " . $this->_deleteImageCount;
      $this->_logMessage = $metrics . "\n\n" . date('M d, Y  H:i:s') . ' - Ending ERROR RETS/Cren - Class ' . $this->_retsClassName;
      //$this->_logEntry('end', $this->_logMessage, 'cren');
      @mail(self::DEV_EMAIL, 'WINR-CREN-RETS Script Error Occurred', $message);
      exit();
    }

    /*
     *
     *  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) {
      $metrics =
        "\n\nUpdate Count: " . $this->_updateCount .
        "\nInsert Count: " . $this->_insertCount .
        "\nPhoto Count: " . $this->_photoCount .
        "\nImages Scaled Count: " . $this->_imageScaleCount .
        "\nProperty Delete Count: " . $this->_deletePropCount .
        "\nImage Delete Count: " . $this->_deleteImageCount;
      $this->_logMessage .= $metrics . "\n\n" . date('M d, Y  H:i:s') . ' - Ending RETS/Cren - Class ' . $this->_retsClassName;
      //$this->_logEntry('end', $this->_logMessage, 'cren');
      @mail($this->_classDebug ? self::DEV_EMAIL : self::ADMIN_EMAIL, 'WINR-CREN-RETS Script Completed',
        $classMsg . $metrics);
      exit();
    }

    private function _convertRetsDate($inValue) {
      //2009-07-24 19:59:00
      $inValue = str_replace('T', ' ', $inValue);
      $dateTime = explode(' ', $inValue);
      $date = explode('-', $dateTime[0]);
      $time = explode(':', $dateTime[1]);
      return mktime((int) $time[0],(int) $time[1], $time[2],(int) $date[1],(int) $date[2],(int) $date[0]);
    }

    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
        //  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'] . $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 Winr_Cren_Rets();

?>