|
+----------------------------------------------------------------------+
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
#Block this file from running in browser to avoid some permission isses
if(isset($_SERVER['HTTP_HOST'])){
die("This file is protected to running from browser
");
}
# this might take a while
set_time_limit(6000); # 100 minutes
# set library path
if (stristr($_SERVER['OS'], "window")) {
$incpath ="C:/projects/nutrix/winr/winr1.0/winr_1_0/includes.1.0/";
}else {
$incpath="/home/flexiss/idx/includes/";
}
// Ross DATE=" 11/22/2006" - start ----
# added for the purpose of facilitating testing
$skip_listings = 0; # set to 1 to skip
$skip_images = 0; # set to 1 to skip
$skip_rets_import = 0; # set to 1 to skip
// Ross DATE=" 11/22/2006" - end ----
# include libraries
define("INCLUDES_PATH",$incpath);
require_once INCLUDES_PATH . "inc.db_fns.php";
require_once INCLUDES_PATH . "inc.idx_fns.php";
require_once INCLUDES_PATH . "inc.config_ags.php";
require_once INCLUDES_PATH . "pcltar.lib.php3";
require_once INCLUDES_PATH . "pclerror.lib.php3";
require_once INCLUDES_PATH . "pcltrace.lib.php3";
# CUSTOM STUFF FOR ASPEN/GLENWOOD SPRINGS
# since zip codes aren't exported (as of the time this is written) we're going to
# fudge a value into the zip code field; we'll do that by creating a hard-coded array
# here compiled from here: http://www.melissadata.com/Lookups/countyzip.asp
# this array will have a city->zip code correspondency; the zip will merely be the first
# zip available for that city, and will not be exact for this address
# the city description, or key in the array, will be every variation that we can think
# a real estate agent will key in
$approximate_city_zips = array(
"ASPEN" => "81612",
"ASPEN HIGHLANDS" => "81612",
"BASALT" => "81621",
"BATTLEMENT MESA" => "81636",
"CARBONDALE" => "81623",
"CRAIG" => "81625",
"DEBEQUE" => "81630",
"GLENWOOD" => "81602",
"GLENWOOD SPRINGS" => "81602",
"GYPSUM" => "81637",
"HAMILTON" => "81638",
"HAYDEN" => "81639",
"MARBLE" => "81623",
"MEEKER" => "81641",
"MEREDITH" => "81642",
"NEW CASTLE" => "81647",
"NEWCASTLE" => "81647",
"OLD SNOWMASS" => "81615",
"PARACHUTE" => "81635",
"RANGELY" => "81648",
"REDSTONE" => "81623",
"RIFLE" => "81650",
"SILT" => "81652",
"SNOWMASS" => "81654",
"SNOWMASS VILLAGE" => "81615",
"SNOWMASS VLG" => "81615",
"VAIL" => "81657",
"WOODY CREEK" => "81656",
"YAMPA" => "80483",
);
# Added For timestamp
$ts = time();
# retrieve the unix timestamp from the last completion
$date_last_completed = get_last_run();
# insert a new record in audits table and fill in the start timestamp
$audit_id = start_audit();
$timestamp = date(AUDIT_DATE_FORMAT);
$audit_text = "
IDX update procedure begun on " . $timestamp . "\n\n";
log_entry($audit_text,$audit_id,$audit_text);
# set the includes path, path for tmp files, and include necessary files
get_ready();
# establish an ftp connection and log in
$ftp_handle = ftp(FTP_HOSTNAME,FTP_USERNAME,FTP_PASSWORD);
if (!$ftp_handle) {
$echo_text = date(AUDIT_DATE_FORMAT) . " *** WARNING *** fatal error: unable to establish an ftp connection\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
send_notification();
exit;
}
# change to the temporary directory we'll be working from
ftp_chdir($ftp_handle,"IDX");
# For handling the auto increment value in the Temporary tables.
#nov 22
temp_auto_increment_values();
############## deal with agent and organization information ############################
if (!$skip_listings) {
# first download the text files
$downloaded_user_tables = download_other($ftp_handle);
if (!$downloaded_user_tables) {
# if we get false back on this f-nction it means there was a fatal error
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** There was a fatal error transferring user tables back to our server\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
} else {
# create user array from the text files
if (!$users_arr = create_users_arr($downloaded_user_tables)) {
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** $user_arr came back empty\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
}
############## deal with text files of listings ########################################
$downloaded_listings = download_listings($date_last_completed,$ftp_handle);
//bhu has added since i dont want to get the files from ftp...s
//$downloaded_listings[] = "listings-residential.txt.gz";
//$downloaded_listings[] = "listings-commercial.txt.gz";
//$downloaded_listings[] = "listings-farm-ranch.txt.gz";
//$downloaded_listings[] = "listings-land.txt.gz";
//$downloaded_listings[] = "listings-multi-family.txt.gz";
//$downloaded_listings[] = "listings-lease.txt.gz";
//bhu has added since i dont want to get the files from ftp...e
if (!$downloaded_listings) {
# if we get false back on this f-nction it means there was a fatal error
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** There was a fatal error transferring listing files back to our server\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
} else if ($downloaded_listings == "everything current") {
# another reason for aborting is that there is nothing to do
$echo_text = date(AUDIT_DATE_FORMAT) . " - * PLEASE NOTE * File timestamps indicate that there were no new listing files to download\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
} else {
# otherwise we'll end up with an array of the filenames that were downloaded
# go through each of these files and import the listings into our listings
# tables locally
$echo_text = date(AUDIT_DATE_FORMAT) . " - successfully downloaded the following listing files: ";
for ($i=0; $i < count($downloaded_listings); $i++) {
$echo_text .= "\n + " . $downloaded_listings[$i];
}
reset($downloaded_listings);
$echo_text .= "\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
#nov 22
$fn_result = import_data($downloaded_listings);
//Bhuva july --- added -s.....
connect();
# comparing the table datas with the temp table....
# if any records in the old table is not found in the temp table Then that record is deleted....
//Nov 22
delete_sold_listing();
# Update the Timestamp column in the temp table And delete the record from the original table.
//Nov 22
update_delete_matchingrow();
#To insert the new values in the original table...
//Nov 22
insert_new_records();
# Deleting the temp table values
//Nov 22
delete_temp_tables();
echo "Done sucessfully";
$echo_text = date(AUDIT_DATE_FORMAT) . " - done importing listings\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
//comment bhuva Oct 05 2006 Thursday -s
}
//comment bhuva Oct 05 2006 Thursday -e
} # end of skip listings conditional
else {
echo "skipped listing download";
$echo_text = date(AUDIT_DATE_FORMAT) . " - skip listing download\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
#Close ftp connection
ftp_close($ftp_handle);
############## deal with image file download and decompress ############################
$ftp_handle = ftp(FTP_HOSTNAME,FTP_USERNAME,FTP_PASSWORD);
# establish an ftp connection and log in
if (!$ftp_handle) {
$echo_text = date(AUDIT_DATE_FORMAT) . " *** WARNING *** fatal error: unable to establish an ftp connection\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
send_notification();
exit;
}
ftp_chdir($ftp_handle,"IDX");
if (!$skip_images) {
//UNCOMMENT THIS ON BHU - OCTOBER 05 2006 -S SINCE IMAGE NEEDED NOT BE DOWNLOADED FOR TESTING PURPOSE.
$downloaded_image_tarballs = download_image_tarballs($date_last_completed,$ftp_handle);
//echo "
".$downloaded_image_tarballs; #Nov 22
//UNCOMMENT THIS ON BHU - OCTOBER 05 2006 -E
//$downloaded_image_tarballs = "everything current";
# Nov 22
if (!$downloaded_image_tarballs) {
# if we get false back on this f-nction it means there was a fatal error
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** There was a fatal error transferring image tarballs back to our server\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
} else if ($downloaded_image_tarballs == "everything current") {
# another reason for aborting is that there is nothing to do
$echo_text = date(AUDIT_DATE_FORMAT) . " - * PLEASE NOTE * File timestamps indicate that there were no new image tar files to download\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
} else {
# otherwise we'll end up with an array of the filenames that were downloaded
# go through each of these files, untar them, and move the files to the appropriate
# directory
$echo_text = date(AUDIT_DATE_FORMAT) . " - successfully downloaded the following listing tar files: ";
for ($i=0; $i < count($downloaded_image_tarballs); $i++) {
//$echo_text = "\n + " . $downloaded_image_tarballs[$i]; # original Doesnot add to the existing string
$echo_text .= "\n + " . $downloaded_image_tarballs[$i];
}
$echo_text .= "\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
$unpack_n_move_results =
unpack_n_move($downloaded_image_tarballs,$ftp_handle);
$echo_text = date(AUDIT_DATE_FORMAT) . " - " . $unpack_n_move_results . "\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
#clear old archive files
cleanup_archive_images();
} # end of skip images conditional
else {
echo "skipped image download";
$echo_text = date(AUDIT_DATE_FORMAT) . " - skip image download\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
if(!$skip_rets_import) {
//import rets data
$result = run_query("SELECT `mls_no`, `lat`, `long` FROM `viele_rets_import`");
while($row = mysql_fetch_assoc($result)) {
$lat = trim($row['lat']);
$long = trim($row['long']);
$mls_no = trim($row['mls_no']);
if($lat != '' && $long != '') {
$sql = "UPDATE `listing_headers` SET `latitude`='$lat', `longitude`='$long', `geocoding_status` = 'Y' WHERE `mls_no` ='$mls_no'";
//echo $sql."\n";
@run_query($sql);
}
}
//archive rets data
$sql = "INSERT INTO `viele_rets_import_archive` (`mls_no`, `class`,`lat`,`long`) SELECT `mls_no`, `class`,`lat`,`long` FROM `viele_rets_import`";
run_query($sql);
$sql = "TRUNCATE TABLE `viele_rets_import`";
run_query($sql);
$sql = "DELETE FROM `viele_rets_import_archive` WHERE DATE_SUB(CURDATE(),INTERVAL 15 DAY) > `date_import`";
run_query($sql);
}else {
echo "skipped RETS import";
$echo_text = date(AUDIT_DATE_FORMAT) . " - skip RETS import\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
############## deal with housekeeping tasks ############################################
$fn_result = rebuild_street_address();
$echo_text = date(AUDIT_DATE_FORMAT) . " - done rebuilding street address from components\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
############## send out email notification(s) ##########################################
send_notification();
end_audit($audit_id);
#Close ftp connection
ftp_close($ftp_handle);
/*================END OF MAIN BLOCK=================================*/
/********************************************************************
| F-NCTION: ftp(3) |
| this f-nction will establish a connection with an ftp server |
| |
| |
| |
*********************************************************************/
function ftp($ftp_server,$ftp_user_name,$ftp_user_pass) {
global $audit_text;
// set up basic connection
$ftp_handle = ftp_connect("idx.fnismls.com", 0, $timeout = 6000);
// login with username and password
$login_result = ftp_login($ftp_handle, $ftp_user_name, $ftp_user_pass);
// check connection
if ((!$ftp_handle) || (!$login_result)) {
$echo_text = "FTP connection has failed!";
$echo_text = "Attempted to connect to $ftp_server for user $ftp_user_name";
//echo $echo_text; #Nov 22
exit;
} else {
$echo_text = "Connected to $ftp_server, for user $ftp_user_name";
//echo $echo_text; #Nov 22
}
return $ftp_handle;
}
/*================END OF ftp()======================================*/
/********************************************************************
| F-NCTION: getready() |
| this f-nction will determine the correct includes path based |
| on whether this is running locally or not, then include the |
| needed files |
| it will also set the tmp path accordingly |
*********************************************************************/
function get_ready() {
$tmppath="";
define("TMP_PATH",$tmppath);
}
/*================END OF get_ready()================================*/
/********************************************************************
| F-NCTION: download_listings |
| download listing files according to contents of global variable |
| $REMOTE_LISTING_TABLES |
| for each element, first check the date/time last modified with |
| ftp_mdtm and only download the ones that carry a time late |
| than the most recent download time |
| |
*********************************************************************/
function download_listings($date_last_completed,$ftp_handle){
global $REMOTE_LISTING_TABLES;
foreach ($REMOTE_LISTING_TABLES as $their_table) {
$i++;
# get the last modified time
$last_modification = ftp_mdtm($ftp_handle, $their_table);
echo "last mod on $their_table: $last_modification\n";
echo "date last completed: $date_last_completed\n";
if ( ($last_modification > $date_last_completed) ||
(!$last_modification) ||
($last_modification == -1) ||
(FORCE_DOWNLOAD==1)
) {
# in case the last mod date is later than last audit date, or
# in case we can't determine the date of the remote file, or
# if the server doesn't support the ftp_mdtm function
# try to download $server_file and save to $local_file
# create a location for the local file
# $local_file = TMP_PATH . $their_table;
$local_file = $their_table;
if (ftp_get($ftp_handle, $local_file, $their_table, FTP_BINARY)) {
$downloaded_listings[] = $local_file;
} else {
$fatal_error = 1;
}
}
}
if ( ( !is_array($downloaded_listings) ) && ($fatal_error) ) {
# there's nothing to work, with, but we know that we encountered at least
# one fatal error, so return false
$downloaded_listings = 0;
} else if ( !is_array($downloaded_listings) ) {
# nothing to work with here, but no errors either
$downloaded_listings = "everything current";
}
return $downloaded_listings;
}
/*================END OF download_listings==========================*/
/********************************************************************
| F-NCTION: import_data(1) |
| for each listing file that was successfully downloaded, |
| delete all entries in the corresponding table |
| (consult $REMOTE_LISTING_TABLES for this correspondency) |
| gunzip the downloaded file |
| work through the downloaded file and create new records for |
| each line in the corresponding local table |
| |
*********************************************************************/
function import_data($downloaded_listings) {
global $REMOTE_LISTING_TABLES, $FIELD_COUNT_CHECK, $audit_text, $audit_id;
global $IS_PARTIAL_IMPORT; #this array is used to keep track is there any fields count mismatch any other import errors occured, so that we can skip sold listing delete
$count = count($downloaded_listings);
for ($i=0; $i<$count; $i++) {
# need to retrieve just the filename from the $downloaded_listings
# variable (since it will have the path mixed in)
$this_filename = $downloaded_listings[$i];
/* original bkp commented since the data from the zip file has to be stored in the temp file.....start
switch ($this_filename) {
case "listings-multi-family.txt.gz":
$table_name = TRAILERS_MULTIFAMILY;
break;
case "listings-land.txt.gz":
$table_name = TRAILERS_LAND;
break;
case "listings-residential.txt.gz":
$table_name = TRAILERS_RESIDENTIAL;
break;
case "listings-commercial.txt.gz":
$table_name = TRAILERS_COMMERCIAL;
break;
case "listings-farm-ranch.txt.gz":
$table_name = TRAILERS_FARM;
break;
case "listings-lease.txt.gz":
$table_name = TRAILERS_LEASE;
break;
}
-----------------end ------------------------*/
//bhuva added.................start
switch ($this_filename) {
case "listings-multi-family.txt.gz":
$table_name = TEMP_TRAILERS_MULTIFAMILY;
break;
case "listings-land.txt.gz":
$table_name = TEMP_TRAILERS_LAND;
break;
case "listings-residential.txt.gz":
$table_name = TEMP_TRAILERS_RESIDENTIAL;
break;
case "listings-commercial.txt.gz":
$table_name = TEMP_TRAILERS_COMMERCIAL;
break;
case "listings-farm-ranch.txt.gz":
$table_name = TEMP_TRAILERS_FARM;
break;
case "listings-lease.txt.gz":
$table_name = TEMP_TRAILERS_LEASE;
break;
}
//echo "bhu:table_name= ".$table_name."
";
//bhuva added.................end
# now delete all the records in our MySQL table which corresponds to this
# text file
/*un comment bhu-s
delete_listings($table_name);
un comment bhu-e*/
# gunzip and open
//original
$gz_handle = gzopen($this_filename,"r");
if (!$gz_handle) {
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** Unable to gzopen the following file: $this_filename\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
continue;
}
# let's get a field count on this file
$this_field_count = $FIELD_COUNT_CHECK[$this_filename];
if (!$FIELD_COUNT_CHECK[$this_filename]) echo "problem on this one: $this_filename
";
$insert_errors = 0;
while (!gzeof($gz_handle)) {
$record = gzgets ($gz_handle, 4096) ;
$fn_result = insert_record($record,$table_name,$this_field_count,$this_filename);
if ($fn_result == -1) {
# I'm going to comment out the next lines, I don't think we need to abort the
# whole import process for the table because of a field count mismatch in one record
# if I'm wrong we might need to put this back in
// $echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** field mismatch in $table_name, ; aborting import for this table\n\n";
// $audit_text .= $echo_text;
// log_entry($audit_text,$audit_id,$echo_text);
// break;
#added 4/30/2008
$IS_PARTIAL_IMPORT[$this_filename] = true;
} else if ($fn_result == 0) {
$insert_errors++;
}
}
if (!$fn_result == -1) {
# -1 would indicate that the process on this table had been aborted, and
# the warning has already been given
$echo_text = date(AUDIT_DATE_FORMAT) . " - completed import of data from $table_name with a total of $insert_errors insertion errors\n\n";
$audit_text .= $echo_text;
//un comment bhu-s
log_entry($audit_text,$audit_id,$echo_text);
//un comment bhu-e*/
}
$tmpn = run_query("select count(*) from $table_name");
$tmprsn = mysql_fetch_row($tmpn);
$echo_text = "\nRows After Insert in $table_name ".$tmprsn[0]."\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
}
/*================END OF import_data================================*/
/********************************************************************
| F-NCTION: download_image_tarballs(2) |
| retrieve a list of all the files in the directory and then |
| determine which files we need to download |
| then download them (obviously) |
| |
*********************************************************************/
function download_image_tarballs($date_last_completed,$ftp_handle) {
global $audit_text, $audit_id;
# get a list of the files in the current directory
$all_files = ftp_nlist($ftp_handle,".");
if(count($all_files) <= 0) {
$echo_text = "\n\n FTP get remote listing is failed by ftp_nlist() \n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
# work our way through the list
for ($i=0; $i < count($all_files); $i++) {
$filename = $all_files[$i];
# if it's one of the old tarballs, skip it
if (stristr($filename,"old")) { continue; }
# if it's one of the compressed text files, skip it
if (stristr($filename,"txt.gz")) { continue; }
# if it's not a tar, skip it
if (!stristr($filename,".tar")) { continue; }
# otherwise the filename should contain an epic date at the end of the
# string before the extension, so let's get it
$filename_epic = substr($filename,-12,8);
# if the overlap isn't present in the config file, let's set it to 7 days
if (!defined(OVERLAP)) {
$overlap = 7;
} else {
$overlap = OVERLAP;
}
# create an epic date from the timestamp and adjust for the overlap
# specified in the config
$date_last_completed = date("Ymd",time()-($overlap * 24 * 60 * 60) );
# if it's later than our date last completed, download it
if ( ($filename_epic > $date_last_completed) || (FORCE_DOWNLOAD == 1) ) {
$local_file = TMP_PATH . $filename;
if (file_exists($local_file)) {
#debug
#$downloaded_image_tarballs[] = $local_file; continue;
unlink($local_file);
}
if (ftp_get($ftp_handle, $local_file, $filename, FTP_BINARY)) {
$downloaded_image_tarballs[] = $local_file;
echo "downloaded $local_file\n";
} else {
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** unable to ftp download: $filename\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
$error = 1;
}
#break;
}
}
#echo "
".count($downloaded_image_tarballs)."
"; #Nov 22
#echo "
".$error."
"; #Nov 22
if ( ( count($downloaded_image_tarballs) == 0) && ($error) ) {
# if the array is empty but there were errors, we'll report accordingly
return 0;
} else if ( count($downloaded_image_tarballs) == 0) {
return "everything current";
} else {
return $downloaded_image_tarballs;
}
}
/*================END OF download_image_tarballs=====================*/
/********************************************************************
| F-NCTION: unpack_n_move(1) |
| untar the files passed in an array |
| then go through all the files and move them to an appropriate |
| sub-directory |
| |
*********************************************************************/
function unpack_n_move($downloaded_image_tarballs,$ftp_handle){
global $audit_text, $audit_id;
for ($i=0; $i < count($downloaded_image_tarballs); $i++) {
# extract files from tar
$file = $downloaded_image_tarballs[$i];
$p_tarname = $file;
$fn_result = PclTarExtract($p_tarname, $p_path="", $p_remove_path="", $p_mode="tar");
}
$echo_text = date(AUDIT_DATE_FORMAT) . " - finished untarring files\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
# now get all the files in the temp directory
$dir = getcwd();
// Open the directory, and proceed to read its contents; only get the names of
# files that are jpegs
if (is_dir($dir)) {
if ($dh = opendir($dir)) {
while (($file = readdir($dh)) !== false) {
if ( stristr($file,".jpg") ) {
$jpg_files[] = $file;
}
}
closedir($dh);
}
}
#counters
$archived_files = 0;
$copied_files = 0;
$thumbnails_created = 0;
$archive_path = IMAGE_ARCHIVE_PATH;
$archive_dir = date('Ymd');
$archive_dir_exists = is_dir($archive_path.'/'.$archive_dir);
# go through the array of jpg file names and copy them where needed
for ($i=0; $i < count($jpg_files); $i++) {
$this_file = $jpg_files[$i];
$dest_dir = find_correct_directory($this_file);
$dest_file = $dest_dir . $this_file;
# let's make sure we're always dealing with lower case filenames
$dest_file = strtolower($dest_file);
if ( file_exists($dest_file) ) {
#archive image
if(!$archive_dir_exists){
$archive_dir_exists = mkdir($archive_path.'/'.$archive_dir);
@chmod($archive_path.'/'.$archive_dir, 0777);
}
if($archive_dir_exists) {
$archive_file = $archive_path.'/'.$archive_dir.'/'.strtolower($this_file);
if(file_exists($archive_file)) {
#chmod($archive_file, 0777);
unlink($archive_file);
}
rename($dest_file, $archive_file);
@chmod($archive_file, 0777);
$archived_files++;
#archive thumb
$dest_file_sm = substr($dest_file,0,(strlen($dest_file)-4)). "_sm.jpg";
if(file_exists($dest_file_sm)){
$archive_thumb_file = $archive_path.'/'.$archive_dir.'/'.strtolower(substr($this_file,0,(strlen($this_file)-4))) . "_sm.jpg";
if(file_exists($archive_thumb_file)) {
#chmod($archive_thumb_file, 0777);
unlink($archive_thumb_file);
}
rename($dest_file_sm, $archive_thumb_file);
@chmod($archive_thumb_file, 0777);
}
}
#delete incase it is not archived
@unlink($dest_file);
@unlink($dest_file_sm);
}
$copy_result = copy($this_file,$dest_file);
if ($copy_result) {
$copied_files++;
} else {
$errors++;
}
# make thumbnail for this image in the same directory
# to make the filename, strip off the jpg extension, add "_sm" and the jpg
# extension back on
$resample_result = make_thumbnail($dest_file);
if ($resample_result) {
$thumbnails_created++;
} else {
$thumbnail_errors++;
}
unlink($this_file);
}
$echo_text = "\n\nfiles not copied: $errors\n";
$echo_text .= "files copied: $copied_files\n";
$echo_text .= "files archived: $archived_files\n";
$echo_text .= "\n\nthumbnails not created: $thumbnail_errors\n";
$echo_text .= "thumbnails created: $thumbnails_created\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
return $copied_files;
}
/*================END OF unpack_n_move==============================*/
/********************************************************************
| F-NCTION: get_last_run() |
| retrieve the datetime from the most recent record in audits |
| |
*********************************************************************/
function get_last_run() {
connect();
$sql = "
SELECT * FROM `audits`
ORDER BY ts_start DESC
LIMIT 1
";
$result = run_query($sql);
$row = mysql_fetch_array($result);
$last_run = $row['end'];
return $last_run;
}
/*================END OF ===========================================*/
/********************************************************************
| F-NCTION: log_entry(2) |
| takes a text parameter and makes an entry to a simple table |
| that tracks runs of this program by date, and then provides |
| a text field; each time the f-nction is called it replaces |
| the existing entry for this date with a the fresh text, it |
| does NOT append |
| the second parameter tells it which record to be dealing with |
*********************************************************************/
function log_entry($audit_text,$audit_id,$echo_text) {
if (AUDIT_ECHO_ON == 1) {
echo ">>" . $echo_text . "\n";
}
connect();
$sql = "
UPDATE `audits`
SET `audit_text` = '$audit_text'
WHERE `audit_id` = '$audit_id'
";
$result = run_query($sql);
return;
}
/*================END OF log_entry==================================*/
/********************************************************************
| F-NCTION: start_audit() |
| will create a new record in the audits table with the current |
| timestamp and return the key from the autoincrement field |
| |
| |
*********************************************************************/
function start_audit() {
connect();
$now = time();
$sql = "
INSERT INTO `audits`
(`audit_id`,
`ts_start`,
`ts_end`,
`mls_system`,
`audit_text`)
VALUES
('',
$now,
'',
'" . MLS_SYSTEM_NAME . "',
'')";
$result = run_query($sql);
$audit_id = mysql_insert_id();
return $audit_id;
}
/*================END OF start_audit ===============================*/
/********************************************************************
| F-NCTION: end_audit() |
| stamps the ending datetime in the audits table |
*********************************************************************/
function end_audit($audit_id) {
connect();
$now = time();
$sql = "
UPDATE audits
SET `ts_end` = $now
WHERE `audit_id` = '$audit_id'
";
$result = run_query($sql);
return 1;
}
/*================END OF end_audit ===============================*/
/********************************************************************
| F-NCTION send_notification() |
| this f-nction will generate an email message sending the full |
| text of the audit messages to the designated recipient |
| the results of this effort will be logged in the audit |
| db table |
*********************************************************************/
function send_notification(){
global $audit_text;
$subject = "IDX Update Results for " . FTP_USERNAME;
$headers = "from: idxupdate@" . ROOT_DOMAIN;
$send_result = mail(AUDIT_EMAIL_TO,$subject,$audit_text,$headers);
if (!$send_result) {
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** There was a problem sending email audit\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
return 0;
} else {
$echo_text = date(AUDIT_DATE_FORMAT) . " - audit e-mail notification successfully sent to " . AUDIT_EMAIL_TO . "\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
return 1;
}
}
/*================END OF ===========================================*/
/********************************************************************
| F-NCTION: delete_trailers |
| this fn receives a filename as an argument and then will delete |
| records based on a switch which determines the table that the |
| filename corresponds to |
| |
| The operation will delete records according to the current |
| MLS_SYSTEM_NAME constant and will remove both header and trailer|
| records that apply. |
| |
| return the number of records affected by the delete (note that |
| if all records are deleted $affected_rows will be 0 |
| |
| |
| |
*********************************************************************/
function delete_listings($table_name) {
global $audit_id;
# count the number of records before
$sql = "
SELECT
*
FROM
" . LISTING_HEADERS . " AS lh JOIN $table_name AS tr
USING
(listing_header_id)
WHERE
lh.mls_system = '" . MLS_SYSTEM_NAME . "'"
;
connect();
$result = run_query($sql);
$records_before = mysql_num_rows($result);
# now delete all these
$sql = "
DELETE
lh,tr
FROM
" . LISTING_HEADERS . " AS lh JOIN $table_name AS tr
USING
(listing_header_id)
WHERE
lh.mls_system = '" . MLS_SYSTEM_NAME . "'
AND
flexiss_status != 'oe'
"; # oe is for "office exclusive"
connect();
$result = run_query($sql);
$affected_rows = mysql_affected_rows();
# count the number of records after
$sql = "
SELECT
*
FROM
" . LISTING_HEADERS . " AS lh JOIN $table_name AS tr
USING
(listing_header_id)
WHERE
lh.mls_system = '" . MLS_SYSTEM_NAME . "'"
;
connect();
$result = run_query($sql);
$records_after = mysql_num_rows($result);
$echo_text = date(AUDIT_DATE_FORMAT) . " Finished deleting " . MLS_SYSTEM_NAME . " records from $table_name and " . LISTING_HEADERS . ". There were $records_before before and $records_after after. Number of affected rows was $affected_rows.\n\n";
$audit_text = $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
return $affected_rows;
}
/*================END OF delete_trailers================================*/
/********************************************************************
| F-NCTION: insert_record |
| |
| |
| |
| |
*********************************************************************/
function insert_record($record,$table_name,$this_field_count,$this_filename) {
global $users_arr;
//bhu added.
global $ts;
$schools = array();
#before we explode the line, we'll replace any backslashes with forward slashes
$record = ereg_replace("\\\\","/",$record);
$this_listing = explode("\t",$record);
# we need to check to make sure that the number of fields in the text file
# matches the number of fields in our table
$count = count($this_listing);
if ($count != $this_field_count) {
echo "\n(Listing number: " . $this_listing[0] . ") this listing count: $count, this_field_count: $this_field_count";
return -1;
}
# start the next sql statement
$sql = "
INSERT INTO `$table_name`
";
# get the information from the mapping table that will tell us how to match up the numeric
# index of the data in the array created from a listing line with the field that the data
# should end up in
$sql = "
SELECT
*
FROM
mapping
WHERE
source_file_name = '$this_filename'
AND
mls_system = '" . MLS_SYSTEM_NAME . "'
ORDER BY
source_field_number
";
connect();
$result = run_query($sql);
# we need to set up arrays with the same count as $this_listing_arr to hold:
# the destination table for each element in $this_listing_arr
# the destination fieldname for each element in $this_listing_arr
while ($row = mysql_fetch_array($result)) {
$source_field_numbers[] = $row['source_field_number'];
$source_field_number = $row['source_field_number'];
$destination_field_names[$source_field_number] = $row['destination_field_name'];
$destination_table[$source_field_number] = $row['destination_table'];
}
$sql_fields_clause_lh .= "listing_header_id, mls_system ";
$sql_values_clause_lh .= "'' , '" . MLS_SYSTEM_NAME . "' ";
/* $lh_autoval = $lh_row['Auto_increment'];
//$sql_values_clause_lh .= "'".$lh_row['Auto_increment']."', '" . MLS_SYSTEM_NAME . "' ";
//
$sql_values_clause_lh .= "'".($lh_autoval + $i)."', '" . MLS_SYSTEM_NAME . "'"; */
# note that this for loop requires the counter variable to be <= instead of the usual <
for ($i=0; $i<=$count; $i++) {
# if we don't have a field with this element number in our mapping table
# we'll skip and move on
if (!in_array($i,$source_field_numbers)) {
continue;
}
$destination_field_name = $destination_field_names[$i];
$this_field_value = $this_listing[$i-1];
if ( (defined("MISSING_LISTING_IDX_IDS")) && (MISSING_LISTING_IDX_IDS == 1)) {
echo "inside the loop";
if ($destination_field_name == "listing_agent_1") {
if (array_key_exists($this_field_value,$users_arr)) {
$temp_arr = $users_arr[$this_field_value];
$listing_agent_id = $temp_arr['agent_idx_id'];
$listing_office_id = $temp_arr['office_idx_id'];
}
}
}
# we will typically anticipate a listing will feed into one of two tables only,
# namely the trailers and the listing_headers
if ($destination_table[$i] == "listing_headers") {
# build sql for the header table
$sql_fields_clause_lh .= ",
$destination_field_name
";
$sql_values_clause_lh .= ",
'" . addslashes($this_field_value) . "'
";
} else {
# build sql for the trailer
$sql_fields_clause_tr .= ",
$destination_field_name
";
$sql_values_clause_tr .= ",
'" . addslashes($this_field_value) . "'
";
}
/* oct 23 */
/* echo "sql_fields_clause_lh".$sql_fields_clause_lh ;
echo "
";
echo "sql_values_clause_lh".$sql_values_clause_lh ;
echo "
";
echo "sql_fields_clause_tr".$sql_fields_clause_tr ;
echo "
";
echo "sql_values_clause_tr".$sql_values_clause_tr ;
echo "
";
*/
# account for special actions that might be needed based on the field destination
# name
if (stristr($destination_field_name,"school")) {
# create an array that we can pass to the school table check fn
# below
$schools[$destination_field_name] = $this_field_value;
}
# we'll store the zip so that we can easily access it below (for example
# we need it for the school table check fn
if ($destination_field_name == "zip") { $this_zip = $this_field_value; }
}
/*original bkp ---s
if ( (defined("MISSING_LISTING_IDX_IDS")) && (MISSING_LISTING_IDX_IDS == 1)) {
$sql_lh = "
INSERT INTO
" . LISTING_HEADERS . "
(
listing_agent_id,
listing_office_id,
$sql_fields_clause_lh
)
VALUES (
'$listing_agent_id',
'$listing_office_id',
$sql_values_clause_lh
)
";
} else {
$sql_lh = "
INSERT INTO
" . LISTING_HEADERS . "
(
$sql_fields_clause_lh
)
VALUES (
$sql_values_clause_lh
)
";
}
--------------e ----------*/
//bhuva added ....s
// Time column added and inserted in to the temporary table...
/**bhu oct 20 -s **/
/*
echo $sql_fields_clause_lh;
echo "
";
echo $sql_values_clause_lh;
echo "
";
echo "
".$sql_fields_clause_tr."
";
echo "
".$sql_values_clause_tr."
";
echo "))))";
exit;*/
/** bhu oct 20 -s e*/
if ( (defined("MISSING_LISTING_IDX_IDS")) && (MISSING_LISTING_IDX_IDS == 1)) {
$sql_lh = "
INSERT INTO
" . TEMP_LISTING_HEADERS . "
(
listing_agent_id,
listing_office_id,
$sql_fields_clause_lh
)
VALUES (
'$listing_agent_id',
'$listing_office_id',
$sql_values_clause_lh
)
";
} else {
$sql_lh = "
INSERT INTO
" . TEMP_LISTING_HEADERS . "
(
$sql_fields_clause_lh,doa_ts
)
VALUES (
$sql_values_clause_lh,$ts
)
";
}
//oct 23 echo "
".$sql_lh."
";
//bhuva added ....e
connect();
$result = run_query($sql_lh);
$listing_header_id = mysql_insert_id();
/* original bkp commented for adding the auto increment value.*/
$sql_fields_clause_tr = "listing_header_id " . $sql_fields_clause_tr;
$sql_values_clause_tr = "'$listing_header_id' " . $sql_values_clause_tr;
//can del -s
/* $sql_fields_clause_tr = "trailers_residential_id"."listing_header_id " . $sql_fields_clause_tr;
$sql_values_clause_tr = "'.$trailers_residential_id.'"."'$listing_header_id' " . $sql_values_clause_tr;
*/
$sql_tr = "
INSERT INTO
" . $table_name . "
(
$sql_fields_clause_tr
)
VALUES (
$sql_values_clause_tr
)
";
connect();
$result = run_query($sql_tr);
# help keep the schools table up to date
schools_table_check($schools,$this_zip);
if (!$result) { return 0; }
return 1;
}
/*================END OF insert_record==============================*/
/********************************************************************
| F-NCTION: download_other |
| |
*********************************************************************/
function download_other($ftp_handle){
$user_info_tables = array(
"users.txt.gz",
"organizations.txt.gz",
);
foreach ($user_info_tables as $their_table) {
$i++;
# try to download $server_file and save to $local_file
# create a location for the local file
# $local_file = TMP_PATH . $their_table;
$local_file = $their_table;
if (ftp_get($ftp_handle, $local_file, $their_table, FTP_BINARY)) {
$downloaded_user_tables[] = $local_file;
} else {
$fatal_error = 1;
}
}
if ( ( !is_array($downloaded_user_tables) ) && ($fatal_error) ) {
# there's nothing to work, with, but we know that we encountered at least
# one fatal error, so return false
$downloaded_user_tables = 0;
}
return $downloaded_user_tables;
}
/*================END OF download_other==========================*/
/********************************************************************
| F-NCTION: create_users_arr |
| |
*********************************************************************/
function create_users_arr($downloaded_user_tables){
foreach ($downloaded_user_tables as $this_filename) {
if ($this_filename == "users.txt.gz") {
# the main part of this function occurs here, since we're only interested
# in users.txt.gz
# gunzip and open
$gz_handle = gzopen($this_filename,"r");
if (!$gz_handle) {
$echo_text = date(AUDIT_DATE_FORMAT) . " - *** WARNING *** Unable to gzopen the following file: $this_filename\n\n";
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
continue;
}
while (!gzeof($gz_handle)) {
$record = gzgets ($gz_handle, 4096) ;
$this_listing = explode("\t",$record);
$agent_idx_id = $this_listing[0];
$office_idx_id = $this_listing[1];
$lname = $this_listing[2];
$fname = $this_listing[3];
$key = trim($lname . ", " . $fname);
$temp_arr['agent_idx_id'] = $agent_idx_id;
$temp_arr['office_idx_id'] = $office_idx_id;
$users_arr[$key] = $temp_arr;
}
}
}
return $users_arr;
}
/*================END OF create_users_arr==========================*/
/********************************************************************
| F-NCTION: create_users_arr |
| |
*********************************************************************/
function rebuild_street_address(){
$sql = "
SELECT
*
FROM
listing_headers
";
$result = run_query($sql);
while ($row = mysql_fetch_array($result)) {
$street_number = $row['street_number'];
$street_direction = $row['street_direction'];
$street_name = $row['street_name'];
$listing_header_id = $row['listing_header_id'];
# if there's nothing in the following fields, it might indicate this is an office
# exclusive, and so any updates would over-write the address (and is unnecessary
# anyway)
if ( (!$street_number) && (!$street_name) ) continue;
if ($street_direction) {
$street = $street_number . " " . $street_direction . " " . $street_name;
} else {
$street = $street_number . " " . $street_name;
}
$street = addslashes($street);
$sql2 = "
UPDATE
listing_headers
SET
street = '$street'
WHERE
listing_header_id = '$listing_header_id'
";
$result2 = run_query($sql2);
}
return 1;
}
/*================END OF rebuild_street_address==========================*/
/********************************************************************
| F-NCTION: schools_table_check |
| |
*********************************************************************/
function schools_table_check($schools,$this_zip){
# normally schools is an array that will pass 0-3 values corresponding to
# elementary, middle, and high schools
foreach ($schools as $destination_field_name => $this_field_value) {
# see if this school is already in the schools table; this_field_value should
# correspond to the school_name_variant field
$sql = "
SELECT COUNT(*) as count FROM `schools`
WHERE school_name_variant = '$this_field_value'
";
$result = run_query($sql);
$row = mysql_fetch_array($result);
$count = $row['count'];
if (!$count) {
# next lines are based on the field name being high_school, middle_school, and
# elementary_school
$school_type_arr = explode("_",$destination_field_name);
$school_type = $school_type_arr[0];
$school_greater_area = retrieve_greater_area_from_zip($this_zip);
$sql = "
INSERT INTO schools (
schools_id,
school_display_name,
school_name_variant,
school_greater_area,
school_type
) VALUES (
'',
'',
'$this_field_value',
'$school_greater_area',
'$school_type'
)
";
$fn_result = run_query($sql);
echo "$sql \n";
}
}
return 1;
}
/*================END OF schools_table_check ==========================*/
/********************************************************************
| F-NCTION: temp_auto_increment_values |
| |
*********************************************************************/
function temp_auto_increment_values(){
//$sql =" DELETE FROM temp_listing_headers WHERE listing_header_id = ".mysql_insert_id();
$sql =" DELETE FROM temp_listing_headers";
run_query($sql);
$sql = "INSERT INTO temp_listing_headers SET listing_header_id = (select max(listing_header_id)+1 from listing_headers)";
run_query($sql);
//$sql ="DELETE FROM temp_trailers_commercial WHERE trailers_commercial_id = ".mysql_insert_id();
$sql ="DELETE FROM temp_trailers_commercial";
run_query($sql);
$sql = "INSERT INTO temp_trailers_commercial SET trailers_commercial_id = (select max(trailers_commercial_id)+1 from trailers_commercial)";
run_query($sql);
//$sql ="DELETE FROM temp_trailers_farm_ranch WHERE trailers_farm_ranch_id = ".mysql_insert_id();
$sql ="DELETE FROM temp_trailers_farm_ranch";
run_query($sql);
$sql = "INSERT INTO temp_trailers_farm_ranch SET trailers_farm_ranch_id = (select max( trailers_farm_ranch_id )+1 from trailers_farm_ranch)";
run_query($sql);
//$sql ="DELETE FROM temp_trailers_land WHERE trailers_land_id = ".mysql_insert_id();
$sql ="DELETE FROM temp_trailers_land";
run_query($sql);
$sql = "INSERT INTO temp_trailers_land SET trailers_land_id = (select max( trailers_land_id)+1 from trailers_land)";
run_query($sql);
//$sql ="DELETE FROM temp_trailers_multifamily WHERE trailers_multifamily_id = ".mysql_insert_id();
$sql ="DELETE FROM temp_trailers_multifamily";
run_query($sql);
$sql = "INSERT INTO temp_trailers_multifamily SET trailers_multifamily_id = (select max( trailers_multifamily_id )+1 from trailers_multifamily)";
run_query($sql);
//$sql ="DELETE FROM temp_trailers_residential WHERE trailers_residential_id = ".mysql_insert_id();
$sql ="DELETE FROM temp_trailers_residential";
run_query($sql);
$sql = "INSERT INTO temp_trailers_residential SET trailers_residential_id = (select max( trailers_residential_id )+1 from trailers_residential)";
run_query($sql);
//$sql ="DELETE FROM temp_trailers_lease WHERE trailers_lease_id = ".mysql_insert_id();
$sql ="DELETE FROM temp_trailers_lease";
run_query($sql);
$sql = "INSERT INTO temp_trailers_lease SET trailers_lease_id = (select max( trailers_lease_id )+1 from trailers_lease)";
run_query($sql);
}
/*================END OF temp_auto_increment_values ==========================*/
/********************************************************************
| F-NCTION: delete_sold_listing() |
| this f-nction will compare the old table and temp table.... |
| if any records in the old table is not found in the temp table |
| Then that record is deleted.... |
*********************************************************************/
function delete_sold_listing() {
global $downloaded_listings, $audit_text, $audit_id, $IS_PARTIAL_IMPORT;
if(is_array($IS_PARTIAL_IMPORT)) {
$echo_text = "\n\nMismatch count for the following listings";
foreach($IS_PARTIAL_IMPORT as $key => $val) {
$echo_text .= "\n".$key."";
}
log_entry($audit_text,$audit_id,$echo_text);
}
connect();
# comparing the old table and temp table....
# if any records in the old table is not found in the temp table Then that record is deleted....
$sql_pre_delete = "
SELECT listing_header_id,mls_no,class FROM " . LISTING_HEADERS . " WHERE mls_system = '" . MLS_SYSTEM_NAME . "' AND flexiss_status != 'oe' AND mls_no NOT IN (
SELECT mls_no FROM " . TEMP_LISTING_HEADERS . " WHERE mls_system = '" . MLS_SYSTEM_NAME . "' AND flexiss_status != 'oe')";
//echo "
sql --".$sql_pre_delete."
"; #nov 22
$result_pre_delete = run_query($sql_pre_delete);
//echo "Num Rows sold ".mysql_num_rows($result_pre_delete)."\n";
$residential_deleted_count = 0;
$commercial_deleted_count = 0;
$farmranch_deleted_count = 0;
$land_deleted_count = 0;
$multifamily_deleted_count = 0;
$lease_deleted_count = 0;
$listing_headers_deleted_count = 0;
while ($row_pre_delete = mysql_fetch_array($result_pre_delete)) {
$need_delete = false;
switch ($row_pre_delete['class']) {
case "RESIDENTIAL":
if(!in_array('listings-residential.txt.gz',$downloaded_listings) ||
isset($IS_PARTIAL_IMPORT['listings-residential.txt.gz'])) {
continue;
}
$sql_pre = "
DELETE FROM " . TRAILERS_RESIDENTIAL . " WHERE listing_header_id ='".$row_pre_delete['listing_header_id']."'";
//echo "
Sql pre --".$sql_pre."
"; #nov 22
$result_inner = run_query($sql_pre);
$residential_deleted_count++;
$need_delete = true;
break;
case "COMMERCIAL":
if(!in_array('listings-commercial.txt.gz',$downloaded_listings)||
isset($IS_PARTIAL_IMPORT['listings-commercial.txt.gz'])) {
continue;
}
$sql_pre = "
DELETE FROM " . TRAILERS_COMMERCIAL . " WHERE listing_header_id ='".$row_pre_delete['listing_header_id']."'";
//echo "
Sql comm --".$sql_pre."
"; #nov 22
$result_inner = run_query($sql_pre);
$commercial_deleted_count++;
$need_delete = true;
break;
case "FARMRANCH":
if(!in_array('listings-farm-ranch.txt.gz',$downloaded_listings)||
isset($IS_PARTIAL_IMPORT['listings-farm-ranch.txt.gz'])) {
continue;
}
$sql_pre = "
DELETE FROM " . TRAILERS_FARM . " WHERE listing_header_id ='".$row_pre_delete['listing_header_id']."'";
$result_inner = run_query($sql_pre);
$farmranch_deleted_count++;
$need_delete = true;
break;
case "LAND":
if(!in_array('listings-land.txt.gz',$downloaded_listings)||
isset($IS_PARTIAL_IMPORT['listings-land.txt.gz'])) {
continue;
}
$sql_pre = "
DELETE FROM " . TRAILERS_LAND . " WHERE listing_header_id ='".$row_pre_delete['listing_header_id']."'";
$result_inner = run_query($sql_pre);
$land_deleted_count++;
$need_delete = true;
break;
case "MULTIFAMILY":
if(!in_array('listings-multi-family.txt.gz',$downloaded_listings)||
isset($IS_PARTIAL_IMPORT['listings-multi-family.txt.gz'])) {
continue;
}
$sql_pre = "
DELETE FROM " . TRAILERS_MULTIFAMILY . " WHERE listing_header_id ='".$row_pre_delete['listing_header_id']."'";
$result_inner = run_query($sql_pre);
$multifamily_deleted_count++;
$need_delete = true;
break;
//bhuva oct 18 -s
case "LEASE":
if(!in_array('listings-lease.txt.gz',$downloaded_listings)||
isset($IS_PARTIAL_IMPORT['listings-lease.txt.gz'])) {
continue;
}
$sql_pre = "
DELETE FROM " . TRAILERS_LEASE . " WHERE listing_header_id ='".$row_pre_delete['listing_header_id']."'";
$result_inner = run_query($sql_pre);
$lease_deleted_count++;
$need_delete = true;
break;
//bhuva oct 18 -e
}
if($need_delete) {
$sql_pre = "
DELETE FROM " . LISTING_HEADERS . " WHERE mls_system = '" . MLS_SYSTEM_NAME . "' AND flexiss_status != 'oe' AND listing_header_id='".$row_pre_delete['listing_header_id']."'";
//echo "
Sqlfinal --".$sql_pre."
"; #nov 22
$result = run_query($sql_pre);
$listing_headers_deleted_count++;
}
}//end while
$echo_text = "\n\nSolded records deleted in delete_sold_listing()";
$echo_text .= "\n".TRAILERS_RESIDENTIAL." : ".$residential_deleted_count;
$echo_text .= "\n".TRAILERS_COMMERCIAL." : ".$commercial_deleted_count;
$echo_text .= "\n".TRAILERS_FARM." : ".$farmranch_deleted_count;
$echo_text .= "\n".TRAILERS_LAND." : ".$land_deleted_count;
$echo_text .= "\n".TRAILERS_MULTIFAMILY." : ".$multifamily_deleted_count;
$echo_text .= "\n".TRAILERS_LEASE." : ".$lease_deleted_count;
$echo_text .= "\n".LISTING_HEADERS." : ".$listing_headers_deleted_count;
$audit_text .= $echo_text;
log_entry($audit_text,$audit_id,$echo_text);
}
/*================END OF delete_sold_listing()================================*/
/****************************************************************************************************
| F-NCTION: update_delete_matchingrow() |
| Update the Timestamp column in the temp table And delete the record from the original table. |
*****************************************************************************************************/
function update_delete_matchingrow() {
/* original backup bhuva oct 11 2006
$sql_pre = "
DELETE FROM " . LISTING_HEADERS . " WHERE mls_system = '" . MLS_SYSTEM_NAME . "' AND flexiss_status != 'oe' AND listing_header_id NOT IN (
SELECT listing_header_id FROM " . TEMP_LISTING_HEADERS . " WHERE mls_system = '" . MLS_SYSTEM_NAME . "' AND flexiss_status != 'oe')";
$result = run_query($sql_pre);
*/
/*********bhu added -s *******************************/
# Check the records and delete the records from the temp table if found in the original table.....
/* Commented, previous one bhuva, Nov 8 2006 wednesday -s
$sql = "
SELECT listing_header_id,class,mls_no FROM " . LISTING_HEADERS . " WHERE mls_system = '" . MLS_SYSTEM_NAME . "' AND flexiss_status != 'oe'";
Commented, previous one bhuva, Nov 8 2006 wednesday -s */
/* Backup Commented for the updation of previous records from the mls system - bhuva - nov 15 2006 -s
$sql = "
SELECT TEMP_LH.listing_header_id as listing_header_id,LH.listing_header_id as lh_id,LH.class,LH.mls_no FROM " . LISTING_HEADERS . " AS LH ," . TEMP_LISTING_HEADERS ." AS TEMP_LH WHERE LH.mls_system = '" . MLS_SYSTEM_NAME . "' AND LH.flexiss_status != 'oe' AND LH.mls_no = TEMP_LH.mls_no";
//echo "
sql --".$sql."
";
$result = run_query($sql);
while ($row = mysql_fetch_array($result)) {
$sql_listing = "
DELETE FROM " . TEMP_LISTING_HEADERS . " WHERE listing_header_id ='".$row['listing_header_id']."' AND mls_system = '" . MLS_SYSTEM_NAME . "'";
//echo "
del: ".$row['listing_header_id']." - ".$row['mls_no']." -- ".$sql_listing."
";
$result_listing = run_query($sql_listing);
switch ($row['class']) {
case "RESIDENTIAL":
$sql_inner = "
DELETE FROM " . TEMP_TRAILERS_RESIDENTIAL . " WHERE listing_header_id ='".$row['listing_header_id']."'";
$result_inner = run_query($sql_inner);
break;
case "COMMERCIAL":
$sql_inner = "
DELETE FROM " . TEMP_TRAILERS_COMMERCIAL . " WHERE listing_header_id ='".$row['listing_header_id']."'";
$result_inner = run_query($sql_inner);
break;
case "FARMRANCH":
$sql_inner = "
DELETE FROM " . TEMP_TRAILERS_FARM . " WHERE listing_header_id ='".$row['listing_header_id']."'";
$result_inner = run_query($sql_inner);
break;
case "LAND":
$sql_inner = "
DELETE FROM " . TEMP_TRAILERS_LAND . " WHERE listing_header_id ='".$row['listing_header_id']."'";
//echo "
LAND DEL: ".$row['listing_header_id']." - ".$row['mls_no']." -- ".$sql_inner."
";
$result_inner = run_query($sql_inner);
break;
case "MULTIFAMILY":
$sql_inner = "
DELETE FROM " . TEMP_TRAILERS_MULTIFAMILY . " WHERE listing_header_id ='".$row['listing_header_id']."'";
$result_inner = run_query($sql_inner);
break;
//bhuva oct 18 -s
case "LEASE":
$sql_inner = "
DELETE FROM " . TEMP_TRAILERS_LEASE . " WHERE listing_header_id ='".$row['listing_header_id']."'";
$result_inner = run_query($sql_inner);
break;
//bhuva oct 18 -e
}
}
Commented for the updation of previous records from the mls system - bhuva - nov 15 2006 -e
*/
connect();
# Update the Timestamp column in the temp table And delete the record from the original table.
$sql = "
SELECT TEMP_LH.listing_header_id as listing_header_id,LH.listing_header_id as lh_id,LH.class,LH.mls_no,LH.doa_ts as lh_doa_ts, LH.longitude, LH.latitude, LH.accuracy_level, LH.geocoding_status FROM " . LISTING_HEADERS . " AS LH ," . TEMP_LISTING_HEADERS ." AS TEMP_LH WHERE LH.mls_system = '" . MLS_SYSTEM_NAME . "' AND LH.flexiss_status != 'oe' AND LH.mls_no = TEMP_LH.mls_no";
//echo "
sql --".$sql."
"; #Nov 22
$result = run_query($sql);
//echo "Update Delete Rows ".mysql_num_rows($result)."\n";
while ($row = mysql_fetch_array($result)) {
//raj - included geocoding_status and other fields along with doa_ts
$sql_listing_update = "UPDATE ".TEMP_LISTING_HEADERS." SET doa_ts = '".$row['lh_doa_ts']."', longitude = '".$row['longitude']."', latitude = '".$row['latitude']."', accuracy_level = '".$row['accuracy_level']."', geocoding_status = '".$row['geocoding_status']."' WHERE mls_no ='".$row['mls_no']."' AND mls_system = '" . MLS_SYSTEM_NAME . "'";
//echo "
sql_listing_update --".$sql_listing_update."
"; #Nov 22
//exit;
$result_listing_update = run_query($sql_listing_update);
$sql_listing = "
DELETE FROM " . LISTING_HEADERS . " WHERE listing_header_id ='".$row['lh_id']."' AND mls_system = '" . MLS_SYSTEM_NAME . "'";
//echo "
del: ".$row['listing_header_id']." - ".$row['mls_no']." -- ".$sql_listing."
"; #Nov 22
$result_listing = run_query($sql_listing);
switch ($row['class']) {
case "RESIDENTIAL":
$sql_inner = "
DELETE FROM " . TRAILERS_RESIDENTIAL . " WHERE listing_header_id ='".$row['lh_id']."'";
//echo "
sql_inner --".$sql_inner."
"; #Nov 22
$result_inner = run_query($sql_inner);
break;
case "COMMERCIAL":
$sql_inner = "
DELETE FROM " . TRAILERS_COMMERCIAL . " WHERE listing_header_id ='".$row['lh_id']."'";
$result_inner = run_query($sql_inner);
break;
case "FARMRANCH":
$sql_inner = "
DELETE FROM " . TRAILERS_FARM . " WHERE listing_header_id ='".$row['lh_id']."'";
$result_inner = run_query($sql_inner);
break;
case "LAND":
$sql_inner = "
DELETE FROM " . TRAILERS_LAND . " WHERE listing_header_id ='".$row['lh_id']."'";
//echo "
LAND DEL: ".$row['listing_header_id']." - ".$row['mls_no']." -- ".$sql_inner."
";
$result_inner = run_query($sql_inner);
break;
case "MULTIFAMILY":
$sql_inner = "
DELETE FROM " . TRAILERS_MULTIFAMILY . " WHERE listing_header_id ='".$row['lh_id']."'";
$result_inner = run_query($sql_inner);
break;
//bhuva oct 18 -s
case "LEASE":
$sql_inner = "
DELETE FROM " . TRAILERS_LEASE . " WHERE listing_header_id ='".$row['lh_id']."'";
$result_inner = run_query($sql_inner);
break;
//bhuva oct 18 -e
}
}
$tmpn = run_query("SELECT count( * ) FROM `listing_headers` WHERE `mls_system` = 'cren' and flexiss_status != 'oe' and `geocoding_status`='Y'");
$tmprsn = mysql_fetch_row($tmpn);
//echo "After Geo update ".$tmprsn[0]."\n";
}
/*================END OF update_delete_matchingrow()================================*/
/********************************************************************
| F-NCTION: delete_temp_tables() |
| Deleting the temp table values |
*********************************************************************/
function delete_temp_tables() {
connect();
# Deleting the temp table values
# DELETE the values From Temp listing header
$sql_trunc_listing = "DELETE FROM " . TEMP_LISTING_HEADERS . "";
$result = run_query($sql_trunc_listing);
# DELETE the values From Temp commercial table
$sql_trunc_comm = "DELETE FROM " . TEMP_TRAILERS_COMMERCIAL . "";
$result = run_query($sql_trunc_comm);
# DELETE the values From Temp farm_ranch table
$sql_trunc_farm = "DELETE FROM " . TEMP_TRAILERS_FARM . "";
$result = run_query($sql_trunc_farm);
#DELETE the values From Temp land table
$sql_trunc_land = "DELETE FROM " . TEMP_TRAILERS_LAND . "";
$result = run_query($sql_trunc_land);
# DELETE the values From Temp multifamily table
$sql_trunc_multifamily = "DELETE FROM " . TEMP_TRAILERS_MULTIFAMILY . "";
$result = run_query($sql_trunc_multifamily);
# DELETE the values From Temp residential table
$sql_trunc_resi = "DELETE FROM " . TEMP_TRAILERS_RESIDENTIAL . "";
$result = run_query($sql_trunc_resi);
//bhuva oct 18 -s
# DELETE the values From Temp lease table
$sql_trunc_lease = "DELETE FROM " . TEMP_TRAILERS_LEASE . "";
$result = run_query($sql_trunc_lease);
//bhuva oct 18 -e
}
/*================END OF delete_temp_tables()================================*/
/********************************************************************
| F-NCTION: insert_new_records() |
| To insert the new values in the original table... |
*********************************************************************/
function insert_new_records() {
connect();
//raj - included geocoding_status and other fields along with doa_ts
# From Temp listing header to the listing header table
$sql_insert_listing = "INSERT INTO " . LISTING_HEADERS . "(
`listing_header_id`,
`mls_no`,
`class`,
`type`,
`area`,
`list_price` ,
`street`,
`address2`,
`city` ,
`state`,
`zip`,
`county` ,
`status`,
`water_supplier`,
`sewer_supplier` ,
`gas_supplier`,
`electric_supplier` ,
`title_company`,
`sold_price` ,
`total_taxes`,
`tax_schedule_number` ,
`remarks` ,
`listing_agent_1`,
`listing_agent_phone`,
`listing_agent_2` ,
`listing_office_1` ,
`listing_office_phone`,
`listing_office_2` ,
`map_horizontal`,
`map_vertical`,
`features` ,
`zoning` ,
`confidential_remarks` ,
`mls_system`,
`board_name`,
`vtour`,
`listing_agent_id`,
`listing_office_id`,
`listing_agent_id_secondary`,
`listing_office_id_secondary`,
`flexiss_status`,
`street_number`,
`street_direction`,
`street_name` ,
`directions` ,
`doa_ts` ,
`geocoding_status` ,
`accuracy_level` ,
`latitude` ,
`longitude` ) (SELECT
`listing_header_id`,
`mls_no`,
`class`,
`type`,
`area`,
`list_price` ,
`street`,
`address2`,
`city` ,
`state`,
`zip`,
`county` ,
`status`,
`water_supplier`,
`sewer_supplier` ,
`gas_supplier`,
`electric_supplier` ,
`title_company`,
`sold_price` ,
`total_taxes`,
`tax_schedule_number` ,
`remarks` ,
`listing_agent_1`,
`listing_agent_phone`,
`listing_agent_2` ,
`listing_office_1` ,
`listing_office_phone`,
`listing_office_2` ,
`map_horizontal`,
`map_vertical`,
`features` ,
`zoning` ,
`confidential_remarks` ,
`mls_system`,
`board_name`,
`vtour`,
`listing_agent_id` ,
`listing_office_id`,
`listing_agent_id_secondary`,
`listing_office_id_secondary`,
`flexiss_status`,
`street_number`,
`street_direction`,
`street_name` ,
`directions` ,
`doa_ts` ,
`geocoding_status` ,
`accuracy_level` ,
`latitude` ,
`longitude` FROM " . TEMP_LISTING_HEADERS . ")";
// echo "
".$sql_insert_listing."
"; #Nov 22
$result = run_query($sql_insert_listing);
# From Temp commercial header to the commercial table
$sql_insert_comm = "INSERT INTO " . TRAILERS_COMMERCIAL . "( `listing_header_id`,
`business_opp_available` ,
`real_estate_for_sale`,
`real_estate_for_lease`,
`loading_dock`,
`railroad_siding`,
`books_available`,
`type_of_sale`,
`inventory_included`,
`fixtures_included`,
`lease_included`,
`year_built`,
`approximate_total_acres`,
`area_main_sf` ,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`area_office_sf`,
`area_warehouse_sf` ,
`approximate_total_sf`,
`number_units` ,
`number_floors`,
`area_finished_sf`,
`property_known_as` ,
`lot_dimensions`,
`current_business` ,
`year_started` ,
`description` ,
`exchange_for` ,
`air_condition` ,
`building_construction` ,
`heat_type` ,
`amperage` ,
`volts` ,
`ceiling_height` ,
`number_overhead_doors` ,
`ada_yn`,
`earnest_money_deposit` ,
`earnest_money_holder` ,
`possession` ,
`lease_class`,
`subdivision_or_location` ,
`lot_sf`,
`under_construction`,
`tenant_pays` ,
`year_remodeled` ,
`hoa_dues` ,
`special_assessment`,
`number_water_meters`,
`number_gas_meters` ,
`number_electric_meters` ,
`number_parking_spaces` ,
`common_area_maintenance` ,
`common_area_maintenance_charges` ,
`monthly_insurance_charges` ,
`lease_type` ,
`remaining_lease_months` ,
`inventory_value` ,
`ebitda` ,
`number_dock_hi_doors`
) (SELECT `listing_header_id`,
`business_opp_available` ,
`real_estate_for_sale`,
`real_estate_for_lease`,
`loading_dock`,
`railroad_siding`,
`books_available`,
`type_of_sale`,
`inventory_included`,
`fixtures_included`,
`lease_included`,
`year_built`,
`approximate_total_acres`,
`area_main_sf` ,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`area_office_sf`,
`area_warehouse_sf` ,
`approximate_total_sf`,
`number_units` ,
`number_floors`,
`area_finished_sf`,
`property_known_as` ,
`lot_dimensions`,
`current_business` ,
`year_started` ,
`description` ,
`exchange_for` ,
`air_condition` ,
`building_construction` ,
`heat_type` ,
`amperage` ,
`volts` ,
`ceiling_height` ,
`number_overhead_doors` ,
`ada_yn`,
`earnest_money_deposit` ,
`earnest_money_holder` ,
`possession` ,
`lease_class`,
`subdivision_or_location` ,
`lot_sf`,
`under_construction`,
`tenant_pays` ,
`year_remodeled` ,
`hoa_dues` ,
`special_assessment`,
`number_water_meters`,
`number_gas_meters` ,
`number_electric_meters` ,
`number_parking_spaces` ,
`common_area_maintenance` ,
`common_area_maintenance_charges` ,
`monthly_insurance_charges` ,
`lease_type` ,
`remaining_lease_months` ,
`inventory_value` ,
`ebitda` ,
`number_dock_hi_doors` FROM " . TEMP_TRAILERS_COMMERCIAL . ")";
// echo "
".$sql_insert_comm."
"; #Nov 22
$result = run_query($sql_insert_comm);
# From Temp farm_ranch header to the farm_ranch table
$sql_insert_farm = "INSERT INTO " . TRAILERS_FARM . "( `listing_header_id`,
`mineral_rights`,
`pasture` ,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school` ,
`approximate_total_acres`,
`approximate_irrigated_acres`,
`irrigation_district`,
`number_shares`,
`irrigation_annual_cost` ,
`year_built` ,
`area_main_sf`,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`approximate_total_sf`,
`number_bedrooms` ,
`number_bathrooms`,
`leased` ,
`dimensions_master_br` ,
`dimensions_living_room`,
`dimensions_br2` ,
`dimensions_dining_room`,
`dimensions_br3`,
`dimensions_family_room`,
`dimensions_br4`,
`dimensions_kitchen`,
`dimensions_office_den`,
`dimensions_laundry_utility`,
`garage_capacity` ,
`garage_type`,
`barn` ,
`corral`,
`living_quarters`,
`can_be_divided`,
`horse_setup` ,
`lot_dimensions`,
`sale_or_rent`,
`irrigation_water`,
`year_remodeled`,
`bathrooms_description`) (SELECT `listing_header_id`,
`mineral_rights`,
`pasture` ,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school` ,
`approximate_total_acres`,
`approximate_irrigated_acres`,
`irrigation_district`,
`number_shares`,
`irrigation_annual_cost` ,
`year_built` ,
`area_main_sf`,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`approximate_total_sf`,
`number_bedrooms` ,
`number_bathrooms`,
`leased` ,
`dimensions_master_br` ,
`dimensions_living_room`,
`dimensions_br2` ,
`dimensions_dining_room`,
`dimensions_br3`,
`dimensions_family_room`,
`dimensions_br4`,
`dimensions_kitchen`,
`dimensions_office_den`,
`dimensions_laundry_utility`,
`garage_capacity` ,
`garage_type`,
`barn` ,
`corral`,
`living_quarters`,
`can_be_divided`,
`horse_setup` ,
`lot_dimensions`,
`sale_or_rent`,
`irrigation_water`,
`year_remodeled`,
`bathrooms_description` FROM " . TEMP_TRAILERS_FARM . ")";
$result = run_query($sql_insert_farm);
# From Temp land header to the land table
$sql_insert_land = "INSERT INTO " . TRAILERS_LAND . "(
`listing_header_id`,
`sewer_hu_available`,
`sewer_septic_in`,
`sewer_paid`,
`water_tap_available`,
`water_tap_installed`,
`water_tap_paid`,
`water_rights`,
`mineral_rights`,
`modular_mobile_allowed`,
`subdivision_number` ,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school`,
`fronts`,
`lot_dimensions` ,
`approximate_total_acres`,
`approximate_irrigated_acres` ,
`irrigation_district` ,
`irrigation_shares` ,
`press_system` ,
`adjudicated` ,
`curbs_gutters_in_paid` ,
`hoa_dues` ,
`special_assessment` ,
`earnest_money_deposit` ,
`earnest_money_holder` ,
`possession` ,
`directions`,
`hoa_dues_frequency`,
`lot_sf` ,
`improved_w_home` ,
`home_sf` ,
`number_bedrooms` ,
`number_bathrooms` ,
`home_condition` ,
`home_style` ,
`home_year_built`,
`hoa`,
`sale_rent`,
`horse_setup`,
`number_water_taps`,
`number_sewer_taps`
) (SELECT
`listing_header_id`,
`sewer_hu_available`,
`sewer_septic_in`,
`sewer_paid`,
`water_tap_available`,
`water_tap_installed`,
`water_tap_paid`,
`water_rights`,
`mineral_rights`,
`modular_mobile_allowed`,
`subdivision_number` ,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school`,
`fronts`,
`lot_dimensions` ,
`approximate_total_acres`,
`approximate_irrigated_acres` ,
`irrigation_district` ,
`irrigation_shares` ,
`press_system` ,
`adjudicated` ,
`curbs_gutters_in_paid` ,
`hoa_dues` ,
`special_assessment` ,
`earnest_money_deposit` ,
`earnest_money_holder` ,
`possession` ,
`directions`,
`hoa_dues_frequency`,
`lot_sf` ,
`improved_w_home` ,
`home_sf` ,
`number_bedrooms` ,
`number_bathrooms` ,
`home_condition` ,
`home_style` ,
`home_year_built`,
`hoa`,
`sale_rent`,
`horse_setup`,
`number_water_taps`,
`number_sewer_taps`
FROM " . TEMP_TRAILERS_LAND . ")";
$result = run_query($sql_insert_land);
# From Temp multifamily header to the multifamily table
$sql_insert_multifamily = "INSERT INTO " . TRAILERS_MULTIFAMILY . " (
`listing_header_id`,
`multi_story`,
`subdivision_number`,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school` ,
`lot_dimension`,
`approximate_total_acres`,
`irrigation_district`,
`approximate_total_sf`,
`year_built`,
`property_manager`,
`no_of_units`,
`unit_type_1_no_units`,
`unit_type_1_no_bedrooms`,
`unit_type_1_no_bathrooms`,
`unit_type_1_sf`,
`unit_type_1_monthly_rent`,
`unit_type_2_no_units`,
`unit_type_2_no_bedrooms`,
`unit_type_2_no_bathrooms`,
`unit_type_2_sf`,
`unit_type_2_monthly_rent`,
`unit_type_3_no_units`,
`unit_type_3_no_bedrooms`,
`unit_type_3_no_bathrooms`,
`unit_type_3_sf`,
`unit_type_3_monthly_rent`,
`unit_type_4_no_units`,
`unit_type_4_no_bedrooms`,
`unit_type_4_no_bathrooms`,
`unit_type_4_sf`,
`unit_type_4_monthly_rent`,
`unit_type_5_no_units`,
`unit_type_5_no_bedrooms`,
`unit_type_5_no_bathrooms`,
`unit_type_5_sf`,
`unit_type_5_monthly_rent`,
`unit_type_6_no_units`,
`unit_type_6_no_bedrooms`,
`unit_type_6_no_bathrooms`,
`unit_type_6_sf`,
`unit_type_6_monthly_rent`,
`hoa` ,
`hoa_dues` ,
`hoa_dues_frequency`,
`sale_rent`,
`garage_carport`,
`approximate_irrigated_acres`,
`number_shares`,
`year_remodeled`,
`gross_scheduled_income`,
`vacancy_as_percent`,
`gross_annual_expenses`,
`net_operating_income`,
`garage_capacity`,
`garage_type`,
`number_parking_spaces`,
`number_water_meters`,
`number_gas_meters`,
`number_electric_meters`,
`electric_supplier`) (SELECT `listing_header_id`,
`multi_story`,
`subdivision_number`,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school` ,
`lot_dimension`,
`approximate_total_acres`,
`irrigation_district`,
`approximate_total_sf`,
`year_built`,
`property_manager`,
`no_of_units`,
`unit_type_1_no_units`,
`unit_type_1_no_bedrooms`,
`unit_type_1_no_bathrooms`,
`unit_type_1_sf`,
`unit_type_1_monthly_rent`,
`unit_type_2_no_units`,
`unit_type_2_no_bedrooms`,
`unit_type_2_no_bathrooms`,
`unit_type_2_sf`,
`unit_type_2_monthly_rent`,
`unit_type_3_no_units`,
`unit_type_3_no_bedrooms`,
`unit_type_3_no_bathrooms`,
`unit_type_3_sf`,
`unit_type_3_monthly_rent`,
`unit_type_4_no_units`,
`unit_type_4_no_bedrooms`,
`unit_type_4_no_bathrooms`,
`unit_type_4_sf`,
`unit_type_4_monthly_rent`,
`unit_type_5_no_units`,
`unit_type_5_no_bedrooms`,
`unit_type_5_no_bathrooms`,
`unit_type_5_sf`,
`unit_type_5_monthly_rent`,
`unit_type_6_no_units`,
`unit_type_6_no_bedrooms`,
`unit_type_6_no_bathrooms`,
`unit_type_6_sf`,
`unit_type_6_monthly_rent`,
`hoa`,
`hoa_dues` ,
`hoa_dues_frequency`,
`sale_rent`,
`garage_carport`,
`approximate_irrigated_acres`,
`number_shares`,
`year_remodeled`,
`gross_scheduled_income`,
`vacancy_as_percent`,
`gross_annual_expenses`,
`net_operating_income`,
`garage_capacity`,
`garage_type`,
`number_parking_spaces`,
`number_water_meters`,
`number_gas_meters`,
`number_electric_meters`,
`electric_supplier` FROM " . TEMP_TRAILERS_MULTIFAMILY . ")";
$result = run_query($sql_insert_multifamily);
# From Temp residential header to the residential table
$sql_insert_resi = "INSERT INTO " . TRAILERS_RESIDENTIAL . "(
`listing_header_id`,
`rv_parking`,
`dining_room`,
`family_room`,
`irrigation_water`,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school`,
`fronts`,
`lot_dimensions`,
`approximate_total_acres`,
`approximate_irrigated_acres`,
`irrigation_district`,
`irrigation_description`,
`area_main_sf`,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`area_fbasement_sf`,
`approximate_total_sf`,
`year_built`,
`number_bedrooms`,
`number_bathrooms`,
`bathrooms_description`,
`dimensions_master_br`,
`dimensions_living_room`,
`dimensions_br2`,
`dimensions_dining_room`,
`dimensions_br3`,
`dimensions_family_room` ,
`dimensions_br4`,
`dimensions_kitchen`,
`dimensions_office_den`,
`dimensions_laundry_utility`,
`garage_capacity`,
`garage_type`,
`hoa`,
`hoa_dues`,
`hoa_dues_frequency`,
`energy_rating`,
`horse_setup`,
`great_room`,
`number_half_baths`,
`lot_sf`,
`lv_ht_sf`,
`above_ground_sf`,
`below_ground_sf`,
`unfinished_sf`,
`garage_sf`,
`patio_deck_sf`,
`number_floors`,
`short_termable`,
`furnished` ,
`under_construction` ,
`year_remodeled`,
`number_phone_lines`,
`internet_service`) (SELECT `listing_header_id`,
`rv_parking`,
`dining_room`,
`family_room`,
`irrigation_water`,
`subdivision_name`,
`elementary_school`,
`middle_school`,
`high_school`,
`fronts`,
`lot_dimensions`,
`approximate_total_acres`,
`approximate_irrigated_acres`,
`irrigation_district`,
`irrigation_description`,
`area_main_sf`,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`area_fbasement_sf`,
`approximate_total_sf`,
`year_built`,
`number_bedrooms`,
`number_bathrooms`,
`bathrooms_description`,
`dimensions_master_br`,
`dimensions_living_room`,
`dimensions_br2`,
`dimensions_dining_room`,
`dimensions_br3`,
`dimensions_family_room`,
`dimensions_br4`,
`dimensions_kitchen`,
`dimensions_office_den`,
`dimensions_laundry_utility`,
`garage_capacity`,
`garage_type`,
`hoa`,
`hoa_dues`,
`hoa_dues_frequency`,
`energy_rating`,
`horse_setup`,
`great_room`,
`number_half_baths`,
`lot_sf`,
`lv_ht_sf`,
`above_ground_sf`,
`below_ground_sf`,
`unfinished_sf`,
`garage_sf`,
`patio_deck_sf`,
`number_floors`,
`short_termable`,
`furnished`,
`under_construction`,
`year_remodeled`,
`number_phone_lines`,
`internet_service` FROM " . TEMP_TRAILERS_RESIDENTIAL . ")";
//echo "
".$sql_insert_resi."
"; #Nov 22
$result = run_query($sql_insert_resi);
//bhuva oct 18 -s
# From Temp Lease header to the Lease table
$sql_insert_lease = "INSERT INTO " . TRAILERS_LEASE . "(
`listing_header_id`,
`sale_rent`,
`lease_class`,
`listing_agreement`,
`trans_broker_percent`,
`buyer_agency_percent`,
`variable_rate`,
`listing_date`,
`expiration_date`,
`lot_dimensions`,
`property_known_as`,
`vacant`,
`year_built`,
`year_remodeled`,
`limited_service`,
`entry_only`,
`area_main_sf`,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`area_office_sf`,
`area_warehouse_sf`,
`approximate_total_sf_comm`,
`number_units`,
`number_floors`,
`area_sf_finished`,
`air_condition`,
`heat_type`,
`ada_yn`,
`amperage`,
`volts`,
`ceiling_height`,
`number_dockhigh_doors`,
`number_overhead_doors`,
`term`,
`security_deposit_amount`,
`possession`,
`style`,
`approximate_total_sf_res`,
`garage_type`,
`number_bedrooms`,
`number_bathrooms`,
`bathrooms_description`,
`dimensions_master_br`,
`dimensions_living_room`,
`dimensions_br2`,
`dimensions_dining_room`,
`dimensions_br3`,
`dimensions_family_room`,
`dimensions_br4`,
`dimensions_kitchen`,
`dimensions_office_den`,
`dimensions_laundry_utility`,
`dimensions_basement`,
`dimensions_fbasement`,
`irrigation_water`,
`barn`,
`corral`,
`possession1`,
`off_market_date`,
`associated_document_count`,
`addendum`,
`price_original`,
`directions`,
`how_sold`,
`contract_date`,
`closing_date`,
`common_area_maintenance`,
`common_area_maintenance_charges`,
`subdivision_name`,
`lease_type`,
`number_parking_spaces`) (SELECT `listing_header_id`,
`sale_rent`,
`lease_class`,
`listing_agreement`,
`trans_broker_percent`,
`buyer_agency_percent`,
`variable_rate`,
`listing_date`,
`expiration_date`,
`lot_dimensions`,
`property_known_as`,
`vacant`,
`year_built`,
`year_remodeled`,
`limited_service`,
`entry_only`,
`area_main_sf`,
`area_upper_sf`,
`area_lower_sf`,
`area_basement_sf`,
`area_office_sf`,
`area_warehouse_sf`,
`approximate_total_sf_comm`,
`number_units`,
`number_floors`,
`area_sf_finished`,
`air_condition`,
`heat_type`,
`ada_yn`,
`amperage`,
`volts`,
`ceiling_height`,
`number_dockhigh_doors`,
`number_overhead_doors`,
`term`,
`security_deposit_amount`,
`possession`,
`style`,
`approximate_total_sf_res`,
`garage_type`,
`number_bedrooms`,
`number_bathrooms`,
`bathrooms_description`,
`dimensions_master_br`,
`dimensions_living_room`,
`dimensions_br2`,
`dimensions_dining_room`,
`dimensions_br3`,
`dimensions_family_room`,
`dimensions_br4`,
`dimensions_kitchen`,
`dimensions_office_den`,
`dimensions_laundry_utility`,
`dimensions_basement`,
`dimensions_fbasement`,
`irrigation_water`,
`barn`,
`corral`,
`possession1`,
`off_market_date`,
`associated_document_count`,
`addendum`,
`price_original`,
`directions`,
`how_sold`,
`contract_date`,
`closing_date`,
`common_area_maintenance`,
`common_area_maintenance_charges`,
`subdivision_name`,
`lease_type`,
`number_parking_spaces` FROM " . TEMP_TRAILERS_LEASE . ")";
//echo "lease -- ".$sql_insert_lease;
$result = run_query($sql_insert_lease);
}
#-------------End of insert_new_records()----------
/*
9/25/2008 1:55:02 PM Ross - Inserting a section to clean up bedroom and bathroom
fields since ags exports numbers as words in those fields
*/
// convert number words to digits
/*UPDATE trailers_residential SET number_bedrooms = '1' WHERE number_bedrooms = 'one';
UPDATE trailers_residential SET number_bedrooms = '2' WHERE number_bedrooms = 'two';
UPDATE trailers_residential SET number_bedrooms = '3' WHERE number_bedrooms = 'three';
UPDATE trailers_residential SET number_bedrooms = '4' WHERE number_bedrooms = 'four';
UPDATE trailers_residential SET number_bedrooms = '5' WHERE number_bedrooms = 'five';
UPDATE trailers_residential SET number_bedrooms = '6' WHERE number_bedrooms = 'six';
UPDATE trailers_residential SET number_bedrooms = '7' WHERE number_bedrooms = 'seven';
UPDATE trailers_residential SET number_bedrooms = '8' WHERE number_bedrooms = 'eight';
UPDATE trailers_residential SET number_bedrooms = '9' WHERE number_bedrooms LIKE '%nine%';
UPDATE trailers_residential SET number_bathrooms = '1' WHERE number_bathrooms = 'one';
UPDATE trailers_residential SET number_bathrooms = '2' WHERE number_bathrooms = 'two';
UPDATE trailers_residential SET number_bathrooms = '3' WHERE number_bathrooms = 'three';
UPDATE trailers_residential SET number_bathrooms = '4' WHERE number_bathrooms = 'four';
UPDATE trailers_residential SET number_bathrooms = '5' WHERE number_bathrooms = 'five';
UPDATE trailers_residential SET number_bathrooms = '6' WHERE number_bathrooms = 'six';
UPDATE trailers_residential SET number_bathrooms = '7' WHERE number_bathrooms = 'seven';
UPDATE trailers_residential SET number_bathrooms = '8' WHERE number_bathrooms = 'eight';
UPDATE trailers_residential SET number_bathrooms = '9' WHERE number_bathrooms LIKE '%nine%';
*/
/********************************************************************
| F-NCTION: insert_new_records() |
| Delete archive image directories which are older than |
| a certain period... |
*********************************************************************/
function cleanup_archive_images() {
$days_to_keep = IMAGE_ARCHIVE_TO_KEEP;
$archive_path = IMAGE_ARCHIVE_PATH;
$days_old = date('Ymd') - $days_to_keep;
if ($handle = opendir($archive_path)) {
while (false !== ($file = readdir($handle))) {
if ($file != "." && $file != "..") {
if(is_dir($archive_path.'/'.$file) && intval($file)>0 && intval($file) < $days_old) {
//echo "\n$file\n";
if ($sub_handle = opendir($archive_path.'/'.$file)) {
while (false !== ($image_file = readdir($sub_handle))) {
if ($image_file != "." && $image_file != "..") {
//echo "\t$image_file\n";
unlink($archive_path.'/'.$file.'/'.$image_file);
}
}
closedir($sub_handle);
rmdir($archive_path.'/'.$file.'/');
}
}
}
}
closedir($handle);
}
}
?>