search

Battler45 / SierraMigrationToGitHub

Migration from unfuddle to GitHub
0 stars 0 forks source link

Convert Region 25 (MIBOR / Indy) to new updater #125

Open Battler45 opened 4 years ago

Battler45 commented 4 years ago

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 commented 4 years ago

George,

I've set you up so that you should be receiving updates on this task / thread -- please confirm. This thread contains the initial details you will need to get started on your first task, and Vihar can provide you with the background, technical details, etc. that you will need to get started.

Please let me know if you have any questions.

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

Battler45 commented 4 years ago

George,

In implementing new updater system for region-25, you might want to refer to the existing updater system implementation (especially for field mappings). I've attached a zip file containing the source-code for the same. It contains the following folders:

mibor - contains code specific to updater-system for region 25. The "mls_autoupdates.asp" file contains the entry-point; that is, the programming in this file will initiate the updates for region-25. It will call appropriate functions like UpdateListings, RemoveOldListings, UpdatePhotos etc... for performing various update steps. Some of these functions could be defined in the mls_functions.asp file present under this folder. And, rest of the functions would be defined in the common code.

res/includes - contains code common to all updater-systems. The mls_functions.asp file for example defines InsertUpdateMLSListing function, which is called to add/update listing records.

I'm not sure if you had a chance to work with libRETS library. If you haven't, I would recommend first creating a sample project that uses the libRETS library. Pls use the above RETS credentials for connecting to the MIBOR RETS server. Ideally, you should first download the metadata from this server. That would help you in mapping listing fields.

Pls note that the MIBOR region data is stored in the sierrainteractive-1 database. You can copy the latest back up of the database (development) from the following FTP location:

Host: 75.103.116.57 User-name: george_dev_db Password: GeorgeM$123

Since the development DB is has restricted access, pls restore the above back-up on your local DB and configure the updater code to work with your local DB.

Pls let me know if you have any queries.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

George,

Forgot to add - you can check the database schema by remoting to the following machine:

IP: 75.103.119.195

User Name: vshah

Password: ViharShah$123

Pls note that this is development DB, and, has almost identical schema to the live DB. There are total five DBs on this server: sierrainteractive-1 to 4, and sierrainteractive-updater. Pls note that currently sierrainteractive-3 is being used by the live sites so pls make sure that you don't change anything in that database. Ideally, you would only be needing to refer to the sierrainteractive-4 and sierrainteractive-updater DBs.

Pls let me know in case of any issues.

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

Battler45 commented 4 years ago

George,

To further clarify, the existing updater for mibor code will probably be confusing to you. It references the sys IDs of each database field, rather than the long field names, which is what you will be using when you query the RETS server using libRETS (I believe). You can use the T5 Field Info RETS Updated spreadsheet attached to the first comment in this thread to help you see which sys IDs match to which long field names.

In bringing any new data feed in line with the new updater system, the most difficult part will always be to determine how the fields from the host RETS database should be mapped into the fields of our own database.

In certain cases, such as when dealing with City, State, and Zip Code, these are straightforward.

However, in dealing with other types of listing data, such as interior and exterior features, lot features, etc., this can be more complicated. So, in working this out, referring to the existing SoCal MLS updater mappings (region 10) may also be quite helpful for you, because that region will (I believe) contain many of the same field names and will share many similar mappings.

Please let me know if you have any questions.

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

Battler45 commented 4 years ago

George,

Yes, it's definitely okay to connect to live servers for testing purposes. Since the data feed only comes in one direction (from them to us), it's fine to query during the testing process.

Thanks Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

Hi Vihar, Could you please explain more detailed how I can test the application? As I understand the specified RETS servers are live not "sandbox"? Is it ok to connect to servers for testing purposes? Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

George,

Yes, the specified RETS servers are live. I think it should be ok to connect to those servers for testing purpose.

I understand that you want to test the new updater system - the SoCal updater system to be specific. To do so, you can run the WindowsServiceTest console application. The UpdateProcessor.cs file in this project contains the entry-point function, PerformUpdatesForRegion. This function will execute appropriate updater steps (depending on the steps supported for a region - as defined in the Updater_MLSRegionUpdateSteps table). If you want to test a specific step, pls comment out appropriate code in the PerformUpdatesForRegion function.

Also, you would need to change certain configuration parameters defined in the app.config file. Some of these parameters are:

Pls let me know if you need additional info.

Thanks.

Posted by Shah Vihar(unfuddle username: vshah)

Battler45 commented 4 years ago

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 commented 4 years ago

Status of task: I'm able to get response from RETS server and going to parse it.

Ben, I have question about query for request: which date should be used in query to select only outstanding records? For example table "RES" has several date fields - "List Date", "Entry Date" and "Change Date". Do you know which is correct? Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

George,

I see -- to give you a bit of background, there are a number of different data providers around the United States. These are companies that build and maintain the software for individual boards of realtors. The Socal MLS and the Indianapolis MLS (MIBOR) both use a company called MarketLinx.

This means that, although some of the fields and field names may vary between SoCal MLS and MIBOR MLS, most of them will be the same, and the way you query the database for MIBOR will be very similar to the way the database is queried for the existing SoCal MLS updater, and so you should feel free to look at SoCal MLS updater as a reference for MIBOR. (Other boards of realty use different data providers, and so this will not always be the case.)

That said, I am not sure of the answer to your question, but I believe Vihar should be able to answer this for you, since I am sure it is the same in MIBOR as it is in SoCal MLS. (My guess is that "Change Date" is the correct answer, but I am not certain.)

Ben Posted by Peskoe Ben(unfuddle username: bpeskoe)

Battler45 commented 4 years ago

George,

I agree - it should be "Change Date". If we filter records by that date, the server should return records that have been changed since the specified date. Also, can you pls share the MIBOR meta-data? I'll use it as reference for calrifying / confirming other this and mappings.

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

Battler45 commented 4 years ago

Ben, Vihar I have some questions regarding common rules of fields' mapping. 1) What should I do if I can not find an appropriate source field for some field of object Property (for example SoCal model has data for ListAgentLastName - p.AgentLastName = resultSet.GetString("ListAgentLastName"), - but I can not find such field in MIBOR's fields). 2) On the other hand there are a lot of MIBOR fields which are not mapped with object Property. 3) Special case is the composite fields (like FeaturesExterior). Such fields in SoCal implementation have fixed headers for specific source fields (for example in composite field FeaturesExterior can be added field PatioFeatures with header Patio). Are these headers fixed or for MIBOR field FeaturesExterior can contain other data with other headers? And if yes - do you have some ideas which MIBOR fields can be included in composite fields of object Property? Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

Vihar, What do you mean by MIBOR meta-data? I'm using T5_field_info_RETS__Updated01-17-09.xls document attached in first post. Or you want to see how I perform this mapping (so I can give my code of "Parse.." function). Thanks Posted by Molchanov George(unfuddle username: george)

Battler45 commented 4 years ago

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 commented 4 years ago

George,

You can find the example for working with RETS meta-data here: http://www.crt.realtors.org/projects/rets/librets/documentation/devguide/. The 3rd point, "Working with metadata" provides some examples of downloading RETS server metadata. This metadata can then be used as reference for mapping MIBOR fields to our database fields.

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

Battler45 commented 4 years ago

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)