Convert Region 25 (MIBOR / Indy) to new updater

[Battler45]

George,

The first project on which you'll be working will be to convert an existing MLS data feed to the new updater system.

Vihar will be able to provide you with the technical details for installing GitHub and downloading a local copy of the current code base.

Vihar, will you also please provide George with whatever server and / or database access he needs for this initial project? Please share with him any necessary logins and create any new users as necessary. Also, I think that converting region 25 will be a good place to start because it shares the same MLS data provider (Marketlinx) as the one you have already converted (SoCal MLS). You can find my existing code in:

C:\Inetpub\mls4.sierrainteractivemls.com\admin\mibor

on DEDQ61

The login credentials to the RETS server are:

• UserID: 31041rets

• Password: BENJ1001

• UserAgent: sierrainteract

• Password: h1ghplateau

RETS URL: http://inr.rets.interealty.com/Login.asmx/Login

As you'll see when you examine my existing code, I was using the sysid name for the database fields rather than the long names for the fields (which were being used for SoCal MLS). I did this because I had a lot of difficulty with the RETS Connector software that I was using to download the daily data files. To help translate between the two, I've attached a spreadsheet provided to me by MIBOR (which stands for Metropolitan Indianapolis Board of Realtors) that cross-references the long field names with their numeric IDs.

Please let me know if you have any questions.

Thanks.

Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Vihar,

I thought scripts under Updater\Sierra.Updater.Database folder are intended only for sierrainteractive-updater database. Isn't? And my script has to be run on sierrainteractive-n databases.

Also I attach script here for your testing while it is not committed.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

Vihar,

RE: #93 -- Yes, I agree about using a common function. Now that Vihang has updated all of the Pro Sites and the new Standard Sites to pull functions from a common set of files, this should be easy for us to implement.

We currently have one function, "FormatDetailLinkFromAddress", which is somewhat similar -- it formats the link to a property listing detail page based on the site's search tools version. We currently have this function in a few different places:

• C:\Inetpub\Sites\Shared\global\sicm\main_includes\functions.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\res\includes\functions.asp

And then also in the current and new updater scripts.

My thought is that we should consolidate this functions.asp script so that we have only one version of this file which is referenced across all sites. I had actually been talking with Vihang about this in our discussion on Ticket #75 / Comment 35. We could then include the new function you are proposing in this file.

For now, though, perhaps we will need to include the new function in both locations:

• C:\Inetpub\Sites\Shared\global\sicm\main_includes\functions.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\res\includes\functions.asp

And this will also need to happen on DEDN173, of course, in:

• C:\Inetpub-LIVE\Sites\Shared\global\sicm\main_includes\functions.asp

• C:\Inetpub-LIVE\Sites\MLS_Tools_Integrated\res\includes\functions.asp

• C:\Inetpub-DEV\Sites\Shared\global\sicm\main_includes\functions.asp

• C:\Inetpub-DEV\Sites\MLS_Tools_Integrated\res\includes\functions.asp

Once that's done, all of the following files will need to be updated to reference the new function:

• C:\Inetpub\Sites\MLS_Tools_Integrated\mls_detail.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\mls_detail_2.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\site_map_xml.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\zoom.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\res\includes\mls_body.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\res\includes\mls_detail_top.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\res\includes\search_application\get_listing.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\res\includes\search_applicaton\get_listings.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\all_photos.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\email_to_friend.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\email_to_friend_overlay.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\flyer.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\mls_mapmarkers.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\request_more_info.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\request_more_info_overlay.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\schedule_a_showing.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\sist_ajax\schedule_a_showing_overlay.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\carousel.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\listings.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\slideshow-1.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\slideshow-2.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\slideshow-4.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\slideshow-5.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\widgets\slideshow-6.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\xml_feeds\luxuryrealestate.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\xml_feeds\trulia.asp
• C:\Inetpub\Sites\MLS_Tools_Integrated\xml_feeds\zillow.asp
• C:\Inetpub\Sites\Shared\global\sicm\widget_saved_listings_body.asp

