· 9 years ago · Oct 12, 2016, 03:54 PM
1<?php
2/*
3.---------------------------------------------------------------------------.
4| Software: RETSHELPER - PHP Class to interface RETS with Database |
5| Version: 1.0 |
6| Contact: corey@coreyrowell.com |
7| Info: None |
8| Support: corey@coreyrowell.com |
9| ------------------------------------------------------------------------- |
10| Author: Corey Rowell - corey@coreyrowell.com |
11| Copyright (c) 2013, Corey Rowell. All Rights Reserved. |
12| ------------------------------------------------------------------------- |
13| License: This content is released under the |
14| (http://opensource.org/licenses/MIT) MIT License. | |
15'---------------------------------------------------------------------------'
16*/
17/*
18.---------------------------------------------------------------------------.
19| This software requires the use of the PHPRETS library |
20| http://troda.com/projects/phrets/ |
21'---------------------------------------------------------------------------'
22*/
23define("BASE_PATH",dirname(__FILE__)."/");
24
25ini_set('mysql.connect_timeout',0);
26ini_set('default_socket_timeout',0);
27
28class RETSHELPER
29{
30// Defaults
31private $rets, $auth, $config, $database, $mysqli, $data, $log, $scriptstart, $scriptend,
32$previous_start_time, $current_start_time, $updates_log, $active_ListingRids = array();
33public function __construct()
34{
35// Require PHRETS library
36require_once("phrets.php");
37// Start rets connection
38$this->rets = new phRETS;
39$this->scriptstart = date("m-d-y_h-i-s", time());
40 // RETS Server Info
41 $this->auth['url'] = 'redacted';//MLS_URL;//MLS_URL;
42 $this->auth['username'] = 'redacted'; //MLS_USERNAME;
43 $this->auth['password'] = 'redacted'; //MLS_PASS;
44 $this->auth['retsversion'] = ''; //USER Agent Version
45 $this->auth['useragent'] = ''; //USER Agent
46 // RETS Options
47 $this->config['property_classes'] = array("A");//,"B","C","D","E","F");
48 $this->config['KeyField'] = "LIST_1";
49 $this->config['offset_support'] = TRUE; // Enable if RETS server supports 'offset'
50 $this->config['useragent_support'] = FALSE;
51 $this->config['images_path'] = BASE_PATH."listing_photos/";
52 $this->config['logs_path'] = BASE_PATH."logs/";
53 $this->config['start_times_path'] = BASE_PATH."logs/";
54 $this->config['previous_start_time'] = $this->get_previous_start_time();
55 $this->config['create_tables'] = FALSE; // Create tables for classes (terminates program)
56 // Log to screen?
57 $this->config['to_screen'] = TRUE;
58 // Database Config
59 $this->database['host'] = 'redacted'; //DB_SERVER;
60 $this->database['username'] = 'redacted'; //DB_USER;
61 $this->database['password'] = 'redacted'; //DB_PASS;
62 $this->database['database'] = 'redacted'; //DB_NAME;
63 $this->config_init();
64 // Load the run function
65 $this->run();
66}
67private function config_init()
68{
69 // Set offset support based on config
70 if($this->config['offset_support'])
71 {
72 $this->rets->SetParam("offset_support", true);
73 } else {
74 $this->rets->SetParam("offset_support", false);
75 }
76 if($this->config['useragent_support'])
77 {
78 $this->rets->AddHeader("RETS-Version", $this->auth['retsversion']);
79 $this->rets->AddHeader("User-Agent", $this->auth['useragent']);
80 }
81}
82public function run()
83{
84 // Start Logging
85 $this->logging_start();
86 // RETS Connection
87 $this->connect();
88 // Connect to Database
89 $this->database_connect();
90 if($this->config['create_tables'])
91 {
92 $this->log_data("Creating database tables, program will exit after finishing.");
93 foreach ($this->config['property_classes'] as $class)
94 {
95 $this->log_data("Creating table for: " . $class);
96 $this->create_table_for_property_class($class);
97 }
98 $this->log_data("Exiting program.");
99 return;
100 }
101 // Get Properties (and images)
102 $this->get_properties_by_class();
103 // Close RETS Connection
104 $this->disconnect();
105 // Delete inactive listings
106 $this->database_delete_records();
107 // Insert new listings
108 $this->database_insert_records();
109 // Disconnect from Database
110 $this->database_disconnect();
111 // End Logging
112 $this->logging_end();
113 // Time for next scheduled update
114 $this->set_previous_start_time();
115}
116private function connect()
117{
118 $this->log_data("Connecting to RETS...");
119 // Connect to RETS
120 $connect = $this->rets->Connect($this->auth['url'], $this->auth['username'], $this->auth['password']);
121 if($connect)
122 {
123 $this->log_data("Successfully connected to RETS.");
124 return TRUE;
125 } else {
126 $error = $this->rets->Error();
127 if($error['text'])
128 {
129 $error = $error['text'];
130 } else {
131 $error = "No error message returned from RETS. Check RETS debug file.";
132 }
133 $this->log_error("Failed to connect to RETS.n".$error);
134 die();
135 }
136}
137private function get_properties_by_class()
138{
139 $this->log_data("Getting Classes...");
140 foreach ($this->config['property_classes'] as $class)
141 {
142 $this->log_data("Getting Class: ".$class);
143 // Set
144 $fields_order = array();
145 $mod_timestamp_field = $this->get_timestamp_field($class);
146 $previous_start_time = $this->config['previous_start_time'];
147 $search_config = array('Format' => 'COMPACT-DECODED', 'QueryType' => 'DMQL2', 'Limit'=> 1000, 'Offset' => 1, 'Count' => 1);
148 /*--------------------------------------------------------------------------------.
149 | |
150 | If you're having problems, they probably lie here in the $query and/or $search. |
151 | |
152 '--------------------------------------------------------------------------------*/
153 // Query
154 $query = "({$mod_timestamp_field}=2016-09-16T00:00:00-2016-09-16T01:00:00)";//{$previous_start_time}+)";
155 // Run Search
156 $search = $this->rets->SearchQuery("Property", $class, $query, $search_config);
157 // Get all active listings
158 $query_all = "({$mod_timestamp_field}=1980-01-01T00:00:00+)";
159 $search_all = $this->rets->SearchQuery("Property", $class, $query_all, array('Format'=>'COMPACT', 'Select'=>$this->config['KeyField']));
160 $tmpArray = array();
161 while($active_rid = $this->rets->FetchRow($search_all)) {
162 array_push($tmpArray, $active_rid[$this->config['KeyField']]);
163 }
164 $this->active_ListingRids['property_'.strtolower($class)] = $tmpArray;
165 $data = array();
166 if ($this->rets->NumRows($search) > 0)
167 {
168 // Get columns
169 $fields_order = $this->rets->SearchGetFields($search);
170 $this->data['headers'] = $fields_order;
171 // Process results
172 while ($record = $this->rets->FetchRow($search))
173 {
174 $this_record = array();
175 // Loop it
176 foreach ($fields_order as $fo)
177 {
178 $this_record[$fo] = $record[$fo];
179 }
180 $ListingRid = $record[$this->config['KeyField']];
181 $data[] = $this_record;
182 }
183 }
184 // Set data
185 $this->data['classes'][$class] = $data;
186 $this->log_data("Finished Getting Class: ".$class . "nTotal found: " .$this->rets->TotalRecordsFound());
187 // Free RETS Result
188 $this->rets->FreeResult($search);
189 }
190}
191private function get_timestamp_field($class)
192{
193 $class = strtolower($class);
194 switch($class)
195 {
196 case 'a':
197 $field = "LIST_87";
198 break;
199 }
200 return $field;
201}
202private function disconnect()
203{
204 $this->log_data("Disconnected from RETS.");
205 $this->rets->Disconnect();
206}
207private function database_connect()
208{
209 $this->log_data("Connecting to database...");
210 $host = $this->database['host'];
211 $username = $this->database['username'];
212 $password = $this->database['password'];
213 $database = $this->database['database'];
214 // Create connection
215 $this->mysqli = new mysqli($host, $username, $password, $database);
216 // Throw error if connection fails
217 if ($this->mysqli->connect_error) {
218 $this->log_error("Database Connection Error". $this->mysqli->connect_error);
219 die('Connect Error (' . $this->mysqli->connect_errno . ') '
220 . $this->mysqli->connect_error);
221 }
222}
223private function database_delete_records()
224{
225 $this->log_data("Updating database...");
226 // Loop through each table and update
227 foreach($this->config['property_classes'] as $class)
228 {
229 // Get Tables
230 $table = "rets_property_".strtolower($class);
231 $activeListings = $this->active_ListingRids['property_'.strtolower($class)];
232 $sql = "DELETE FROM {$table} WHERE {$this->config['KeyField']} NOT IN (".implode(',', $activeListings).");";
233
234 $this->mysqli->query($sql);
235 if($this->mysqli->affected_rows > 0)
236 {
237 $this->log_data("Deleted {$this->mysqli->affected_rows} Listings.");
238// return TRUE;
239 } else if($this->mysqli->affected_rows == 0) {
240 $this->log_data("Deleted {$this->mysqli->affected_rows} Listings.");
241 } else {
242 $this->log_data("Deleting database records failed nn" . mysqli_error($this->mysqli));
243// return FALSE;
244 }
245 }
246}
247private function database_insert_records()
248{
249 $this->log_data("Inserting records...");
250 foreach($this->config['property_classes'] as $class)
251 {
252 // Get Tables
253 $table = "rets_property_".strtolower($class);
254 // Get data
255 $data_row = $this->data['classes'][$class];
256 // Defaults
257 $total_rows = 0;
258 $total_affected_rows = 0;
259 // Loop through data
260 foreach($data_row as $drow)
261 {
262 // Clean data
263 // replace empty with NULL
264 // and wrap data in quotes
265 $columns = array();
266 $values = array();
267 foreach($drow as $key => $val)
268 {
269 if($val === '')
270 {
271 $val = '""';
272 } else {
273 $val = mysqli_real_escape_string($this->mysqli ,$val);
274 $val = "'$val'";
275 }
276 $columns[] = $key;
277 $values[] = $val;
278 }
279 // Implode data rows with commas
280 $values = implode(', ', $values);
281 $columns = implode(', ', $columns);
282 // Build SQL
283 $sql = "REPLACE INTO {$table} ({$columns}) VALUES ({$values})";
284 // Do query
285 $this->mysqli->query($sql);
286 if($this->mysqli->affected_rows > 0)
287 {
288 $total_affected_rows++;
289 } else {
290 $this->log_error("Failed to insert the following record: ".$sql . "nn" . mysqli_error($this->mysqli));
291 }
292 $total_rows++;
293 }
294 $this->log_data("Done inserting data. ".$class."nTotal Records: ".$total_rows." .nTotal Inserted: ".$total_affected_rows);
295 }
296}
297private function database_disconnect()
298{
299 $this->log_data("Database disconnected...");
300 // Close connection
301 $this->mysqli->close();
302}
303private function create_table_for_property_class($class)
304{
305 // gets resource information. need this for the KeyField
306 $rets_resource_info = $this->rets->GetMetadataInfo();
307 $resource = "Property";
308 // pull field format information for this class
309 $rets_metadata = $this->rets->GetMetadata($resource, $class);
310 $table_name = "rets_".strtolower($resource)."_".strtolower($class);
311 // i.e. rets_property_resi
312 $sql = $this->create_table_sql_from_metadata($table_name, $rets_metadata, $rets_resource_info[$resource]['KeyField']);
313 $this->mysqli->query($sql);
314}
315private function create_table_sql_from_metadata($table_name, $rets_metadata, $key_field, $field_prefix = "")
316{
317 $sql_query = "CREATE TABLE {$table_name} (n";
318 foreach ($rets_metadata as $field) {
319 $field['SystemName'] = "`{$field_prefix}{$field['SystemName']}`";
320 $cleaned_comment = addslashes($field['LongName']);
321 $sql_make = "{$field['SystemName']} ";
322 if ($field['Interpretation'] == "LookupMulti") {
323 $sql_make .= "TEXT";
324 }
325 elseif ($field['Interpretation'] == "Lookup") {
326 $sql_make .= "VARCHAR(50)";
327 }
328 elseif ($field['DataType'] == "Int" || $field['DataType'] == "Small" || $field['DataType'] == "Tiny") {
329 $sql_make .= "INT({$field['MaximumLength']})";
330 }
331 elseif ($field['DataType'] == "Long") {
332 $sql_make .= "BIGINT({$field['MaximumLength']})";
333 }
334 elseif ($field['DataType'] == "DateTime") {
335 $sql_make .= "DATETIME default '0000-00-00 00:00:00' not null";
336 }
337 elseif ($field['DataType'] == "Character" && $field['MaximumLength'] <= 255) {
338 $sql_make .= "VARCHAR({$field['MaximumLength']})";
339 }
340 elseif ($field['DataType'] == "Character" && $field['MaximumLength'] > 255) {
341 $sql_make .= "TEXT";
342 }
343 elseif ($field['DataType'] == "Decimal") {
344 $pre_point = ($field['MaximumLength'] - $field['Precision']);
345 $post_point = !empty($field['Precision']) ? $field['Precision'] : 0;
346 $sql_make .= "DECIMAL({$field['MaximumLength']},{$post_point})";
347 }
348 elseif ($field['DataType'] == "Boolean") {
349 $sql_make .= "CHAR(1)";
350 }
351 elseif ($field['DataType'] == "Date") {
352 $sql_make .= "DATE default '0000-00-00' not null";
353 }
354 elseif ($field['DataType'] == "Time") {
355 $sql_make .= "TIME default '00:00:00' not null";
356 }
357 else {
358 $sql_make .= "VARCHAR(255)";
359 }
360 $sql_make .= " COMMENT '{$cleaned_comment}'";
361 $sql_make .= ",n";
362 $sql_query .= $sql_make;
363 }
364 $sql_query .= "`Photos` TEXT COMMENT 'Photos Array', ";
365 $sql_query .= "PRIMARY KEY(`{$field_prefix}{$key_field}`) )";
366 return $sql_query;
367}
368private function get_previous_start_time()
369{
370 $filename = "previous_start_time_A.txt";
371 // See if file exists
372 if(file_exists($this->config['start_times_path'].$filename))
373 {
374 $time=time();
375 $this->updates_log = fopen($this->config['start_times_path'].$filename, "r+");
376 $this->previous_start_time = fgets($this->updates_log);
377 $this->current_start_time = date("Y-m-d", $time) . 'T' . date("H:i:s", $time);
378 } else {
379 // Create file
380 $this->updates_log = fopen($this->config['start_times_path'].$filename, "w+");
381 fwrite($this->updates_log, "1980-01-01T00:00:00n");
382 $this->get_previous_start_time();
383 }
384 // fgets reads up to & includes the first newline, strip it
385 return str_replace("n", '', $this->previous_start_time);
386}
387private function set_previous_start_time()
388{
389 $file = $this->config['start_times_path'] . "previous_start_time_A.txt";
390 $file_data = $this->current_start_time."n";
391 $file_data .= file_get_contents($file);
392 file_put_contents($file, $file_data);
393}
394private function logging_start()
395{
396 $filename = "Log".date("m-d-y_h-i-s", time()).".txt";
397 // See if file exists
398 if(file_exists($this->config['logs_path'].$filename))
399 {
400 $this->log = fopen($this->config['logs_path'].$filename, "a");
401 } else {
402 // Create file
403 $this->log = fopen($this->config['logs_path'].$filename, "w+");
404 }
405}
406private function log_data($data)
407{
408 $write_data = "nInfo Message: [".date("m/d/y - h:i:s", time())."]n------------------------------------------------n";
409 $write_data .= $data."n";
410 $write_data .= "n------------------------------------------------n";
411 fwrite($this->log, $write_data);
412 if($this->config['to_screen'])
413 {
414 echo str_replace(array("n"), array('<br />'), $write_data);
415 }
416}
417private function log_error($error)
418{
419 $write_data = "nError Message: [".date("m/d/y - h:i:s", time())."]n------------------------------------------------n";
420 $write_data .= $error."n";
421 $write_data .= "n------------------------------------------------n";
422 fwrite($this->log, $write_data);
423 if($this->config['to_screen'])
424 {
425 echo str_replace(array("n"), array('<br />'), $write_data);
426 }
427}
428private function logging_end()
429{
430 $this->scriptend = date("m-d-y_h-i-s", time());
431 $this->log_data("Closing log file.n
432 Start Time: {$this->scriptstart}n
433 End Time: {$this->scriptend}");
434 fclose($this->log);
435}
436}
437// Load the class
438$retshelper = new RETSHELPER;