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