'property_residential', 'ld_2' => 'property_land', 'ci_3' => 'property_commercial', 'mf_4' => 'property_multifamily', 'fr_5' => 'property_farm_ranch', 'ls_6' => 'property_lease', 'agent' => 'agent', 'office' => 'office' ); // left out for now...('tf_7' => 'property_timeshare_fractional') 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() { //ob_start(); $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/Cren - Class ' . $this->_retsKey; $this->__logEntry('append', $this->_logMessage, 'cren'); // 1) setup rets check for any date stamps that are out if (!preg_match('/(agent|office)/i', $this->_retsClassID)) { /* properties */ $this->_winrTrailersTable = sprintf($this->_cwmlsTrailersTables, str_replace('property_', '', $classValue)); //$sql = "SELECT DISTINCT(t1.L_ListingID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1, listing_headers t2 WHERE DATEDIFF(t1." . $this->_retsListingTimeStampField . ", FROM_UNIXTIME(t2.doa_ts)) > 1 AND t1.L_ListingID = t2.mls_no"; $sql = "SELECT DISTINCT(t1.L_ListingID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1 LEFT JOIN listing_headers t2 ON (t1.L_ListingID = t2.mls_no AND t2.mls_system = 'cren') WHERE DATEDIFF(t1." . $this->_retsListingTimeStampField . ", FROM_UNIXTIME(t2.doa_ts)) > " . $this->_numCompareDays . ";"; } else { if ($this->_retsClassID == 'agent') { // agents //$sql = "SELECT DISTINCT(t1.U_AgentID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1, listing_agents t2 WHERE DATEDIFF(t1." . $this->_retsAgentTimeStampField . ", FROM_UNIXTIME(t2.update_ts)) > 1 AND t1.U_AgentID = t2.listing_agent_id"; $sql = "SELECT DISTINCT(t1.U_AgentID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1 LEFT JOIN listing_agents t2 ON (t1.U_AgentID = t2.listing_agent_id AND t2.mls_system = 'cren') WHERE DATEDIFF(t1." . $this->_retsAgentTimeStampField . ", FROM_UNIXTIME(t2.update_ts)) > " . $this->_numCompareDays . ";"; } else { // offices //$sql = "SELECT DISTINCT(t1.O_OfficeID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1, organizations t2 WHERE DATEDIFF(t1." . $this->_retsOfficeTimeStampField . ", FROM_UNIXTIME(t2.update_ts)) > 1 AND t1.O_OfficeID = t2.listing_office_id"; $sql = "SELECT DISTINCT(t1.O_OfficeID) AS ID, t1.* from " . sprintf($this->_retsDataTables, $this->_retsKey) . " t1 LEFT JOIN listing_agents t2 ON (t1.O_OfficeID = t2.listing_office_id AND t2.mls_system = 'cren') 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/Cren - Class ' . $this->_retsKey; $this->__logEntry('append', $this->_logMessage, 'cren'); // 1) setup rets check for any date stamps that are out if (!preg_match('/(agent)|(office)/i', $this->_retsClassID)) { /* properties */ switch($this->_retsClassID) { case 're_1': $classType = 'RESIDENTIAL'; break; case 'ld_2': $classType = 'LAND'; break; case 'ci_3': $classType = 'COMMERCIAL'; break; case 'mf_4': $classType = 'MULTIFAMILY'; break; case 'fr_5': $classType = 'FARMRANCH'; break; case 'ls_6': $classType = 'LEASE'; break; } $this->_winrTrailersTable = sprintf($this->_cwmlsTrailersTables, str_replace('property_', '', $classValue)); $sql = "SELECT * from " . sprintf($this->_retsDataTables, $this->_retsKey) . " where L_ListingID NOT IN (SELECT mls_no from " . $this->_cwmlsHeadersTable . " WHERE mls_system = 'cren' AND class = '" . $classType . "');"; //fb($sql, 'new listings records check sql'); } else { if ($this->_retsClassID == 'agent') { // agents $sql = "SELECT * from " . sprintf($this->_retsDataTables, $this->_retsKey) . " where U_AgentID NOT IN (SELECT listing_agent_id from listing_agents WHERE mls_system = 'cren');"; //fb($sql, 'new agents records check sql'); } else { // offices $sql = "SELECT * from " . sprintf($this->_retsDataTables, $this->_retsKey) . " where O_OfficeID NOT IN (SELECT listing_office_id from organizations WHERE mls_system = 'cren');"; //fb($sql, 'new office records check sql'); } } //echo $sql; //exit(); //fb($sql, 'new records check sql'); $this->_runRetsQueries($sql); } } /*** HELPER FUNCTIONS ***/ private function _runRetsQueries($sql) { // run rets query try { //$sql = sprintf($this->_selectSQL, sprintf($this->_retsDataTables, $this->_retsKey), $retsWhere); //echo $sql; $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', $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 { echo 'test new trailer insert'; $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 ($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 = <<_cwmlsHeadersTable WHERE (geocoding_status = 'N' OR geocoding_status = 'R') $this->_classSystemWhere LIMIT 1000; SQL; $sqlUpdateSuccesses = <<_cwmlsHeadersTable SET accuracy_level='%s', latitude = '%s', longitude = '%s', geocoding_status = 'Y' WHERE listing_header_id = %s; SQL; $sqlUpdateFailures = <<_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', $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['L_ListingID']; $this->_retsRecordTimeStampValue = (int) $this->_convertRetsDate($this->_rs->fields[$this->_retsListingTimeStampField]); $this->_currentRecordIDWhere = sprintf(" WHERE %s = %s AND mls_system = 'cren'", $this->_cwmlsRecordMLSIDField, $this->_retsRecordIDValue); } else { // agents, offices if ($this->_retsClassID == 'agent') { // agent $this->_cwmlsRecordTable = $this->_cwmlsAgentsTable; $this->_cwmlsRecordIDField = 'listing_agent_id'; $this->_cwmlsRecordTimeStampField = 'update_ts'; $this->_retsRecordIDValue = $this->_rs->fields['U_AgentID']; $this->_retsRecordTimeStampValue = (int) $this->_convertRetsDate($this->_rs->fields[$this->_retsAgentTimeStampField]); } else { // office $this->_cwmlsRecordTable = $this->_cwmlsOfficesTable; $this->_cwmlsRecordIDField = 'listing_office_id'; $this->_cwmlsRecordTimeStampField = 'update_ts'; $this->_retsRecordIDValue = $this->_rs->fields['O_OfficeID']; $this->_retsRecordTimeStampValue = (int) $this->_convertRetsDate($this->_rs->fields[$this->_retsOfficeTimeStampField]); } $this->_currentRecordIDWhere = sprintf(" WHERE %s = %s AND mls_system = 'cren'", $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', $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', $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 = <<__setDB('cwmls_db.config.php'); foreach ($this->_retsClassIDS as $classID => $classTable) { $this->_classID = $classID; // rets class pseudo-function switch($this->_classID) { case 're_1': $trailersTable = 'trailers_residential'; break; case 'ld_2': $trailersTable = 'trailers_land'; break; case 'ci_3': $trailersTable = 'trailers_commercial'; break; case 'mf_4': $trailersTable = 'trailers_multifamily'; break; case 'fr_5': $trailersTable = 'trailers_farm_ranch'; break; case 'ls_6': $trailersTable = 'trailers_lease'; break; default: $trailersTable = ''; } 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))); } /* echo sprintf($deleteTrailerHeaderIDs, $trailersTable, implode(',', $badIDs)); echo '