You can search in all of these files for the constant MLS_REGION_SOCAL to find where the reference to the new function will need to be included.

And then in both the old and new updaters. On DEDQ61, this is located in:

C:\Inetpub\mls4.sierrainteractivemls.com\admin\res\includes\mls_functions.asp in SendEmailAlerts function.

Also, the functions HandleMLSPhoto and HandleMLSPhotoWithPhotoPath will need to be updated to reference the new parameter for the stored procedure (unless a default value can be given to that field which would correspond to the old updater).

One issue is that Vihang has been working on updating the development version of the search tools for the Prototype-to-JQuery Conversion, and you have been updating the new search application for clustering, and so we will need to be careful in implementing this new function and pushing it out to the live sites that we don't break existing functionality on the live sites or overwrite any of the changes you have been working on.

Please let me know if you have any additional thoughts or questions.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

As you have time can you pls work on building a small application for migrating photos from old-updater to new-updater? As discussed above, the old-updater server's performance is not always good and so when a region is converted to the new updater system, photos of that region should be moved to the Crystaltech cloud server.

As far as photo processing is concerned, the main difference between two updater systems is the way photos are organized. As you already know, each photo is resized in three different sizes i.e. thumbnail , regular and large. These photos are organized under the mlsthumbs, mlspics and mlslarge folders respectively. The actual organization however varies for some regions. Some of the recently added regions support organization in sub-folders. As of now the following three regions support organization by sub-folders - 10 (SoCal), 24 (Denver) and 25 (MIBOR); for rest of the regions photos are organized under single folder.

To give you an idea about the folder organization on the existing photo serve:

Root-Photo-Folder

    3
      mlsthumbs
      mlspics
      mlslarge

    ...

    25_1
      mlsthumbs
      mlspics
      mlslarge

     ...

    25_100
      mlsthumbs
      mlspics
      mlslarge
...

This structure has been tweaked for new updater system as follows

Root-Photo-Folder

  10
    mlsthumbs
        AA
        BB

        ...

    mlspics
        AA
        BB

        ...

    mlslarge
        AA
        BB

        ...

Going forward all regions will need to conform to the new photo-organization approach (as implemented in the new updater system). This will be done on region-by-region basis as and when regions are converted to the new updater system.

So the goal of the new application would be to organize photos per the new approach. It will have to deal with two types of existing organization

• no-sub-folder - where all photos would be stored under the corresponding "size" folder
• region_[number] - where photos would be stored under sub-folder. The PhotoPath field of the MLSPhotos table stores the number of the folder under which the photos are stored.

For start, you can implement the application to process MIBOR photos and move them per new organization. It can later be refined to support all regions.

As and when photos are moved to new organization, we would have to set certain flag in the corresponding MLSPhotos record so that the front-end site builds correct photo-url. As discussed in the comment-#52, a new field PhotoServer would need to be added to the MLSPhotos table. When a photo is organized per new approach, the corresponding PhotoServer value would be set to 1 so that the front-end site builds appropriate url.

My thought is that the application can be implemented to perform the following steps

1 -- to read photo records from the MLSPhotos table for MIBOR region (25).

2 -- it will then copy the corresponding photos (thumbnail, regular and large) from the old folder-structure and move to new structure. Pls use the logic of generating sub-folder name from the PhotoDownloader code (GetHashedDirectoryName function).

3 -- if successful, the corresponding PhotoServer flag should be set to 1.

Since the goal of the application is to support all regions, a configurable parameter should be added to indicate the region to process. A couple of additional parameters can be used for setting source / destination folders. Also, it would be fine to create a new console application (similar to the updater console application) under the updater solution so that it can utilize the existing framework / source-code where possible.

Pls let me know if you have any queries / suggestions.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

Thank you. I able to connect to 208.106.250.174 now.

Thanks Posted by Molchanov George(unfuddle username: george)

[Battler45]

George,

