function insert_update_listing($value) { global $useradmin, $rets, $current_YVR_time_string; if (!empty($value)) { $selectSQL = "SELECT * FROM listings WHERE sysid=" . $value['sysid']; $get_listing = mysql_query_or_die($selectSQL, $useradmin); if ($row_get_listing = mysql_fetch_assoc($get_listing)) { echo "<span style=\"color:#336633\">Record found in database</span>\n"; $updateSQL = sprintf("UPDATE listings SET property_type=%s, address=%s, postal_code=%s, unit_number=%s, area_desc=%s, area=%s, sub_area_desc=%s, sub_area=%s, city=%s, province=%s, built_year=%s, house_number=%s, list_price=%s, gross_taxes=%s, for_tax_year=%s, list_date=%s, mls_number=%s, bedrooms=%s, bathrooms=%s, public_remarks=%s, public_remarks_2=%s, lot_size_sqt=%s, lot_size_sqm=%s, floor_area_total=%s, site_influences=%s, type_of_dwelling=%s, title_to_land=%s, publish_on_internet=%s, status=%s, last_trans_date=%s, date_updated_local=%s WHERE sysid=" . $value['sysid'], GetSQLValueString($value['1'], "text"), GetSQLValueString($value['14'], "text"), GetSQLValueString($value['11'], "text"), GetSQLValueString($value['2971'], "int"), GetSQLValueString($value['2233'], "text"), GetSQLValueString($value['2283'], "text"), GetSQLValueString($value['2570'], "text"), GetSQLValueString($value['2568'], "text"), GetSQLValueString($value['3794'], "text"), GetSQLValueString($value['88'], "text"), GetSQLValueString($value['16'], "int"), GetSQLValueString($value['181'], "int"), GetSQLValueString($value['226'], "double"), GetSQLValueString($value['2673'], "double"), GetSQLValueString($value['2651'], "int"), GetSQLValueString($value['224'], "date"), GetSQLValueString($value['248'], "text"), GetSQLValueString($value['378'], "int"), GetSQLValueString($value['3928'], "int"), GetSQLValueString($value['411'], "text"), GetSQLValueString($value['3985'], "text"), GetSQLValueString($value['2457'], "double"), GetSQLValueString($value['2460'], "double"), GetSQLValueString($value['3922'], "double"), GetSQLValueString($value['3926'], "text"), GetSQLValueString($value['2733'], "text"), GetSQLValueString($value['2737'], "text"), GetSQLValueString($value['3'] == 'Yes' ? 'Y' : 'N', "text"), GetSQLValueString($value['363'] == 'Active' ? 'A' : 'D', "text"), GetSQLValueString($value['217'], "date"), GetSQLValueString($current_YVR_time_string, "date"), GetSQLValueString($value['sysid'], "int")); $result = mysql_query_or_die($updateSQL, $useradmin); $album_id = get_album_id($value['sysid']); if (!$album_id) { $album_id = create_album($value['sysid']); } check_insert_firm($value['222'], $value['2679'], $value['2681'], $value['2675'], $value['2685']); check_insert_firm($value['2689'], $value['2325'], $value['2683'], $value['2677'], $value['2687']); } else { //$response=$rets->GetPhoto('Property',$value['sysid'].':*', 'images'); //$photo_array=$response['photo_array']; $address = $value['14'] . ' ' . $value['3794'] . ' ' . $value['88']; if (!get_geocode($address, $value['sysid'])) { echo '<br/><span style="color:#ff0000;">[Error] No geo code found! Address:' . urlencode($address) . ' sysid: ' . $value['sysid'] . '</span><br/>'; } echo "<span style=\"color:#336633;\">Creating new record</span>\n"; $insertSQL = sprintf("INSERT INTO listings (sysid, property_type, address, postal_code, unit_number, area_desc, area, sub_area_desc, sub_area, city, province, built_year, house_number, list_price, gross_taxes, for_tax_year, list_date, mls_number, bedrooms, bathrooms, public_remarks, public_remarks_2, lot_size_sqt, lot_size_sqm, floor_area_total, site_influences, type_of_dwelling, title_to_land, publish_on_internet,status, date_imported_local, date_updated_local, last_trans_date) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", GetSQLValueString($value['sysid'], "int"), GetSQLValueString($value['1'], "text"), GetSQLValueString($value['14'], "text"), GetSQLValueString($value['11'], "text"), GetSQLValueString($value['2971'], "int"), GetSQLValueString($value['2233'], "text"), GetSQLValueString($value['2283'], "text"), GetSQLValueString($value['2570'], "text"), GetSQLValueString($value['2568'], "text"), GetSQLValueString($value['3794'], "text"), GetSQLValueString($value['88'], "text"), GetSQLValueString($value['16'], "int"), GetSQLValueString($value['181'], "int"), GetSQLValueString($value['226'], "double"), GetSQLValueString($value['2673'], "double"), GetSQLValueString($value['2651'], "int"), GetSQLValueString($value['224'], "date"), GetSQLValueString($value['248'], "text"), GetSQLValueString($value['378'], "int"), GetSQLValueString($value['3928'], "int"), GetSQLValueString($value['411'], "text"), GetSQLValueString($value['3985'], "text"), GetSQLValueString($value['2457'], "double"), GetSQLValueString($value['2460'], "double"), GetSQLValueString($value['3922'], "double"), GetSQLValueString($value['3926'], "text"), GetSQLValueString($value['2733'], "text"), GetSQLValueString($value['2737'], "text"), GetSQLValueString($value['3'] == 'Yes' ? 'Y' : 'N', "text"), GetSQLValueString($value['363'] == 'Active' ? 'A' : 'D', "text"), GetSQLValueString($current_YVR_time_string, "date"), GetSQLValueString($current_YVR_time_string, "date"), GetSQLValueString($value['217'], "date")); $result = mysql_query_or_die($insertSQL, $useradmin); //$album_id=create_album($value['sysid']); /*for($i=0;$i<count($photo_array);$i++){ $insertPhotoSQL = sprintf("INSERT INTO photo_profile(photo_path)VALUES(%s)", GetSQLValueString($photo_array[$i],"text")); $result=mysql_query_or_die($insertPhotoSQL,$useradmin); $photo_id=mysql_insert_id($useradmin); $insertAlbumSQL = sprintf("INSERT INTO album_profile(album_id, photo_id, cover)VALUES(%s,%s,%s)", GetSQLValueString($album_id,"int"), GetSQLValueString($photo_id,"int"), GetSQLValueString($i==0?'Y':'N',"text")); $result=mysql_query_or_die($insertAlbumSQL,$useradmin); }*/ //insert record to listing_realtors $insertSQL = sprintf("INSERT INTO listing_realtors(sysid, list_realtor_1_id, list_realtor_2_id, list_realtor_3_id) VALUES(%s,%s,%s,%s)", GetSQLValueString($value['sysid'], "int"), GetSQLValueString($value['342'], "text"), GetSQLValueString($value['2697'], "text"), GetSQLValueString($value['2699'], "text")); //list_realtor_3_id $result = mysql_query_or_die($insertSQL, $useradmin); check_insert_realtor($value['342'], $value['2703'], $value['2711'], $value['2685']); check_insert_realtor($value['2697'], $value['2327'], $value['2713'], $value['2707']); check_insert_realtor($value['2699'], $value['2329'], $value['2717'], $value['2701']); //insert record to listing_firms table $insertSQL = sprintf("INSERT INTO listing_firms (sysid, list_firm_1_code, list_firm_1_name, list_firm_1_phone, list_firm_1_fax, list_firm_1_url, list_firm_2_code, list_firm_2_name, list_firm_2_phone, list_firm_2_fax, list_firm_2_url) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", GetSQLValueString($value['sysid'], "int"), GetSQLValueString($value['222'], "text"), GetSQLValueString($value['2679'], "text"), GetSQLValueString(str_replace('-', '', $value['2681']), "text"), GetSQLValueString(str_replace('-', '', $value['2675']), "text"), GetSQLValueString($value['2685'], "text"), GetSQLValueString($value['2689'], "text"), GetSQLValueString($value['2325'], "text"), GetSQLValueString(str_replace('-', '', $value['2683']), "text"), GetSQLValueString(str_replace('-', '', $value['2677']), "text"), GetSQLValueString($value['2687'], "text")); $result = mysql_query_or_die($insertSQL, $useradmin); check_insert_firm($value['222'], $value['2679'], $value['2681'], $value['2675'], $value['2685']); check_insert_firm($value['2689'], $value['2325'], $value['2683'], $value['2677'], $value['2687']); } return true; } else { return false; } }
foreach ($realtor_array as $realtor_id) { $realtor_profile = get_realtor_profile($realtor_id); array_push($realtor_profile_array, $realtor_profile); } foreach ($firm_array as $firm_code) { $firm_profile = get_firm_profile($firm_code); array_push($firm_profile_array, $firm_profile); } //var_dump($realtor_profile_array); //var_dump($firm_profile_array); } else { echo "Error: The property you are looking for is no longer active"; exit; } $address = $propertyObject['address'] . ' ' . $propertyObject['city'] . ' ' . $propertyObject['province']; $geo_address = get_geocode($address, $sysid); } function get_album($sysid) { global $useradmin; $selectAlbumSQL = "SELECT * FROM listing_album" . " LEFT JOIN album_profile ON listing_album.album_id=album_profile.album_id" . " LEFT JOIN photo_profile ON photo_profile.photo_id=album_profile.photo_id" . " WHERE listing_album.sysid=" . $sysid; $get_album = mysql_query_or_die($selectAlbumSQL, $useradmin); $photo_array = array(); while ($row_get_album = mysql_fetch_assoc($get_album)) { array_push($photo_array, $row_get_album['photo_path']); } //var_dump($photo_array); return $photo_array; } function get_geocode($address, $sysid) {
foreach ($listings_array as $key => $value) { $selectSQL = "SELECT * FROM listings WHERE sysid=" . $value['sysid']; $get_listing = mysql_query_or_die($selectSQL, $useradmin); if ($row_get_listing = mysql_fetch_assoc($get_listing)) { echo "Record found in database\n"; $updateSQL = sprintf("UPDATE listings SET property_type=%s, address=%s, postal_code=%s, unit_number=%s, city=%s, province=%s, built_year=%s, house_number=%s, list_price=%s, list_date=%s, mls_number=%s, bedrooms=%s, bathrooms=%s, public_remarks=%s, public_remarks_2=%s, lot_size_sqt=%s, lot_size_sqm=%s, floor_area_total=%s, site_influences=%s, type_of_dwelling=%s,publish_on_internet=%s, status=%s WHERE sysid=" . $value['sysid'], GetSQLValueString($value['1'], "text"), GetSQLValueString($value['14'], "text"), GetSQLValueString($value['11'], "text"), GetSQLValueString($value['2971'], "int"), GetSQLValueString($value['3794'], "text"), GetSQLValueString($value['88'], "text"), GetSQLValueString($value['16'], "int"), GetSQLValueString($value['181'], "int"), GetSQLValueString($value['226'], "double"), GetSQLValueString($value['224'], "date"), GetSQLValueString($value['248'], "text"), GetSQLValueString($value['378'], "int"), GetSQLValueString($value['3928'], "int"), GetSQLValueString($value['411'], "text"), GetSQLValueString($value['3985'], "text"), GetSQLValueString($value['2457'], "double"), GetSQLValueString($value['2460'], "double"), GetSQLValueString($value['3922'], "double"), GetSQLValueString($value['3926'], "text"), GetSQLValueString($value['2733'], "text"), GetSQLValueString($value['3'] == 'Yes' ? 'Y' : 'N', "text"), GetSQLValueString($value['363'] == 'Active' ? 'A' : 'D', "text"), GetSQLValueString($value['sysid'], "int")); $result = mysql_query_or_die($updateSQL, $useradmin); $album_id = get_album_id($value['sysid']); if (!$album_id) { $album_id = create_album($value['sysid']); } } else { $response = $rets->GetPhoto('Property', $value['sysid'] . ':*', 'images'); $photo_array = $response['photo_array']; $address = $value['14'] . ' ' . $value['3794'] . ' ' . $value['88']; get_geocode($address, $value['sysid']); echo "Creating new record\n"; $insertSQL = sprintf("INSERT INTO listings (sysid, property_type, address, postal_code, unit_number, city, province, built_year, house_number, list_price, list_date, mls_number, bedrooms, bathrooms, public_remarks, public_remarks_2, lot_size_sqt, lot_size_sqm, floor_area_total, site_influences, type_of_dwelling,publish_on_internet,status) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)", GetSQLValueString($value['sysid'], "int"), GetSQLValueString($value['1'], "text"), GetSQLValueString($value['14'], "text"), GetSQLValueString($value['11'], "text"), GetSQLValueString($value['2971'], "int"), GetSQLValueString($value['3794'], "text"), GetSQLValueString($value['88'], "text"), GetSQLValueString($value['16'], "int"), GetSQLValueString($value['181'], "int"), GetSQLValueString($value['226'], "double"), GetSQLValueString($value['224'], "date"), GetSQLValueString($value['248'], "text"), GetSQLValueString($value['378'], "int"), GetSQLValueString($value['3928'], "int"), GetSQLValueString($value['411'], "text"), GetSQLValueString($value['3985'], "text"), GetSQLValueString($value['2457'], "double"), GetSQLValueString($value['2460'], "double"), GetSQLValueString($value['3922'], "double"), GetSQLValueString($value['3926'], "text"), GetSQLValueString($value['2733'], "text"), GetSQLValueString($value['3'] == 'Yes' ? 'Y' : 'N', "text"), GetSQLValueString($value['363'] == 'Active' ? 'A' : 'D', "text")); $result = mysql_query_or_die($insertSQL, $useradmin); $album_id = create_album($value['sysid']); for ($i = 0; $i < count($photo_array); $i++) { $insertPhotoSQL = sprintf("INSERT INTO photo_profile(photo_path)VALUES(%s)", GetSQLValueString($photo_array[$i], "text")); $result = mysql_query_or_die($insertPhotoSQL, $useradmin); $photo_id = mysql_insert_id($useradmin); $insertAlbumSQL = sprintf("INSERT INTO album_profile(album_id, photo_id, cover)VALUES(%s,%s,%s)", GetSQLValueString($album_id, "int"), GetSQLValueString($photo_id, "int"), GetSQLValueString($i == 0 ? 'Y' : 'N', "text")); $result = mysql_query_or_die($insertAlbumSQL, $useradmin); } //insert record to listing_realtors $insertSQL = sprintf("INSERT INTO listing_realtors(sysid, list_realtor_1_id, list_realtor_2_id, list_realtor_3_id) VALUES(%s,%s,%s,%s)", GetSQLValueString($value['sysid'], "int"), GetSQLValueString($value['342'], "text"), GetSQLValueString($value['2697'], "text"), GetSQLValueString($value['2699'], "text")); //list_realtor_3_id $result = mysql_query_or_die($insertSQL, $useradmin);
$get_sysid = mysql_query_or_die($selectSQL, $useradmin); $row = mysql_fetch_row(mysql_query("SELECT FOUND_ROWS()", $useradmin)); if ($row[0] > 0) { echo "<br/>There are " . $row[0] . " listings that don't have geo code address (or don't have accurate geo code).<br/><br/>"; //login and receive server response. //$response=$rets->Login(); //var_dump($response); while ($row_get_sysid = mysql_fetch_assoc($get_sysid)) { $temp_sysid = $row_get_sysid['sysid']; //$temp_address = $row_get_sysid['address'].' '.$row_get_sysid['city'].' '.$row_get_sysid['province']; $temp_address = $row_get_sysid['house_number'] . ' ' . $row_get_sysid['street_name'] . ' ' . $row_get_sysid['street_type'] . ' ' . $row_get_sysid['city'] . ' ' . $row_get_sysid['province']; $temp_postal = $row_get_sysid['postal_code']; echo '<br/>Sysid to insert geocode:' . $temp_sysid . '<br/>'; echo '<br/>Address of the sysid:' . $temp_address . '<br/>'; echo '<br/>Postal code:' . $temp_postal . '<br/>'; $result = get_geocode($temp_address, $temp_postal, $temp_sysid); echo '<br/><span style="color:#ff00ff;">' . $result . '</span><br/>'; } } else { echo "<br/>All listings have their geo code address(except those who have errors).<br/>"; } #=====================functions=======================# function get_data_array($property_type, $sysid) { global $rets; $temp_array = $rets->GetDataArray('Property', $property_type, '(sysid=' . $sysid . ')', null, null); if (!empty($temp_array)) { return $temp_array; } else { echo '<br/><span style="color:#ff0000;">[Warning]No listing array found</span><br/>'; return false;