'; echo sprintf($deleteListingHeaderIDs, implode(',', $badIDs)); exit; */ } } } /* * * 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 = sprintf($this->_cwmlsTrailersTables, str_replace('property_', '', $classValue)); $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['L_AddressNumber']); $addressDirection = addslashes($rs->fields['L_AddressDirection']); $addressStreetName = addslashes($rs->fields['L_AddressStreet']); $address = $addressNumber . ($addressDirection != '' ? ' ' . $addressDirection : '') . ' ' . $addressStreetName; $phone1 = $this->_formatPhone($rs->fields['LA1_PhoneNumber1Area'], $rs->fields['LA1_PhoneNumber1'], $rs->fields['LA1_PhoneNumber1Ext']); $phone2 = $this->_formatPhone($rs->fields['LO1_PhoneNumber1Area'], $rs->fields['LO1_PhoneNumber1'], $rs->fields['LO1_PhoneNumber1Ext']); $areaMajor = addslashes($this->_getAreaMajorName(trim($rs->fields['LM_char10_43']))); $areaMinor = addslashes(trim($rs->fields['L_Area'])); if ($areaMinor != '') { $this->_saveMinorArea($areaMajor, $areaMinor); // area major from board name } else { $areaMinor = 'Unknown'; } $headerData = array( 'class' => $rs->fields['L_Class'], 'type' => $rs->fields['L_Type_'], 'area' => $rs->fields['L_Area'], 'list_price' => $rs->fields['L_SystemPrice'], 'street' => $address, 'address2' => $rs->fields['L_Address2'], 'city' => $rs->fields['L_City'], 'state' => $rs->fields['L_State'], 'zip' => $rs->fields['L_ZipCode'], 'county' => $rs->fields['LM_char10_47'], 'status' => $rs->fields['L_Status'], 'water_supplier' => $rs->fields['LM_Char25_10'], 'sewer_supplier' => $rs->fields['LM_Char25_11'], 'gas_supplier' => $rs->fields['LM_Char25_12'], 'electric_supplier' => $rs->fields['LM_Char25_13'], 'title_company' => $rs->fields['LM_Char25_18'], 'sold_price' => $rs->fields['L_SoldPrice'], 'total_taxes' => $rs->fields['LM_char10_69'], 'remarks' => $rs->fields['LR_remarks44'], 'listing_agent_1' => addslashes($rs->fields['LA1_UserFirstName'] . ' ' . $rs->fields['LA1_UserLastName']), 'listing_agent_phone' => $phone1, 'listing_agent_2' => addslashes($rs->fields['LA2_UserFirstName'] . ' ' . $rs->fields['LA2_UserLastName']), 'listing_office_1' => addslashes($rs->fields['LO1_OrganizationName']), 'listing_office_phone' => $phone2, 'listing_office_2' => addslashes($rs->fields['LO2_OrganizationName']), 'map_horizontal' => $rs->fields['LM_Char10_5'], 'map_vertical' => $rs->fields['LM_Char10_6'], 'zoning' => $rs->fields['LM_char30_9'], 'confidential_remarks' => $rs->fields['LR_remarks22'], 'mls_system' => $this->_mlsSystem, 'board_name' => $this->_convertBoardName($rs->fields['LM_char10_43']), 'vtour' => (trim($rs->fields['VT_VTourURL']) != '' ? (!preg_match('/(http)/', trim($rs->fields['VT_VTourURL'])) ? 'http://' . $rs->fields['VT_VTourURL'] : $rs->fields['VT_VTourURL']) : ''), 'vtour' => $rs->fields['VT_VTourURL'], 'listing_agent_id' => $rs->fields['L_ListAgent1'], 'listing_office_id' => $rs->fields['L_ListOffice1'], 'listing_agent_id_secondary' => $rs->fields['L_ListAgent1'], 'listing_office_id_secondary' => $rs->fields['L_ListOffice2'], 'street_number' => $addressNumber, 'street_direction' => $addressDirection, 'street_name' => $addressStreetName, 'directions' => $rs->fields['LM_char255_2'], 'doa_ts' => (int) $this->_convertRetsDate($rs->fields[$this->_retsListingTimeStampField]), 'picture_count' => $rs->fields['L_PictureCount'], 'rets_class' => $this->_retsClassID, 'structure_type' => $this->_formatCommas($rs->fields['LFD_TYPESTYLE_1']), 'short_sale' => $rs->fields['LM_Char1_1'], 'major_area' => $areaMajor, 'tax_schedule_number' => '', /* $rs->fields[''], */ /*'features' => '' <- turn back on once caught up */ 'tax_year' => $rs->fields['LM_char5_22'] ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { $headerData = array_merge($headerData, array( 'mls_no' => $rs->fields['L_ListingID'], 'internal_listing_id' => $rs->fields['L_ListingID'], '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; //mail('dennis.e.williams@gmail.com', 'test', print_r($rs, true)); //exit(); // class specific trailers_* table values switch ($this->_retsClassID) { case 're_1': // class re_1 (trailers_residential) $trailerData = array( 'rv_parking' => $rs->fields['L_Keyword2'], 'dining_room' => $rs->fields['L_Keyword3'], 'family_room' => $rs->fields['L_Keyword4'], 'irrigation_water' => $rs->fields['L_Keyword6'], 'subdivision_name' => $rs->fields['LM_Char25_1'], 'elementary_school' => $rs->fields['LM_Char25_2'], 'middle_school' => $rs->fields['LM_Char25_3'], 'high_school' => $rs->fields['LM_Char25_4'], 'fronts' => $rs->fields['LM_Char25_5'], 'lot_dimensions' => $rs->fields['LM_Char25_6'], 'approximate_total_acres' => $rs->fields['L_NumAcres'], 'approximate_irrigated_acres' => $rs->fields['LM_Dec_1'], 'irrigation_district' => $rs->fields['LM_Char25_8'], 'irrigation_description' => $rs->fields['LM_char30_6'], 'area_main_sf' => $rs->fields['LM_int4_24'], 'area_upper_sf' => $rs->fields['LM_int4_25'], 'area_lower_sf' => $rs->fields['LM_int4_26'], 'area_basement_sf' => $rs->fields['LM_int4_27'], 'area_fbasement_sf' => $rs->fields['LM_int4_28'], 'approximate_total_sf' => $rs->fields['LM_Int4_1'], 'year_built' => $rs->fields['LM_Int2_6'], 'number_bedrooms' => $rs->fields['LM_Int1_3'], 'number_bathrooms' => $rs->fields['LM_Dec_12'], 'bathrooms_description' => $rs->fields['LM_Char25_9'], 'dimensions_master_br' => $rs->fields['LM_char30_13'], 'dimensions_living_room' => $rs->fields['LM_char30_14'], 'dimensions_br2' => $rs->fields['LM_char30_15'], 'dimensions_dining_room' => $rs->fields['LM_char30_16'], 'dimensions_br3' => $rs->fields['LM_char30_17'], 'dimensions_family_room' => $rs->fields['LM_char30_18'], 'dimensions_br4' => $rs->fields['LM_char30_19'], 'dimensions_kitchen' => $rs->fields['LM_char30_20'], 'dimensions_office_den' => $rs->fields['LM_char30_21'], 'dimensions_laundry_utility' => $rs->fields['LM_char30_22'], 'garage_capacity' => $rs->fields['LM_Int1_6'], 'garage_type' => $rs->fields['LM_Char10_9'], 'hoa' => $rs->fields['LM_Char10_10'], 'hoa_dues' => $rs->fields['LM_Char10_11'], 'hoa_dues_frequency' => $rs->fields['LM_Char10_12'], 'energy_rating' => $rs->fields['LM_char5_21'], 'horse_setup' => $rs->fields['LM_char5_21'], 'great_room' => $rs->fields['L_Keyword10'], 'year_remodeled' => $rs->fields['LM_Int2_20'], 'number_phone_lines' => $rs->fields['LM_Int1_5'], 'internet_service' => $rs->fields['LM_char30_7'], 'fireplace' => $this->_formatCommas($rs->fields['LFD_FIREPLACES_14']), 'heating' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_7']), 'hoa_fees_include' => $this->_formatCommas($rs->fields['LFD_HOAINCL_23']), 'roof' => $this->_formatCommas($rs->fields['LFD_ROOF_5']), 'real_estate_included' => $rs->fields['L_Keyword9'], 'additional_living_quarters' => $rs->fields['L_Keyword1'], 'stories' => $this->_formatCommas($rs->fields['LFD_STORIES_2']), 'construction_siding' => $this->_formatCommas($rs->fields['LFD_CONSTRUCTIONSIDING_3']), 'foundation' => $this->_formatCommas($rs->fields['LFD_FOUNDATION_4']), 'flooring' => $this->_formatCommas($rs->fields['LFD_FLOORING_6']), 'heating_source' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_7']), 'cooling' => $this->_formatCommas($rs->fields['LFD_COOLING_8']), 'dom_water' => $this->_formatCommas($rs->fields['LFD_DOMWATER_9']), 'appliances_included' => $this->_formatCommas($rs->fields['LFD_APPLIANCESINCLUDED_11']), 'dining_description' => $this->_formatCommas($rs->fields['LFD_DINING_12']), 'master_bedroom_description' => $this->_formatCommas($rs->fields['LFD_MASTERBR_13']), 'interior_inclusions' => $this->_formatCommas($rs->fields['LFD_INTERIORINCL_15']), 'exterior_inclusions' => $this->_formatCommas($rs->fields['LFD_EXTERIORINCL_16']), 'pool_description' => $this->_formatCommas($rs->fields['LFD_POOL_17']), 'fence_description' => $this->_formatCommas($rs->fields['LFD_FENCE_18']), 'street_description_access' => $this->_formatCommas($rs->fields['LFD_STDESCACCESS_19']), 'lot_size_acres' => $this->_formatCommas($rs->fields['LFD_LOTSZACRE_20']), 'common_facilities' => $this->_formatCommas($rs->fields['LFD_COMMONFACILITIES_24']), 'sewer_description' => $this->_formatCommas($rs->fields['LFD_DOMSEWER_30']), 'views_description' => $this->_formatCommas($rs->fields['LFD_VIEWS_175']), 'lot_description' => $this->_formatCommas($rs->fields['LFD_LOTDESCRIPTION_176']), 'inclusions' => '', /* $rs->fields[''], */ 'hoa_amenities' => '', /* $rs->fields[''], */ 'extras' => '', /* $rs->fields[''], */ 'building_faces'=> '', /* $rs->fields[''], */ 'number_half_baths' => '', /* $rs->fields[''], */ 'lot_sf' => '', /* $rs->fields[''], */ 'lv_ht_sf' => '', /* $rs->fields[''], */ 'above_ground_sf' => '', /* $rs->fields[''], */ 'below_ground_sf' => '', /* $rs->fields[''], */ 'unfinished_sf' => '', /* $rs->fields[''], */ 'garage_sf' => '', /* $rs->fields[''], */ 'patio_deck_sf' => '', /* $rs->fields[''], */ 'number_floors' => '', /* $rs->fields[''], */ 'short_termable' => '', /* $rs->fields[''], */ 'furnished' => '', /* $rs->fields[''], */ 'under_construction' => '' /* $rs->fields[''], */ ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { /* $trailerData = array_merge($trailerData, array( '' => $rs->fields[''] )); */ } break; case 'ld_2': // class ld_2 (trailers_land) $trailerData = array( 'sewer_hu_available' => $rs->fields['L_Keyword1'], 'sewer_septic_in' => $rs->fields['L_Keyword2'], 'sewer_paid' => $rs->fields['L_Keyword3'], 'water_tap_available' => $rs->fields['L_Keyword4'], 'water_tap_installed' => $rs->fields['L_Keyword5'], 'water_tap_paid' => $rs->fields['L_Keyword6'], 'water_rights' => $rs->fields['L_Keyword7'], 'mineral_rights' => $rs->fields['L_Keyword8'], 'modular_mobile_allowed' => $rs->fields['L_Keyword9'], 'subdivision_number' => $rs->fields['LM_Char10_8'], 'subdivision_name' => $rs->fields['LM_Char25_1'], 'elementary_school' => $rs->fields['LM_Char25_2'], 'middle_school' => $rs->fields['LM_Char25_3'], 'high_school' => $rs->fields['LM_Char25_4'], 'fronts' => $rs->fields['LM_Char25_5'], 'lot_dimensions' => $rs->fields['LM_Char25_6'], 'approximate_total_acres' => $rs->fields['L_NumAcres'], 'approximate_irrigated_acres' => $rs->fields['LM_Dec_1'], 'irrigation_district' => $rs->fields['LM_Char25_8'], 'irrigation_shares' => $rs->fields['LM_char10_70'], 'press_system' => $rs->fields['LM_Char10_14'], 'adjudicated' => $rs->fields['LM_Char10_15'], 'curbs_gutters_in_paid' => $rs->fields['LM_char10_48'], 'hoa_dues' => $rs->fields['LM_Char10_11'], 'special_assessment' => $rs->fields['LM_Char10_13'], 'earnest_money_deposit' => $rs->fields['LM_Char25_15'], 'earnest_money_holder' => $rs->fields['LM_Char25_16'], 'possession' => $rs->fields['LM_Char25_17'], 'directions' => $rs->fields['LM_char255_2'], 'hoa_dues_frequency' => $rs->fields['LM_Char10_12'], 'hoa' => $rs->fields['LM_Char10_10'], 'sale_rent' => $rs->fields['L_SaleRent'], 'horse_setup' => $rs->fields['LM_Char10_26'], 'number_water_taps' => $rs->fields['LM_Int1_7'], 'number_sewer_taps' => $rs->fields['LM_Int1_8'], 'hoa_fees_include' => $this->_formatCommas($rs->fields['LFD_HOAINCLUDES_47']), 'lot_description' => $this->_formatCommas($rs->fields['LFD_LOTDESCRIPTION_38']), 'lot_improvements' => $this->_formatCommas($rs->fields['LFD_IMPROVEMENTS_42']), 'reo_lender_owned' => $rs->fields['LM_char10_36'], 'exchange_ok' => $rs->fields['LM_char10_37'], 'elevation' => $rs->fields['LM_char30_30'], 'irrigated_acreage_description' => $this->_formatCommas($rs->fields['LFD_IRRIGATEDACREAGE_32']), 'irrigation_water_description' => $this->_formatCommas($rs->fields['LFD_IRRIGATIONWATER_33']), 'property_type' => $this->_formatCommas($rs->fields['LFD_PROPERTYTYPE_36']), 'present_use' => $this->_formatCommas($rs->fields['LFD_PRESENTUSE_37']), 'street_description_access' => $this->_formatCommas($rs->fields['LFD_STREETDESCACCESS_43']), 'fence_description' => $this->_formatCommas($rs->fields['LFD_FENCE_46']), 'utilities_description' => $this->_formatCommas($rs->fields['LFD_UTILITIES_39']), 'views_description' => $this->_formatCommas($rs->fields['LFD_VIEWS_178']), 'possible_use' => '', /* $rs->fields[''], */ 'lot_sf' => '', /* $rs->fields[''], */ 'improved_w_home' => '', /* $rs->fields[''], */ 'home_sf' => '', /* $rs->fields[''], */ 'number_bedrooms' => '', /* $rs->fields[''], */ 'number_bathrooms' => '', /* $rs->fields[''], */ 'home_condition' => '', /* $rs->fields[''], */ 'home_style' => '', /* $rs->fields[''], */ 'home_year_built' => '', /* $rs->fields[''], */ 'extras' => '', /* $rs->fields[''], */ 'hoa_amenities' => '', /* $rs->fields[''], */ 'allowable_uses' => '' /* $rs->fields[''], */ ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { /* $trailerData = array_merge($trailerData, array( '' => $rs->fields[''] )); */ } break; case 'ci_3': // class ci_3 (trailers_commercial) $trailerData = array( 'business_opp_available' => '', /* $rs->fields[''], */ 'real_estate_for_sale' => $rs->fields['L_Keyword2'], 'real_estate_for_lease' => $rs->fields['L_Keyword3'], 'loading_dock' => $rs->fields['L_Keyword10'], 'railroad_siding' => $rs->fields['L_Keyword6'], 'books_available' => $rs->fields['L_Keyword7'], 'type_of_sale' => $rs->fields['L_Keyword8'], 'inventory_included' => $rs->fields['L_Keyword5'], 'fixtures_included' => $rs->fields['L_Keyword4'], 'lease_included' => $rs->fields['L_Keyword9'], 'year_built' => $rs->fields['LM_Int2_6'], 'approximate_total_acres' => $rs->fields['L_NumAcres'], 'area_main_sf' => $rs->fields['LM_int4_24'], 'area_upper_sf' => $rs->fields['LM_int4_25'], 'area_lower_sf' => $rs->fields['LM_int4_26'], 'area_basement_sf' => $rs->fields['LM_Int4_5'], 'area_office_sf' => $rs->fields['LM_int4_29'], 'area_warehouse_sf' => $rs->fields['LM_int4_30'], 'approximate_total_sf' => $rs->fields['L_SquareFeet'], 'number_units' => $rs->fields['L_NumUnits'], 'number_floors' => $rs->fields['LM_char5_23'], 'area_finished_sf' => $rs->fields['LM_Int4_4'], 'property_known_as' => $rs->fields['LM_Char25_28'], 'lot_dimensions' => $rs->fields['LM_Char25_6'], 'current_business' => $rs->fields['LM_Char25_20'], 'exchange_for' => $rs->fields['LM_char50_9'], 'air_condition' => $this->_formatCommas($rs->fields['LFD_AIRCONDITIONING_147']), 'heat_type' => $this->_formatCommas($rs->fields['LFD_HEATINGSYSTEM_145']), 'amperage' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_144']), 'volts' => $rs->fields['LM_char5_55'], 'ceiling_height' => $rs->fields['LM_Char10_28'], 'number_overhead_doors' => $rs->fields['LM_Char10_30'], 'ada_yn' => $rs->fields['LM_Char10_25'], 'possession' => $rs->fields['LM_char30_2'], 'lease_class' => $rs->fields['L_Keyword10'], 'subdivision_or_location' => $rs->fields['LM_Char10_8'], 'under_construction' => $rs->fields[''], 'tenant_pays' => $this->_formatCommas($rs->fields['LFD_TENANTPAYS_156']), 'year_remodeled' => $rs->fields['LM_Int2_20'], 'number_parking_spaces' => $rs->fields['LM_Int1_10'], 'common_area_maintenance' => $rs->fields['LM_char10_50'], 'common_area_maintenance_charges' => $rs->fields['LM_char10_51'], 'monthly_insurance_charges' => $rs->fields['LM_char10_52'], 'lease_type' => $rs->fields['L_Type_'], 'number_dock_hi_doors' => $rs->fields['LM_Char10_29'], 'allowable_uses' => $this->_formatCommas($rs->fields['LFD_BUILDINGUSE_141']), 'conditions' => '', /* $rs->fields[''], */ 'location_amenities' => '', /* $rs->fields[''], */ 'parking' => $this->_formatCommas($rs->fields['LFD_PARKING_158']), 'roof' => $this->_formatCommas($rs->fields['LFD_ROOF_150']), 'gas_supplier' => $rs->fields['LM_Char25_12'], 'subdivision_name' => $rs->fields['LM_Char25_1'], 'heating_source' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_58']), 'heating_system' => $this->_formatCommas($rs->fields['LFD_HEATINGSYSTEM_59']), 'fence_description' => $this->_formatCommas($rs->fields['LFD_FENCE_60']), 'lighting_description' => $this->_formatCommas($rs->fields['LFD_LIGHTING_62']), 'floor_type' => $this->_formatCommas($rs->fields['LFD_FLOORTYPE_65']), 'existing_utilities' => $this->_formatCommas($rs->fields['LFD_EXISTINGUTILITIES_66']), 'available_utilities' => $this->_formatCommas($rs->fields['LFD_AVAILABLEUTILITIES_67']), 'hoa_includes' => $this->_formatCommas($rs->fields['LFD_HOAINCLUDES_73']), 'inclusions' => $this->_formatCommas($rs->fields['LFD_INCLUSIONS_76']), 'street_description_access' => $this->_formatCommas($rs->fields['LFD_STREEDESCRACCESS_77']), 'building_construction' => '', /* $rs->fields[''], */ 'earnest_money_deposit' => '', /* $rs->fields[''], */ 'earnest_money_holder' => '', /* $rs->fields[''], */ 'year_started' => '', /* $rs->fields[''], */ 'description' => '', /* $rs->fields[''], */ 'lot_sf' => '', /* $rs->fields[''], */ 'hoa_dues' => '', /* $rs->fields[''], */ 'special_assessment' => '', /* $rs->fields[''], */ 'number_water_meters' => '', /* $rs->fields[''], */ 'number_gas_meters' => '', /* $rs->fields[''], */ 'number_electric_meters' => '', /* $rs->fields[''], */ 'remaining_lease_months' => '', /* $rs->fields[''], */ 'inventory_value' => '', /* $rs->fields[''], */ 'ebitda' => '', /* $rs->fields[''], */ 'condominiumized' => '', /* $rs->fields[''], */ 'construction' => '', /* $rs->fields[''], */ 'current_use' => '', /* $rs->fields[''], */ 'expenses_include' => '', /* $rs->fields[''], */ 'green_features' => '', /* $rs->fields[''], */ 'amenities' => '', /* $rs->fields[''], */ 'extras' => '', /* $rs->fields[''], */ 'heating_cooling' => '' /* $rs->fields[''], */ ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { /* $trailerData = array_merge($trailerData, array( '' => $rs->fields[''] )); */ } break; case 'mf_4': // class mf_4 (trailers_multifamily) $trailerData = array( 'multi_story' => $rs->fields['L_Keyword2'], 'subdivision_number' => $rs->fields['LM_Char10_8'], 'subdivision_name' => $rs->fields['LM_Char25_1'], 'elementary_school' => $rs->fields['LM_Char25_2'], 'middle_school' => $rs->fields['LM_Char25_3'], 'high_school' => $rs->fields['LM_Char25_4'], 'lot_dimension' => $rs->fields['LM_Char25_6'], 'approximate_total_acres' => $rs->fields['L_NumAcres'], 'irrigation_district' => $rs->fields['LM_Char25_8'], 'approximate_total_sf' => $rs->fields['LM_Int4_1'], 'year_built' => $rs->fields['LM_Int2_6'], 'property_manager' => $rs->fields['LM_Char25_22'], 'no_of_units' => $rs->fields['L_NumUnits'], 'unit_type_1_no_units' => $rs->fields['LM_char5_24'], 'unit_type_1_no_bedrooms' => $rs->fields['LM_char5_30'], 'unit_type_1_no_bathrooms' => $rs->fields['LM_char5_36'], 'unit_type_1_sf' => $rs->fields['LM_int4_33'], 'unit_type_1_monthly_rent' => $rs->fields['LM_char5_48'], 'unit_type_2_no_units' => $rs->fields['LM_char5_25'], 'unit_type_2_no_bedrooms' => $rs->fields['LM_char5_31'], 'unit_type_2_no_bathrooms' => $rs->fields['LM_char5_37'], 'unit_type_2_sf' => $rs->fields['LM_int4_34'], 'unit_type_2_monthly_rent' => $rs->fields['LM_char5_49'], 'unit_type_3_no_units' => $rs->fields['LM_char5_26'], 'unit_type_3_no_bedrooms' => $rs->fields['LM_char5_32'], 'unit_type_3_no_bathrooms' => $rs->fields['LM_char5_38'], 'unit_type_3_sf' => $rs->fields['LM_int4_35'], 'unit_type_3_monthly_rent' => $rs->fields['LM_char5_50'], 'unit_type_4_no_units' => $rs->fields['LM_char5_27'], 'unit_type_4_no_bedrooms' => $rs->fields['LM_char5_33'], 'unit_type_4_no_bathrooms' => $rs->fields['LM_char5_39'], 'unit_type_4_sf' => $rs->fields['LM_int4_36'], 'unit_type_4_monthly_rent' => $rs->fields['LM_char5_51'], 'unit_type_5_no_units' => $rs->fields['LM_char5_28'], 'unit_type_5_no_bedrooms' => $rs->fields['LM_char5_34'], 'unit_type_5_no_bathrooms' => $rs->fields['LM_char5_40'], 'unit_type_5_sf' => $rs->fields['LM_int4_37'], 'unit_type_5_monthly_rent' => $rs->fields['LM_char5_52'], 'unit_type_6_no_units' => $rs->fields['LM_char5_29'], 'unit_type_6_no_bedrooms' => $rs->fields['LM_char5_35'], 'unit_type_6_no_bathrooms' => $rs->fields['LM_char5_41'], 'unit_type_6_sf' => $rs->fields['LM_int4_38'], 'unit_type_6_monthly_rent' => $rs->fields['LM_char5_53'], 'hoa' => $rs->fields['LM_Char10_10'], 'hoa_dues' => $rs->fields['LM_Char10_11'], 'hoa_dues_frequency' => $rs->fields['LM_Char10_12'], 'sale_rent' => $rs->fields['L_SaleRent'], 'garage_carport' => $rs->fields['L_Keyword1'], 'approximate_irrigated_acres' => $rs->fields['LM_Dec_1'], 'number_shares' => $rs->fields['LM_Int4_2'], 'year_remodeled' => $rs->fields['LM_Int2_20'], 'gross_scheduled_income' => $rs->fields['LM_Dec_7'], 'vacancy_as_percent' => $rs->fields['LM_int4_40'], 'gross_annual_expenses' => $rs->fields['LM_Dec_18'], 'net_operating_income' => $rs->fields['LM_Dec_13'], 'garage_capacity' => $rs->fields['LM_Int1_6'], 'garage_type' => $rs->fields['LM_Char10_9'], 'number_parking_spaces' => $rs->fields['LM_Int1_10'], 'number_water_meters' => $rs->fields['LM_Int1_7'], 'number_gas_meters' => $rs->fields['LM_Int1_8'], 'number_electric_meters' => $rs->fields['LM_Int1_9'], 'electric_supplier' => $rs->fields['LM_Char25_13'], 'interior_inclusions' => $this->_formatCommas($rs->fields['LFD_INTERIORINCLUSIONS_82']), 'appliances_included' => $this->_formatCommas($rs->fields['LFD_APPLIANCESINCLUDED_83']), 'dining_description' => $this->_formatCommas($rs->fields['LFD_DINING_84']), 'flooring' => $this->_formatCommas($rs->fields['LFD_FLOORING_85']), 'heating_source' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_87']), 'cooling' => $this->_formatCommas($rs->fields['LFD_COOLING_88']), 'exterior_inclusions' => $this->_formatCommas($rs->fields['LFD_EXTERIORINCLUSIONS_89']), 'pool_description' => $this->_formatCommas($rs->fields['LFD_POOL_90']), 'fireplaces_description' => $this->_formatCommas($rs->fields['LFD_FIREPLACES_91']), 'fence_description' => $this->_formatCommas($rs->fields['LFD_FENCE_92']), 'structure_type' => $this->_formatCommas($rs->fields['LFD_TYPESTYLE_93']), 'stories' => $this->_formatCommas($rs->fields['LFD_STORIES_94']), 'construction_siding' => $this->_formatCommas($rs->fields['LFD_CONSTRUCTIONSIDING_95']), 'foundation' => $this->_formatCommas($rs->fields['LFD_FOUNDATION_96']), 'roof' => $this->_formatCommas($rs->fields['LFD_ROOF_97']), 'lot_size_acres' => $this->_formatCommas($rs->fields['LFD_LOTSIZEACREAGE_160']), 'street_description_access' => $this->_formatCommas($rs->fields['LFD_STREETDESCACCESS_161']), 'hoa_includes' => $this->_formatCommas($rs->fields['LFD_HOAINCLUDES_163']), 'common_facilities' => $this->_formatCommas($rs->fields['LFD_COMMONFACILITIES_164']), 'parking_description' => $this->_formatCommas($rs->fields['LFD_PARKING_174']), 'lot_description' => $this->_formatCommas($rs->fields['LFD_LOTDESCRIPTION_195']) ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { /* $trailerData = array_merge($trailerData, array( '' => $rs->fields[''] )); */ } break; case 'fr_5': // class fr_5 (trailers_farm_ranch) $trailerData = array( 'mineral_rights' => $rs->fields['L_Keyword1'], 'pasture' => $rs->fields['L_Keyword10'], 'subdivision_name' => $rs->fields['LM_Char25_1'], 'elementary_school' => $rs->fields['LM_Char25_2'], 'middle_school' => $rs->fields['LM_Char25_3'], 'high_school' => $rs->fields['LM_Char25_4'], 'approximate_total_acres' => $rs->fields['L_NumAcres'], 'approximate_irrigated_acres' => $rs->fields['LM_Dec_1'], 'irrigation_district' => $rs->fields['LM_Char25_8'], 'number_shares' => $rs->fields['M_char10_70'], 'irrigation_annual_cost' => $rs->fields['LM_Dec_3'], 'year_built' => $rs->fields['LM_Int2_6'], 'area_main_sf' => $rs->fields['LM_int4_24'], 'area_upper_sf' => $rs->fields['LM_int4_25'], 'area_lower_sf' => $rs->fields['LM_int4_26'], 'area_basement_sf' => $rs->fields['LM_int4_27'], 'approximate_total_sf' => $rs->fields['LM_Int4_1'], 'number_bedrooms' => $rs->fields['LM_Int1_3'], 'number_bathrooms' => $rs->fields['LM_Dec_12'], 'leased' => $rs->fields['LM_Char10_23'], 'dimensions_master_br' => $rs->fields['LM_char30_13'], 'dimensions_living_room' => $rs->fields['LM_char30_14'], 'dimensions_br2' => $rs->fields['LM_char30_15'], 'dimensions_dining_room' => $rs->fields['LM_char30_16'], 'dimensions_br3' => $rs->fields['LM_char30_17'], 'dimensions_family_room' => $rs->fields['LM_char30_18'], 'dimensions_br4' => $rs->fields['LM_char30_19'], 'dimensions_kitchen' => $rs->fields['LM_char30_20'], 'dimensions_office_den' => $rs->fields['LM_char30_21'], 'dimensions_laundry_utility' => $rs->fields['LM_char30_22'], 'garage_capacity' => $rs->fields['LM_Int1_6'], 'garage_type' => $rs->fields['LM_Char10_9'], 'barn' => $rs->fields['LM_Char10_21'], 'corral' => $rs->fields['LM_Char10_22'], 'living_quarters' => $rs->fields['L_Keyword4'], 'can_be_divided' => $rs->fields['L_Keyword5'], 'horse_setup' => $rs->fields['L_Keyword2'], 'lot_dimensions' => $this->_formatCommas($rs->fields['LFD_LOTSIZEACREAGE_140']), 'sale_or_rent' => $rs->fields['L_SaleRent'], 'irrigation_water' => $rs->fields['L_Keyword3'], 'year_remodeled' => $rs->fields['LM_Int2_20'], 'bathrooms_description' => $rs->fields['LM_Char25_9'], 'subdivision_number' => $rs->fields['LM_Char10_8'], 'structure_type' => $this->_formatCommas($rs->fields['LFD_TYPESTYLE_100']), 'stories' => $this->_formatCommas($rs->fields['LFD_STORIES_101']), 'construction_siding' => $this->_formatCommas($rs->fields['LFD_CONSTRUCTIONSIDING_102']), 'foundation' => $this->_formatCommas($rs->fields['LFD_FOUNDATION_103']), 'basement' => $this->_formatCommas($rs->fields['LFD_BASEMENT_104']), 'roof' => $this->_formatCommas($rs->fields['LFD_ROOF_105']), 'flooring' => $this->_formatCommas($rs->fields['LFD_FLOORS_106']), 'heating_source' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_107']), 'heating_system' => $this->_formatCommas($rs->fields['LFD_HEATINGSYSTEM_108']), 'cooling' => $this->_formatCommas($rs->fields['LFD_AC_109']), 'appliances_included' => $this->_formatCommas($rs->fields['LFD_APPLIANCESINCLUDED_114']), 'dining_description' => $this->_formatCommas($rs->fields['LFD_DINING_115']), 'fireplaces_description' => $this->_formatCommas($rs->fields['LFD_FIREPLACES_116']), 'interior_inclusions' => $this->_formatCommas($rs->fields['LFD_INTERIORINCLUSIONS_117']), 'exterior_inclusions' => $this->_formatCommas($rs->fields['LFD_EXTERIORINCLUSIONS_118']), 'irrigated_acreage_description' => $this->_formatCommas($rs->fields['LFD_IRRIGATEDACREAGE_119']), 'irrigation_water_description' => $this->_formatCommas($rs->fields['LFD_IRRIGATIONWATER_120']), 'allowable_uses' => $this->_formatCommas($rs->fields['LFD_LANDUSE_122']), 'property_type_description' => $this->_formatCommas($rs->fields['LFD_PROPERTYTYPE_123']), 'present_use' => $this->_formatCommas($rs->fields['LFD_PRESENTUSE_124']), 'outbuildings_description' => $this->_formatCommas($rs->fields['LFD_OUTBUILDINGS_126']), 'pool_description' => $this->_formatCommas($rs->fields['LFD_POOL_127']), 'utilities_description' => $this->_formatCommas($rs->fields['LFD_UTILITIES_128']), 'improvements' => $this->_formatCommas($rs->fields['LFD_IMPROVEMENTS_129']), 'street_description_access' => $this->_formatCommas($rs->fields['LFD_STREETDESCACCESS_130']), 'fence_description' => $this->_formatCommas($rs->fields['LFD_FENCE_133']), 'hoa_includes' => $this->_formatCommas($rs->fields['LFD_HOAINCLUDES_134']), 'lot_size_acres' => $this->_formatCommas($rs->fields['LFD_LOTSIZEACREAGE_140']), 'views_description' => $this->_formatCommas($rs->fields['LFD_VIEWS_186']) ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { /* $trailerData = array_merge($trailerData, array( '' => $rs->fields[''] )); */ } break; case 'ls_6': // // class ls_6 (trailers_lease) $trailerData = array( 'sale_rent' => $rs->fields['L_SaleRent'], 'lease_class' => $rs->fields['L_Keyword10'], 'listing_agreement' => $rs->fields['LM_Char10_2'], 'trans_broker_percent' => $rs->fields['LM_char30_28'], 'buyer_agency_percent' => $rs->fields['LM_char30_29'], 'variable_rate' => $rs->fields['LM_Char10_3'], 'listing_date' => $rs->fields['L_ListingDate'], 'expiration_date' => $rs->fields['L_ExpirationDate'], 'lot_dimensions' => $rs->fields['LM_Char25_6'], 'property_known_as' => $rs->fields['LM_Char25_28'], 'vacant' => $rs->fields['LM_Char10_24'], 'year_built' => $rs->fields['LM_Int2_6'], 'year_remodeled' => $rs->fields['LM_Int2_20'], 'limited_service' => $rs->fields['LM_char10_45'], 'entry_only' => $rs->fields['LM_char10_46'], 'area_main_sf' => $rs->fields['LM_int4_24'], 'area_upper_sf' => $rs->fields['LM_int4_25'], 'area_lower_sf' => $rs->fields['LM_int4_26'], 'area_basement_sf' => $rs->fields['LM_int4_27'], 'area_office_sf' => $rs->fields['LM_int4_29'], 'area_warehouse_sf' => $rs->fields['LM_int4_30'], 'approximate_total_sf_comm' => $rs->fields['LM_Int4_1'], 'number_units' => $rs->fields['L_NumUnits'], 'number_floors' => $rs->fields['LM_char5_23'], 'area_sf_finished' => $rs->fields['LM_Int4_4'], 'air_condition' => $rs->fields['LM_Char25_29'], 'heat_type' => $rs->fields['LM_Char25_30'], 'ada_yn' => $rs->fields['LM_Char10_25'], 'amperage' => $rs->fields['LM_char5_54'], 'volts' => $rs->fields['LM_char5_55'], 'ceiling_height' => $rs->fields['LM_Char10_28'], 'number_dockhigh_doors' => $rs->fields['LM_Char10_29'], 'number_overhead_doors' => $rs->fields['LM_Char10_30'], 'term' => $rs->fields['LM_char10_32'], 'security_deposit_amount' => $rs->fields['LM_char30_1'], 'possession' => $rs->fields['LM_char30_2'], 'style' => $rs->fields['LM_char30_3'], 'approximate_total_sf_res' => $rs->fields['LM_Int4_9'], 'garage_type' => $rs->fields['LM_Char10_9'], 'number_bedrooms' => $rs->fields['LM_Int1_3'], 'number_bathrooms' => $rs->fields['LM_Int1_4'], 'bathrooms_description' => $rs->fields['LM_Char25_9'], 'dimensions_master_br' => $rs->fields['LM_char30_13'], 'dimensions_living_room' => $rs->fields['LM_char30_14'], 'dimensions_br2' => $rs->fields['LM_char30_15'], 'dimensions_dining_room' => $rs->fields['LM_char30_16'], 'dimensions_br3' => $rs->fields['LM_char30_17'], 'dimensions_family_room' => $rs->fields['LM_char30_18'], 'dimensions_br4' => $rs->fields['LM_char30_19'], 'dimensions_kitchen' => $rs->fields['LM_char30_20'], 'dimensions_office_den' => $rs->fields['LM_char30_21'], 'dimensions_laundry_utility' => $rs->fields['LM_char30_22'], 'dimensions_basement' => $rs->fields['LM_char10_38'], 'dimensions_fbasement' => $rs->fields['LM_char10_39'], 'irrigation_water' => $rs->fields['LM_char10_40'], 'barn' => $rs->fields['LM_char10_41'], 'corral' => $rs->fields['LM_char10_42'], 'possession1' => $rs->fields['LM_char30_2'], 'off_market_date' => $rs->fields['L_OffMarketDate'], 'associated_document_count' => $rs->fields['L_listings_associated_doc_count'], 'addendum' => $rs->fields['LR_remarks11'], 'price_original' => $rs->fields['L_OriginalPrice'], 'directions' => $rs->fields['LM_char255_2'], 'how_sold' => $rs->fields['L_HowSold'], 'contract_date' => $rs->fields['L_ContractDate'], 'closing_date' => $rs->fields['L_ClosingDate'], 'common_area_maintenance' => $rs->fields['LM_char10_50'], 'common_area_maintenance_charges' => $rs->fields['LM_char10_51'], 'subdivision_name' => $rs->fields['LM_Char25_1'], 'lease_type' => $rs->fields['LM_Char10_21'], 'number_parking_spaces' => $rs->fields['LM_Int1_10'], 'building_use' => $this->_formatCommas($rs->fields['LFD_BUILDINGUSE_141']), 'heating_source' => $this->_formatCommas($rs->fields['LFD_HEATINGSOURCE_144']), 'heating_system' => $this->_formatCommas($rs->fields['LFD_HEATINGSYSTEM_145']), 'fence_description' => $this->_formatCommas($rs->fields['LFD_FENCE_146']), 'roof' => $this->_formatCommas($rs->fields['LFD_ROOF_150']), 'floor' => $this->_formatCommas($rs->fields['LFD_FLOORTYPE_151']), 'utilities_description' => $this->_formatCommas($rs->fields['LFD_EXISTINGUTILITIES_152']), 'parking_description' => $this->_formatCommas($rs->fields['LFD_PARKING_158']), 'lot_description' => $this->_formatCommas($rs->fields['LFD_LOTDESCRIPTION_197']) ); // stuff not to update if ($action == 'insert') { /* $trailerData[] = array('' => $rs->fields['']); */ } break; case 'tf_7': // // class tf_7 (property_timeshare_fractional) $trailerData = array( ); // on insert only... // i.e. stuff not to update if ($action == 'insert') { /* $trailerData = array_merge($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; $addressNumber = addslashes($rs->fields['U_AddressNumber']); $addressDirection = addslashes($rs->fields['U_AddressDirection']); $addressStreetName = addslashes($rs->fields['U_AddressStreet']); $mailAddressNumber = addslashes($rs->fields['U_MailAddressNumber']); $mailAddressDirection = addslashes($rs->fields['U_MailAddressDirection']); $mailAddressStreetName = addslashes($rs->fields['U_MailAddressStreet']); $address = $addressNumber . ($addressDirection != '' ? ' ' . $addressDirection : '') . ' ' . $addressStreetName; $mailAddress = $mailAddressNumber . ' ' . ($mailAddressDirection != '' ? ' ' . $mailAddressDirection : '') . ' ' . $mailAddressStreetName; $phone1 = $this->_formatPhone($rs->fields['U_PhoneNumber1Area'], $rs->fields['U_PhoneNumber1'], $rs->fields['U_PhoneNumber1Ext']); $phone2 = $this->_formatPhone($rs->fields['U_PhoneNumber2Area'], $rs->fields['U_PhoneNumber2'], $rs->fields['U_PhoneNumber2Ext']); $phone3 = $this->_formatPhone($rs->fields['U_PhoneNumber3Area'], $rs->fields['U_PhoneNumber3'], $rs->fields['U_PhoneNumber3Ext']); $fax = $this->_formatPhone($rs->fields['U_PhoneNumber3Area'], $rs->fields['U_PhoneNumber3']); $boardName = $this->_getBoardName($rs->fields['AO_board_id']); $agentData = array( 'agent_designation' => $rs->fields['U_FreeFormDesig'], /* all together now */ 'listing_office_id' => $rs->fields['U_HiddenOrgID'], /* not entirely sure here */ 'fname' => $rs->fields['U_UserFirstName'], 'lname' => $rs->fields['U_UserLastName'], 'address' => $address, 'address2' => $rs->fields['U_Address2'], 'city' => $rs->fields['U_City'], 'state' => $rs->fields['U_State'], 'zip' => $rs->fields['U_Zip'], 'mail_address' => $mailAddress , 'mail_address2' => $rs->fields['U_MailAddress2'], 'mail_city' => $rs->fields['U_MailCity'], 'mail_state' => $rs->fields['U_MailState'], 'mail_zip' => $rs->fields['U_MailZip'], 'phone1' => $phone1, 'phone2' => $phone2, 'phone3' => $phone3, 'fax' => $fax, 'email' => $rs->fields['U_Email'], 'url' => $rs->fields['U_WebPage'], 'status' => $this->_convertAgentStatus($rs->fields['U_Status']), 'photos' => $rs->fields['U_Photos'], 'agent_id2' => $rs->fields['U_LoginName'], 'agent_type' => $this->_convertAgentType($rs->fields['U_HiddenUsCID']), 'update_ts' => (int) $this->_convertRetsDate($rs->fields[$this->_retsAgentTimeStampField]), 'nrds' => $rs->fields['U_users_nrds_id'], 'license_number' => $rs->fields['U_AgentLicenseID'], 'paragon_password' => '', /* not used */ '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['U_AgentID'], '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; $addressNumber = addslashes($rs->fields['O_OrgAddressNumber']); $addressDirection = addslashes($rs->fields['O_OrgAddressDirection']); $addressStreetName = addslashes($rs->fields['O_OrgAddressStreet']); $mailAddressNumber = addslashes($rs->fields['O_MailAddressNumber']); $mailAddressDirection = addslashes($rs->fields['O_MailAddressDirection']); $mailAddressStreetName = addslashes($rs->fields['O_MailAddressStreet']); $address = $addressNumber . ($addressDirection != '' ? ' ' . $addressDirection : '') . ' ' . $addressStreetName; $mailAddress = $mailAddressNumber . ' ' . ($mailAddressDirection != '' ? ' ' . $mailAddressDirection : '') . ' ' . $mailAddressStreetName; $phone1 = $this->_formatPhone($rs->fields['O_PhoneNumber1Area'], $rs->fields['O_PhoneNumber1'], $rs->fields['O_PhoneNumber1Ext']); $phone2 = $this->_formatPhone($rs->fields['O_PhoneNumber2Area'], $rs->fields['O_PhoneNumber2'], $rs->fields['O_PhoneNumber2Ext']); $fax = $this->_formatPhone($rs->fields['O_PhoneNumber2Area'], $rs->fields['O_PhoneNumber2']); $boardName = $this->_getBoardName($rs->fields['O_board_id']); $officeData = array( 'branch_id' => $rs->fields['O_BranchOfOrgID'], 'org_short_name' => $rs->fields['O_ShortName'], 'org_name' => $rs->fields['O_OrganizationName'], 'org_address' => $address, 'org_address2' => $rs->fields['O_OrgAddress2'], 'org_city' => $rs->fields['O_OrgCity'], 'org_state' => $rs->fields['O_OrgState'], 'org_zip' => $rs->fields['O_OrgZip'], 'org_mail_address' => $mailAddress, 'org_mail_address2' => $rs->fields['O_MailAddress2'], 'org_mail_city' => $rs->fields['O_MailCity'], 'org_mail_state' => $rs->fields['O_MailState'], 'org_mail_zip' => $rs->fields['O_MailZip'], 'org_contact' => $rs->fields['O_Contact'], 'org_phone1' => $phone1, 'org_phone2' => $phone2, 'org_email' => $rs->fields['O_EMail'], 'org_url' => $rs->fields['O_WebPage'], 'org_affiliate' => $rs->fields['O_Affiliate'], 'org_status' => $rs->fields['O_HiddenOtyID'], 'fax' => $fax, 'idx_yes_no' => $rs->fields['O_MemberOfMLS'], '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['O_OfficeID'], '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 _getAreaMajorName() * * get WINR area name value from board name * * @access private * @var (string) $boardName * board name * @return string * */ private function _getAreaMajorName($boardName) { switch(true) { case preg_match('/(montrose)/i', $boardName): $areaName = 'Montrose'; break; case preg_match('/(junction)/i', $boardName): $areaName = 'Grand Junction'; break; case preg_match('/(four)/i', $boardName): $areaName = 'Cortez'; break; case preg_match('/(durango)/i', $boardName): $areaName = 'Durango'; break; case preg_match('/(delta)/i', $boardName): $areaName = 'Delta'; break; case preg_match('/(pagosa)/i', $boardName): $areaName = 'Pagosa Springs'; break; case preg_match('/(luis)/i', $boardName): $areaName = 'San Luis Valley'; break; default: $areaName = 'Unknown'; } return $areaName; } /* * * function _getBoardName() * * get MLS board name value from board id * * @access private * @var (integer) $boardID * board id number * @return string * */ private function _getBoardName($boardID) { switch($boardID) { case 0: $boardName = 'cren'; break; case 1: $boardName = 'montrose'; break; case 2: $boardName = 'gjara'; break; case 3: $boardName = 'cortez'; break; case 4: $boardName = 'durango'; break; case 5: $boardName = 'delta'; break; case 6: $boardName = 'pagosa springs'; break; case 7: $boardName = 'san luis valley'; break; default: $boardName = 'cren'; } return $boardName; } /* * * function _getBoardID() * * get MLS board id value from board name * * @access private * @var (string) $boardName * board id number * @return integer * */ private function _getBoardID($boardName) { switch(true) { case preg_match('/(montrose)/i', $boardName): $boardID = 1; break; case preg_match('/(junction)/i', $boardName): $boardID = 2; break; case preg_match('/(four|cortez)/i', $boardName): $boardID = 3; break; case preg_match('/(durango)/i', $boardName): $boardID = 4; break; case preg_match('/(delta)/i', $boardName): $boardID = 5; break; case preg_match('/(pagosa)/i', $boardName): $boardID = 6; break; case preg_match('/(luis)/i', $boardName): $boardID = 7; break; default: $boardID = 0; } return $boardID; } /* * * function _convertIDXStatus() * * get converted MLS value from RETS record * * @access private * @var (string) $inValue * value to convert * @return string * */ private function _convertIDXStatus($inValue) { switch(strtolower($inValue)) { case 'in': $outValue = 'yes'; break; case 'out': $outValue = 'no'; break; default: $outValue = 'no'; } return $outValue; } /* * * function _convertAgentStatus() * * get converted MLS value from RETS record * * @access private * @var (string) $inValue * value to convert * @return string * */ private function _convertAgentStatus($inValue) { $inValue = strtolower(trim($inValue)); switch(true) { case preg_match('/(non-member)/i', $inValue): $outValue = 'N'; // to be changed? break; case preg_match('/(mls and board)/i', $inValue): $outValue = 'MB'; // to be changed? break; case preg_match('/(staff)/i', $inValue): $outValue = 'S'; // to be changed? break; case preg_match('/(mls only)/i', $inValue): $outValue = 'M'; // to be changed? break; case preg_match('/(board only)/i', $inValue): $outValue = 'B'; // to be changed? break; case preg_match('/(affiliate)/i', $inValue): $outValue = 'A'; // to be changed? break; default: $outValue = 'unknown'; } return $outValue; } /* * * 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('/(affiliate)/i', $inValue): $outValue = '70'; break; case preg_match('/(appraiser)/i', strtolower(trim($inValue))): $outValue = '71'; break; case preg_match('/(mls participant)/i', strtolower(trim($inValue))): case preg_match('/(board staff)/i', strtolower(trim($inValue))): $outValue = '72'; break; case preg_match('/(designated realtor)/i', strtolower(trim($inValue))): $outValue = '73'; // translate to 'broker' break; case preg_match('/(inactive)/i', strtolower(trim($inValue))): $outValue = '75'; break; case preg_match('/(non member)/i', strtolower(trim($inValue))): case preg_match('/(vendor)/i', strtolower(trim($inValue))): case preg_match('/(mls non-user)/i', strtolower(trim($inValue))): case preg_match('/(institute affiliate)/i', strtolower(trim($inValue))): $outValue = '77'; break; case preg_match('/(real estate office)/i', strtolower(trim($inValue))): $outValue = '78'; break; case preg_match('/(asst. non-licensed)/i', strtolower(trim($inValue))): $outValue = '80'; break; case preg_match('/(realtor)/i', strtolower(trim($inValue))): case preg_match('/(secondry dsg realtor)/i', strtolower(trim($inValue))): case preg_match('/(secondary realtor)/i', strtolower(trim($inValue))): $outValue = '83'; // translate to 'broker associate' break; case preg_match('/(asst. licensed)/i', strtolower(trim($inValue))): $outValue = '84'; // translate to 'licensed assistant' break; default: $outValue = '77'; } return $outValue; } /* * * 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) { //2009-07-24 19:59:00 $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]); } /* * * function _convertBoardName() * * get converted MLS value from RETS record * * @access private * @var (string) $inValue * value to convert * @return string * */ private function _convertBoardName($inValue) { // board name, type and status hacks switch(true) { case preg_match('/(junction)/i', strtolower($inValue)): $outValue = 'gjara'; break; case preg_match('/(luis)/i', strtolower($inValue)): $outValue = 'san luis valley'; break; case preg_match('/(pagosa)/i', strtolower($inValue)): $outValue = 'pagosa springs'; break; case preg_match('/(four)/i', strtolower($inValue)): $outValue = 'four corners'; break; case preg_match('/(durango)/i', strtolower($inValue)): $outValue = 'durango'; break; case preg_match('/(montrose)/i', strtolower($inValue)): $outValue = 'montrose'; break; case preg_match('/(delta)/i', strtolower($inValue)): $outValue = 'delta'; break; default: $outValue = 'cren'; } return $outValue; } /* * * function _convertStateNames() * * get converted MLS value from RETS record * * @access private * @var (string) $inValue * value to convert * @return string * */ private function _convertStateNames($inValue) { $stateNames = array('alabama' => 'al', 'arizona' => 'az', 'arkansas' => 'ar', 'california' => 'ca', 'colorado' => 'co', 'connecticut' => 'ct', 'delaware' => 'de', 'district of columbia' => 'dc', 'florida' => 'fl', 'georgia' => 'ga', 'hawaii' => 'hi', 'idaho' => 'id', 'illinois' => 'il', 'indiana' => 'in', 'iowa' => 'ia', 'kansas' => 'ks', 'kentucky' => 'ky', 'louisiana' => 'la', 'maine' => 'me', 'maryland' => 'md', 'massachusetts' => 'ma', 'michigan' => 'mi', 'minnesota' => 'mn', 'mississippi' => 'ms', 'missouri' => 'mo', 'montana' => 'mt', 'nebraska' => 'ne', 'nevada' => 'nv', 'new hampshire' => 'nh', 'new jersey' => 'nj', 'new mexico' => 'nm', 'new york' => 'ny', 'north carolina' => 'nc', 'north dakota' => 'nd', 'ohio' => 'oh', 'oklahoma' => 'ok', 'oregon' => 'or', 'pennsylvania' => 'pa', 'rhode island' => 'ri', 'south carolina' => 'sc', 'south dakota' => 'sd', 'tennessee' => 'tn', 'texas' => 'tx', 'utah' => 'ut', 'vermont' => 'vt', 'virginia' => 'va', 'washington' => 'wa', 'west virginia' => 'wv', 'wisconsin' => 'wi', 'wyoming' => 'wy'); $outValue = ''; foreach ($stateNames as $stateName => $stateCode) if (preg_match('/(' . $stateName . ')/i', strtolower(trim($inValue)))) $outValue = $stateCode; return $outValue == '' ? 'OTH' : strtoupper($outValue); } /* * * function _formatPhone() * * get converted MLS value from RETS record * * @access private * @var (string) $inAreaCode [optional] * value to convert * @var (string) $inPhoneNumber [optional] * value to convert * @var (string) $inExtension [optional] * value to convert * @return string * */ private function _formatPhone($inAreaCode = '', $inPhoneNumber = '', $inExtension = '') { $searchArray = array('(', ')', '-', '.', ':', 'ext', 'x'); if (!empty($inAreaCode)) { $outAreaCode = str_replace($searchArray, '', trim($inAreaCode)); if (strlen($outAreaCode) == 3) { $outAreaCode = '(' . $outAreaCode . ') '; } else { $outAreaCode = ''; } } else { $outAreaCode = ''; } if (!empty($inPhoneNumber)) { $outPhoneNumber = str_replace($searchArray, '', trim($inPhoneNumber)); if (strlen($outPhoneNumber) == 7) { $outPhoneNumber = substr($outPhoneNumber, 0, 3) . '-' . substr($outPhoneNumber, 3); } else { $outPhoneNumber = ''; } } else { $outPhoneNumber = ''; } if (!empty($inExtension)) { $outExtension = str_replace($searchArray, '', trim($inExtension)); $outExtension = ' ext: ' . $outExtension; } else { $outExtension = ''; } return $outAreaCode . $outPhoneNumber . $outExtension; } /*** 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)); 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_cren_%s'; $this->_retsListingTimeStampField = 'L_UpdateDate'; $this->_retsAgentTimeStampField = 'U_UpdateDate'; $this->_retsOfficeTimeStampField = 'O_UpdateDate'; $this->_selectSQL = "SELECT * from %s%s;"; $this->_insertSQL = "INSERT INTO %s (%s) VALUES (%s);"; $this->_updateSQL = "UPDATE %s SET %s%s;"; $this->_deleteCwmlsTrailerDataSQL = <<_deleteCwmlsHeaderDataSQL = <<_cwmlsHeadersTable WHERE mls_system = '%s' AND mls_no NOT LIKE 'oe%s'; SQL; $this->_deleteCwmlsAgentDataSQL = <<_cwmlsAgentsTable WHERE mls_system = '%s' AND status != 'oe'; SQL; $this->_deleteCwmlsOfficeDataSQL = <<_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/Cren Migration' . "\n"; $this->__logEntry('start', $this->_logMessage, 'cren'); $this->_migrateRetsData(); $this->_logMessage = "\n" . date('H:i:s') . ' - Cleaning up RETS/Cren Data'; $this->__logEntry('append', $this->_logMessage, 'cren'); $this->_winrCleanup(); $this->_logMessage = "\n" . date('H:i:s') . ' - Updating RETS/Cren Geo-Coding'; $this->__logEntry('append', $this->_logMessage, 'cren'); $this->_updateGeoCoding(); $this->_logMessage = "\n\n" . date('M d, Y H:i:s') . ' - Ending RETS/Cren Migration'; $this->__logEntry('end', $this->_logMessage, 'cren'); $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 = 'CREN 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 = 'CREN 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_Cren_Winr_Migration();