We needed to change the data-types of Latitude / Longitude columns (Geocodes table) to numeric(12,9). This led to change in a couple of updater specific store-procedures, and, hence, change in the new updater system. I've made these changes, and, have pushed the same to the updater repository. Pls fetch the latest changes and update your local copy.

Pls note that you'll also need to make a minor change in the way latitude / longitude values are set in the MIBOR import. Until now these values were set as string, but, now they would need to be set as Double. Could you pls update MIBOR import code to set latitude / longitude values as double. You can use the ToDouble string extension I've used in SoCal importer. Pls let me know if you have any queries.

Pls note that I've applied these changes to the dev. sierrainteractive-1 DB. I've attached an SQL script that you can run on your local DB to apply data-type specific changes.

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

Vihar,

Yes, I agree that it makes sense to add a new field, photo-server, to be used in conjunction with the MLSPhotos table for situations such as this.

As far as the best way to copy images, I believe the fastest and easiest way would be to use an FTP program to copy the photos manually to a location on the new (cloud) server from which they could be processed by the new updater into their appropriate folder locations. I'm not able to set up an FTP account right now to connect to the appropriate folder on DEDQ61 to allow this file download due to operations currently taking place on that server, but I will do that tomorrow and forward you the FTP credentials.

If you or George could begin writing the application that will move / process these photos once they are downloaded, that would be great.

Thannks. Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

Vihar,

Can you explain to me how you are able to delete listings from SoCal? Since the same company provides the data feed service for both SoCal and MIBOR, hopefully we can use the same method. If we do not have access to a needed resource, let me know and I will contact the MIBOR representative and Marketlinx to see if we can get access as needed.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

[Battler45]

George,

Ben would be able to provide more details about exact mappings for the above mentioned fields.

Ben, could you pls check the mapping file attached in comment-17 and let us know the mappings for the remaining Features fields?

Thanks. Posted by Shah Vihar(unfuddle username: vshah)

[Battler45]

George,

Through Librets, there is a way to pull the system meta-data, which is an outline of the table structures of all tables -- kind of like the database schema. So, it gives you all of the available fields for the Residential property type, what data type each field is, the length, etc. And it does this for each table in the RETS database.

I'm not sure how you pull the meta-data using librets, but if you search the librets documentation for "metadata" or "meta-data" I am sure there is an easy way to do it.

This is similar to the spreadsheet I attached in the first email, but a bit different in that it is more complete and more up-to-date, and so it would probably be preferable to use this as opposed to the spreadsheet.

RE: #11 above:

1 - These are good questions. Each data feed is, unfortunately, different. In the case of the MIBOR feed, they only use one field for the full agent name ("Listing Agent Name"), rather than separate fields for the first and last name, so you will need to use string functions to parse the full name into a first and last name.

2 - Yes, it will usually be the case that not all available fields in the data feed will be mapped. This is the most difficult part of setting up a new data feed -- determining which fields should be mapped, and which should not. Often there will be multiple fields with similar names, and we will need to determine which one contains the data we are actually looking for.

There are certain fields which all feeds will contain (using different field names) and that we will always want to use, such as agent name, listing price, and MLS #. I will try to prepare a list of these to send over to you.

Then, there are fields available in some feeds but not others, such as different types of interior and exterior features, etc. The difficult part is determining which of these fields need to be included. I will help you with this now, and over time you will get a better sense of this.

3 - Some of our fields (mainly those beginning with "Feature" such as "FeaturesExterior") do contain composites of multiple fields from the data feed. There are no fixed rules here, and so while FeaturesExterior might combine 4 different data fields in SoCal MLS, it might combine 5 different fields in MIBOR. This needs to be worked out on a case-by-case basis, following common-sense guidelines which will become more clear as you gain experience working with these systems.

To help you start, I will try to prepare a list of which fields in the data feed should be mapped to which fields in our database and will send that over to you later today, along with a general explanation of the type of data each composite field should include.

Thanks. Posted by Peskoe Ben(unfuddle username: bpeskoe)